another sql question..
i am trying to learn how to get data form more than 1 table at once. below is some code i am trying to get to work, but all the variables are 0.
<?php
$sql=$Db1->query(\"SELECT
COUNT(tutorials.id) AS tutorials,
COUNT(resources.id) AS resources,
COUNT(downloads.id) AS downloads,
COUNT(user.userid) AS refers,
COUNT(articles.id) AS articles,
COUNT(tutorial_com.id) AS tut_com,
COUNT(article_com.id) AS article_com,
COUNT(download_com.id) AS download_com,
COUNT(resource_com.id) AS resource_com,
COUNT(forum_posts.id) AS forum_threads
FROM
tutorials,
resources,
downloads,
user,
articles,
tutorial_com,
article_com,
download_com,
resource_com,
forum_posts
WHERE
tutorials.subm='$id' AND
resources.subm='$id' AND
downloads.subm='$id' AND
user.referer='$id' AND
articles.subm='$id' AND
tutorial_com.subm='$id' AND
article_com.subm='$id' AND
download_com.subm='$id' AND
resource_com.subm='$id' AND
forum_posts.subm='$id'
\");
$Db1->next_record();
$points['tutorials']=$Db1->Record['tutorials'];
$points['resources']=$Db1->Record['resources'];
$points['downloads']=$Db1->Record['downloads'];
$points['refers']=$Db1->Record['refers'];
$points['articles']=$Db1->Record['articles'];
$points['tut_com']=$Db1->Record['tut_com'];
$points['article_com']=$Db1->Record['article_com'];
$points['download_com']=$Db1->Record['download_com'];
$points['resource_com']=$Db1->Record['resource_com'];
$points['forum_threads']=$Db1->Record['forum_threads'];
$points['used']=$Db1->Record['used'];
?>http://www.newbie-developer.com - Newbie web-developer community.
zollet posted this at 07:01 — 26th June 2002.
He has: 1,016 posts
Joined: May 2002
Here's a simple example of how to get data from two different tables in one database..
<?php
//Let's say we have 2 tables (\"users\" and \"messages\") and we want to select user information along with the message subjects and post times.
$sql=$Db1->query(\"SELECT u.username, u.posts, m.subject, m.posttime FROM users u, messages m WHERE u.id LIKE m.userid AND u.username LIKE 'JohnDoe'\");
?>
I hope this helps. Read the page about table joins on DevShed.com carefully and try simple examles until you get a hang of it. Don't try to the most complicating query right away.
korndragon posted this at 07:09 — 26th June 2002.
They have: 87 posts
Joined: Dec 2001
i have already seen a dozen exampled like that while looking through vbulletin code. I need to be able to count the number of occurances in each table, as in the code i posted.
as for the devshed.com article.. i have been reading that over and over again for about a half an hour, and the only thing im getting from it is a headache....
maybe its just because im tired. but can you please tell me whats wrong with the code above, or how else i could get all that information out of 1 query?
thank you
korndragon
http://www.newbie-developer.com - Newbie web-developer community.
zollet posted this at 07:44 — 26th June 2002.
He has: 1,016 posts
Joined: May 2002
The best way to get the row counts (and other info) of all tables in a database is..
<?php$sql=$Db1->query(\"SHOW TABLE STATUS FROM databasename\");
?>
Peter J. Boettcher posted this at 13:10 — 26th June 2002.
They have: 812 posts
Joined: Feb 2000
SQL only allows for one top level COUNT statement. If you want more than one then you have to use sub-queries, for example:
select
CountTutorials=(select count(*) from tutorials),
CountResources=(select count(*) from resources)
And so on, you can add WHERE clauses to your sub-queries.
PJ | Are we there yet?
pjboettcher.com
zollet posted this at 14:11 — 26th June 2002.
He has: 1,016 posts
Joined: May 2002
Peter,
I didn't know MySQL supportet sub-queries?
Peter J. Boettcher posted this at 14:18 — 26th June 2002.
They have: 812 posts
Joined: Feb 2000
Maybe it doesn't, I was just refering to the Transact-SQL standard. OOps...
Mark Hensler posted this at 16:03 — 26th June 2002.
He has: 4,048 posts
Joined: Aug 2000
Mark Hensler
If there is no answer on Google, then there is no question.
zollet posted this at 16:19 — 26th June 2002.
He has: 1,016 posts
Joined: May 2002
Interesting. Have you guys tried the "SHOW TABLE STATUS FROM databasename"? It is very useful for getting rowcount and such from all tables in a database.
Peter J. Boettcher posted this at 17:26 — 26th June 2002.
They have: 812 posts
Joined: Feb 2000
I don't have a lot of experience with mySQL, but I'm guessing that commands returns other info too. Does it return the values as separate fields/rows or one large string that has to be parsed?
PJ | Are we there yet?
pjboettcher.com
zollet posted this at 17:57 — 26th June 2002.
He has: 1,016 posts
Joined: May 2002
Each table's information is return in a row with the following columns:
Name
Type
Row_format
Rows
Avg_row_length
Data_length
Max_data_length
Index_length
Data_free
Auto_increment
Create_time
Update_time
Check_time
Create_options
Comment
korndragon posted this at 05:44 — 27th June 2002.
They have: 87 posts
Joined: Dec 2001
but what would take more resources? using 10 queryies to get specific information, or use i sql querie that has to return every bit on information about the 10 tables?
http://www.newbie-developer.com - Newbie web-developer community.
korndragon posted this at 05:46 — 27th June 2002.
They have: 87 posts
Joined: Dec 2001
damnit, why cant mysql be that simple? I saw that they are implementing sub-queries in 4.1
http://www.newbie-developer.com - Newbie web-developer community.
korndragon posted this at 05:47 — 27th June 2002.
They have: 87 posts
Joined: Dec 2001
let me just ask all you sql masters (lol), how would YOU get this information? Think about if you were trying to make your scripts efficient.... how would you work this problem?
http://www.newbie-developer.com - Newbie web-developer community.
zollet posted this at 06:35 — 27th June 2002.
He has: 1,016 posts
Joined: May 2002
I think the SHOW TABLE STATUS FROM databasename is pretty safe to use. You could test them though! Make 2 scripts, one with the SHOW STASTUS and one with 10 queries or whatever and then use microtime() to get the exact time before and after the queries. Then subtract the time before from the time after for each script and you'll be left with the processing time for each script. Finally post the results here so we can see them too.
Peter J. Boettcher posted this at 12:33 — 27th June 2002.
They have: 812 posts
Joined: Feb 2000
This would be interesting to find out. Using COUNT would probably be faster up to a certain point (depending on number of records), then the SHOW TABLE.. command would probably be faster.
PJ | Are we there yet?
pjboettcher.com
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.