27 replies to this topic

[ian mullen]

I've tried searching Google for the last hour to figure out how to display the total number of records in a MySQL database using PHP. All I can find is how to find the number of rows in a single table, and, although I already know how to do that, that is not what I want. I want the ENTIRE database - all five of my tables.

Such as "TOTAL DATABASE RECORDS: *INSERT NUMBER*" kinda thing.

So does anybody have any idea what code will do that? Any help would be much appreciated!

[Mr. Matt]

This can be done in 2 steps, 2nd step you already have, which is getting the count of the table, first step you need to get a list of the tables.

A simple mysql query:

SHOW TABLES

Will return to you a list of table names, loop through the result set and take a count off all the rows.

I am sure you can work the code out as it is nice and easy

Matt

Edited by Mr. Matt, 18 March 2008 - 06:19 PM.

[ian mullen]

Thanks! You couldn't give me a sample code of the entire thing? I'm still a n00b at this MySQL and PHP stuff - I've been at it two years but only ever needed the basics.

'preciate it.

[Mr. Matt]

I am going to be mean and not give you the code, and I don't want anybody after me to post the code, if you have been doing php for 2 years you should be able to do this as it is very basic, and you won't get better unless you learn

In my previous post I have pretty much said how to do it in psudo form, just read that a few times and work out which functions you need.

Like I said it is pretty simple and is no more then 10 lines of code, you can do it

Matt

[BigDog]

As Matt gave the psudo code, i'll give you a link to MySQL with more information:
http://dev.mysql.com...how-tables.html

[ian mullen]

Thanks. I did just learn something. Learned a new SQL function and I also learned how to use them.

Seriously - the basics are as much as I've ever needed to use. The SELECT function is the only SQL I've ever used.

$sql_query = mysql_query("show tables");
$totalrows = mysql_num_rows($sql_query);
print "<b><u>Total Database Entries:</u></b><br>";
print "$totalrows<br><br>";

That's what I've used - however that is only printing "5" when there are 5 tables and 6 records. I know what I've done wrong, I just don't know how to fix it.

As for the learning thing - I'm weird in that I learn by reading other people's code and then seeing what it does - somehow that flicks a light switch in my head. MySQL is not something I know, PHP I barely know and I certainly don't know how to mix the two.

Edited by ian mullen, 19 March 2008 - 12:32 PM.

[Mr. Matt]

Ok so what is it you did wrong?

[ian mullen]

mysql_num_rows($sql_query);

This bit is finding the number of tables, rather than the number of rows in the tables. That's where I've gone wrong. I think anyway. Maybe that line is needed, but I need to do something else first? I dunno what though.

[Mr. Matt]

Right so you know you are passing the wrong query into the mysql_num_rows. The key to doing this is all in the first query 'SHOW TABLES' which returns to you a list of all the table names in that database. So if you know the name of every table in the database, its then possible to get the number of rows in each one isn't it?

[ian mullen]

$sql_query = mysql_query("show tables");
print "$sql_query";

Produces the following, not a list of tables:
"Resource id #5"

Seriously dude I don't think I'm gonna get this - I've tried everything I can think of.

[BigDog]

Hum? Print?

Try either print_r or do a while loop like:

while($tbl = mysql_fetch_array($query))
{
echo $tbl[0]
}

[Mr. Matt]

have you ever done things with mysql before, as in pulling out data then looping through using mysql_fetch_assoc/mysql_fetch_array?

[ian mullen]

fetch_array I've used - I use that a lot all over my websites. Sometimes obvious stuff like that does evade me, even when I know it. I don't believe I would know how to use it here though...

[BigDog]

Well, it's a query though and under normal bases, you need to call the query....

But did it work for you now?

[Mr. Matt]

Ok so the SQL 'SHOW TABLES' does exactly the same as a select query, it returns a dataset, a list of tables, so you can treat it the same as any other sql select statement, which is where the mysql_fetch_array comes in, so you can use that to loop through each table, we getting somewhere now?

[ian mullen]

I can't figure this out!!! I can't do this - that's the whole point in me being here. Every single thing I try does not work and I'm wasting my time. I do not have the experience required to code this myself - I need a tutorial and seen as though Google is in love with tables, I came here for help - not to be told to do it myself.

Yes I'm getting frustrated. This problem is starting to really annoy me and I'm getting no help from anyone.

Will someone please just give me a sample of the code I want. I'll learn from that! That's how I've learned everything I know so far - tutorials and other people's coding.

Edited by ian mullen, 19 March 2008 - 04:04 PM.

[BigDog]

$query= mysql_query("SHOW TABLE STATUS");

while($tbl = mysql_fetch_array($query))
{
echo $tbl[Rows]."<br>";
}

that prints out the number of rows in each table. Basically, create a veriable outside the while loop. Then in the while loop, add the number to the veriable outside.

Edit:

Also, i was just looking around the php manual and i found this:mysql_stat()
http://www.php.net/m....mysql-stat.php

Not too sure if it helps but it could

Edited by BigDog, 19 March 2008 - 04:33 PM.

[ian mullen]

Okay, thank you!

That displays the number of rows in each table perfectly. Still a numbnuts here though - how do I create a variable outside the while loop and what would it look like?

I'm sorry I got annoyed - but it really started to frustrate me when nothing worked.

[BigDog]

So you've been coding for two years?

$mycount = 0;
while(XXXXX)
{
$mycount .= $tbl[XXX];
}

I think that shoul do.

[ian mullen]

My friend coded my first website, and left it in my hands so I had to learn his coding. Which I did. So everything he did in that website, I can now do myself. Anything else, I always look for tutorials and learn it that way. But I never read in depth how to do stuff and what certain stuff means. What can I say, busy life means no time for learning!

2bh, I don't even know what to put in place of the Xs...

Yes, I do feel quite stupid now but hey - as long as it all works in the end, I'll be happy.