How to UPDATE a column using an array

Question

I have a table whose primary key is a column named St_ID. I want to update another column in that same (ID) using values stored in an array. But when I try the code below, the result is a new record with an St_ID value of '0' and all other columns are empty.

Note, courseID is a value chosen through a drop down list. Do you have any idea where I went wrong?

for ($i = 0; $i < $count; $i++){
   $Student = $foo[$i];
   $res = mysql_query("SELECT St_ID FROM student WHERE St_ID='$Student' ");
   while($row = mysql_fetch_array($res))
   {
      $sql = "INSERT INTO student (ID) VALUES
      ('" . $_POST[$row['courseID']] . "')";
   }
}
if (!mysql_query($sql,$connectdb))
{
   die ('Error :'.mysql_error());
}
echo "The Students are add to the course <br />";

You want update or insert as new ?

safarov
– safarov

2012-04-01 08:33:51 +00:00
Commented Apr 1, 2012 at 8:33 — safarov
– safarov, Commented Apr 1, 2012 at 8:33

safarov · Accepted Answer · 2012-04-01 10:45:29Z

2

Here simplified code, with only one query

$where = "'".implode("','", $foo)."'";
$res = mysql_query("UPDATE student set ID = courseID WHERE St_ID IN ($where)") 
         or die('Error :'.mysql_error());

 echo "The Students are add to the course <br />";

edited Apr 1, 2012 at 10:45

answered Apr 1, 2012 at 8:44

safarov

7,7942 gold badges38 silver badges52 bronze badges

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

5 Comments

wimh Over a year ago

+1, but I would prefer the IN operator instead of .. or ... or ..

Sara S'h Over a year ago

well , it did work but the opposite ! I want to add the courseID (is tha ID) not the St_ID , what in foo is the selected St_ID that i want to add the courseID to or update this selected records with ,

Sara S'h Over a year ago

yes , to update the recored with the selected Sct_ID with this value

Sara S'h Over a year ago

I got this error ! do you have anyidea why ? Error :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 'WHERE St_ID IN (2011)' at line 1 (while 2011 is the selected St_ID )

safarov Over a year ago

@SaraS'h please check the whole query. use die("UPDATE student set ID = courseID WHERE St_ID IN ($where)") to see the whole query

Haim Evgi · Accepted Answer · 2012-04-01 08:33:16Z

0

you select St_ID but try to insert courseID

in this line

$sql = "INSERT INTO student (ID) VALUES
     ('" . $_POST[$row['courseID']] . "')";

answered Apr 1, 2012 at 8:33

Haim Evgi

126k46 gold badges222 silver badges227 bronze badges

Comments

wimh · Accepted Answer · 2012-04-01 08:35:46Z

0

SELECT St_ID FROM student WHERE St_ID='$Student' 
INSERT INTO student (ID) VALUES ...

If you want to update that record you chose, you must use the UPDATE sql command instead;

UPDATE student 
SET ID=...
WHERE St_ID='$Student'

answered Apr 1, 2012 at 8:35

wimh

15.3k6 gold badges50 silver badges100 bronze badges

Collectives™ on Stack Overflow

How to UPDATE a column using an array

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related