Here’s some good documentation on PHP prepared statements and stored procedures including how to call stored procedures with output parameters.
Category Archives: Database
Do you need to call PDOStatement::closeCursor when you’re done with the statement?
The answer is no, so long as you’re not preparing to execute the statement again. I figured this out by looking at the code for PDOStatement::closeCursor and the MySQL implementation. Seems to me that all the freeing necessary is done in the destructor so if you’re not planning to use the statement again it seems to me that you can safely omit the call to PDOStatement::closeCursor(). On the other hand if you are going to reuse the statement calling closeCursor seems like it’s a pretty important thing to do. It would be nice if PDOStatement::fetchAll() called closeCursor() for us, but I don’t think it does.
Ignoring MySQL constraints
You can disable foreign key checks with
SET foreign_key_checks = 0;
And you can disable unique index checks with
SET unique_checks = 0;
A great MySQL session
I found this great MySQL session over here. It has a clever approach for creating a big table, and shows how to invoke shell commands from the `mysql` client.
USE test; SET GLOBAL innodb_file_per_table=1; SET GLOBAL autocommit=0; -- Create an uncompressed table with a million or two rows. CREATE TABLE big_table AS SELECT * FROM information_schema.columns; INSERT INTO big_table SELECT * FROM big_table; INSERT INTO big_table SELECT * FROM big_table; INSERT INTO big_table SELECT * FROM big_table; INSERT INTO big_table SELECT * FROM big_table; INSERT INTO big_table SELECT * FROM big_table; INSERT INTO big_table SELECT * FROM big_table; INSERT INTO big_table SELECT * FROM big_table; INSERT INTO big_table SELECT * FROM big_table; INSERT INTO big_table SELECT * FROM big_table; INSERT INTO big_table SELECT * FROM big_table; COMMIT; ALTER TABLE big_table ADD id int unsigned NOT NULL PRIMARY KEY auto_increment; SHOW CREATE TABLE big_table\G select count(id) from big_table; -- Check how much space is needed for the uncompressed table. \! ls -l data/test/big_table.ibd CREATE TABLE key_block_size_4 LIKE big_table; ALTER TABLE key_block_size_4 key_block_size=4 row_format=compressed; INSERT INTO key_block_size_4 SELECT * FROM big_table; commit; -- Check how much space is needed for a compressed table -- with particular compression settings. \! ls -l data/test/key_block_size_4.ibd
PDOStatement::fetchAll
There’s some really good PHP PDO documentation over here: PDOStatement::fetchAll. Of particular interest were PDO::FETCH_KEY_PAIR, PDO::FETCH_PROPS_LATE, PDO::FETCH_GROUP, and PDO::FETCH_FUNC.
That’s a lot of databases
It’s true, that is a lot of databases.
SQLite is not a toy database
A run down on SQLite features: SQLite is not a toy database.
Database Programming – An Intermediate MySQL Tutorial – Scripting, Data Types, Examples
I found this great collection of notes: Database Programming – An Intermediate MySQL Tutorial – Scripting, Data Types, Examples.
Prepared statement needs to be re-prepared
I have seen this error before, Prepared statement needs to be re-prepared, I’m not sure if I will see it again, but just in case I’m keeping this link…
MySQL admin with Ian Gilfillan
I was doing some reading today and I came upon an old series of articles over on www.databasejournal.com by a dude called Ian Gilfillan:
- MySQL backups
- Database Replication in MySQL
- Restoring lost data from the Binary Update Log
- Optimizing MySQL: Queries and Indexes
- Optimizing MySQL: Hardware and the Mysqld Variables
- MySQL’s Query Cache
- MySQL’s Over-looked and Under-worked Slow Query Log
For the ‘type’ in SQL ‘EXPLAIN’ Ian says: from best to worst the types are: system, const, eq_ref, ref, range, index, all.
It seems this Ian Gilfillan fellow has been rather prolific.