MySQL Update function help

[drumdogg]

Hi guys,

Trying to update all values in a table within my database but cant get it to do any. Probably something silly i have done or left out.

I have a table that will be filled in and when submit is clicked the table should be updated with the new values.

There should only be one row of data in this table as it is a specification and will be reading the values out of the table later on.

Here is the Code:

<form id="test" action="SpecUpdateInsert.php" method="POST" name="SpecUpdate">
<table>
<tr>
    <th scope="col">Sieves</th>
  </tr>
  <tr>
    <th scope="row">+163&micro;m</th>
    <td><input name="S1TS1" type="text" id="S1TS1" size="12" maxlength="12" /></td>
    <td><input name="S1TS2" type="text" id="S1TS2" size="12" maxlength="12" /></td>
  </tr>
  <tr>
    <th scope="row">-63&micro;m</th>
    <td><input name="S2TS1" type="text" id="S2TS1" size="12" maxlength="12" /></td>
    <td><input name="S2TS2" type="text" id="S2TS2" size="12" maxlength="12" /></td>
  </tr>
  
  <tr>
    <th scope="col">Sieves</th>
  </tr>
  <tr>
    <th scope="row">+163&micro;m</th>
    <td><input name="S1NC1" type="text" id="S1NC1" size="12" maxlength="12" /></td>
    <td><input name="S1NC2" type="text" id="S1NC2" size="12" maxlength="12" /></td>
    <td><input name="S1NC3" type="text" id="S1NC3" size="12" maxlength="12" /></td>
    <td><input name="S1NC4" type="text" id="S1NC4" size="12" maxlength="12" /></td>
  </tr>
  <tr>
    <th scope="row">-63&micro;m</th>
    <td><input name="S2NC1" type="text" id="S2NC1" size="12" maxlength="12" /></td>
    <td><input name="S2NC2" type="text" id="S2NC2" size="12" maxlength="12" /></td>
    <td><input name="S2NC1" type="text" id="S2NC1" size="12" maxlength="12" /></td>
    <td><input name="S2NC2" type="text" id="S2NC2" size="12" maxlength="12" /></td>
  </tr>
</table>
<input type="hidden" name="submitted" value="1"> 
<input name="SpecUpdateInsert" type="submit" id="SpecUpdateInsert" value="Submit Testing";/>
<input name="Reset Fields" type="reset" value="Reset Fields" />
</form>

if($_POST['submitted']==1){
	
$S1TS1 = mysql_real_escape_string($_POST['S1TS1']);
$S1TS2 = preg_replace('/[^0-9]/', '',$_POST['S1TS2']);
$S1NC1 = mysql_real_escape_string($_POST['S1NC1']);
$S1NC2 = preg_replace('/[^0-9\.]/', '', $_POST['S1NC2']);
$S1NC3 = preg_replace('/[^0-9\.]/', '', $_POST['S1NC3']);
$S1NC4 = preg_replace('/[^0-9\.]/', '', $_POST['S1NC4']);
$S2TS1 = preg_replace('/[^0-9\.]/', '', $_POST['S2TS1']);
$S2TS2 = preg_replace('/[^0-9\.]/', '', $_POST['S2TS2']);
$S2NC1 = preg_replace('/[^0-9\.]/', '', $_POST['S2NC1']);
$S2NC2 = preg_replace('/[^0-9\.]/', '', $_POST['S2NC2']);
$S2NC3 = preg_replace('/[^0-9\.]/', '', $_POST['S2NC3']);
$S2NC4 = preg_replace('/[^0-9\.]/', '', $_POST['S2NC4']);

}
 $con = mysql_connect("localhost","root","............");
 if (!$con)
   {
   die('Could not connect: ' . mysql_error());
   }
 
mysql_select_db("productspec", $con);
 

$query = mysql_query("UPDATE product SET S1TS1 = ".$S1TS1. " AND SET S1TS2 = ".$S1TS2);
 
mysql_close($con);

I have only tried to update the first two values in the table to see if it would work.

Could anyone help please?

Thanks

 

[AS_Platinum]

UPDATE table_name SET this=that, this2=that2

hth

 

[drumdogg]

UPDATE table_name SET this=that, this2=that2

Yea i have tried this and no joy. Have tried it this way too and didnt work.

$query =("UPDATE product SET S1TS1 = '".$S1TS1."', S1TS2 = '".$S1TS2."'");

 

[Muffherwell]

Have you tried...

$query =("UPDATE product SET S1TS1 = $S1TS1, S1TS2 = $S1TS2");

Also, not sure seeing as you only have one line of data in the table, whether you would need to tell the update where to put the data, for example....


$query =("UPDATE product SET S1TS1 = $S1TS1, S1TS2 = $S1TS2 where lineID = line ID");

 

[drumdogg]

Have you tried...

$query =("UPDATE product SET S1TS1 = $S1TS1, S1TS2 = $S1TS2");

Also, not sure seeing as you only have one line of data in the table, whether you would need to tell the update where to put the data, for example....


$query =("UPDATE product SET S1TS1 = $S1TS1, S1TS2 = $S1TS2 where lineID = line ID");

Yea i have tired the above with no luck

 

[Muffherwell]

Have you tried echoing the query to see exactly what the code is you are trying to update the table with?

 

[Daveangle]

Been a long time since I touched MySQL but are you not just putting the update statement into another string variable.

$query = mysql_query("UPDATE product SET S1TS1 = ".$S1TS1. " AND SET S1TS2 = ".$S1TS2);

Have you tried removing the $query and just using mysql_query.

 

[sb89]

Depending on the type of the columns, won't you need to put quotations in the query around the variables?

 

[FragnaticDeath]

try this $query = mysql_query("UPDATE product SET S1TS1 ='{$S1TS1}' AND SET S1TS2 = '{$S1TS2}'");

 

[kwerk]

use commas instead of AND SET for a start.

$query = "UPDATE product SET S1TS1 = '$S1TS1', S1TS2 = '$S1TS2'";
mysql_query($query) or die('Error: '. mysql_error());

 

[Stryda09]

You are not executing the query so it won't do anything?

Edit: Looks like I was beaten to it. Well maybe not as his query above does not specify what row you want updating you will need a where in the query directing it to the correct row to up date.

 

[kwerk]

oh yeah it needs to know which row to update, or are you trying to INSERT a new row?

 

[visibleman]

Have you tried echoing the query to see exactly what the code is you are trying to update the table with?

Follow kwerk's advise (although you should really use graves arounds table/col names), then echo the query, run it via PHPMyAdmin etc and see what result you get.

And the WHERE condition is optional, although you'd be applying the update to all rows.

 

[drumdogg]

OK all thanks for the help and advice. I have now got the issue sorted.

I removed the $query and just left mysql_query and also realised that the table that i was trying to update was empty!! So once i put something into it the UPDATE function worked. Cant believe it was something stupid like that!! lol

Again thats for the help guys