Inserting Data Into MySQL database with errors spitting back error?

Question

I'm trying to insert data into a mysql database using php. Seems like a simple task.

While using dummy data (just strings of text), it was working perfectly fine. And when I replaced the dummy data with the actual variables, I get this error:

ERROR: Could not able to execute INSERT INTO tbl_users (id, timestamp, testid, email, wants_newsletter, ip, country) VALUES (NULL, CURRENT_TIMESTAMP, , , , , ). 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 ' , , , )' at line 1

Below is the script that I'm using. I am 100% sure that all the variables are working. I currently have them spitting out dummy data as well.

$testid = 1;
$email = 'test@yahoo.com'
$wants_newsletter = true;
//get $ip
if (!empty($_SERVER['HTTP_CLIENT_IP'])) {
    $ip = $_SERVER['HTTP_CLIENT_IP'];
} elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])) {
    $ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
} else {
    $ip = $_SERVER['REMOTE_ADDR'];
}
$country = 'US';

$user_id = 100; 
$question = 14
$answer = 14    

function saveToDB() {

/* Attempt MySQL server connection. */
$link = mysqli_connect("localhost", "$user", "$pass", "$db");

// Check connection
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}

// Attempt insert query execution
$sql = "INSERT INTO tbl_users (id, timestamp, testid, email, wants_newsletter, ip, country) VALUES (NULL, CURRENT_TIMESTAMP, $testid, $useremail, $newsletter, $ip, $country)";

if(mysqli_query($link, $sql)){
    echo "Records inserted successfully.";
    $user_id = $link->insert_id;
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}

// Attempt insert query execution
$sql = "INSERT INTO tbl_answers (id, user_id, question_id, answer) VALUES (NULL, $user_id, $question, $answer)";

if(mysqli_query($link, $sql)){
    echo "Records inserted successfully.";
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}

// Close connection
mysqli_close($link);


}//end saveToDB

saveToDB(); //Call saveToDB()

I've been busting my head for hours trying to figure out this error. It might be something small that I'm overlooking and perhaps I need a fresh pair of eyes on it.

Any help would be greatly appreciated.

You should be using prepared statements. As it stands, you have `string` type variables and no quotes around the values. — Jeremy Harris, Apr 10 '17 at 20:02
You will need single quotes around the strings. '$useremail' — Jason K, Apr 10 '17 at 20:04
I've tried using single quotes around the strings and im getting a null (or 0) value — iammikerodriguez, Apr 10 '17 at 20:06
@AbraCadaver even though he should be using prepared statements, it's wrong to say that this is a duplicate of "How to prevent sql injections" when this is not what the user asks about. — Andrew Larsen, Apr 10 '17 at 20:06
Over to your question, seems like you're missing single quotes around the strings, like @JasonK pointed out over. If you variable contains an integer, the single quotes is not neccessary, but if it's a string then you have to add it. — Andrew Larsen, Apr 10 '17 at 20:09
Is id allowed to be null? echo out your sql and submit it with a tool to your sql server. See what error you have there I suspect it's not a php error. — Jason K, Apr 10 '17 at 20:10
@AndrewLarsen I've tried with single quotes around the values and I'm getting a null value when it's recorded to the database. I'm not sure why. The script only works when I hard code a value into the INSERT script. — iammikerodriguez, Apr 10 '17 at 20:11
Okay, but you don't get any errors when using single quotes? — Andrew Larsen, Apr 10 '17 at 20:11
I do not get any errors, just not the actual value of the variable that was set before. When I echo or var_dump the variable outside of the function or outside of the insert statement, the correct data is displayed. — iammikerodriguez, Apr 10 '17 at 20:12
Can you show us a picture of the table structure with column data types and properties? — Andrew Larsen, Apr 10 '17 at 20:16
Where are you getting the values for your variables? I don't see them being set. Inside the function. — Jason K, Apr 10 '17 at 20:17
@AndrewLarsen here is a picture the table structure: http://imgur.com/a/RUZQ6 — iammikerodriguez, Apr 10 '17 at 20:18
When you're saying that you you're using var_dump and it's displaying the correct value, is that inside this function or outside it? — Andrew Larsen, Apr 10 '17 at 20:19
As @JasonK pointed out I don't see any way for those variables to be accessible inside that function. If they are defined outside that function you have to use global $var; inside a function before accessing them. Preferably you would send them as arguments when calling the function. — Andrew Larsen, Apr 10 '17 at 20:20
@AbraCadaver what a ridiculous thing to say. Of course it will solve his problem, but thats not what he asked for. Lets help him based on his question, not what you personally would prefer (though I agree it's best to make prepared statements). — Andrew Larsen, Apr 10 '17 at 20:22
@AndrewLarsen Outside of the function, just to make sure they are working properly. The variables are defined outside of the function. I edited my answer with the variables. Like I mentioned before, they are static variables. — iammikerodriguez, Apr 10 '17 at 20:23
That wont work. If you want it to work you have to add this inside your function (at the top): global $testid; global $useremail; global $newsletter; global $ip; global $country; global $user_id; global $question; global $answer; — Andrew Larsen, Apr 10 '17 at 20:25
The variables are defined outside the function and are therefore not accessible inside the function. You either have to set them as global inside the function to be able to reach them, define them as constants (not good in this case) or send them as arguments to the function (preferable). — Andrew Larsen, Apr 10 '17 at 20:26
Okay, I'm with you. If I'm re-writing this in PDO, do I still have to follow that rule and make the variable global from within the function? Is there a good guide in writing PDO that is not the PHP docs? — iammikerodriguez, Apr 10 '17 at 20:30
I do not know if there are any performance benefits using PDO over lets say Mysqli, however PDO can be used with different DBMS. — Andrew Larsen, Apr 10 '17 at 20:33
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/141387/discussion-between-andrew-larsen-and-iammikerodriguez). — Andrew Larsen, Apr 10 '17 at 20:33

score 0 · Answer 1 · answered Apr 10 '17 at 20:03

0

Based on the SQL error you're getting, it seems the variables you're passing into the interpolated string are not set.

Check the values of $testid, $useremail, $newsletter, $ip, and $country before your insert statement.

In any case, as some others suggested, you would want to use prepared statements either way. Look into PDO objects.

answered Apr 10 '17 at 20:03

Ananth Rao

1,232
1
9
19

The values of the variables are spitting out fine. I've tried different methods from wrapping the values in single quotes to not having the in quotes at all. The only way the script works is if I hard cord a string value into the INSERT script. – iammikerodriguez Apr 10 '17 at 20:07
Are they defined globally or locally? By default PHP scope for global variables doesn't extend inside functions. So if they're global, at the top of your function you'll need to use the `global` keyword and then the names of all the global variables you want to use. This declares that those variable names are to be resolved globally within the function – Ananth Rao Apr 10 '17 at 20:24
If you have problems even if you do that, and the variables are definitely defined inside the function, I'd take that as an opportunity to switch to using prepared statements with PDO. By far the better way of running SQL queries in PHP – Ananth Rao Apr 10 '17 at 20:25
Is there any guide out there that can help me rewrite this in PDO, that is not the PHP docs? Sometimes those can be hard to follow. Is there a performance benefit in using PDO? – iammikerodriguez Apr 10 '17 at 20:31
I'm not sure if it has a performance advantage, but PDO is much better because it abstracts the queries away and allows you to approach database access in a much more high level and organized way. The less SQL you have to write, the less room for SQL errors, so to speak. And plus it also prevents SQL injection. I can't vouch for any guide personally, but this one looks promising https://phpdelusions.net/pdo – Ananth Rao Apr 11 '17 at 17:25

Inserting Data Into MySQL database with errors spitting back error?

1 Answers1