Best practice - database mysql queries

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

AKA Panama Jack wrote:You do know that your TillingDatabaseAccess.php is really a simple database abstraction layer. :D
And its simplicity means it won't...[quoteme]slow down the application, and prevent you from making database specific optimizations.[/quoteme]. When I want to add a new query I have to write the query myself only the real basics are being abstracted.

Its not claiming to work across loads of different database technologies, that is what makes things slow. Although actually my feeling on this are changing as I'm planning on supporting PDO in OsisForms. PDO is highly recommended though much better than PEAR DB or even Zend_Db (when its done).
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

Code: Select all

select @category := LAST_INSERT_ID();
Doesn't this create a race condition?
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

Ambush Commander wrote:

Code: Select all

select @category := LAST_INSERT_ID();
Doesn't this create a race condition?
No. Why would it?
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

It's an extremely small one, I agree, but there's always the slight possibility that in between the time you performed the insert and then you make the variable assignment another insert took place, causing LAST_INSERT_ID() to give an invalid ID.

Transactions, of course, would make this a non-issue.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

Is a race condition just a fancy term for poor multi-user support?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

If i understand well, the OP notices that SQL always have the same form (just like each html page that has a head, title, body, etc...). Just like you can build templates for html you can build them for sql...

Instead of building a wrapper around a database api you would implement a SQL Builder class... Eg: a SELECT query has the following properties: from, where, group by, having, selections... I've seen SQL Builders that are smart enough to 'qualify' (place user and table name before the elements in the selections property)... Lot's of nice stuff to do ;)

At work i usually write the basic CRUD operations as a stored procedure... And in php i simply use variable binding and call the procedure.. For the more complex operations i could write more specialized stored procedures or use the SQL Builders that live in our database as other stored procedures....
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

I don't think php uses the same connection at the time for multiple request (one connection per request)...

So basically, in most common usages it's impossible to run into concurrency issues because last_insert_id returns the most recently generated id on a per connection basis... (i don't consider multithreading with php 'common use')
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Ambush Commander wrote:It's an extremely small one, I agree, but there's always the slight possibility that in between the time you performed the insert and then you make the variable assignment another insert took place, causing LAST_INSERT_ID() to give an invalid ID.

Transactions, of course, would make this a non-issue.
last insert id is stored in connection thread context, last_insert_id() just returns that value. Thus it does not create race condition.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

ole wrote:Is a race condition just a fancy term for poor multi-user support?
http://en.wikipedia.org/wiki/Race_condition
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Ambush Commander wrote:mysql_pconnect()
All pconnect says is that the connection will be re-used..

(As long as a request is being handled by php.. php will use that connection for the request it's handling... Only when the request has been handled, the connection will become available again for other requests...)
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

Ahhh... I stand corrected then.

Hmm... that probably would mean that I can abandon my `key` table! Whee!
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

I've been playing around with some SQL stuff this evening, and I've decided that prepared statements are better than simple bound parametres. Rather than doing:

Code: Select all

select @title := 'Product title';
select @description := 'Product description';
insert into product (title, description) values (@title, @description);
it's actually a bit quicker (excution time, not typing :) ), and safer regarding SQL injection to do

Code: Select all

prepare insert_product from "insert into product (title, description) values (?, ?)";
select @title := 'Product title';
select @description := 'Product description';
execute insert_product using @title, @description
deallocate prepare insert_product;
It's another level of difficulty for a hacker to get past, and for servers without stored procedures it's a pretty good step in the right direction. Coupled with mysql_pconnect() it would actually be virtually the same as using stored procedures (assuming of course that there's a way to check if a prepared statement exists).

Obviously mysqli has a nicer interface to prepared statements than multiple mysql_query() calls, but I'm just bothered with the SQL at the moment.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

Great post onion2k, just tried that myself very cool. I never actually knew what prepaired statements looked like and did till now.
Post Reply