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:

PHP Code:

$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:

PHP Code:

$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 Code:

<?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:

PHP Code:

$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]

Originally Posted by assassingod

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]

Originally Posted by filburt1

Why? It's only brutally inefficient if you used COUNT(*).

I read somewhere that it would mess up the query. I must be wrong