[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