Issue with MySQL INSERT via PHP mysql_query

Question

I'm having problems with a really bizarre problem when trying to insert a record into mysql via PHP and I was wondering if anyone could shed any light on it, because I'm out of ideas now.

Database table:

Field       Type        Null    Default Comments
UserID      bigint(20)  No      Autoincrement
UserGUID    text        No       
ServerID    int(11)     No       
UserName    text        No       
Passwrd     text        No       
Prompt      text        No       
Answer      text        No       
EMail       text        No       
Verified    int(11)     No  0    
Language    text        No       
Gender      int(1)      Yes     NULL     
DateOfBirth int(11)     Yes     NULL     
Country     int(11)     Yes     NULL     
PostCode    text        Yes     NULL     
State       text        Yes     NULL     
Town        text        Yes     NULL

Snippit of relevant PHP code...

public function signupUser($uid, $pwd, $prompt, $answer, $email, $lang, ... &$result) 
{
    $guid = $this->getGUID();

    $serverID = 1;
    $result = mysql_query("INSERT INTO User(UserGUID, ServerID, UserName, Passwrd, EMail) " + 
                                   "VALUES ('$guid', $serverID, '$uid', '$pwd', '$email')");

Before anyone tells me I should be using mysqli, parameterising my queries and the like, please be aware that this PHP/MySQL is a local test harness only, on a private network and only for development purposes until the real web service (dotNet/Oracle) comes available.

So if I call the function above with suitable parameters, $result comes back with...

"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 '0' at line 1"

but if I reduce the query to just...

$result = mysql_query("INSERT INTO User(UserGUID, ServerID, UserName, Passwrd) " + 
                                       "VALUES ('$guid', $serverID, '$uid', '$pwd')");

it works! Happily inserts the record (despite there being nulls in not null fields!?!) Just to add further complications, if I run the (original) query with suitable values e.g.

INSERT INTO User(UserGUID, ServerID, UserName, Passwrd, EMail) VALUES ('guid', serverID, 'uid', 'pwd', 'email')

directly against the database via phpMyAdmin, it also works!!

This is driving me nuts. I've tried changing field names, nullability, order, which fields I use in the query (after the first four which work), values - none of it makes a difference. It almost seems as if it doesn't want more than four fields.

Please... anyone...? I really am at a loss to understand why it won't accept the fifth field, it makes no sense that I can see. Unfortunately the error returned is of no help at all, too vague and seems to be the equivalent of the oh-so-useful "errors occurred" from MS.

If I have to change over to mysqli I will but I'd prefer not to have to re-craft the test harness if I can avoid it.

Since you're creating the whole SQL query (no bindings, etc), put it in a string, execute it AND write it to the screen: $sql = "INSERT..."; echo $sql; $result = mysql_query($sql); and see what the query actually looks like when it returns error. — Vedran Šego
– Vedran Šego, Commented May 19, 2013 at 1:47
Concatenation in php is done with ., not +. That may be the problem — Explosion Pills
– Explosion Pills, Commented May 19, 2013 at 1:58
@Vedran Šego Well ****** me! You have indirectly answered it for me. I'm totally new to PHP (as you might have guessed) and have been using the + as a string concatenation operator because it appeared to work elsewhere. What a flamin' idiot I am to assume that + would be concat. Please feel free to add that as a formal answer and I'll mark it. @ Explosion Pills sorry mate, he beat you to it but you're spot on. — Mike
– Mike, Commented May 19, 2013 at 2:04
@Mike I didn't notice that, since I thought it was just to make it fit here better and you did say that the short version worked O.K. Now I really don't understand why "+" was not an issue there. — Vedran Šego
– Vedran Šego, Commented May 19, 2013 at 2:09
@Vedran The + was the issue but I didn't realise it. However, now you mention it, you are correct - the shortened version DOES work with a + when the string is embedded in the mysql_query call, it was only when I pulled it out and assigned it to a variable that it didn't. — Mike
– Mike, Commented May 19, 2013 at 2:19

Community · Accepted Answer · 2017-05-23 12:11:18Z

1

edited after a revelation: take a look in the comments that concatenation operator in php is absolutely a . not a + i would put money that is your problem right there... didnt even see it on my first look.

in the past ive run into odd troubles somewhat similar to this - and was able to solve it by encapsulating my table and column names in backticks columnX, columnY - i would also try adding a space between the table name 'User' and the parenthesis containing your column names in the SQL syntax

mysql_query("INSERT INTO User(UserGUID, ServerID, UserName, Passwrd, EMail) " + 
                               "VALUES ('$guid', $serverID, '$uid', '$pwd', '$email')");

take a look at this question? very similar to yours with successful results

hope i could be of some assistance

edited May 23, 2017 at 12:11

CommunityBot

11 silver badge

answered May 19, 2013 at 2:04

Joe

2,1051 gold badge18 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mike Over a year ago

Thanks mate, some useful stuff to look at there.

Tim Duncklee · Accepted Answer · 2013-05-19 02:12:28Z

0

If your example is literal, I doubt either works. The string concatenation operator is "." not "+" in php. Additionally, php will not interpret variables inside single quotes. Try re-writing the query like this:

$result = mysql_query("INSERT INTO User SET UserGUID = '" . $guid . "', ServerID = " . $serverID . ", UserName = '" . $uid . "', Passwrd = '" . $pwd . "'";

answered May 19, 2013 at 2:12

Tim Duncklee

1,4201 gold badge24 silver badges36 bronze badges

1 Comment

Mike Over a year ago

The example is literal and, yes, it was the '+'. However, the variables are happily being interpreted in the singles quotes and going through to the database.

Marjeta · Accepted Answer · 2013-05-19 02:17:08Z

0

I always create SQL inside a string, and, if there is a problem, I insert an echo $sql or error_log($sql) statement.

Sometimes there's something very subtle. When you copy and paste the query from debug output into phpMyAdmin, you will see a more meaningful error message.

One thing is possible that the variable $email contains something that breaks out of the string. Make sure you use mysql_real_escape_string on your PBP variables before including them inside an SQL query..

answered May 19, 2013 at 2:17

Marjeta

1,1611 gold badge10 silver badges26 bronze badges

Collectives™ on Stack Overflow

Issue with MySQL INSERT via PHP mysql_query

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related