Query Help

[reefland]

Ok, I am not completely literate when it comes to PHP but...



What I am trying to do is modify a free link system. I have everything working but am trying to add a couple of queries to pull some more info; mainly right now the total number of links per category.



First there is a link table and a category table and I am guessing that I need to work something out together. Both tables have a cat ID in them and I need to get them working together to pull the results but don't know how.



If I do this, I get the total number of categories:

$countcats="SELECT COUNT(id) FROM categories WHERE visible='1'";

$countcatsresult=mysql_query($countcats);

$countcatsrow=mysql_fetch_row($countcatsresult);



If I do this I can get the total number of links:

$countquery="SELECT COUNT(id) FROM links WHERE visible='1'";

$countresult=mysql_query($countquery);

$countrow=mysql_fetch_row($countresult);



Now I need to take each of the cateogry ID's and run a query on the link table to count the total links per cat but I can't see to get it together.



Any ideas or help would be greatly appreciated!

Thanks for helping the PHP illiterate,

Scott Z.

[assassingod]

<font size="1">Just to let you know, its not a good idea to use a WHERE clause when using COUNT(col)</font>

Why dont you add a catagoryid column in the links table and then grab the info from that? I say that because you have 2 columns of the same name (id) and I don't think they'll work together

[reefland]

Hi assassingod,

There is a catid column in the links table but how would I go about getting the total number of links for each ID that is stored?

Thanks,
Scott Z.
Scott Z.

[assassingod]

I got it to work using something like:

<?php
$foo = mysql_connect("localhost","root","")
or die("Could not connec to DB");

$foo = mysql_select_db("links",$foo)
or die("Could not select DB");

$sql = "SELECT * FROM links";
$foo = mysql_query($sql)
or die("Could not run query");

while ($foobar = mysql_fetch_array($foo))
{
echo("$foobar[links] links for catid $foobar[catid]<br />");
}
?>


I think thats what you mean, using a while-loop works:)

[Lesane]

Try this:


$get_info = mysql_query("
select A.catid
, count(AR.id) as tot
from categories A
left outer
join links AR
on A.catid = AR.catid
where A.visible='1'
AND AR.visible='1'
group
by A.catid
") or die("Unable to complete query: '".mysql_error()." ");
if(mysql_num_rows($get_info)) {
while ($info= mysql_fetch_array($get_info)) {

echo '<br> '.$info[catid] .' has '.$info[tot].' links! ';
}
} else {
echo "No records found!";
}

[reefland]

Alrighty, got that one working. Now I was wondering if you could help me with another. :)

The table has cat_id and timestamp in it. I want to pull the row with the most recent timestamp for each cat_id. Any ideas? :)

Thanks,
Scott Z.

[reefland]

Never mind, I actually figured this one out on my own. PHP/MYSQL, it's so fun! :)

Thanks again for all your help,
Scott Z.

[filburt1]

Just to let you know, its not a good idea to use a WHERE clause when using COUNT(col)
Why? It's only brutally inefficient if you used COUNT(*).

[assassingod]

Why? It's only brutally inefficient if you used COUNT(*).
I read somewhere that it would mess up the query. I must be wrong;):)