bigsilk Posted October 19, 2009 Share Posted October 19, 2009 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. Link to comment Share on other sites More sharing options...
justsomeguy Posted October 19, 2009 Share Posted October 19, 2009 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 More sharing options...
bigsilk Posted October 19, 2009 Author Share Posted October 19, 2009 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 More sharing options...
justsomeguy Posted October 19, 2009 Share Posted October 19, 2009 I was assuming you wanted to use the same form, so you would add a field called 'users' to the existing form. You can also create a search form with only that field, which would submit to the above page. Link to comment Share on other sites More sharing options...
bigsilk Posted October 20, 2009 Author Share Posted October 20, 2009 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)) Link to comment Share on other sites More sharing options...
justsomeguy Posted October 20, 2009 Share Posted October 20, 2009 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 More sharing options...
bigsilk Posted October 20, 2009 Author Share Posted October 20, 2009 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:As you can see, the table exists, as does the field 'users'. Link to comment Share on other sites More sharing options...
dsonesuk Posted October 20, 2009 Share Posted October 20, 2009 ? 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 Link to comment Share on other sites More sharing options...
bigsilk Posted October 20, 2009 Author Share Posted October 20, 2009 It's only one table, 'bigsilkd_attorneys', and 'users' is one column. Link to comment Share on other sites More sharing options...
bigsilk Posted October 20, 2009 Author Share Posted October 20, 2009 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 More sharing options...
dsonesuk Posted October 20, 2009 Share Posted October 20, 2009 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 More sharing options...
bigsilk Posted October 20, 2009 Author Share Posted October 20, 2009 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 More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.