Jump to content

Getting Deeper... Displaying Records To Edit


bigsilk
 Share

Recommended Posts

Ok, so I have a form to enter data into a db. But soon 2000 new records are going into this db and there is going to have be a way to search for, retrieve and edit records individually.I assume I'll use the SELECT FROM WHERE, but the tutorial explains it as WHERE is hard-coded into the request. The SELECT and FROM are given, of course.Baby steps.Can I use the same form used to create a new record in the db to display a record for editing? Or does it need to be a different form?This is the code from the tutorial. How do I make it so I can enter the WHERE and SUBMIT it to query the db and return the results in a form for editing?

<?php$con = mysql_connect("localhost","peter","abc123");if (!$con) { die('Could not connect: ' . mysql_error()); }mysql_select_db("my_db", $con);$result = mysql_query("SELECT * FROM PersonsWHERE FirstName='Peter'");while($row = mysql_fetch_array($result)) { echo $row['FirstName'] . " " . $row['LastName']; echo "<br />"; }?>
Here's an example of the form I want to be able to edit the record with.View the formIt's the input form. So, what I'm looking to do is to be able to type in a unique identifier (username), click SUBMIT to find the record, and have it populate the form fields for editing and then allow the user to UPDATE the record. Edited by bigsilk
Link to comment
Share on other sites

You can get the value they entered from the $_POST array, like other form data. So your page can check $_POST['username'], for example, and if it's non-empty then it would add the username to the WHERE clause in the statement and collect all the values. When you write out the form you would also try to write each of the values, which would only be populated if they searched for something (i.e., if they didn't search it would show a blank form). e.g.:

$username = '';$firstname = '';$lastname = '';if (!empty($_POST['username'])){  $result = mysql_query('SELECT * FROM users WHERE username=\'' . mysql_real_escape_string($_POST['username']) . '\'');  if ($row = mysql_fetch_assoc($result))  {	$username = $row['username'];	$firstname = $row['firstname'];	$lastname = $row['lastname'];  }  else	echo 'Username not found.';}...<input type="text" name="firstname" value="<?php echo $firstname; ?>">

If they entered a username that matched a record in the database, it will output the firstname field in that input, or else it will use the default blank value and show a blank form instead.

Link to comment
Share on other sites

I'm a simple caveman.I don't know where to put the input field for the query itself. Here's what I've got:

<html><body><?php$LawFirmName = '';$MainOfficeAddress = '';$MainOfficeCity = '';$MainOfficeState = '';$MainOfficeZip = '';$MainOfficePhone = '';$MainOfficeEmail = '';$WebAddress = '';$Overview = '';$YearsofExperience = '';$Languages = '';$GeographiesServing = '';$PracticeAreas = '';$MainAttorneyName = '';$MainAttorneyPhone = '';$MainAttorneyEmail = '';if (!empty($_POST['users'])){  $result = mysql_query('SELECT * FROM users WHERE users=\'' . mysql_real_escape_string($_POST['users']) . '\'');  if ($row = mysql_fetch_assoc($result))  {$LawFirmName = $row['LawFirmName'];$MainOfficeAddress = $row['MainOfficeAddress'];$MainOfficeCity = $row['MainOfficeCity'];$MainOfficeState = $row['MainOfficeState'];$MainOfficeZip = $row['MainOfficeZip'];$MainOfficePhone = $row['MainOfficePhone'];$MainOfficeEmail = $row['MainOfficeEmail'];$WebAddress = $row['WebAddress'];$Overview = $row['Overview'];$YearsofExperience = $row['YearsofExperience'];$Languages = $row['Languages'];$GeographiesServing = $row['GeographiesServing'];$PracticeAreas = $row['PracticeAreas'];$MainAttorneyName = $row['MainAttorneyName'];$MainAttorneyPhone = $row['MainAttorneyPhone'];$MainAttorneyEmail = $row['MainAttorneyEmail'];} else  echo 'Username not found.';}<input type="text" name="LawFirmName" value="<?php echo $LawFirmName; ?>"><input type="text" name="MainOfficeAddress" value="<?php echo $MainOfficeAddress; ?>"><input type="text" name="MainOfficeCity" value="<?php echo $MainOfficeCity; ?>"><input type="text" name="MainOfficeState" value="<?php echo $MainOfficeState; ?>"><input type="text" name="MainOfficeZip" value="<?php echo $MainOfficeZip; ?>"><input type="text" name="MainOfficePhone" value="<?php echo $MainOfficePhone; ?>"><input type="text" name="MainOfficeEmail" value="<?php echo $MainOfficeEmail; ?>"><input type="text" name="WebAddress" value="<?php echo $WebAddress; ?>"><input type="text" name="Overview" value="<?php echo $Overview; ?>"><input type="text" name="YearsofExperience" value="<?php echo $YearsofExperience; ?>"><input type="text" name="Languages" value="<?php echo $Languages; ?>"><input type="text" name="GeographiesServing" value="<?php echo $GeographiesServing; ?>"><input type="text" name="PracticeAreas" value="<?php echo $PracticeAreas; ?>"><input type="text" name="MainAttorneyName" value="<?php echo $MainAttorneyName; ?>"><input type="text" name="MainAttorneyPhone" value="<?php echo $MainAttorneyPhone; ?>"><input type="text" name="MainAttorneyEmail" value="<?php echo $MainAttorneyEmail; ?>">?></body></html>

Link to comment
Share on other sites

It really should be a separate form. People can choose between creating a new record or editing an existing one. I know I'll have to add an UPDATE somewhere on the editable form, but that can wait. For now, just getting the fields to populate with the 'users' record data will be victory enough. Here's what I've got now for the form to enter the search value:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"><head>	<title>Profile</title>	<link href="CSS/profile.css" rel="stylesheet" type="text/css">	<meta http-equiv="content-type" content="text/html;charset=utf-8">	<meta http-equiv="Content-Style-Type" content="text/css"></head><body><div class="centered_div"><div class="header">My Will Made Easy - Attorney Profile</div><div class="profile_input"><form action="retrieve.php" method="post">  User name: <input type="text" size="90" name="users"></form></body></html>

And the PHP:

<html><body><?php$con = mysql_connect("localhost","***","***");if (!$con)  {  die('Could not connect: ' . mysql_error());  }mysql_select_db("bigsilkd_attorneys", $con);$users = '';$LawFirmName = '';$MainOfficeAddress = '';$MainOfficeCity = '';$MainOfficeState = '';$MainOfficeZip = '';$MainOfficePhone = '';$MainOfficeEmail = '';$WebAddress = '';$Overview = '';$YearsofExperience = '';$Languages = '';$GeographiesServing = '';$PracticeAreas = '';$MainAttorneyName = '';$MainAttorneyPhone = '';$MainAttorneyEmail = '';if (!empty($_POST['users'])){  $result = mysql_query('SELECT * FROM users WHERE users=\'' . mysql_real_escape_string($_POST['users']) . '\'');  if ($row = mysql_fetch_assoc($result))  {$LawFirmName = $row['LawFirmName'];$MainOfficeAddress = $row['MainOfficeAddress'];$MainOfficeCity = $row['MainOfficeCity'];$MainOfficeState = $row['MainOfficeState'];$MainOfficeZip = $row['MainOfficeZip'];$MainOfficePhone = $row['MainOfficePhone'];$MainOfficeEmail = $row['MainOfficeEmail'];$WebAddress = $row['WebAddress'];$Overview = $row['Overview'];$YearsofExperience = $row['YearsofExperience'];$Languages = $row['Languages'];$GeographiesServing = $row['GeographiesServing'];$PracticeAreas = $row['PracticeAreas'];$MainAttorneyName = $row['MainAttorneyName'];$MainAttorneyPhone = $row['MainAttorneyPhone'];$MainAttorneyEmail = $row['MainAttorneyEmail'];} else  echo 'Username not found.';}?><input type="text" name="LawFirmName" value="<?php echo $LawFirmName; ?>"> <br /><input type="text" name="MainOfficeAddress" value="<?php echo $MainOfficeAddress; ?>"> <br /><input type="text" name="MainOfficeCity" value="<?php echo $MainOfficeCity; ?>"> <br /><input type="text" name="MainOfficeState" value="<?php echo $MainOfficeState; ?>"> <br /><input type="text" name="MainOfficeZip" value="<?php echo $MainOfficeZip; ?>"> <br /><input type="text" name="MainOfficePhone" value="<?php echo $MainOfficePhone; ?>"> <br /><input type="text" name="MainOfficeEmail" value="<?php echo $MainOfficeEmail; ?>"> <br /><input type="text" name="WebAddress" value="<?php echo $WebAddress; ?>"> <br /><input type="text" name="Overview" value="<?php echo $Overview; ?>"> <br /><input type="text" name="YearsofExperience" value="<?php echo $YearsofExperience; ?>"> <br /><input type="text" name="Languages" value="<?php echo $Languages; ?>"> <br /><input type="text" name="GeographiesServing" value="<?php echo $GeographiesServing; ?>"> <br /><input type="text" name="PracticeAreas" value="<?php echo $PracticeAreas; ?>"> <br /><input type="text" name="MainAttorneyName" value="<?php echo $MainAttorneyName; ?>"> <br /><input type="text" name="MainAttorneyPhone" value="<?php echo $MainAttorneyPhone; ?>"> <br /><input type="text" name="MainAttorneyEmail" value="<?php echo $MainAttorneyEmail; ?>"> <br /></body></html>

When I try to run it, I get this:Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/bigsilkd/public_html/will/retrieve.php on line 33Username not found.And line 33 is:

if ($row = mysql_fetch_assoc($result))

Edited by bigsilk
Link to comment
Share on other sites

If you're making a dedicated search page, we can clean this up. There's not really a reason to show a blank form if the user wasn't found, might as well show an error message instead. Maybe even show the same search form again.

<html><body><?php$con = mysql_connect("localhost","***","***");if (!$con)  {  die('Could not connect: ' . mysql_error());  }mysql_select_db("bigsilkd_attorneys", $con);if (!empty($_POST['users'])){  $result = mysql_query('SELECT * FROM users WHERE users=\'' . mysql_real_escape_string($_POST['users']) . '\'');  if ($row = mysql_fetch_assoc($result))  {	?><input type="text" name="LawFirmName" value="<?php echo $row['LawFirmName']; ?>"> <br /><input type="text" name="MainOfficeAddress" value="<?php echo $row['MainOfficeAddress']; ?>"> <br /><input type="text" name="MainOfficeCity" value="<?php echo $row['MainOfficeCity']; ?>"> <br /><input type="text" name="MainOfficeState" value="<?php echo $row['MainOfficeState']; ?>"> <br /><input type="text" name="MainOfficeZip" value="<?php echo $row['MainOfficeZip']; ?>"> <br /><input type="text" name="MainOfficePhone" value="<?php echo $row['MainOfficePhone']; ?>"> <br /><input type="text" name="MainOfficeEmail" value="<?php echo $row['MainOfficeEmail']; ?>"> <br /><input type="text" name="WebAddress" value="<?php echo $row['WebAddress']; ?>"> <br /><input type="text" name="Overview" value="<?php echo $row['Overview']; ?>"> <br /><input type="text" name="YearsofExperience" value="<?php echo $row['YearsofExperience']; ?>"> <br /><input type="text" name="Languages" value="<?php echo $row['Languages']; ?>"> <br /><input type="text" name="GeographiesServing" value="<?php echo $row['GeographiesServing']; ?>"> <br /><input type="text" name="PracticeAreas" value="<?php echo $row['PracticeAreas']; ?>"> <br /><input type="text" name="MainAttorneyName" value="<?php echo $row['MainAttorneyName']; ?>"> <br /><input type="text" name="MainAttorneyPhone" value="<?php echo $row['MainAttorneyPhone']; ?>"> <br /><input type="text" name="MainAttorneyEmail" value="<?php echo $row['MainAttorneyEmail']; ?>"> <br />	<?php  }  else  {	echo 'Username not found.';	?><div class="profile_input"><form action="retrieve.php" method="post">  User name: <input type="text" size="90" name="users" value="<?php echo $_POST['users']; ?>"></form>	<?php  }}else{  echo 'You must enter a user';}?></body></html>

The warning message means that the query failed. You can use the mysql_error function to print the error message:$result = mysql_query('SELECT * FROM users WHERE users=\'' . mysql_real_escape_string($_POST['users']) . '\'') or exit(mysql_error());

Link to comment
Share on other sites

The error message coming back is:Table 'bigsilkd_attorneys.users' doesn't existBut I know the table exists because I'm able to add records to it. At least 'bigsilkd_attorneys' exists. Does '.users' indicate the column? Because if so, I know that column exists. Or is it trying to look for a table named 'bigsilkd_attorneys.users'?Here's an img of the db:attorneys.jpgAs you can see, the table exists, as does the field 'users'.

Edited by bigsilk
Link to comment
Share on other sites

? do you have 'user' table? is your sql "SELECT * FROM users WHERE users"....OR 'bigsilkd_attorneys' table"SELECT * FROM bigsilkd_attorneys WHERE users"....if you have an 'user' field in multiple tables, and are searching through these tables, you identify, which specific table and 'user' field data you require, by supplying table name then field reference. Table| Fieldbigsilkd_attorneys.usersusers.users

Edited by dsonesuk
Link to comment
Share on other sites

I got it. I was trying to pull from db 'bigsilkd_attorneys' and table 'bigsilkd_attorneys', where the table name was actually just 'attorneys'.YAY!Now I have to be able to update the fields if someone changes info in them. I'll see if I can't figure that out on my own (for a little while, anyway :))

Link to comment
Share on other sites

Then you have not been supplying the table reference correctly, you have been using$result = mysql_query('SELECT * FROM users WHERE users=\''The first reference of users relates to Table, and the second reference relates to field/column.according to what i can gather:database = bigsilkd_attorneystable = bigsilkd_attorneysfield/column = usersso SQL should be$result = mysql_query('SELECT * FROM bigsilkd_attorneys WHERE users=\''

Link to comment
Share on other sites

Then you have not been supplying the table reference correctly, you have been using$result = mysql_query('SELECT * FROM users WHERE users=\''The first reference of users relates to Table, and the second reference relates to field/column.according to what i can gather:database = bigsilkd_attorneystable = bigsilkd_attorneysfield/column = usersso SQL should be$result = mysql_query('SELECT * FROM bigsilkd_attorneys WHERE users=\''
Actually, it'sdb=bigsilkd_attorneystable=attorneyscolumn=usersIt works now...
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...