> Before I send the following SQL to MySQL from PHP I print it to screen.
> PHP chokes on it, but I can paste the exact same query from the screen
> directly to MySQL and it works just fine. For example:
>
> Here's the relevant PHP code:
> ======================================
> $sql_insert_registration = sprintf("INSERT INTO
> Registrations (
> Class_ID,
> prid,
> Registrant,
> Company,
> Phone,
> Email
> )
> VALUES (
> $_POST[Class_ID],
> $_POST[prid],
> '%s',".
> parseNull($_POST['Company']).",
> '$_POST[Phone]',
> '$_POST[Email]'
> )", mysql_real_escape_string($_POST['Registrant']));
>
> echo "<pre>".$_POST["Registrant"]."</pre>";
> echo "<pre>".mysql_real_escape_string($_POST["Registrant"])."</pre>";
> echo "<pre>".$sql_insert_registration."</pre>";
>
> if (!mysql_query($sql_insert_registration, $con)) {
> die('Error: ' . mysql_error());
> ....
> ======================================
>
>
> Here's the output:
> =======================
>
> INSERT INTO
> Registrations (
> Class_ID,
> prid,
> Registrant,
> Company,
> Phone,
> Email
> )
> VALUES (
> 355,
> 257,
> 'Brian O\'Brien',NULL,
> '612-456-5678',
> 'paul_s_johnson@xxxxxxxxxxxxxxxx'
> )
> Error: You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use near
> 'Brien', 'Class registration confirmation', ' This email ' at line 16
> ==================================================
>
>
> Also very oddly if the name "O'Brien" is input into the HTML form with two
> apostrophes side by side (O''Brien) then MySQL will take it (but then of
> course we have the problem of two apostrophes side by side inserted into
> the MySQL table). For example:
>
> ===================================
>
> INSERT INTO
> Registrations (
> Class_ID,
> prid,
> Registrant,
> Company,
> Phone,
> Email
> )
> VALUES (
> 355,
> 257,
> 'Brian O\'\'Brien',NULL,
> '612-456-5678',
> 'paul_s_johnson@xxxxxxxxxxxxxxxx'
> )
> You have been signed up for the class,
> and a confirmation email has been sent to you.
> =================================
>
> Very strange.
>
> I've checked various PHP variables and cannot figure out. It works fines
> from another PHP server that's using the same MySQL database.
>
> Thanks,
>
> Paul
Probably needs a double backslash for O'Brien. One to escape the
apostrophe and one to escape the backslash escaping the apostrophe. ;-)
This would be because you're not using mysql_real_escape_string() on the
third parameter. Try this (not tested):
$sql_insert_registration = sprintf("INSERT INTO
Registrations (
Class_ID,
prid,
Registrant,
Company,
Phone,
Email
)
VALUES (%s, %s, '%s', '%s', '%s', '%s')",
$_POST[Class_ID],
$_POST[prid],
mysql_real_escape_string(parseNull($_POST['Company'])),
mysql_real_escape_string($_POST[Phone]),
mysql_real_escape_string($_POST[Email]),
mysql_real_escape_string($_POST['Registrant']));
--
Niel Archer
niel.archer (at) blueyonder.co.uk
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
