PHP from database and query

Question

I have a table:

id, affiliate

Each time somebody clicks a link, a new row is inserted,

ID being the ID of the page, and affiliate being the ID of the affiliate.

For example:

Page ID: 9 Affiliate ID: 1

Page ID: 9 Affiliate ID: 2

Page ID: 9 Affiliate ID: 3

I only have 3 affiliates.

I want to select this information, and group them by affiliate, for the ID.

I have tried this query:

SELECT COUNT(*) FROM table WHERE id = '9' GROUP BY affiliate

It works fine when I do it in php my admin, how do I get the info in PHP?

I have tried:

$q = mysql_query("SELECT COUNT(*) FROM table WHERE id = '" . $id . "' GROUP BY affiliate");

$r = mysql_fetch_array($q);

When trying to print the data onto the page, I am only getting one result.

Do I need to use a foreach/while loop to get all 3? How would I go about doing this?

Thank you!

You ask her for a foreach/while solution but don't want a loop according to your comments? I'm confused... — DrColossos
– DrColossos, Commented Jan 9, 2011 at 13:01

Kel · Accepted Answer · 2011-01-09 11:53:31Z

3

You should do mysql_fetch_array() (or mysql_fetch_assoc()) in a loop:

while ($row = mysql_fetch_assoc($q)) {
    echo $row["id"];
    echo $row["affiliate"];
}

UPDATE (in accordance with comment):

If you ALWAYS have 3 rows in your result, probably mysql_result() function would be helpful:

$firstAffiliate = mysql_result($q, 0, "affiliate");
$secondAffiliate = mysql_result($q, 1, "affiliate");
$thirdAffiliate = mysql_result($q, 2, "affiliate");

BTW, be careful and check, whether query actually returns 3 results.

edited Jan 9, 2011 at 11:53

answered Jan 9, 2011 at 11:40

Kel

7,8103 gold badges32 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Latox Over a year ago

I don't want to loop inside my content though, I want to basically define 3 variables which I can call further down the page, without having to place my content into a while loop.

Kel Over a year ago

Updated answer for case, when you have 3 rows only

Kim Over a year ago

@Kyle: A loop IS needed as the query returns 3 rows. Arguing against it is foolish AND bad practice to use mysql_result().

Oli · Accepted Answer · 2011-01-09 11:39:58Z

2

Loop like this:

while($row = mysql_fetch_array($q)) {
        print_r($row);
}

answered Jan 9, 2011 at 11:39

Oli

2,5043 gold badges29 silver badges48 bronze badges

1 Comment

Latox Over a year ago

I don't want to loop inside my content though, I want to basically define 3 variables which I can call further down the page, without having to place my content into a while loop.

ajreal · Accepted Answer · 2011-01-09 13:59:33Z

0

If you just want to get list of Affiliate ID, you can consider this query

select group_concat(affiliate)
from table 
where id=9;

The above will return a comma separate value like 1,2,3 in single row

So, you can then in PHP explode using ,, and the size of the exploded array is the same as count(*)

answered Jan 9, 2011 at 13:59

ajreal

47.4k11 gold badges97 silver badges119 bronze badges

Comments

Lloyd Moore · Accepted Answer · 2011-01-09 17:19:18Z

0

you can use a variable to hold state inside the actual mysql statement itself. Try the following:

affiliates: page_id afil_id

set @page_id=0; select page_id, if(@page_id <> page_id, count(*), 0), @page_id:=page_id from clicks group by page_id;

Hope that helps

answered Jan 9, 2011 at 17:19

Lloyd Moore

3,2081 gold badge35 silver badges32 bronze badges

Collectives™ on Stack Overflow

PHP from database and query

4 Answers 4

3 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related