Update using Select query

[azareth]

I can't update my database by using select .. i've tried searching for different solutions but those didn't helped me ..
any ideas why this doesn't update my DB??

i need to update the row with such conditions:
the query/queries i used

$records = mysql_query("SELECT * FROM cot_cargovolume WHERE VoyageNo = '$_POST[txtVoyageNo]'"); 
$cvCOC = array($records);
$i = 0;
while($con = mysql_fetch_array($records)){
    $cvCOC[$i] = $con['COC'];
    $i++;
}

    $CV_contents = array("$_POST[cargoVol1]","$_POST[cargoVol2]");
    for($j=0;$j<$i;$j++){
    $sql = "UPDATE cot_cargovolume SET CIC='CV_contents[$j]' WHERE VoyageNo='$_POST[txtVoyageNo]' AND COC = '$cvCOC[$j]'";

    /*ERROR Report*/
         if (!mysql_query($sql))
          {
          die('Error: ' . mysql_error());
          }
        echo "Updated - Details</br>";

    }

the first query is that i want to retrieve the data from the column coc and use it for the condition as to where i want the update to happen. the VoyageNo works properly but i need the COC condition too..

i don't get any error using this code and it says "Updated details" but it doesn't really ..
thanks for any help, greatly appreciate it ..

[pritaeas]

Echo the query before it runs, to see what it contains. Then you can run it against your database and see what happens.

[azareth]

ok i'll take that idea, though i changed my approach regarding this problem, i just retrieved the data from the DB and used it for the condition WHERE of the UPDATE .

[diafol]

I really can't see what you're trying to do here. You're retrieving all records that has a certain voyage number to get their 'coc' values.
Then you are trying to update the same recordset.
Do you need cvCOC at all? It will be the same as last time (select) anyway?
THe $j variable bothers me too. Say $i = 10 (after running in your loop 10 times), then you run $j 10 times in its own loop, BUT you have:

CV_contents[$j] in the SQL - which probably should be $CV_contents[$j] with a set of braces around it: {$CV_contents[$j]} and the variable itself only has 2 items

$CV_contents = array("$_POST[cargoVol1]","$_POST[cargoVol2]");

BTW - no need for the surrounding x2 quotes and I'd suggest wrapping the item name in single quotes. BUT as this is to be used in a mysql statement, it needs to be cleaned with something like mysql_real_escape_string().

$CV_contents = array($_POST['cargoVol1'],$_POST['cargoVol2']);

So you'll soon get an error that the array index for $CV_contents (e.g. $CV_contents[3]) does not exist.

This is pretty messy. The more I look at it, the less sense it makes.

Could you have another think and let us know what exactly you are trying to accomplish with the routine?

[azareth]

as far as i can remember i've tried using single quotes but it didn't work, so i tried to use double quotes which actually worked. but oh well, i'll take your advice, i'll rewrite my code then.

you're right btw it looks messy especially now that i've solve my problem but the thing is i don't think that this is the right approach to what i've been trying to achieve.

actually the problem is i just simply want to update a row based from the voyageNo provided by the user and based from the COC which has already been saved in the database .. (i know it's confusing, and i think i made a mistake in creating the database due to some reason and i just need to go with what i have now because i'm running out of time..)

[azareth]

variables should be cleaned using mysql_real_escape_string()
which i suppose the variable will look like this (for example)
$var = mysql_real_escape_string($var);

but what if i have a bunch of variables let's say

$portA = array(
    $_POST['CSM_blackmere_portside1_1'],$_POST['CSM_blackmere_portside2_1'],$_POST['CSM_blackmere_portside3_1'],
    $_POST['CSM_blackmere_portside4_1'],$_POST['CSM_blackmere_portside5_1'],$_POST['CSM_blackmere_portside6_1'],
    $_POST['CSM_blackmere_portside7_1'],$_POST['CSM_blackmere_portside8_1'],$_POST['CSM_blackmere_portside9_1']);
$portB = array(
    $_POST['CSM_blackmere_portside1_2'],$_POST['CSM_blackmere_portside2_2'],$_POST['CSM_blackmere_portside3_2'],
    $_POST['CSM_blackmere_portside4_2'],$_POST['CSM_blackmere_portside5_2'],$_POST['CSM_blackmere_portside6_2'],
    $_POST['CSM_blackmere_portside7_2'],$_POST['CSM_blackmere_portside8_2'],$_POST['CSM_blackmere_portside9_2']);
$portC = array(
    $_POST['CSM_blackmere_portside1_3'],$_POST['CSM_blackmere_portside2_3'],$_POST['CSM_blackmere_portside3_3'],
    $_POST['CSM_blackmere_portside4_3'],$_POST['CSM_blackmere_portside5_3'],$_POST['CSM_blackmere_portside6_3'],
    $_POST['CSM_blackmere_portside7_3'],$_POST['CSM_blackmere_portside8_3'],$_POST['CSM_blackmere_portside9_3']);
$portD = array(
    $_POST['CSM_blackmere_portside1_4'],$_POST['CSM_blackmere_portside2_4'],$_POST['CSM_blackmere_portside3_4'],
    $_POST['CSM_blackmere_portside4_4'],$_POST['CSM_blackmere_portside5_4'],$_POST['CSM_blackmere_portside6_4'],
    $_POST['CSM_blackmere_portside7_4'],$_POST['CSM_blackmere_portside8_4'],$_POST['CSM_blackmere_portside9_4']);
$portE = array(
    $_POST['CSM_blackmere_portside1_5'],$_POST['CSM_blackmere_portside2_5'],$_POST['CSM_blackmere_portside3_5'],
    $_POST['CSM_blackmere_portside4_5'],$_POST['CSM_blackmere_portside5_5'],$_POST['CSM_blackmere_portside6_5'],
    $_POST['CSM_blackmere_portside7_5'],$_POST['CSM_blackmere_portside8_5'],$_POST['CSM_blackmere_portside9_5']);
$portF = array(
    $_POST['CSM_blackmere_portside1_6'],$_POST['CSM_blackmere_portside2_6'],$_POST['CSM_blackmere_portside3_6'],
    $_POST['CSM_blackmere_portside4_6'],$_POST['CSM_blackmere_portside5_6'],$_POST['CSM_blackmere_portside6_6'],
    $_POST['CSM_blackmere_portside7_6'],$_POST['CSM_blackmere_portside8_6'],$_POST['CSM_blackmere_portside9_6']);
$portG = array(
    $_POST['CSM_blackmere_portside1_7'],$_POST['CSM_blackmere_portside2_7'],$_POST['CSM_blackmere_portside3_7'],
    $_POST['CSM_blackmere_portside4_7'],$_POST['CSM_blackmere_portside5_7'],$_POST['CSM_blackmere_portside6_7'],
    $_POST['CSM_blackmere_portside7_7'],$_POST['CSM_blackmere_portside8_7'],$_POST['CSM_blackmere_portside9_7']);

this are the cell values from the table i have in the form ..

[diafol]

You can do all these with array_map:

$post = array_map('mysql_real_escape_string', $_POST);