SELECT COUNT

[WatsonN]

I'm trying to figure out how to implement SELECT COUNT into a php generated list.

My script lists all the users in one table

$data = mysql_query("SELECT * FROM YBK_Login") or die(mysql_error()); 
while($info = mysql_fetch_array( $data )) { 
Print "<tr>"; 
Print "<td>".$info['UID'] . " </td>";
Print "<td>"."#"."</td>";
Print "<td>".$info['ID'] . "</td> "; 
Print "<td>".$info['Allowed'] . "</td> ";
Print "<td>".$info['pass'] . " </td>";
Print "<td>".$info['HR'] . " </td>"; } 

?>

 

And I want to use the count to count the number of entries each user has in a diffrent table.

Like pull the $info['ID'] and check against posts in YBK_Ads with the column name UID

[joel24]

A subquery should work?

 

$data = mysql_query("SELECT a.*, (SELECT COUNT(posts) FROM YBK_Ads a WHERE a.UID = y.UID) AS postCount FROM YBK_Login y") or die(mysql_error()); 
while($info = mysql_fetch_array( $data )) { 



Print "<tr>"; 

Print "<td>".$info['postCount'] . " </td>";

Print "<td>".$info['UID'] . " </td>";



Print "<td>"."#"."</td>";



Print "<td>".$info['ID'] . "</td> "; 



Print "<td>".$info['Allowed'] . "</td> ";



Print "<td>".$info['pass'] . " </td>";



Print "<td>".$info['HR'] . " </td>"; } 

?>

[eran]

Left join the ads table

SELECT y.*,COUNT(ads.UID) AS posts FROM YBK_Login AS y
LEFT JOIN YBK_Ads AS ads ON ads.UID=y.ID
GROUP BY y.ID

[WatsonN]

@joel24 I got unknown table a

@eran it gives be a blank value  it would be $info['postCount'] right?

[joel24]

sorry, mixed up a with y.

 

$data = mysql_query("SELECT y.*, (SELECT COUNT(posts) FROM YBK_Ads a WHERE a.UID = y.UID) AS postCount FROM YBK_Login y") or die(mysql_error()); while($info = mysql_fetch_array( $data )) { Print "<tr>"; Print "<td>".$info['postCount'] . " </td>";Print "<td>".$info['UID'] . " </td>";Print "<td>"."#"."</td>";Print "<td>".$info['ID'] . "</td> "; Print "<td>".$info['Allowed'] . "</td> ";Print "<td>".$info['pass'] . " </td>";Print "<td>".$info['HR'] . " </td>"; } ?>

 

[eran]

@joel24 I got unknown table a

@eran it gives be a blank value  it would be $info['postCount'] right?

No, it should be $info['posts']. var_dump($info) to see what it contains

[WatsonN]

@eran works perfectly Thanks both of you

$data = mysql_query("SELECT y.*,COUNT(ads.UID) AS posts FROM YBK_Login AS y LEFT JOIN YBK_Ads AS ads ON ads.UID=y.ID GROUP BY y.ID") or die(mysql_error()); 
while($info = mysql_fetch_array( $data )) {  
Print "<tr>"; 
Print "<td>".$info['UID'] . " </td>";
Print "<td>".$info['posts'] . " </td>";
Print "<td>".$info['ID'] . "</td> "; 
Print "<td>".$info['Allowed'] . "</td> ";
Print "<td>".$info['pass'] . " </td>";
Print "<td>".$info['HR'] . " </td>"; }