vb.org Archive - Query Help

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - Query Help (https://vborg.vbsupport.ru/showthread.php?t=58484)

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.

<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

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.

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

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!";

}

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.

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.

Quote:

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(*).

Quote:

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;):)

X vBulletin 3.8.12 by vBS Debug Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (4)bbcode_php_printable (2)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (9)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages:

Phrase Groups Available:

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01142 seconds Memory Usage 1,744KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (4)bbcode_php_printable (2)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (9)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: