...

View Full Version : UPDATE SQL statement not working properly



VickP07
09-27-2011, 06:09 PM
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.

Fou-Lu
09-27-2011, 06:36 PM
Are these blocks of code in the same script? According to the form you're self posting it.

VickP07
09-27-2011, 06:48 PM
@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;
";

Fou-Lu
09-27-2011, 06:57 PM
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
or
tags to preserve the formatting.

VickP07
09-27-2011, 07:13 PM
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




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

Fou-Lu
09-27-2011, 07:26 PM
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




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


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
or
tags to preserve the formatting.

The selection MUST happen after the UPDATE, otherwise it is the previous data within it.

VickP07
09-27-2011, 08:05 PM
Got it now!!! Thank u very much for your time and help



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum