TalkPHP - mysql update with multi fields

TalkPHP (http://www.talkphp.com/forums.php)

- Absolute Beginners (http://www.talkphp.com/absolute-beginners/)

- - mysql update with multi fields (http://www.talkphp.com/absolute-beginners/3610-mysql-update-multi-fields.html)

mysql update with multi fields

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

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