Updating MySQL Database via form

-Karl- · July 8, 2009

I have the following code:

<?php

$con = mysql_connect("localhost","-------","--------");

if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("--------", $con);

// assuming guide.php?id=2
if(isset($_GET['id'])){
   
   // first grab the id
   $id = mysql_real_escape_string($_GET['id']);
   
   // second query for that id
   $query = "SELECT `questname`,`description`,`difficulty`,`length`,`reqs`,`unlocked`,`items`,`qp`,`reward`,`start`,`instructions` FROM `quests` WHERE `id` = '$id'";
   $run = mysql_query($query);
   if($run){
      
      // third display the guide
      $arr = mysql_fetch_assoc($run);{
         echo <<<HTML

<form action="updatequest.php" method="post">
<table border="0">
<tr>
<td>
<font color="#FFFFFF" size="1"><b>Quest Name:</font></b>
</td>
<td>
<input type="text" name="questname" value="{$arr['questname']}">
</td></tr>


<td>
<font color="#FFFFFF" size="1"><b>Description:</font></b>
</td>
<td>
<input type="text" name="description" value="{$arr['description']}">
</td></tr>


<td>
<font color="#FFFFFF" size="1"><b>Difficulty:</font></b>
</td>
<td>
<input type="text" name="difficulty" value="{$arr['difficulty']}">
</td></tr>


<td>
<font color="#FFFFFF" size="1"><b>Length: </font></b>
</td>
<td>
<input type="text" name="length" value="{$arr['length']}">
</td></tr>

<td>
<font color="#FFFFFF" size="1"><b>Reqs: </font></b>
</td>
<td>
<input type="text" name="reqs" value="{$arr['reqs']}">
</td></tr>

<td>
<font color="#FFFFFF" size="1"><b>Unlocked: </font></b>
</td>
<td>
<input type="text" name="unlocked" value="{$arr['unlocked']}">
</td></tr>

<td>
<font color="#FFFFFF" size="1"><b>Items: </font></b>
</td>
<td>
<input type="text" name="items" value="{$arr['items']}">
</td></tr>

<td>
<font color="#FFFFFF" size="1"><b>Quest Points: </font></b>
</td>
<td>
<input type="text" name="qp" value="{$arr['qp']}">
</td></tr>

<td>
<font color="#FFFFFF" size="1"><b>Reward: </font></b>
</td>
<td>
<input type="text" name="reward" value="{$arr['reward']}">
</td></tr>

<td>
<font color="#FFFFFF" size="1"><b>Start: </font></b>
</td>
<td>
<input type="text" name="start" value="{$arr['start']}">
</td></tr>

<td>
<font color="#FFFFFF" size="1"><b>Instructions: </font></b>
</td>
<td>
<textarea name="instructions" cols=75 rows=30 maxlength=10000 value={$arr['instructions']}></textarea>
</td></tr>


<td>
</td>
<td>
<input type="submit" value="submit">
</td>
</tr>
</table> 
</form>

updatequest.php:

<?php
$con = mysql_connect("localhost","-----","---");
$id = mysql_real_escape_string($_GET['id']);
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("-----", $con);

$sql="UPDATE quests SET `questname` = '$_POST[questname]', `description` = '$_POST[description]', `difficulty` = '$_POST[difficulty]',`length` = '$_POST[length]',`reqs` = '$_POST[reqs]', `unlocked` = '$_POST[unlocked]',`items` = '$_POST[items]', `qp` = '$_POST[qp]', `reward` = '$_POST[reward]',`start` = '$_POST[start]',`instructions` = '$_POST[instructions]' WHERE `id` = '$id'";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "<font color='#FFFFFF' size='2'>1 record updated</font>";

mysql_close($con)
?>

The actual code works, but it doesn't update. I know why it doesn't, I'm just not sure how to go about fixing it.

It's to do with "WHERE `id` = '$id'";", obviously it doesn't know the id as it's another page, therefore it doesn't know which ID to update.

Any help is greatly appreciated.

allenskd · July 8, 2009

Hello!

First in your script, make your the request is from a POST

if(isset($_POST['NameOfTheSubmitButton'])) { ... code ... }

Anyway I noticed in updaterequest.php

$id = mysql_real_escape_string($_GET['id']);

Which I believe should be

$id = mysql_real_escape_string($_POST['id']);

var_dump the POSTS and GETS, should help you to identify these problems (on each input/submit)

-Karl- · July 8, 2009

It just returns as a blank page when I submit.

allenskd · July 8, 2009

I just noticed my own error, needed to look back twice

in updaterequest.php change it back the POST to GET and try

<form action="updatequest.php?id=<?php echo $id; ?>" method="post">

(or echo the id however you want it

when you submit it leads to updaterequest.php URL that means there is never an ID appended right?

ignace · July 8, 2009

Add this line to your form:

<input type="hidden" name="id" value="<?php print $id; ?>">

Now your updatequest.php knows the id to if you submit the form. However the next line of code isn't considered good practice:

`questname` = '$_POST[questname]'

As it assumes that questname is a constant which it clearly isn't therefor if using array's use sprintf() (also because of security reasons):

`questname` = \'%s\'

Also remove the " from the start and end of your query string as you then no longer require string parsing and use ' instead. Use as:

$fquery = sprintf($query, $_POST['questname'], ..);

-Karl- · July 8, 2009

ignace, thanks, but it still doesn't update the database.

Also, I have

<textarea name="instructions" cols=75 rows=30 value={$arr['instructions']}></textarea>

Yet it doesn't return the data properly.

If I had this in the database:

Hello blah blah <br> Hello

it would return this:

Hello>

allenskd · July 8, 2009

ignace, thanks, but it still doesn't update the database.

Also, I have
<textarea name="instructions" cols=75 rows=30 value={$arr['instructions']}></textarea>
Yet it doesn't return the data properly.

If I had this in the database:

Hello blah blah <br> Hello

it would return this:

Hello>

If you mixed my solution with ignace's solution it will not work since he hides the ID in a hidden input so it can be seen in $_POST later on, *actually his way is a bit more secure, but never let your guard down*. The solution I did was to attach the ID into the FORMs action so when you submitted the info, it would go to http://blahblah.com/updatequest.php?id=X

Anyway, just do us a favor a var_dump these variables

updaterequest.php

<?php
$con = mysql_connect("localhost","-----","---");
$id = mysql_real_escape_string($_GET['id']);

## Remove later 

echo "ID from $_POST ";
var_dump($_POST['id'])."<br />".PHP_EOL;
echo "ID from $_GET ";
var_dump($_GET['id'])."<br />".PHP_EOL;
# Remove later

if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("-----", $con);

$sql="UPDATE quests SET `questname` = '$_POST[questname]', `description` = '$_POST[description]', `difficulty` = '$_POST[difficulty]',`length` = '$_POST[length]',`reqs` = '$_POST[reqs]', `unlocked` = '$_POST[unlocked]',`items` = '$_POST[items]', `qp` = '$_POST[qp]', `reward` = '$_POST[reward]',`start` = '$_POST[start]',`instructions` = '$_POST[instructions]' WHERE `id` = '$id'";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "<font color='#FFFFFF' size='2'>1 record updated</font>";

mysql_close($con)
?>

Another tip(s), really, put some quotes inside those brackets, and htmlspecialchars the data. and take a look at Daniel's Stop using "or die()"

Sign In

Updating MySQL Database via form

Recommended Posts

-Karl-

Link to comment

Share on other sites

allenskd

Link to comment

Share on other sites

-Karl-

Link to comment

Share on other sites

allenskd

Link to comment

Share on other sites

ignace

Link to comment

Share on other sites

-Karl-

Link to comment

Share on other sites

allenskd

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information