Page 1 of 1

Updating a record from a dynamically generated drop-down.

Posted: Thu Jan 20, 2005 10:39 am
by Frapster
I can confidently add a new record but now I want to update an existing record. I'm using the following code but it's not working. As you can see in the code I'm using a fancy-schmancy dynamically generated form list feature and I don't think there's any conflicts from that code - but I could be wrong. If you see what's wrong - any help is greatly appreciated. Thanks in advance.

In the meantime I'll work on deleting records. That should be easy.

Code: Select all

<?php
//open database
$dbhost = 'localhost';
$dbuser = 'username';
$dbpass = 'password';
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error connecting to mysql');

$dbname = 'lamps';
mysql_select_db($dbname);
$dbname = mysql_select_db($dbname) or die('Error connecting to database');
?>

<?php
if(isset($_POST&#1111;'update']))
&#123; 
$family_name = $_POST&#1111;'family_name'];
$id = $_POST&#1111;'id'];
$nameadd = $_POST&#1111;'nameadd'];

$query = "UPDATE family2 SET family_name = $nameadd" . "WHERE id = $id";
mysql_query($query) or die('Error, update failed');
&#125; 
else
&#123;
?>
<form name="update" method="post" action="fam_add_del2.php">
                    <table width="100%"  border="0" cellpadding="3" cellspacing="1" bgcolor="#666666">
                      <tr align="center" valign="top" bgcolor="#CCCCCC">
                        <td><span class="style34">Pick a Lamp Family Name </span></td>
                        <td><span class="style34">Enter Modified Name Here </span></td>
                        <td><span class="style34">Click this button to submit change. </span></td>
                      </tr>
                      <tr align="center" valign="top" bgcolor="#FFFFFF">
                        <td width="33%"><select name="fieldname">
                            <?php
// php to select drop down menu options from tablename table
$result = @mysql_query("SELECT family_name, id FROM family2 ORDER BY family_name asc");
if (!$result)
&#123;
echo("<p>Error performing query: " . mysql_error() . "</p>");
exit();
&#125;
while ( $row = mysql_fetch_array($result) )
&#123;
$searchfieldname=$row&#1111;"family_name"];
$searchfieldid=$row&#1111;"id"];
echo '<option value="' . $searchfieldid . '">' . $searchfieldname . '</option>';
&#125;
?>
                          </select></td>
                        <td width="33%"><input name="nameadd" type="text" id="nameadd"></td>
                        <td width="33%"><input name="update" type="submit" id="update" value="Update"></td>
                      </tr>
                    </table>
                  </form><?php &#125; ?>

Posted: Thu Jan 20, 2005 10:47 am
by feyd
at the least you need to do this:

Code: Select all

$query = "UPDATE family2 SET family_name = $nameadd" . " WHERE id = $id";
mysql_query($query) or die(mysql_error());
if that doesn't work, then please post the error message it should output. Also, be careful of what you blindly send to a database.. like these:

Code: Select all

$family_name = $_POST&#1111;'family_name'];
$id = $_POST&#1111;'id'];
$nameadd = $_POST&#1111;'nameadd'];
read our discussions about "addslashes AND *magic*quotes*"

Apparently it's in my 'where id = $id' area?

Posted: Thu Jan 20, 2005 11:01 am
by Frapster
This is the error I got:
You have an error in your SQL syntax near 'TESTING 3 WHERE id = ' at line 1
I understand why I'm getting that error. I know I need to essentially say "update the record where the record ID is equal to the record ID in the dropdown list". Would that look something like this?

Code: Select all

$query = "UPDATE family2 SET family_name = $nameadd" . " WHERE $searchfieldid = $id"; 
 mysql_query($query) or die(mysql_error());

Posted: Thu Jan 20, 2005 11:04 am
by feyd

Code: Select all

UPDATE `family2` SET `family_name` = '$nameadd' WHERE `$searchfieldid` = '$id'
need to be very careful with the variables there as SQL injection could be very possible.

Posted: Thu Jan 20, 2005 11:43 am
by Frapster

Code: Select all

$query = "UPDATE family2 SET family_name = '$nameadd' WHERE '$searchfieldid' = '$id'";
LOL! That worked except that it changed the field 'family_name' in every record of the database into the words 'LAMP TEST 3' which is what I entered in my field in an effort to change one record.

Does that mean I should flip-flop my $searchfieldid and $id variables? *shrug* goes off to test.

Of course now maybe after this the IT group will set up a development version of the site for me. lol

Posted: Thu Jan 20, 2005 11:47 am
by feyd
there where clause most likely equates to always true, so every record in the database would change.

You did change what I posted though.. so it could be almost anything.

I used `id`, which you switched to $searchfieldid.. :|

Posted: Thu Jan 20, 2005 11:56 am
by Frapster
argh - i got confused. anyway - I'm going to set up a test database real quick. Good thing I asked IT to back up the data last night - I fully expected something like this to happen. lol

thanks so much for your help.

In regards to the SQL injection issue - i found one discussion in the security forum that points to 'magic quotes are evil' or something like that. In that article it discusses a couple of solutions. Do you recommend the POV of that article or should I look somewhere else?

Thanks again for all your help.

Rob

Posted: Thu Jan 20, 2005 12:00 pm
by feyd
that article is a start.. however there are many issues, google "sql injection" you'll find a lot ;)

Posted: Thu Jan 20, 2005 12:02 pm
by Frapster
lol, I did, you're right. *prepares to spend the rest of tthe day reading*

SUCCESS!

Posted: Thu Jan 20, 2005 1:21 pm
by Frapster

Code: Select all

$nameadd = $_POST&#1111;'nameadd'];
$fieldname = $_POST&#1111;'fieldname'];

$query = "UPDATE family2 SET family_name = '$nameadd' WHERE '$fieldname' = id";
mysql_query($query) or die(mysql_error());
I realized that I was calling the entirely wrong variable/field name because of how the 'select' list was labeled and values assigned.

Now I have to work on figuring out how to validate the name so that duplicates are not added. Plus I have to figure out how to avoid SQL injection. This has been a great learning experience for me! Thanks so much for the help.