Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Sep 2011
    Posts
    78
    Thanks
    15
    Thanked 0 Times in 0 Posts

    UPDATE SQL statement not working properly

    Hey guys,

    So i am populating a php page with some data from the DB but i am trying to allow the user to enter in data to update a field in a table of my DB, but it isn't working.

    Here is my code for the update:

    <?php
    $db = mysql_connect( "localhost","root", "temp1234");
    mysql_select_db( "Peters_restaurantDB");

    $id = $_GET['id'];

    $sql = "
    SELECT id, name, description, preptime, totaltime, rating
    FROM recipes
    WHERE id = $id;
    ";

    if( $_POST )
    {

    $rate = $_POST['rating'];

    $sql5 = "
    UPDATE recipes
    SET rating = $rate
    WHERE id = $id;
    ";

    $result5 = mysql_query( $sql5 )
    or die( "Insert failed ($sql5): " . mysql_error() );

    }

    ?>

    HEre is where i am allowing the user to enter in a new "rating" for the recipe being displayed on the page (ShowRecipe.php):


    <form action="ShowRecipe.php?id=<?echo $id?>" method="POST">

    <table>

    <tr><td align="right">Enter new Rating: </td>
    <td><input type="text" name="rating"></td>
    </tr>

    </table>

    <input type="submit" value="Update Rating">

    </form>

    Right now the problem is that after i enter in a new raiting in the textbox and click submit.....the page refreshes itself but the update is not working and the data is not being changed.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Are these blocks of code in the same script? According to the form you're self posting it.

  • #3
    New Coder
    Join Date
    Sep 2011
    Posts
    78
    Thanks
    15
    Thanked 0 Times in 0 Posts
    @Fou-Lu:

    Okay i got the update working properly now buttttttttt when i click the submit button the data is not being displayed with the new update.

    This is my new if statement:
    <?
    if( isset($_POST['rating']) )
    {

    $rate = $_POST['rating'];

    $sql5 = "
    UPDATE recipes
    SET rating = $rate
    WHERE id = $id;
    ";

    $result5 = mysql_query( $sql5 )
    or die( "Update failed ($sql5): " . mysql_error() );

    }

    ?>

    And here is where i am allowing the user to enter in a new "Rating"
    <form action="ShowRecipe.php?id=<?echo $id?>" method="POST">
    <table>

    <tr><td align="right">Enter new Rating: </td>
    <td><input type="text" name="rating"></td>
    </tr>

    </table>

    <input type="submit" value="Update Rating">

    </form>

    ShowRecipe.php is the same page the user is on displaying the data and then a text box to allow updates to be made to the 'rating' field in the table.

    Inside of the if statement do i need to do another sql query:
    $sql = "
    SELECT id, name, description, preptime, totaltime, rating
    FROM recipes
    WHERE id = $id;
    ";

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Displayed where; in this script?
    You need to select the record from the database and print it into the 'rating' input's value field.

    Also, please wrap your code in [code][/code] or [php][/php] tags to preserve the formatting.

  • #5
    New Coder
    Join Date
    Sep 2011
    Posts
    78
    Thanks
    15
    Thanked 0 Times in 0 Posts
    This is where i am creating a table and displaying the data prior to the update....when the user enters in a value ( 2.5) in the text box and clicks update button, i want the same page to refresh and dispplay the new value

    Code:
    <html>
    <body>
      
    <table border="2" width ="700">
    <tr>
    <th>Recipe Name</th>
    <th>Desc.</th>
    <th>Prep Time</th>
    <th>Total Time</th>
    <th>Rating</th>
    </tr>
    
    <!--display the data for the recipe that the user clicked on -->
       <tr><td><?=$row['name']?></td>
           <td><?=$row['description']?></td>
    	   <td><?=$row['preptime']?></td>
    	   <td><?=$row['totaltime']?></td>
    	   <td><?=$row['rating']?></td>
       </tr>
    </table>
    
    <form action="ShowRecipe.php?id=<?echo $id?>" method="POST">
    <table>
    
    <tr><td align="right">Enter new Rating: </td>
        <td><input type="text" name="rating"></td>
    </tr>
    
    </table>
    
    <input type="submit" value="Update Rating">
    
    </form>

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Quote Originally Posted by VickP07 View Post
    This is where i am creating a table and displaying the data prior to the update....when the user enters in a value ( 2.5) in the text box and clicks update button, i want the same page to refresh and dispplay the new value

    Code:
    <html>
    <body>
      
    <table border="2" width ="700">
    <tr>
    <th>Recipe Name</th>
    <th>Desc.</th>
    <th>Prep Time</th>
    <th>Total Time</th>
    <th>Rating</th>
    </tr>
    
    <!--display the data for the recipe that the user clicked on -->
       <tr><td><?=$row['name']?></td>
           <td><?=$row['description']?></td>
    	   <td><?=$row['preptime']?></td>
    	   <td><?=$row['totaltime']?></td>
    	   <td><?=$row['rating']?></td>
       </tr>
    </table>
    
    <form action="ShowRecipe.php?id=<?echo $id?>" method="POST">
    <table>
    
    <tr><td align="right">Enter new Rating: </td>
        <td><input type="text" name="rating"></td>
    </tr>
    
    </table>
    
    <input type="submit" value="Update Rating">
    
    </form>
    Okay, then I'll refer you back to my previous post:

    Quote Originally Posted by Fou-Lu View Post
    Displayed where; in this script?
    You need to select the record from the database and print it into the 'rating' input's value field.

    Also, please wrap your code in [code][/code] or [php][/php] tags to preserve the formatting.
    The selection MUST happen after the UPDATE, otherwise it is the previous data within it.

  • Users who have thanked Fou-Lu for this post:

    VickP07 (09-27-2011)

  • #7
    New Coder
    Join Date
    Sep 2011
    Posts
    78
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Got it now!!! Thank u very much for your time and help


  •  

    Posting Permissions

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