SitePoint Sponsor

User Tag List

Results 1 to 16 of 16

Hybrid View

  1. #1
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Retrieve records and update MySQL table

    Hi everyone,

    I'm returning some records from a MySQL table. I'm looping out a name, then an empty text input (which will be used to update the name), and then a price. The SELECT query might return any number of names and prices. Now the problem at the moment is that I can enter new names into the form but only the last name I enter will be added to the table replacing all of the names with the same name. Clearly, I don't want this to happen. Each text input must only replace the name associated with it. Do I need to add some counter (
    PHP Code:
    name=['name'][$i]; 
    ) ?

    Could someone please help me?



    Thank you!!

    PHP Code:
    if ($num 0) { 
    while (
    $row mysqli_fetch_array($rMYSQLI_ASSOC)) { 
    print
    '<p>'.$row['name'].'</p>
    <input type="text" name="name" size="" maxlength="" value="'
    ;
    if (isset(
    $_POST['name'])) echo $_POST['name'];
    print
    '"  /> </p>';

    print
    '<p>'.$row['price'].'</p>';
    }
    }


    if (
    $_SERVER['REQUEST_METHOD'] == 'POST') {    
          
    $phpdummy mysqli_real_escape_string($dbctrim($_POST['name'])); 
    $q "UPDATE  table1 INNER JOIN table2 USING (some_id) SET  name='$phpdummy' WHERE user_id = {$_SESSION['user_id']}  "

  2. #2
    SitePoint Evangelist
    Join Date
    Aug 2006
    Location
    Nantwich, Cheshire
    Posts
    439
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Yes, that's your problem - all your input text fields are called 'name'. If you right-click the form when it's displayed you'll see that you just have a row of inputs all called the same thing. I'd probably do something like:

    Code:
    if ($num > 0) {  
    $cnt = 0;
    while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {  
    $fldname = "name" . $cnt;
    print '<p>'.$row['name'].'</p> 
    <input type="text" name="' . $fldname . '" size="" maxlength="" value="'; 
    if (isset($_POST['name'])) echo $_POST['name']; 
    print'"  /> </p>'; 
    print'<p>'.$row['price'].'</p>'; 
    } 
    }
    Not sure where your record ID is coming from but that's why they're all being set to the same thing - it must always use the last value for duplicate variable names, just like if you have duplicate column names in your query you'll get the last value.
    http://www.firenza.net - my homage to a car from the 1970s

  3. #3
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi droopsnoot,

    thank you for the info! I tried the code but could not yet get it to work. How would I re-work this section?

    PHP Code:
    if ($_SERVER['REQUEST_METHOD'] == 'POST') {    
          
    $phpdummy mysqli_real_escape_string($dbctrim($_POST['name'])); 
    $q "UPDATE  table1 INNER JOIN table2 USING (some_id) SET  name='$phpdummy' WHERE user_id = {$_SESSION['user_id']}  "


    Thanks

  4. #4
    SitePoint Evangelist
    Join Date
    Aug 2006
    Location
    Nantwich, Cheshire
    Posts
    439
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I don't think you can, as your table doesn't have enough information. You'll need to have another field that contains the record id and also has an incrementing fieldname so you can find it. Then you'd probably need some kind of loop:

    Code:
    $process = 0;
    while ($process < $cnt) {
       $q = "update tablename set name = " .$_POST['name' . $process] . " where recordid = " . $_POST['id' . $process];
       // execute the query, not sure of the mysqli syntax
       $process += 1;
       }
    You'd maybe want to store the original value somewhere and only do the update if the name has changed. Obviously I don't know your record structure so I don't know whether the query would be more complex than this. And I have done any data checking which you would have to do, either with a prepared statement or escape_string or whatever.
    http://www.firenza.net - my homage to a car from the 1970s

  5. #5
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi droopsnoot,

    thanks a lot for getting back to me. I appreciate the help.

    The table I'm updating has an auto-incremented primary key (name_id) column and a (name) column. I'll probably end up updating multiple tables and doing validation etc. For now I'm just trying to get it to work at all.

    The code you provided looks helpful - will give it a try. Thanks

  6. #6
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi guys,

    I have not been able to make the updates function as intended. Is it feasbile to use a while loop to loop a query as in the code below?

    PHP Code:
    $process 0;
    while (
    $process $cnt) {
       
    $q "update tablename set name = " .$_POST['name' $process] . " where recordid = " $_POST['id' $process];
       
    // execute the query, not sure of the mysqli syntax
       
    $process += 1;
       } 
    Where does the
    PHP Code:
    $_POST['id' $process
    come from? Must I add an id='''' to the text input and add the same type of code as in
    PHP Code:
    name="' . $fldname . '" 
    ? And does the "where recordid" conditional refers to the auto-incremented primary key (name_id) column?




    I really want this to work. Thank you in advance!

  7. #7
    SitePoint Evangelist
    Join Date
    Aug 2006
    Location
    Nantwich, Cheshire
    Posts
    439
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Yes, in your original output loop each line will have to have the id (primary key) as well as the name. Then you can loop through all the names, get the id field for the corresponding name field, and update the database. Probably didn't help that my example above never incremented the field name counter suffix, but you probably noticed that, also I didn't pass through the value of 'count' to do the second loop.

    Code:
    if ($num > 0) {  
    print '<form method="post" action="whatever.php">';
    $cnt = 0;
    while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {  
      $fldname = "name" . $cnt;
      $idname = "id" . $cnt;
      print '<p>'.$row['name'].'</p> 
      <input type="hidden" name="' . $idname . '" value="' . $row['name_id'] .'> 
      <input type="text" name="' . $fldname . '" size="" maxlength="" value="'; 
      echo $row['name'];  // this is the name from the query
      print'"  /> </p>'; 
      print'<p>'.$row['price'].'</p>'; 
      $cnt +=1;
      } 
      print '<p><input type="hidden" name="count" value = "' . $cnt . '"><input type="submit"></p></form>';
    }
    so the above should give you a table that has the name, price and a hidden var for each field that contains the id, assuming your id is in $row['name_id'] in the table, something like:

    Code:
    <form method="post" action="whatever.php">
    <p>Jet Black</p>
    <input type="hidden" name="id0" value="1001">
    <input type="text" name="name0" value="Jet Black">
    </p>
    <p>15.75</p>
    <p>Dave Greenfield</p>
    <input type="hidden" name="id1" value="1002">
    <input type="text" name="name1" value="Dave Greenfield">
    </p>
    <p>18.95</p>
    <input type="hidden" name="count" value="2">
    <input type="submit">
    </form>
    As you see, it's created fields with different names for each name entry (which it wouldn't have in my first example code as I didn't increment $cnt). Then to store the updated data, in your whatever.php:

    Code:
    $process = 0; 
    $cnt = $_POST['count'];
    while ($process < $cnt) { 
       $q = "update tablename set name = " .$_POST['name' . $process] . " where name_id = " . $_POST['id' . $process]; 
       // execute the query, not sure of the mysqli syntax 
       $process += 1; 
       }
    So it will run around that loop twice, for values 0 and 1 in the example, retrieve name0 and id0, update the table, then retrieve name1 and id1, and update the table, then end the loop. As I mentioned, it would probably be nice to add a feature to only update if the name has changed.

    Also obviously some sanitising of data is needed, I haven't bothered with that in the example. Looking again at your first post, you seem to loop around a query but then try to display the 'name' field from $_POST, wouldn't that be from $row[] instead? I have assumed so in this post though I didn't notice it first time around.
    http://www.firenza.net - my homage to a car from the 1970s

  8. #8
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi droopsnoot,

    I must apologize for getting back to you only now. I have been unable to access my computer for the past few days and it might be another week before I can test your code. Thank you again for helping me! You really have gone out of your way to assist me with this issue!

    Will post back after I'm able to try out the code. Once more, sorry for the late reply.

  9. #9
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi droopsnoot,

    so I've had an opportunity to test the code but somehow I cannot get it to work. Just to confirm, my name_id column consists of numbers from 1 upwards and the name column consists of names.

    Could the problem be with the query?

    PHP Code:
    $process 0
    $cnt $_POST['count'];
    while (
    $process $cnt) { 
       
    $q "update tablename set name = " .$_POST['name' $process] . " where name_id = " $_POST['id' $process]; 
       
    // execute the query, not sure of the mysqli syntax 
       
    $process += 1
       } 

    I'm unsure of where

    PHP Code:
    $_POST['id'
    is coming from because there's no id="" in the form. Perhaps someone can figure this out since my PHP skills aren't that great.

    Thank you very much in advance!

  10. #10
    SitePoint Evangelist
    Join Date
    Aug 2006
    Location
    Nantwich, Cheshire
    Posts
    439
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    There are two groups of variables in the form, one hidden one called 'id' and suffixed with the count variable, so they will be called 'id0', 'id1' and so on. The other is called 'name' and also suffixed with the count variable, so 'name0', 'name1'. The 'id' fields equate to your column 'name_id', and the 'name' fields equate to your column 'name'. Finally the form contains the value of the count, so that when posted, we know how many of each field there are.

    In the second section of code, we retrieve the count variable, then do a loop with the value starting from zero in the variable $process, building a query each time. In the query, we set your column 'name' to be equal to $_POST['name' . $process] where your column 'name_id' is equal to $_POST['id' . $process]. So in the first loop that query will read:

    Code:
    update tablename set name='Jet Black' where name_id = '1001'
    and the second time around the loop, it will read:

    Code:
    update tablename set name='Dave Greenfield' where name_id = '1002'
    When you say "cannot get it to work", precisely what is happening? Do you get error messages, or does nothing happen? Have you added in the bit where you actually execute the query, as I left it out above as I can't remember the syntax for mysqli query execution. If none of this helps, could you post your complete code for the page that draws the table, and the page that updates the database?
    http://www.firenza.net - my homage to a car from the 1970s

  11. #11
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi droopsnoot,

    thank you for your continued support. I didn't expect to get such a fast reply!

    Ok, here is my code:


    PHP Code:


    $q 
    "SELECT name_id, name FROM testupdate  ";
        
    $r = @mysqli_query($dbc$q);   
      
        
    $num mysqli_num_rows($r);
    if (
    $num 0) {  
    print 
    '<form method="post" action="">';
    $cnt 0;
    while (
    $row mysqli_fetch_array($rMYSQLI_ASSOC)) {  
      
    $fldname "name" $cnt;
      
    $idname "id" $cnt;

      print 
    '<p>'.$row['name'].'</p> 
      <input type="hidden" name="' 
    $idname '" value="' $row['name_id'] .'"> 
      <input type="text" name="' 
    $fldname '" size="" maxlength="" value="'
      echo 
    $row['name'];  // this is the name from the query
      
    print'"  /> '
      
    $cnt +=1;
      } 
      print 
    '<p><input type="hidden" name="count" value = "' $cnt '"><input type="submit"></p></form>';
    }

     
        
    if (
    $_SERVER['REQUEST_METHOD'] == 'POST') {    
          

          
    $process 0
    $cnt $_POST['count'];
    while (
    $process $cnt) {
       
    $q "UPDATE testupdate SET name={$_POST['name' $process]} WHERE name_id = {$_POST['id' $process]}   ";    
       
       
    $process += 1;
       }     
          
           
          
        
                
    $r = @mysqli_query($dbc$q);
                
                if (
    mysqli_affected_rows($dbc) == 1) {} 


    I don't receive any error codes, but nothing is happening. I have done updates before, which is why I can't understand why the query doesn't work.

  12. #12
    SitePoint Evangelist
    Join Date
    Aug 2006
    Location
    Nantwich, Cheshire
    Posts
    439
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    My intention was that you'd run the query statement within the loop, you have after the loop. It needs to be run once for each value/id pair.

    Code:
    $process = 0;  
    $cnt = $_POST['count']; 
    while ($process < $cnt) { 
       $q = "UPDATE testupdate SET name={$_POST['name' . $process]} WHERE name_id = {$_POST['id' . $process]}   ";     
       $r = mysqli_query($dbc, $q); 
       $process += 1; 
       }
    That said, it should have run once, but only for the last entry in the table.

    Also note, the @ symbol will suppress any error messages, so whil you're developing / learning, it might be worth leaving it out.
    http://www.firenza.net - my homage to a car from the 1970s

  13. #13
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, it's still not working.

    Adding

    PHP Code:
    $r mysqli_query($dbc$q); 
    to the while loop hasn't made any differemce.

  14. #14
    SitePoint Evangelist
    Join Date
    Aug 2006
    Location
    Nantwich, Cheshire
    Posts
    439
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    OK, instead of that line, add:

    Code:
    echo $q;
    and post the results, let's see what the query is.

    One thing just occurred to me - I use PDO and the prepare() and bindColumn() functions to put parameters into queries, and that means I don't have to worry about quotes. If the queries look like they might be OK - try one in phpmyadmin to see if you get an error - it might be a quotes-related thing. The more I think about it, the more I imagine that's it, especially if the names are multiple words split by spaces.
    http://www.firenza.net - my homage to a car from the 1970s

  15. #15
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hallelujah, it's finally working. Echoing out the query proved to be useful since it showed that there weren't any quotation marks surrounding
    PHP Code:
    {$_POST['name' $process]} 
    and
    PHP Code:
     {$_POST['id' $process]} 
    Perhaps I should echo out my queries more often...

    Thanks a lot for sticking around and helping me with this problem!

  16. #16
    SitePoint Evangelist
    Join Date
    Aug 2006
    Location
    Nantwich, Cheshire
    Posts
    439
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    No problem, glad it's OK now.
    http://www.firenza.net - my homage to a car from the 1970s


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •