[RESOLVED] Updating database through HTML form

gopanthers

I'll try to setup the situation as briefly as possible:

I have a simple single-table database that stores personal data in a spreadsheet like style. I made a custom page within WordPress that can display every person (every table row) in this non-WordPress database.
Since this is on WordPress and every desired visitor is already logged in, I am able to display info about the currently logged in WordPress user. I'm not sure exactly how it works but the variable is $current_user->display_name'

So I successfully combined the two. With a field I created in my custom database for each person's WordPress username - wp_user - I am able to display ONLY the data from my custom database that corresponds to the person who is already logged in to WordPress. Here is the code that works fine:

$db = mysql_connect("localhost", "XXXX", "XXXX");
	mysql_select_db("XXXX",$db);

	$query = "SELECT * FROM brotherhood WHERE wp_user='$current_user->display_name'"; 
	$result = mysql_query ($query);

	if (@mysql_num_rows($result)) 
	{ 
	print "<table border=\"1\" >\n"; 

	print "<tr> 
		<td>First Name</td>
		<td>Last Name</td>
		<td>Copy of WordPress Display Name</td>
	</tr>\n"; 

	while($row = mysql_fetch_array($result)) { 

	print "<tr>\n";  

	    print "<td>".$row['firstname']."</td>\n";    

	    print "<td>".$row['lastname']."</td>\n";    

	    print "<td>".$row['wp_user']."</td>\n";     

	    print "</tr>\n";
	}
	print "</table>\n"; 

	} else { echo "<p>Sorry, no records were found!</p>"; }

So that code above works fine for simply displaying. But my final goal is not to simply display the logged-in user's data (from my custom, non-WordPress database), but to allow them to edit their own data in that database. So now I switch gears...

I have some simple code that works fine for updating data from my custom database but I just need to modify it and I haven't been successful so far. This code below starts off by listing everybody in the database (each row). When you click on a person's name it then reloads the same page with a value for "id" within the url (like ?id=119), which then displays a form so that that the data for that chosen person is pre-populated in the form and can be edited and updated in the browser. The first thing I need to modify is that I don't need to first list everybody in the database. I want it to go immediately to the editng form. The second thing I need to modify is that the person's data that will be edited/updated needs to be determined by the user who's already logged in to WordPress and not the value of "id" within the URL (like ?id=119). I tried to modify this code several times but it didn't work, and hopefully somebody can tell me what I did wrong.

First the code as it worked in a different situation:

[code=php]<?php

$db = mysql_connect("localhost", "XXXXX", "XXXXX"); 
mysql_select_db("XXXXX",$db);

if ($id) {
  if ($submit) {
    $sql = "UPDATE brotherhood SET firstname='$firstname', lastname='$lastname', nickname='$nickname', graduationyear='$graduationyear' WHERE id=$id";
    $result = mysql_query($sql);
    echo "Thank you! Information updated.\n";

  } else {
    $sql = "SELECT * FROM brotherhood WHERE id=$id";
    $result = mysql_query($sql);	
    $myrow = mysql_fetch_array($result);
    ?>

<form method="post" action="<?PHP echo $PHP_SELF?>"> 
<input type=hidden name="id" value="<?php echo $myrow["id"] ?>">
	First Name: <input type="Text" name="firstname" value="<?php echo $myrow["firstname"] ?>"><br /> 
	Last Name: <input type="Text" name="lastname" value="<?php echo $myrow["lastname"] ?>"><br />
	Nickname: <input type="Text" name="nickname" value="<?php echo $myrow["nickname"] ?>"><br /> 
	Graduation Year: <input type="Text" name="graduationyear" value="<?php echo $myrow["graduationyear"] ?>"><br />
<input type="Submit" name="submit" value="Process Information"> 

	<?php
	}
} else {

  // display list again
  $result = mysql_query("SELECT * FROM brotherhood ORDER BY lastname",$db);
  while ($myrow = mysql_fetch_array($result)) {
    printf("<a href=\"%s?id=%s\">%s %s</a><br />\n", $PHP_SELF, $myrow["id"], $myrow["lastname"], $myrow["firstname"]);
  }
    echo "<p><a href=\"/\">Click here</a> to go home.\n";
}
?>[/code]

OK, so that code above works fine in another location and for a different purpose. I now have to modify it to only edit/update the data of the person who's already logged in to WordPress, and make it have nothing to do with values passed along through the URL. Here's my best attempt below. I've added comments to tell you what I tried changing and why.

[code=php]<?php

$db = mysql_connect("localhost", "XXXXX", "XXXXX"); 
mysql_select_db("XXXXX",$db);

// before it said >>> if ($id) {  <<< but I just removed this line
  if ($submit) {
    $sql = "UPDATE brotherhood SET firstname='$firstname', lastname='$lastname', nickname='$nickname', graduationyear='$graduationyear' WHERE wp_user='$current_user->display_name'";
    // Above, I replaced >>> WHERE id=$id <<< and replaced it with what should designate the data that matches the logged in user
    $result = mysql_query($sql);
    echo "Thank you! Information updated.\n";

  } else {

    $sql = "SELECT * FROM brotherhood WHERE wp_user='$current_user->display_name'";
    // Above, I replaced >>> WHERE id=$id <<< and replaced it with what should designate the data that matches the logged in user
    $result = mysql_query($sql);	
    $myrow = mysql_fetch_array($result);
    ?>

<form method="post" action="<?PHP echo $PHP_SELF?>">

<input type=hidden name="wp_user" value="<?php echo $myrow["wp_user"] ?>">

First Name: <input type="Text" name="firstname" value="<?php echo $myrow["firstname"] ?>"><br />
Last Name: <input type="Text" name="lastname" value="<?php echo $myrow["lastname"] ?>"><br />
Nickname: <input type="Text" name="nickname" value="<?php echo $myrow["nickname"] ?>"><br />
Graduation Year: <input type="Text" name="graduationyear" value="<?php echo $myrow["graduationyear"] ?>"><br />
<input type="Submit" name="submit" value="Process Information">

	<?php
	// I removed this bracket >>> } <<< because I think it goes with that "if" statement I already removed earlier.


  // I removed this whole "display list again" section, from } else { to }


    echo "<p><a href=\"/\">Click here</a> to go home.\n";
}
?>[/code]

The result of the code I tried above is that it shows me the form fields and they are properly pre-populated with the data that is already in the database. However when I hit the submit button ("Process Information") it just seems to reload the same page and disregards any changes I attempted to make, re-populating the form with the data that was already in the database. If it worked anything like the original code, it shouldn't have shown me the same form again, and instead it should have displayed the confirmation message of "Thank you! Information updated". But it didn't update the info or display the confirmation message. I think I'm close, but does anybody have any idea what I'm doing wrong here, or how to fix it. Thanks so much for any help.

gopanthers

anyone, please?

psuplat

From just a quick look your problem lies in the UPDATE query.

UPDATE brotherhood SET firstname='$firstname',...

you're using variable that don't exist. I think what you should do is:

if ($submit) {
$firstname = $_GET['firstname'];
$lastname = $_GET['lastname'];
$nickname = $_GET['nickname'];
$graduationyear = $_GET['graduationyear'];

//---you can echo those values for testing purposes, to see if they are being ctually passed

echo $firstname."<br>";
echo $lastname."<br>";
echo $nickname."<br>";
echo $graduationyear."<br>";


$sql = "UPDATE brotherhood SET firstname='$firstname', lastname='$lastname',....

}

this is a very rough draft, it may not work but it should put you in the right direction

best of luck playing with it

bradgrafelman

Note that $submit is undefined as well, and that the form's method is 'POST' and not 'GET'.

Also note that user-supplied data should never be placed directly into a SQL query string else your code will be vulnerable to SQL injections and/or just plain SQL errors. Instead, you must first sanitize the data with a function such as [man]mysql_real_escape_string/man (for string data).

psuplat

hehe, like I said quick look 😃

and of course your right the $submit is undefined as well and I didn't notice the method was set to post

gopanthers

Thanks. I've been fiddling with this for a while now. I do have different results but still not working right.

First, the fact that $submit was undefined makes sense to me but what doesn't make sense is that $submit is used in the original code (the 2nd block of code from my top post) and it works fine. Not sure how but it works.

In any case, I tried changing the code from if ($submit) to if(isset($Submit). Now when I click the submit button it at least gives me the nice confirmation message that the information was updated. Unfortunately the outcome is that it ERASED all the data in the database for that person that being submitted by the form. In this example, firstname, lastname, nickname, and graduationyear were pre-filled into the form based on existing data, but each of those data fields were erased after submitting the form, whether I left the existing value alone or changed it form my testing.

So it seems like the form is now submitting "something" to the database, but unfortunately that "something" is literally nothing instead of the values entered in the form. Perhaps I'm a step closer but still not working quite right.

As for the POST vs GET, not knowing the difference until just now when I looked them up, it seems to me that POST should be the desired method. Don't both work the same but GET just throws the data into the URL? (I shortened my examples for simplicity. In reality I have 2 dozen fields to update.) And just like my puzzlement on the $submit question, since it worked in my original code shouldn't POST continue to work?

Thanks

bradgrafelman

gopanthers wrote:
what doesn't make sense is that $submit is used in the original code

The original code probably relied on the infamous register_globals directive, a directive that was long ago deprecated and disabled by default (see this man page to know why: [man]security.globals[/man]).

gopanthers wrote:
Unfortunately the outcome is that it ERASED all the data in the database for that person that being submitted by the form.

Can you show us a new copy of what your code looks like now? Also, did you note the error regarding $_GET that I pointed out above?

gopanthers

bradgrafelman;10969503 wrote:
Can you show us a new copy of what your code looks like now? Also, did you note the error regarding $_GET that I pointed out above?

Here's my latest version:

	<?php

$db = mysql_connect("localhost", "XXXXX", "XXXXX");
mysql_select_db("XXXXX",$db);

  if(isset($_POST['submit']))  {

	$sql = "UPDATE brotherhood SET firstname='$firstname', lastname='$lastname', nickname='$nickname', graduationyear='$graduationyear' WHERE id=$id";
	$result = mysql_query($sql);
		echo "Thank you! Information updated.  <br /><p><a href=\"http://xxxxxxxxxxxx\">Click here</a> to reload this same page for testing purposes.</p>\n";

} else {

	// query the DB
	$sql = "SELECT * FROM brotherhood WHERE wp_user='$current_user->display_name'";
	$result = mysql_query($sql);	
	$myrow = mysql_fetch_array($result);
	?>

	Database ID: <?php echo $myrow["id"] ?>

	<form method="post" action="<?php echo $PHP_SELF?>"> 
	<input type="hidden" name="id" value="<?php echo $myrow["id"] ?>">
		First Name: <input type="Text" name="firstname" value="<?php echo $myrow["firstname"] ?>"><br /> 
		Last Name: <input type="Text" name="lastname" value="<?php echo $myrow["lastname"] ?>"><br />
		Nickname: <input type="Text" name="nickname" value="<?php echo $myrow["nickname"] ?>"><br /> 
		Graduation Year: <input type="Text" name="graduationyear" value="<?php echo $myrow["graduationyear"] ?>"><br />
	<input type="Submit" name="submit" value="Update Information"> 

<?php }	?>

I'm not sure what other little things I changed but I do know that I changed the WHERE in the processing upper part (end of the 8th line) from

wp_user='$current_user->display_name'

id=$id

Not sure if that was the right move or not but studying how I think this code works, using the WordPress Username works fine for calling the right row into the form but since I'm submitting the database id field as a hidden value anyway, and since that's a truly unique field I can use that to assure that the correct row gets updated, which is how the original code worked anyway. But an interesting note is that before I changed it, submitting an info update erased all info from those fields that were being updated. This did prove that the correct fields were being updated on the correct row but unfortunately they were always being updated as completely blank (the data itself wasn't getting through). After changing the WHERE to id=$id the fields don't update as being completely blank anymore, but the changes I make still aren't getting through to update the database. This doesn't blank out data but I also can't even tell if anything is happening anymore either.

bradgrafelman

You still have the problem that psuplat mentioned back in post #3 here:

$sql = "UPDATE brotherhood SET firstname='$firstname', lastname='$lastname', nickname='$nickname', graduationyear='$graduationyear' WHERE id=$id";

You're using variables $firstname, $lastname, $nickname, $graduationyear, and $id - none of which have been previously defined.

gopanthers

bradgrafelman;10969570 wrote:
You still have the problem that psuplat mentioned back in post #3 here:
$sql = "UPDATE brotherhood SET firstname='$firstname', lastname='$lastname', nickname='$nickname', graduationyear='$graduationyear' WHERE id=$id"; 
        
You're using variables $firstname, $lastname, $nickname, $graduationyear, and $id - none of which have been previously defined.

I understood what you meant. I hadn't thought about that before because the original form just worked somehow, but the original got it's value from the URL (which wouldn't work in this case). So I tried over and over again to figure out how to define it like you said but I wasn't able to.

At a dead end (with my skills) I searched the internet for a different solution. First couple of attempts still didn't work but then I found a new solution that I was able to modify with various parts of what I was already trying. And finally I was able to get something to work. Hooray!

In case it helps others or if you were curious what is different, here's the code that I got to work. Again, this has some parts from what I was trying (like pre-populating the fields with their existing values, and choosing the row based on the logged in WordPress user) but other parts are completely new.

<?php $server = "localhost";
  $username = "XXXXX";
  $password = "XXXXX";
  $database = "XXXXX";
  $con = mysql_connect($server, $username, $password);
  mysql_select_db($database, $con); // Don't actually need to make this a variable. 

  if($_POST){ // since there a single form that can be submitted
    $sql = "UPDATE brotherhood SET 
    	firstname='".mysql_real_escape_string($_POST['firstname'])."', 
    	lastname='".mysql_real_escape_string($_POST['lastname'])."', 
    	nickname='".mysql_real_escape_string($_POST['nickname'])."', 
    	graduationyear='".mysql_real_escape_string($_POST['graduationyear'])."' 
    	WHERE wp_user='$current_user->display_name'";
    mysql_query($sql) or die(mysql_error());
echo 'Updated.';
  }

// query the DB
$sql2 = "SELECT * FROM brotherhood WHERE wp_user='$current_user->display_name'";
$result = mysql_query($sql2);	
$myrow = mysql_fetch_array($result);

?>

<form method="post" action="">
	First Name: <input type="Text" name="firstname" value="<?php echo $myrow["firstname"] ?>"><br /> 
	Last Name: <input type="Text" name="lastname" value="<?php echo $myrow["lastname"] ?>"><br />
	Nickname: <input type="Text" name="nickname" value="<?php echo $myrow["nickname"] ?>"><br /> 
	Graduation Year: <input type="Text" name="graduationyear" value="<?php echo $myrow["graduationyear"] ?>"><br />
	<input type="submit" name="change" value="Change now" />
</form>

THANK YOU for your help, including a better understanding of how my code was functioning in the first place.