Update with WHERE not working?

Question

For some reason I cannot get my database to update when I use a WHERE statement. I'm new to all of this, but in my brain it should work. Any help would be greatly appreciated. I only echoed the $stu_dent to see if it worked.

        <?php

include 'connect.php';

    print_r($_POST);

// Get values from form 
$resp=$_POST['responsibility'];
$org=$_POST['organization'];
$ind=$_POST['independentwork'];
$coll=$_POST['collaboration'];
$init=$_POST['initiative'];
$self=$_POST['selfregulation'];

$sql= "SELECT * FROM studentlist";

$result=mysqli_query($connect, $sql);


while($row = mysqli_fetch_array($result)) {

$stu_dent=$row['Student'];

 $sql1 = "UPDATE studentlist 
    SET responsibility='$resp', organization='$org', independentwork='$ind', 
    collaboration='$coll', initiative='$init', selfregulation='$self' WHERE Student='$stu_dent'";

    $result1=mysqli_query($connect, $sql1);

}





// if successfully insert data into database, displays message "Successful". 
if($result1){



echo $sql1;

echo "Successful";


}

else {
echo "ERROR";
}
?> 

<?php 
// close connection 
mysqli_close($connect);
?>

Here's what I get after the the print_r statement...and the echo $sql1

   Array
(
    [responsibility] => G
    [organization] => G
    [independentwork] => G
    [collaboration] => G
    [initiative] => G
    [selfregulation] => G
)
UPDATE studentlist 
    SET responsibility='G', organization='G', independentwork='G', 
    collaboration='G', initiative='G', selfregulation='G' WHERE Student='STALEY, PETER DOUGLA'Successful<br />
<br />
<br />
<br />

The name is actually the last name in the database, and not the name I tried to update.

You should use a prepared statement so that the content of your variables cannot break your sql. And to avoid sql injection... — jeroen, Jun 10 '14 at 16:35
This code is vulnerable to SQL injection. See [How can I prevent SQL injection in PHP](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Since you're learning, now is a great time to learn to properly use [`prepare()/execute()`](http://www.php.net//manual/en/mysqli.prepare.php) in MySQLi so you never start out with bad habits. — Michael Berkowski, Jun 10 '14 at 16:35
I'm trying to get more than one student. I'm using checkboxes and I want to update the database for each student that has boxes checked...if that makes sense — user3006141, Jun 10 '14 at 16:38
@user3006141 Ok that makes sense - I don't see anything in this code that receives those checkboxes though. — Michael Berkowski, Jun 10 '14 at 16:38
I just echoed $sql1 and it is taking the last student in the list, but all of the records are being updated — user3006141, Jun 10 '14 at 16:41
@MichaelBerkowski -- I'm not sure what you mean by "receives the checkboxes" -- I have another page that has the checkboxes with name as responsibility, organization, independentwork...and on this page I am getting those values with $_POST --- am I way out in left field??? — user3006141, Jun 10 '14 at 16:45
@user3006141 I mean that among all your inputs for `responsibility, organization, etc`, you don't appear to have any which identify a studen. You said "_I want to update the database for each student that has boxes checked_" and I took that to mean you had a list of students with checkboxes, and those selected should be updated, but the code above has no provision for handling student checkboxes (only the properties like responsibility,organization, etc) — Michael Berkowski, Jun 10 '14 at 16:52
@MichaelBerkowski -- I think I understand what you are saying, although I'm not sure if I know how to do that. I've tried using IF ISSET but that still didn't work. — user3006141, Jun 10 '14 at 16:58
Forgive my stupidity, but I'm not sure what you're asking...but I will past what I have. I removed the echo student part... — user3006141, Jun 10 '14 at 17:06
@MichaelBerkowski -- alright, I updated the post. Also I tried this a while back with a different question if you want to see that post. http://stackoverflow.com/questions/24020446/how-do-update-a-specific-row-using-checkboxes — user3006141, Jun 10 '14 at 17:09
I see you've echo'd out `$sql1`. Place `print_r($_POST);` at the top of your script and run it; it will format the full contents of `$_POST` into your output. Look at the browser's page source (ctl-U/cmd-U) and copy/paste the `print_r()` output into your question so we can get a look at what values were sent to the script. It would also help to post the HTML `
` which posts to this script. — Michael Berkowski, Jun 10 '14 at 17:12
@MichaelBerkowski -- ok, I've edited it (I really thank you for your help with this...I have been stumped for weeks!!!) — user3006141, Jun 10 '14 at 17:19
@keiv.fly -- alright, I've updated with print_r and echo $sql1 — user3006141, Jun 10 '14 at 17:31
Only the last row is affected. The name does look like that in the database, but that is not the one I tried to update. I tried to update the first row, but the last row was affected. — user3006141, Jun 10 '14 at 17:49
It doesn't look like the checkboxes you posted have any relation to any specific user. Perhaps you should generate the HTML form with checkboxes like `` The values in the `$_POST` would intrinsically carry the student id. — Michael Berkowski, Jun 10 '14 at 17:57
@MichaelBerkowski -- I tried doing what you said I changed it to but the result was that it said array in the database. — user3006141, Jun 10 '14 at 18:47
You would need to reorganize some code if you use that method. View `print_r($_POST);` to see the new structure of the array. The checkboxes would be sent as an array, indexed by student id. — Michael Berkowski, Jun 10 '14 at 18:54
@MichaelBerkowski -- could you please give me an example of what you mean... — user3006141, Jun 10 '14 at 19:02
@user3006141 In your other question, you are building checkboxes in a loop. Use the student id to differentiate them `echo "";` That will associate each checkbox with a student. You can loop over them with `foreach ($_POST['student'] as $Student => $properties) { // perform UPDATE with $Student in WHERE... }` wherein `$Student` holds the id, and `$properties` is an array like `$properties['responsibility'], $properties['organization']`.. Inspect with `print_r($_POST)` to understand its structure. — Michael Berkowski, Jun 11 '14 at 01:12
@MichaelBerkowski -- THANK YOU SO MUCH!!!! It took a little fiddling around trying to figure out how to work the array to get the variables set and updated, but it works beautifully. Thank you...!!!!! — user3006141, Jun 13 '14 at 17:48

score 1 · Answer 1 · answered Jun 10 '14 at 17:35

1

If you do not run the update query inside the while row your variables will be set to the last value.

Alternatively: get a single row from the db or run the update query inside the while loop to update each row.

answered Jun 10 '14 at 17:35

Lelio Faieta

6,457
7
40
74

I moved the update inside the while but it's still not working...I feel like I am missing something simple and small. – user3006141 Jun 10 '14 at 17:40
We're getting closer. As of right now, it's updating the database with the right information, just the wrong row. It's updating the last row, but I need it to update the selected row. – user3006141 Jun 10 '14 at 17:43
Put $result1=mysqli_query($connect, $sql1); inside the while loop – keiv.fly Jun 10 '14 at 17:47
code has been updated to what I have right now...it's updating the last row as opposed to the row I've selected. – user3006141 Jun 10 '14 at 17:51
This is exactly what your code is supposed to do with the info you submit. YOu do not retrieve the list of checked student but select all the students and update each of them. – Lelio Faieta Jun 10 '14 at 17:55
so how do you retrieve the list of checked students then?? – user3006141 Jun 10 '14 at 17:56
you have ckeckboxes, right? each of them has the value of a student. Retrieve the array of the selected checkboxes, do a while on the array and run the update (in other words replace the while from the select with this one). You don't need the select at all – Lelio Faieta Jun 10 '14 at 17:58

Update with WHERE not working?

1 Answers1