update mysql table

[rthomson]

Hi, what I am trying to do is "update" a mysql table via a form post, instead of "insert into" a table.  I have a selected ID field or RID in the case of my code below...

 

<?php

$q=$_GET["q"];

$con = mysql_connect('my-host', 'my_user', 'my_pwd');
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);

$sql="SELECT *
      FROM daterange
  WHERE DEND > DATE(NOW())
  AND STATUS='A' AND MONTH = '".$q."'
      ORDER BY RID, DATE, SITE";

$result = mysql_query($sql);


// Determine the number of reservation dates
$number = mysql_numrows($result);


// Create drop-down menu of reservation dates
print "<font size=\"3\" face=\"Arial\"><b>Select Your Reservation:</b><br>
<form action=\"resersend.php\" method=\"post\">
<select name=\"RID\">
    <option value=\"\">Choose One</option>";

for ($i=0; $i<$number; $i++) {
     $RID = mysql_result($result,$i,"RID");
     $DATE = mysql_result($result,$i,"DATE");
     $SITE = mysql_result($result,$i, "SITE");
 $PRICE = mysql_result($result,$i, "PRICE");
     print "<option value=\"$RID\">$DATE, $SITE, $PRICE</option>";
}

print "</select><p align=left><label><font size=\"3\" face=\"Arial\">First Name: <input type=\"text\" name=\"FNAME\" size=\"50\" maxlength=\"50\" tabindex=\"1\"<br>";

print "<p align=left><label>Last Name: <input type=\"text\" name=\"LNAME\" size=\"50\" maxlength=\"50\" tabindex=\"2\"<br>";

print "<p align=left><label>Address Line 1: <input type=\"text\" name=\"ADDR1\" size=\"50\" maxlength=\"50\" tabindex=\"3\"<br>";

print "<p align=left><label>Address Line 2: <input type=\"text\" name=\"ADDR2\" size=\"50\" maxlength=\"50\" tabindex=\"4\"<br>";

print "<p align=left><label>City: <input type=\"text\" name=\"CITY\" size=\"50\" maxlength=\"50\" tabindex=\"5\"<br>";

print "<p align=left><label>State (abbrev.): <input type=\"text\" name=\"STATE\" size=\"2\" maxlength=\"2\" tabindex=\"6\"<br>";

print "<p align=left><label>Zip Code: <input type=\"text\" name=\"ZIP\" size=\"5\" maxlength=\"5\" tabindex=\"7\"<br>";

print "<p align=left><label>Contact Phone Number: (<input type=\"text\" name=\"PHONE1\" size=\"3\" maxlength=\"3\" tabindex=\"8\"";
print "<label>)<input type=\"text\" name=\"PHONE2\" size=\"3\" maxlength=\"3\" tabindex=\"9\"";
print "<label>-<input type=\"text\" name=\"PHONE3\" size=\"4\" maxlength=\"4\" tabindex=\"10\"<br>";

print "<p align=left><label>Email: <input type=\"text\" name=\"EMAIL\" size=\"50\" maxlength=\"50\" tabindex=\"11\"<br>";
print "<p align=left><input type=\"submit\" value=\"Book Now!\"
    name=\"submit\">";

print "<input type=\"reset\" value=\"reset\"
    name=\"reset\"></form>";


// Close the database connection
mysql_close($con);
?>

 

Can someone help me update the selected RID field with the remainder of the fields from the form above, point me in the right direction?  Thanks.

[gizmola]

You get the value of form submissions in the $_POST[] superglobal array.

 

An update statement is generally:

 

UPDATE table SET column="...some value" WHERE ... some criteria

 

 

[phpJoeMo]

Hi,

 

I cleaned up the HTML.  Be sure to use CSS for all stying.  Avoid font tags to the greatest extent possible!!!

 

I have it configured to echo out a sample query.  You'll need to add the WHERE clause to the UPDATE query:

<?php

$q=$_GET["q"];

$con = mysql_connect('my-host', 'my_user', 'my_pwd');
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);

/*MAKE SURE TO VALIDATE ALL DATA!*/

function Check_post($val)
{
return (isset($_POST[$val])?$_POST[$val]:false);
}
/*THIS IS CONFIGURED TO DEMAND ALL INPUT*/
$inputs = array(
'FNAME',
'LNAME',
'ADDR1',
'ADDR2',
'CITY',
'STATE',
'ZIP',
'PHONE1',
'PHONE2',
'PHONE3',
'EMAIL'
);
$valuesToUpdate[];
foreach ($inputs as $key) {
$postValue = Check_post($key);
if ( $postValue ) {
	$valuesToUpdate[$key] = $postValue
}
}
$setData = '';
if (count($valuesToUpdate)) { 
$c = 0;
foreach ( $valuesToUpdate as $field => $value ) {

	$setData .= ($c>0?', ':'')."$field = '$value'";
	++c;
}
}
if ( $setData ) {
echo "
	UPDATE mytable SET $setData
";
}


$sql="SELECT *
      FROM daterange
  WHERE DEND > DATE(NOW())
  AND STATUS='A' AND MONTH = '".$q."'
      ORDER BY RID, DATE, SITE";

$result = mysql_query($sql);


// Determine the number of reservation dates
$rows = mysql_fetch_array($result);

ob_start();
?>
<b>Select Your Reservation:</b><br>
<form action="resersend.php" method="post">
<select name="RID">
    <option value="">Choose One</option>

<?php
foreach ($rows as $b) {
     $RID = $b["RID"];
     $DATE = $b["DATE"];
     $SITE = $b["SITE"];
 $PRICE = $b["PRICE"];
     echo "<option value='$RID'>$DATE, $SITE, $PRICE</option>";
}
?>

</select>
<input type="text" name="FNAME" size="50" maxlength="50" tabindex="1"/><br>

Last Name: <input type="text" name="LNAME" size="50" maxlength="50" tabindex="2"/><br>

Address Line 1: <input type="text" name="ADDR1" size="50" maxlength="50" tabindex="3"/><br>

Address Line 2: <input type="text" name="ADDR2" size="50" maxlength="50" tabindex="4"/><br>

City: <input type="text" name="CITY" size="50" maxlength="50" tabindex="5"/><br>

State (abbrev.): <input type="text" name="STATE" size="2" maxlength="2" tabindex="6"/><br>

Zip Code: <input type="text" name="ZIP" size="5" maxlength="5" tabindex="7"/><br>

Contact Phone Number: (<input type="text" name="PHONE1" size="3" maxlength="3" tabindex="8"/>)<br>
<input type="text" name="PHONE2" size="3" maxlength="3" tabindex="9"/><br>
<input type="text" name="PHONE3" size="4" maxlength="4" tabindex="10"/><br>

Email: <input type="text" name="EMAIL" size="50" maxlength="50" tabindex="11"/></label><br>
<input type="submit" value="Book Now!" name="submit"><br>

<input type="reset" value="reset" name="reset">
</form>
<?php
echo ob_get_contents();
ob_clear();
?>

 

ALSO:: Never allow any $_GET or $_POST Data into a query string WITHOUT sanitizing / filtering it!

[phpJoeMo]

Were you able to figure it out?

 

If so can you please mark the thread as solved?

 

Thanks.