[SOLVED] Using " and ' in Queries

[phpFirstJoy]

Hi, I'm having difficulty understanding why the following is correct:

 

$query = 'INSERT INTO blog_diary_entries (IndexID, CreateDate, EntryTitle, Entry, Author) VALUES (NULL, \'2007-10-26 20:49:19\', \'' . $title . '\', \'' . $entry . '\', \'ef\')';
$result = mysql_query($query);

 

but the following would be wrong:

 

$query = "INSERT INTO blog_diary_entries (IndexID, CreateDate, EntryTitle, Entry, Author) VALUES (NULL, \'2007-10-26 20:49:19\', \'' . $title . '\', \'' . $entry . '\', \'ef\')";
$result = mysql_query($query);

 

and the following would also be just as wrong:

 

$query = "INSERT INTO blog_diary_entries (IndexID, CreateDate, EntryTitle, Entry, Author) VALUES (NULL, \'2007-10-26 20:49:19\', \'" . $title . "\', \'" . $entry . "\', \'ef\')";
$result = mysql_query($query);

 

I can only see that " (double quote) is not accepted in the query (I also tried using them before $title and after $title - see also $entry) even if I use them in the correct order and such but somehow ' is the only thing that I can use.  Would someone be able to explain this to me?  It's driving me nuts!

 

-- Driven Crazy

[phpFirstJoy]

Nevermind.  I solved it.  It should be corrected to:

 

$query = "INSERT INTO blog_diary_entries (IndexID, CreateDate, EntryTitle, Entry, Author) VALUES (NULL, '2007-10-26 20:49:19', '$title', '$entry', 'ef')";
$result = mysql_query($query);

[fenway]

Actually, you should NEVER specify the UID that like with NULL... drop references both to the column and it's faked value.

[toplay]

Quote

Actually, you should NEVER specify the UID that like with NULL... drop references both to the column and it's faked value.

 

The use of "NEVER" is strong. His use of NULL is fine. If the IndexID column is an auto_increment, MySQL will increment it fine. No need to change anything.

 

 

Quote

I can only see that " (double quote) is not accepted in the query (I also tried using them before $title and after $title - see also $entry) even if I use them in the correct order and such but somehow ' is the only thing that I can use.  Would someone be able to explain this to me?  It's driving me nuts!

 

-- Driven Crazy

 

The reason you probably couldn't use double quotes in the query was because you started (enclosed) the query with double quotes. So, it's not a MySQL thing, but a PHP syntax issue.

 

This is the right syntax when starting with double quotes and wanting to use double quotes in query too:

 

$query = "INSERT INTO blog_diary_entries

                        (IndexID, CreateDate, EntryTitle, Entry, Author)

                        VALUES

                        (NULL, \"2007-10-26 20:49:19\", \"$title\", \"$entry\", \"ef\")";

 

The escaping there is for PHP and not MySQL.

 

 

Edited January 20, 2022 by requinix

[fenway]

Quote

Quote

Actually, you should NEVER specify the UID that like with NULL... drop references both to the column and it's faked value.

 

The use of "NEVER" is strong. His use of NULL is fine. If the IndexID column is an auto_increment, MySQL will increment it fine. No need to change anything.

 

It's intentionally strong -- just because it produces the correct result doesn't make it "fine".  The auto_increment happens on its own, there's no need to specify anything -- so don't.  Break this habit now.

Edited January 20, 2022 by requinix

[toplay]

Quote

It's intentionally strong -- just because it produces the correct result doesn't make it "fine".  The auto_increment happens on its own, there's no need to specify anything -- so don't.  Break this habit now.

 

I disagree. Of course it's "fine".

 

Quote

...the value is automatically generated by storing the special values NULL or 0 into the column...

 

SQL allows inserting rows without specifying column names and just their values (although I don't recommend using this syntax approach). So, with that syntax you have to give a value to an auto_increment column. Specifying a NULL or zero will still automatically increment the value in that column. The MySQL manual has examples of such SQL:

 

Quote

INSERT INTO t VALUES (NULL, 'Bob');

 

I like listing all the columns (including auto_increment) in an SQL insert.

 

FYI - Just last week I had to remove the auto_increment on a column (but still leave it as a primary key). Since the code already had all the column names listed, all I had to change was specify a non-null value and I was done.

 

Happy coding.

 

 

Edited January 20, 2022 by requinix

[fenway]

It's intentionally strong -- just because it produces the correct result doesn't make it "fine".  The auto_increment happens on its own, there's no need to specify anything -- so don't.  Break this habit now.

 

I disagree. Of course it's "fine".

So is using the comma operator instead of proper JOIN syntax... but that doesn't make it desiable or beneficial in any way.

 

...the value is automatically generated by storing the special values NULL or 0 into the column...

 

SQL allows inserting rows without specifying column names and just their values (although I don't recommend using this syntax approach). So, with that syntax you have to give a value to an auto_increment column. Specifying a NULL or zero will still automatically increment the value in that column. The MySQL manual has examples of such SQL:

 

INSERT INTO t VALUES (NULL, 'Bob');

 

I like listing all the columns (including auto_increment) in an SQL insert.

 

FYI - Just last week I had to remove the auto_increment on a column (but still leave it as a primary key). Since the code already had all the column names listed, all I had to change was specify a non-null value and I was done.

I sincerely hope you're not using 0 in place of NULL for auto-increment -- there's an sql mode issue with this, as well as mysqldump concerns.  And IMHO, 0 is just as magical as NULL... it serves no purpose to have it there.  Of course you MUST specify all the columns (wait, you think it's fine not too, right?), but having the PK in there just leaves the door open for a non-NULL value to be inserted (e.g. in it's coming from a hash).

 

But hey, to each his own.