Hi there! I am used to using PDO to prepare my SQL queries in my PHP code. I was following this tutorial to learn how to return the row count of all of my database tables at once in MySQL. However, this tutorial just uses vanilla prepared statements instead of PDO, so I’m a little bit confused on how to translate it over to my own PHP script.
The tutorial teaches how to construct some code in the general form of:
SELECT
...some stuff getting table rows...
INTO @sql
FROM
(SELECT
...some subquery getting table names....) table_list
and then says to run:
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;
Now normally I would just do something along the lines of:
$s = $db->prepare($sqlString);
$s->execute();
to prepare and execute my SQL query. But this tutorial is using INTO @sql in the middle of their query and then executing @sql. What is my equivalent with PDO? How do I store a query into a user-defined variable and then execute it in PHP?
Oh, sorry, I didn’t think that the full query would need to be re-written, only the INTO @sql part so I avoided putting the whole query into the question to avoid bloat. The full query (taken from that tutorial I linked) is:
SELECT
CONCAT(GROUP_CONCAT(CONCAT('SELECT \'',
table_name,
'\' table_name,COUNT(*) rows FROM ',
table_name)
SEPARATOR ' UNION '),
' ORDER BY table_name')
INTO @sql
FROM
(SELECT
table_name
FROM
information_schema.tables
WHERE
table_schema = 'classicmodels'
AND table_type = 'BASE TABLE') table_list;
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;
It counts up all the rows for every table in a database and returns the exact number of rows for each table along with each table name.
How do I do this in PHP using prepared statements instead of calling the last three "PREPARE, EXECUTE, DEALLOCATE queries at the end?
Well for starters, depending on your database engine, this query could possibly be vastly shortened, because the database is already accumulating this data.
For example, in MySQL 8.0 with MyISAM tables, this query will get you the same result:
SELECT table_name,table_rows FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'classicmodels';
If however you’re using InnoDB tables, this amount is known to be inaccurate.
Yeah, that’s what I was using before but the reason I am trying to write my own query is because the table_rows is known to be inaccurate with InnoDB (which is what I’m using). How do I send a query to an InnoDB database to get an accurate count of the rows?
Just wondering, why do you need to know the total rows of all the tables and even more specifically, all at once? I have been a DB Admin for over 30 years and have never had a reason to know that. For an individual table, yes, but not all of them all at once.
@benanamen I have a series of forms that are each saved as their own individual table. On one page I am displaying the number of items in each form (each row in a table represents an item). I thought it would be much more cost efficient/faster to do one database call and get the row counts from all of the tables and then parsing it in the Javascript instead of making seperate database calls for each individual table. Is that not the case? (It totally could be, I’m new DB administration )
(These are some examples from my testing database, but in production the sizes will all be much larger. You can see how inaccurate the sizes [i.e. row counts] are because I am currently using table_rows.)
I have order forms that employees at a company use to send orders to different suppliers.
The employer is able to create specific forms for different suppliers. 1 supplier = 1 form = 1 table in the database. The employer can set “default” information for items ordered from each of the different suppliers using a an online system I have set up. These “defaults” will fill in all of the fields for an item in a supplier’s form when the employee is using that form and types in one of the default values.
Though all this said, unfortunately I am not able to restructure my entire database right now. I don’t have the hours to do that for this project, sadly, so I will have to use it as-is. If you want to critique it, that is fine and I will use that as learning experience for next time. But I will not be able to restructure the database to solve this current problem.
Thanks for the details. That is what I was looking for. I understand the position your in. Would you be able to provide a zip of this project with everything I need to run it? It will be pretty difficult for me to help without seeing it. You can PM me a download link.
)