[RESOLVED] Reading Mulitple Tables At Once

eVrn

I hope this isn't considered cross-posting. I have tried to ask this question in the Newbie section and didn't get much response, possible because of my poor explanation. So I thought I would try it again from scratch.

I am attempting to list all the tables (names of the tables) which I have done, but I want to add a couple of outputs to the list. I want to ALSO return how many records are in each table, and one of the row values in each table - all in the same output.

I am hoping to end up with an output that looks like:

TableNameOne - 4 records - id
TableNameTwo - 8 records - id
TableNameThree - 1 records - id

So far, all I have is

TableNameOne
TableNameTwo
TableNameThree

Thanks for any direction.

alimadzi

Run the query "SHOW TABLES", loop through the result set, and run "SELECT COUNT(id) FROM $table" on each table (assuming 'id' is a valid column of each table. For more specific guidance, ask a follow up question.

Roger_Ramjet

just run SHOW TABLE STATUS

alimadzi

Roger Ramjet wrote:
just run SHOW TABLE STATUS LIKE with a wildcard

Great option for MyISAM tables, but (from the quoted link)...

"The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40 to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count."

Roger_Ramjet

Yes, that is why I pointed to the manual in the first place, newbies need to learn. Being a newbie, my guess is that they are all myisam tables anyway, but your comment is valid.

alimadzi

You're probably right about them using MyISAM tables. And for the record, I learning something from your answer. I'd never thought of using "SHOW TABLE STATUS" in a PHP script though I've used it many times in the MySQL console. Thanks.

eVrn

OK, I have been trying to get a handle on how to get this. I know the best way to learn is to try it. Here is what I have ended up with, but no good.

$result = mysql_query("SHOW tables", $db);

echo "<BR><BR><B>Current table:</B><BR>";
while($row = mysql_fetch_row($result)){
	$query2 = "SELECT COUNT(*) FROM $row";
	$result2 = mysql_query($query2);
	$count = mysql_result($result2,0,0);
	echo $row[0]. " has " .$count[0]. "records.<BR>";
}

I think my problem is not actually identifying a table to read the number of records, but I am not sure how to do that. Thanks for any advise ...

(hopefully this doens't make me look TOO awesfully stupid! :bemused: )

Roger_Ramjet

Your problem is that you fetch a row as an enumerated array but do not then identify which array element to use in the second query.

// this is wrong
$query2 = "SELECT COUNT(*) FROM $row";

Have a read on the manual to see how to access the data in a $row

eVrn

// this is wrong

That's what I figured. I have been looking over the manual -

http://us3.php.net/mysql_num_rows

- but in the example, they use "table1" as the name of the table to read. I need to know how to get a variable there instead, but the variable isn't named until the loop happens. That is my point of not understanding.

Roger_Ramjet

No the SYNTAX is wrong: ie. $row. Go read the manual for mysql_fetch_row to see how you should be accessing the data.

eVrn

I am reading the manual. I am not understanding it. Thanks anyway.

Anyone out there care to help?

alimadzi

After you open your while loop, add this line...

print_r($row);

That will tell you how to reference the array key for the table names.

Roger_Ramjet

IE $row is an ARRAY. Do you know what an ARRAY is?

eVrn

Well with no help, and not understanding the manual, I just kept hacking until I stumbled across a solution. Would have rather got some help so I could understand it, but it is what it is.

Maybe next time.

alimadzi

Sorry, but we were trying to teach you to fish rather than just giving you a fish. If you don't like this approach, then eschew the forums next time and pay somebody to do the work for you. Have a great day... 😉

eVrn

I VERY much appreciate the attempt to teach. I did what you said and got the display, but still don't know what it is telling me. I do know what an array is, and I did add the [0] back into the $row variable, but I had other issues and couldn't figure out what all the problems were. I erased it all, started over and got it to work.

So .. what SHOULD I learned from adding " print_r($row);" in there and looking at the readout? I see the result but don't know what it is telling me.

Thanks again. 🙂

Roger_Ramjet

syonara

alimadzi

eVrn wrote:
So .. what SHOULD I learned from adding " print_r($row);" in there and looking at the readout? I see the result but don't know what it is telling me.

Thanks again. 🙂

A few things you could do:

Look up print_r() in the PHP manual.
Post the result in this thread and ask us about it.
Take a guess and see what happens.

I'm glad you got this working and I'm glad you appreciate our help. The reality of these forums is that you're not going to get a lot of people willing to solve your entire problem for you. Sometimes we get a really interesting problem that intrigues us and people will just post complete solutions, but that is rare.

I can't speak for everyone else who answers questions around here, but I subscribe to the RSS feed, follow what's going on, and post replies from time to time as I have a couple minutes. Usually I just try to get the person "unstuck" so they can proceed with solving the problem themselves. I'm a full-time PHP programmer with contract gigs on the side and a family to take care of, so I don't spend loads of time on here.

Speaking of family, duty calls. Take care...

eVrn

Thanks for that feedback. This is new to me. I have been doing freelance design work for years and am just now learning PHP and MySQL. I do indeed know what you mean about taking time. My oldest daughter is getting married in September and the preparations are incredibly time consuming. Along with owning three businesses, and being a marriage counselor on top of it all! Speaking of counseling, the phone is ringing ... again! 🙂