[thelist] MySQL queries, quotes, and AUTO_INCREMENT

Max Schwanekamp lists at neptunewebworks.com
Thu Apr 6 12:52:53 CDT 2006


Bernardo Escalona-Espinosa wrote:
> (A)
> I am inserting photo descriptions into a MySQL table, but the problem
> is that these descriptions sometimes have single quotes (example: this
> is a picture of john's foot) and sometimes double quotes (example: the
> src parameter in a link tag).
> I solved the problem where single quotes break my MySQL queries by
> using double quotes in the query instead of single quotes, like such:
> 
> $query = "INSERT INTO photo_db VALUES (\"\", \"".$i."\",
> \"".$images[$i][0].".jpg\", \"".$images[$i][1]."\",
> \"".$images[$i][2]."\", \"".$images[$i][3]."\",
> \"".$images[$i][4]."\", \"".$category."\")";
> 
> But of course now my query breaks if $images[$i][2] (the description)
> has double quotes in it. 

mysql_real_escape_string() is your friend!  Your query seems to be going 
through a lot of unnecessary contortions.  How about:
$imgdata = array_map('mysql_real_escape_string',$images[$i]);
$query = "INSERT INTO photo_db VALUES
   ('', '$i', '{$imgdata[0]}', '{$imgdata[1]}',
    '{$imgdata[2]}','{$imgdata[3]}',
    '{$imgdata[4]}','$category')
";
Or maybe even shorten that query string further with implode(), assuming 
each row of $images array has just the five values:
$imgs = implode("','",
   array_map('mysql_real_escape_string',$images[$i])
);
$query = "INSERT INTO photo_db VALUES
   ('', '$i', '$imgs', '$category')
";

> (B)
> Second problem: has to do with deleting entries of a table when you
> have the primary key as AUTO_INCREMENT.
> Seems that when deleting rows, the primary keys of the deleted rows
> aren't automatically re-used when new rows are inserted.

As Matt said, this is by design and is a good idea.  What difference 
does it make what value your table.id is, so long as it's unique?  When 
you delete a row, the data is gone and the storage file's size is 
accordingly smaller.  What is your rationale for wanting to reuse 
primary keys?

> I googled some and found that this behaviour depends on whether your
> tables are of the type BDB, MyISAM, or InnoDB. Unfortunately I have no
> idea what engine my server uses and have really no understanding of
> these db types all together.

RTFM. http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html

-- 
Max Schwanekamp
http://www.neptunewebworks.com/



More information about the thelist mailing list