1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL Update function help

Discussion in 'HTML, Graphics & Programming' started by drumdogg, 23 Jan 2013.

  1. drumdogg

    Hitman

    Joined: 6 Mar 2009

    Posts: 495

    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:

    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>
    Code:
    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
     
    Last edited: 23 Jan 2013
  2. AS_Platinum

    Wise Guy

    Joined: 5 Jun 2004

    Posts: 1,330

    Location: Hythe, Hants

    UPDATE table_name SET this=that, this2=that2

    hth
     
  3. drumdogg

    Hitman

    Joined: 6 Mar 2009

    Posts: 495

    Yea i have tried this and no joy. Have tried it this way too and didnt work.
    Code:
    $query =("UPDATE product SET S1TS1 = '".$S1TS1."', S1TS2 = '".$S1TS2."'");
     
  4. Muffherwell

    Associate

    Joined: 8 Mar 2012

    Posts: 18

    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");
     
  5. drumdogg

    Hitman

    Joined: 6 Mar 2009

    Posts: 495

    Yea i have tired the above with no luck:(
     
  6. Muffherwell

    Associate

    Joined: 8 Mar 2012

    Posts: 18

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

    Gangster

    Joined: 21 Aug 2008

    Posts: 199

    Location: Cambridge

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

    Code:
    $query = mysql_query("UPDATE product SET S1TS1 = ".$S1TS1. " AND SET S1TS2 = ".$S1TS2);
    Have you tried removing the $query and just using mysql_query.
     
  8. sb89

    Gangster

    Joined: 24 May 2011

    Posts: 252

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

    Gangster

    Joined: 20 Apr 2010

    Posts: 363

    Location: Kent, Canterbury

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

    Wise Guy

    Joined: 23 May 2009

    Posts: 5,748

    use commas instead of AND SET for a start.

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

    Gangster

    Joined: 28 Jun 2009

    Posts: 466

    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.
     
    Last edited: 24 Jan 2013
  12. kwerk

    Wise Guy

    Joined: 23 May 2009

    Posts: 5,748

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

    Wise Guy

    Joined: 3 Jun 2005

    Posts: 1,808

    Location: The South

    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.
     
  14. drumdogg

    Hitman

    Joined: 6 Mar 2009

    Posts: 495

    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:)