Have something to say? Contribute New Article Reply to this Article
Update MYSQL Database with a PHP/HTML Form
Hi all,
I am very new to PHP and MYSQL and have a class assignment I need help with. I am trying to make a page with an HTML form that will update my MYSQL database. You can view my pages online here: http://baileyjumper.aisites.com/Scripting-Week7/homework4/exercise-five.php
I need help with the "Edit" section, if you look online.
Here is the code for my Edit page:
<?php
$hostname = "localhost";//host name
$dbname = "baileyjumper_imd203";//database name
$username = "baileyjumper_imd";//username you use to login to php my admin
$password = "chris4ever";//password you use to login
//CONNECTION OBJECT
//This Keeps the Connection to the Databade
$conn = new MySQLi($hostname, $username, $password, $dbname) or die('Can not connect to database')
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>
<body>
<?php
//Create a query
$sql = "SELECT * FROM Books WHERE BookID = '".$bookid."'";
//submit the query and capture the result
$result = $conn->query($sql) or die(mysql_error());
$query=getenv(QUERY_STRING);
parse_str($query);
//$ud_title = $_POST['Title'];
//$ud_pub = $_POST['Publisher'];
//$ud_pubdate = $_POST['PublishDate'];
//$ud_img = $_POST['Image'];
?>
<h2>Update Record <?php echo $bookid;?></h2>
<form action="" method="post">
<?php
while ($row = $result->fetch_assoc()) {?>
<table border="0" cellspacing="10">
<tr>
<td>Title:</td> <td><input type="text" name="updatetitle" value="<?php echo $row['Title']; ?>"></td>
</tr>
<tr>
<td>Publisher:</td> <td><input type="text" name="updatepublisher" value="<?php echo $row['Publisher']; ?>"></td>
</tr>
<tr>
<td>Publish Date:</td> <td><input type="text" name="updatepubdate" value="<?php echo $row['PublishDate']; ?>"></td>
</tr>
<tr>
<td>Image URL:</td> <td><input type="text" name="updateimg" size="100" value="<?php echo $row['Image']; ?>"></td>
</tr>
<tr>
<td><INPUT TYPE="Submit" VALUE="Update the Record" NAME="Submit"></td>
</tr>
</table>
<?php }
?>
</form>
<?php
if(isset($_POST['Submit'])){//if the submit button is clicked
$update = $_POST['updatepubdate'];
$query="UPDATE Books SET PublishDate=$update where BookID = '".$bookid."'";
//$query = "UPDATE Books WHERE BookID = '".$bookid."'";//update the database query
mysql_query($query) or die("Cannot update");//update or error
}
?>
</body>
</html>
I don't have the form currently redirecting to another page because right now I can't even get the update to work. But, I eventually want to display a confirmation message either on the same or a different page.
Thank You!!!!
What specific error messages are you getting?
at a quick glance I can advise the following:
You will probably need to surround your SET variables with quotes, ie:
"... SET PublishDate='$update' ..."Also, assuming that PublishDate is a datetime field of some sort in your database, you will need to ensure the data being input is in a format that MySQL can convert to a date, such as "yyyy-mm-dd"
The Date is just a VARCHAR right now in the database. When I click the submit button, this error comes up:
Warning: mysql_query() [function.mysql-query]: Access denied for user 'apache'@'localhost' (using password: NO) in /ebs/home/baileyjumper/domains/baileyjumper.aisites.com/public_html/Scripting-Week7/homework4/edit.php on line 81
Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in /ebs/home/baileyjumper/domains/baileyjumper.aisites.com/public_html/Scripting-Week7/homework4/edit.php on line 81
Cannot update
Before clicked there are no errors.
OK... it looks like you are using MySQLi to handle the connection, should you therefore be using mysqli_query to perform the update operation?
On line 79 use $conn->query(...) as you did in your other queries. Use the mysqli object you create at the top of your script.
Ok thanks! Now no error but it is still not updating the record.
<?php
$hostname = "localhost";//host name
$dbname = "baileyjumper_imd203";//database name
$username = "baileyjumper_imd";//username you use to login to php my admin
$password = "chris4ever";//password you use to login
//CONNECTION OBJECT
//This Keeps the Connection to the Databade
$conn = new MySQLi($hostname, $username, $password, $dbname) or die('Can not connect to database')
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>
<body>
<?php
//Create a query
$sql = "SELECT * FROM Books WHERE BookID = '".$bookid."'";
//submit the query and capture the result
$result = $conn->query($sql) or die(mysql_error());
$query=getenv(QUERY_STRING);
parse_str($query);
//$ud_title = $_POST['Title'];
//$ud_pub = $_POST['Publisher'];
//$ud_pubdate = $_POST['PublishDate'];
//$ud_img = $_POST['Image'];
?>
<h2>Update Record <?php echo $bookid;?></h2>
<form action="" method="post">
<?php
while ($row = $result->fetch_assoc()) {?>
<table border="0" cellspacing="10">
<tr>
<td>Title:</td> <td><input type="text" name="updatetitle" value="<?php echo $row['Title']; ?>"></td>
</tr>
<tr>
<td>Publisher:</td> <td><input type="text" name="updatepublisher" value="<?php echo $row['Publisher']; ?>"></td>
</tr>
<tr>
<td>Publish Date:</td> <td><input type="text" name="updatepubdate" value="<?php echo $row['PublishDate']; ?>"></td>
</tr>
<tr>
<td>Image URL:</td> <td><input type="text" name="updateimg" size="100" value="<?php echo $row['Image']; ?>"></td>
</tr>
<tr>
<td><INPUT TYPE="Submit" VALUE="Update the Record" NAME="Submit"></td>
</tr>
</table>
<?php }
?>
</form>
<?php
if(isset($_POST['Submit'])){//if the submit button is clicked
$update = $_POST['updatepubdate'];
$query="UPDATE Books SET PublishDate=$update where BookID = '".$bookid."'";
//$query = "UPDATE Books WHERE BookID = '".$bookid."'";//update the database query
$conn->query($sql) or die("Cannot update");//update or error
}
?>
</body>
</html>Thank you! Ok, getting closer. Now I get the cannot update message.
<?php
$hostname = "localhost";//host name
$dbname = "baileyjumper_imd203";//database name
$username = "baileyjumper_imd";//username you use to login to php my admin
$password = "chris4ever";//password you use to login
//CONNECTION OBJECT
//This Keeps the Connection to the Databade
$conn = new MySQLi($hostname, $username, $password, $dbname) or die('Can not connect to database')
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>
<body>
<?php
//Create a query
$sql = "SELECT * FROM Books WHERE BookID = '".$bookid."'";
//submit the query and capture the result
$result = $conn->query($sql) or die(mysql_error());
$query=getenv(QUERY_STRING);
parse_str($query);
//$ud_title = $_POST['Title'];
//$ud_pub = $_POST['Publisher'];
//$ud_pubdate = $_POST['PublishDate'];
//$ud_img = $_POST['Image'];
?>
<h2>Update Record <?php echo $bookid;?></h2>
<form action="" method="post">
<?php
while ($row = $result->fetch_assoc()) {?>
<table border="0" cellspacing="10">
<tr>
<td>Title:</td> <td><input type="text" name="updatetitle" value="<?php echo $row['Title']; ?>"></td>
</tr>
<tr>
<td>Publisher:</td> <td><input type="text" name="updatepublisher" value="<?php echo $row['Publisher']; ?>"></td>
</tr>
<tr>
<td>Publish Date:</td> <td><input type="text" name="updatepubdate" value="<?php echo $row['PublishDate']; ?>"></td>
</tr>
<tr>
<td>Image URL:</td> <td><input type="text" name="updateimg" size="100" value="<?php echo $row['Image']; ?>"></td>
</tr>
<tr>
<td><INPUT TYPE="Submit" VALUE="Update the Record" NAME="Submit"></td>
</tr>
</table>
<?php }
?>
</form>
<?php
if(isset($_POST['Submit'])){//if the submit button is clicked
$update = $_POST['updatepubdate'];
$sql="UPDATE Books SET PublishDate=$update where BookID = '".$bookid."'";
//$query = "UPDATE Books WHERE BookID = '".$bookid."'";//update the database query
$conn->query($sql) or die("Cannot update");//update or error
}
?>
</body>
</html>don't forget you need quotes around your variable in the query, as I said previously
this is what I have now: "UPDATE Books SET PublishDate=$update where BookID = '".$bookid."'";
Put more quotes around that?
around the variable in the middle, so that MYSQL will treat it as a string value:
"UPDATE Books SET PublishDate='$update' where BookID = '".$bookid."'";
is BookID an integer? if so, you will not need the single quotes around it and your code should look like: "UPDATE Books SET PublishDate='$update' where BookID = ".$bookid;
around the variable in the middle, so that MYSQL will treat it as a string value:
"UPDATE Books SET PublishDate='$update' where BookID = '".$bookid."'";is BookID an integer? if so, you will not need the single quotes around it and your code should look like:
"UPDATE Books SET PublishDate='$update' where BookID = ".$bookid;
It works!!! Yes, it is an integer. Thank you very much, I am excited! :D
So, one more, question. How can I display a confirmation if successful. I know it would be an if statement, but if what?
there are a few ways you could do it. One might be to use the query result, like:
if ($conn->query($sql))
echo "Update Successful.";
else die("Cannot update");And what if I wanted to show the new updated information?
Thank you very much for your help!
If you move your update code php block to the top of the page, right before the SELECT, it will automatically display the updated data when the page loads after the POST.
Wonderful! It works. Now, you are probably going to kill me, but how it is set up now only allows updates to the publish date. So, how could I make the other sections update as well? The title, publisher, and image.
you just need to grab the other values from the POST and include them in your sql query
eg:
//format: $variable = $_POST['<input textbox id>'];
$pubdate = $_POST['updatepubdate'];
$title = $_POST['updatetitle'];
$publisher = $_POST['updatepublisher'];
//...etc, as many as you need...
$sql="UPDATE Books SET PublishDate='$pubdate', Title='$title', Publisher='$publisher' WHERE BookID = ".$bookid;
// check your field names are correct, and don't forget the quotes for string valuesIt works!! Thank you very very much for your expertise!
For anyone else having issues, here is my final code:
<?php
$hostname = "localhost";//host name
$dbname = "baileyjumper_imd203";//database name
$username = "baileyjumper_imd";//username you use to login to php my admin
$password = "chris4ever";//password you use to login
//CONNECTION OBJECT
//This Keeps the Connection to the Databade
$conn = new MySQLi($hostname, $username, $password, $dbname) or die('Can not connect to database')
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>
<body>
<?php
if(isset($_POST['Submit'])){//if the submit button is clicked
$pubdate = $_POST['updatepubdate'];
$title = $_POST['updatetitle'];
$publisher = $_POST['updatepublisher'];
$img = $_POST['updateimg'];
$update = "UPDATE Books SET PublishDate='$pubdate', Title='$title', Publisher='$publisher', Image='$img' WHERE BookID = ".$bookid;
$conn->query($update) or die("Cannot update");//update or error
}
?>
<?php
//Create a query
$sql = "SELECT * FROM Books WHERE BookID = '".$bookid."'";
//submit the query and capture the result
$result = $conn->query($sql) or die(mysql_error());
$query=getenv(QUERY_STRING);
parse_str($query);
//$ud_title = $_POST['Title'];
//$ud_pub = $_POST['Publisher'];
//$ud_pubdate = $_POST['PublishDate'];
//$ud_img = $_POST['Image'];
?>
<h2>Update Record <?php echo $bookid;?></h2>
<form action="" method="post">
<?php
while ($row = $result->fetch_assoc()) {?>
<table border="0" cellspacing="10">
<tr>
<td>Title:</td> <td><input type="text" name="updatetitle" value="<?php echo $row['Title']; ?>"></td>
</tr>
<tr>
<td>Publisher:</td> <td><input type="text" name="updatepublisher" value="<?php echo $row['Publisher']; ?>"></td>
</tr>
<tr>
<td>Publish Date:</td> <td><input type="text" name="updatepubdate" value="<?php echo $row['PublishDate']; ?>"></td>
</tr>
<tr>
<td>Image URL:</td> <td><input type="text" name="updateimg" size="100" value="<?php echo $row['Image']; ?>"></td>
</tr>
<tr>
<td><INPUT TYPE="Submit" VALUE="Update the Record" NAME="Submit"></td>
</tr>
</table>
<?php }
?>
</form>
<?php
if($update){//if the update worked
echo "<b>Update successful!</b>";
}
?>
</body>
</html>This question has already been solved
You
© 2012 DaniWeb® LLC
