[thelist] MySQL queries, quotes, and AUTO_INCREMENT

Bernardo Escalona-Espinosa escalonab at gmail.com
Thu Apr 6 05:20:37 CDT 2006


Hello dear list,

I am desperate. This was supposed to be a simple upgrade, but I've
been running into so many MySQL problems its not even funny. My most
recent frustration has to do with single and double quotes.

(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. This is so dumb and obvious and i feel i
should know the solution.. but i don´t :(

(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. This creates
major chaos and confusion. I "shifted up" (or down, if you will) the
remaining rows to close the gap manually. Then when i inserted new
rows it continued with the old sequence numbering, leaving a gap
again. This increased the already existing chaos and confusion.

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.

Can anyone please enlighten me?

--
___________________________________________
Bernardo Escalona Espinosa
  handy: 0176 / 24 82 35 34
  http://www.bernsonline.com/



More information about the thelist mailing list