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 is terribly worrying. Please DO NOT do this. — tadman
– tadman, Commented Oct 28, 2012 at 3: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
– Tim, Commented Oct 28, 2012 at 3: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
– tadman, Commented Oct 28, 2012 at 6:22
@tadman I understand, I have started to covert most of my mysql queries to mysqliits 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
– Tim, Commented Oct 28, 2012 at 11:32

Community · Accepted Answer · 2017-05-23 10:25:10Z

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, 2017 at 10:25

CommunityBot

11 silver badge

answered Oct 28, 2012 at 2:26

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

4 Comments

Tim Over a year ago

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.

John Woo Over a year ago

ok, well, did you check on table ibtask_task2_75beep that the user_id you were searching already existed?

Tim Over a year ago

yes, the user_id is already inserted into the database using an INSERT INTO query

Tim Over a year ago

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.

Collectives™ on Stack Overflow

MySQL query not inserting data

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related