MySQL query not inserting data

Question

I'm in a bit of a pickle here, its just that I'm trying to enter some data that I get from users into a table, but for some reason it won't let me insert the data, however I have exactly the same query for another part of the table and that seems to work perfectly fine.

for example when I execute this query, it doesn't work:

$updateibtask2 = "UPDATE ibtask_task2_75beep SET 

    Trial1_tone_actual=  '$taskerror[0]', Trial2_tone_actual= '$taskerror[1]',  Trial3_tone_actual= '$taskerror[3]',

    Trial4_tone_actual=  '$taskerror[4]', Trial5_tone_actual= '$taskerror[5]',  Trial6_tone_actual= '$taskerror[6]', 

    Trial7_tone_actual=  '$taskerror[7]', ... WHERE user_id = '$memberid'";

However, when I try this query it works perfectly fine:

$updateibtask2_estimate = "UPDATE ibtask_task2_75beep SET 

    Trial1_tone_estimate=  '$taskerror[0]', Trial2_tone_estimate= '$taskerror[1]',  Trial3_tone_estimate= '$taskerror[3]',

    Trial4_tone_estimate=  '$taskerror[4]', Trial5_tone_estimate= '$taskerror[5]',  Trial6_tone_estimate= '$taskerror[6]', 

    Trial7_tone_estimate=  '$taskerror[7]', ... WHERE user_id = '$memberid'";

I'm just wondering where I'm going wrong?

Also if it helps the PHP code that I'm using to run these queries are:

 $task2 = array();
 $task2 = $_SESSION['task2'];

 $task2estimate = array();
 $task2estimate = $_SESSION['estimatedpress2'];

 $task2actual = array();
 $task2actual = $_SESSION['actualpress2'];

 addacutalerror_75($memberid, $task2actual);
 addestimatederror_75($memberid, $task2estimate);

Also to check whether there was data present for $task2actual I had done an echo ..[0], .. [1].. etc and there was data present in the array.

Updated

For those who are searching for solutions and have the same problem, here's what I did:

function addacutalerror_75($memberid, $task2actual) {

$insertmember = "INSERT INTO ibtask_task2_75beep (user_id, Trial1_tone_actual,
    Trial2_tone_actual, Trial3_tone_actual, Trial13_tone_actual,
    Trial14_tone_actual, ..., Trial40_notone_actual) VALUES ('$memberid', '$task2actual[0]', '$task2actual[1]', '$task2actual[3]', '$task2actual[18]', '$task2actual[21]', '$task2actual[22]', '..., '$task2actual[24]', '$task2actual[29]', '$task2actual[33]','$task2actual[38]' )";

mysql_query($insertmember) or die(mysql_error());

}

Your lack of responsible [SQL escaping](http://bobby-tables.com/php) is terribly worrying. Please **DO NOT** do this. — tadman, Oct 28 '12 at 03:03
@tadman I SQL escape the data, before I bring it to this stage. I just didn't include it here as I didn't think it was relevant. — Tim, Oct 28 '12 at 03:11
If you're doing string interpolation to create your queries, then what you're doing is worryingly risky. You absolutely must use proper SQL placeholders to do the insertion for you or you are just one mistake away from your system being vulnerable or buggy enough to crash your app. All you should ever see is something like `?` or `:memberid` in your query string, never user supplied variables no matter how much care is taken to clean them up. This is why you should **never** be using `mysql_query` in new applications. For your own sake, switch to PDO or `mysqli`, they are much safer. — tadman, Oct 28 '12 at 06:22
@tadman I understand, I have started to covert most of my mysql queries to `mysqli`its a tedious process but I guess it has to be done. Thank you, for pointing it out, it'll also help others that may come across this post. — Tim, Oct 28 '12 at 11:32

score 3 · Accepted Answer · edited May 23 '17 at 10:25

3

by the way, UPDATE is very different from INSERT.

UPDATE - modify the existing record(s) on the table.
INSERT - adds new record(s) on the table.

Your query is fine but you are doing update. But you want to insert record not to update record right? The query when you insert record looks like this,

$updateibtask2 = "INSERT INTO ibtask_task2_75beep 
                     (Trial1_tone_actual, Trial2_tone_actual, 
                      Trial3_tone_actual,...) 
                  VALUES ('$taskerror[0]', '$taskerror[1]',...)";

and your query is vulnerable with SQL Injection. Please take time to read the article below to protect against SQL injection,

Best way to prevent SQL injection in PHP?

edited May 23 '17 at 10:25

Community

1
1

answered Oct 28 '12 at 02:26

John Woo

258,903
69
498
492

Hi John, I clean the data before I input it into the database, I just didn't show it here as I didn't want to take up more space, I use a separate method before I even come to this step. It's just that, I have three other tables where I do exactly the same thing, and it works perfectly fine. I guess I'll insert the data. I do know that Update is very different from Insert. But Thank you for your help. – Tim Oct 28 '12 at 02:34
ok, well, did you check on table `ibtask_task2_75beep` that the `user_id` you were searching already existed? – John Woo Oct 28 '12 at 02:44
yes, the user_id is already inserted into the database using an INSERT INTO query – Tim Oct 28 '12 at 02:46
1

I changed the statement to `INSERT INTO`, which now works, anyways thanks for helping out. I'm sorry if I came across slightly arrogant, thanks anyway. – Tim Oct 28 '12 at 03:14

MySQL query not inserting data

1 Answers1