mysql update with multi fields

sarmenhb · 11-15-2008, 05:01 AM

if i have something like this

Code:

status: &nbsp;
<select name="status">
<option value="pending">pending</option>
<option value="complete">complete</option>
<option value="declined">declined</option>
</select>

<br>

deadline: <input type="text" name="deadline">

<br>

status: &nbsp;
<select name="status">
<option value="pending">pending</option>
<option value="complete">complete</option>
<option value="declined">declined</option>
</select>

<br>

deadline: <input type="text" name="deadline">

<br>

status: &nbsp;
<select name="status">
<option value="pending">pending</option>
<option value="complete">complete</option>
<option value="declined">declined</option>
</select>

<br>

deadline: <input type="text" name="deadline">

<br>

how do i update each field with mysql update if the table structure looks something like this

Code:

id -- primary key, auto_increment
hash - varchar(100)
status -- varchar(25)
deadline -- date

and the values in this table looks something like this

Code:

id (1)
hash (90j23d093209jk309j3)
status (pending)
deadline(10/10/2008)

id (2)
hash (90j23d093209jk309j3)
status (pending)
deadline(10/10/2008)

id (3)
hash (90j23d093209jk309j3)
status (pending)
deadline(10/10/2008)

just a note the purpose of hash is an md5 or sha1 conversion of the users name to track the users information from table to table. and if i have like 100 people each with a different hash value i can track their information. but the problem i'm having is how do i update the fields if i have being displayed on the page?? u would it it would probably be with the id value but how or where do i display that to track that.??

Code:

this is what the sql update query looks like that im using

$status = mysql_real_escape_string($_POST['status']);
$deadline = mysql_real_escape_string($_POST['deadline'];
$user = $_GET['user']; 
mysql_query("update tbl_userpackage set status = '$status', deadline = '$deadline' where hash = '$user'");

but when i do the update the onyl value that comes in is the last item which is in id 3 all the other stuff doesnt go through

trmbne2000 · 02-06-2009, 10:03 PM

The way I read this is that you have the 3 records, and a form where you want to be able to update the status for all three at the same time? You would need to give each input element a unique name that allows you to figure out which record it goes with.
Make your form like this:

HTML Code:

<select name="status[]">...</select>
<input type="text" name="deadline[]" />
<input type="hidden" name="ids[]" value = "1" />
<select name="status[]">...</select>
<input type="text" name="deadline[]" />
<input type="hidden" name="ids[]" value = "2" />
<select name="status[]">...</select>
<input type="text" name="deadline[]" />
<input type="hidden" name="ids[]" value = "3" />

This will create an array of the statuses, deadlines and row IDs. In your php, then use this:

Code:

for (i=0;i<count($_POST['status']);i++){
    $status = mysql_real_escape_string($_POST['status'][$i]);
    $deadline = mysql_real_escape_string($_POST['deadline'][$i];
    $user = $_GET['user']; 
    $id = floatval($_POST['ids'][$i]);
    mysql_query("update tbl_userpackage set status = '$status', deadline = '$deadline' where hash = '$user' AND id = $id");
}

--Andrew