Page 1 of 1

[PHP] Help with forms and sql

Posted: Fri Nov 05, 2010 11:45 am
by pryomancer
I've got a problem with some PHP code I'm writing.

The aim is for the user to enter the id of a song from a database.
The page then reloads with the information of that song displayed automatically in a form. The user can then edit what they need and submit it. That form passes the info to another page which executes the sql update query.

I've got the first two pages done, where the user enters an id, and then the page reloads to display the autofilled form.
But it doesn't actually update the records. I've been stuck on it for a number of hours now, I can't find the problem.

Here's the code for the page that reads in the id and displays the autofilled form:

Code: Select all

<?php
//Connect to database
$con = mysql_connect("localhost", "root");

mysql_select_db("dfti");

$a = $_POST["id"];

$result = mysql_query ("select * from hits where id='$a'");
while($row = mysql_fetch_array($result))
{
    echo
    "<table><br /><br />
    <a href=\"updatedetails.html\">Back to Update Details</a>
    
    <p>
    <tr>
    <td> ID </td>
    <td> Song </td>
    <td> Artist </td>
    <td> Year </td>
    <td> Amount </td>
    <td> Price </td>
    <td> Genre </td>
    </tr>
    
    <tr>
    <td> $row[ID] <br /><br /></td>
    <td> $row[song] <br /><br /></td>
    <td> $row[artist] <br /><br /></td>
    <td> $row[year] <br /><br /></td>
    <td> $row[amount] <br /><br /></td>
    <td> $row[price] <br /><br /></td>
    <td> $row[genre] <br /><br /></td>
    </tr>

    </p>
    </table>";
    
}
mysql_close($con);
?>


<?php
$a = $_POST["id"];
//Connect to database
$con = mysql_connect("localhost", "root");

mysql_select_db("dfti");

$result1 = mysql_query ("select * from hits where id='$a'");
$row1 = mysql_fetch_array($result1) ?>


<table>
<form method="post" action="updatedetails2.php">
<tr><td>ID:</td> <td><input name="id" type="hidden" id="id" value="<?php echo "$row1[ID]";?>"></td><td><?php echo "$row1[ID]";?><br /></td></tr>
<tr><td>Song:</td> <td><input name="song" type="text" id="song" value="<?php echo "$row1[song]";?>"><br /></td></tr>
<tr><td>Artist:</td> <td><input name="artist" type="text" id="artist" value="<?php echo "$row1[artist]";?>"><br /></td></tr>
<tr><td>Year:</td> <td><input name="year" type="text" id="year" value="<?php echo "$row1[year]";?>"><br /></td></tr>
<tr><td>Amount:</td> <td><input name="amount" type="text" id="amount" value="<?php echo "$row1[amount]";?>"><br /></td></tr>
<tr><td>Price:</td> <td><input name="price" type="text" id="price" value="<?php echo "$row1[price]";?>"><br /></td></tr>
<tr><td>Genre:</td> <td><input name="genre" type="text" id="genre" value="<?php echo "$row1[genre]";?>"></td></tr>
<tr><td><input type="submit" value="Confirm"/></td></tr>                                                    
</form>
</table>
This code outputs:
Image


This is the PHP that handles the information from this form, and executes the sql:

Code: Select all

<?php
//Connect to database
$con = mysql_connect("localhost", "root");

mysql_select_db("dfti");

$id = $_POST["id"];
$song = $_POST["song"];
$artist = $_POST["artist"];
$year = $_POST["year"];
$amount = $_POST["amount"];
$price = $_POST["price"];
$genre = $_POST["genre"];

mysql_query("UPDATE hits SET song=$song, artist=$artist, year=$year, amount=$amount, price=$price, genre=$genre WHERE id=$id");

echo "Records updated.<br /><br />";
echo "<a href=\"updatedetails.html\">Back to Update Details</a>";


mysql_close($con);
?>
The code runs, I don't get any errors, it displays the page saying 'Records updated.' So as far as I can tell the process is not erroneous. It just doesn't update the database.

Can anyone help?

Thank you in advance.

Re: [PHP] Help with forms and sql

Posted: Fri Nov 05, 2010 12:02 pm
by AbraCadaver
You need to add some error checking, at least while developing. There are better ways but the easiest is to add it to the query line:

Code: Select all

mysql_query("UPDATE hits SET song=$song, artist=$artist, year=$year, amount=$amount, price=$price, genre=$genre WHERE id=$id")
 or die(mysql_error());
Your error is most likely due to not quoting the data, especially since there are spaces in the data:

[text]SET song='$song'[/text]

Re: [PHP] Help with forms and sql

Posted: Fri Nov 05, 2010 1:24 pm
by pryomancer
Gee, that was embarrassing. You were right, it was the quotes in the sql query. :oops:

I tried that before and it didn't work, though. I must have had some other flaw that caused it to screw up.

Ah well, thank you very much for your help.