Wastedfun Posted May 17, 2014 Share Posted May 17, 2014 Hello, to start off I am very new to this and trying to learn as I go by using sample code and putting things together as I need them. I am working on a basic database editor that will make a select able list from a sql table. In this case, "Employees". Then, after I select a name from that list it gives me the option to alter that information and save it. What I am missing however, is the option to pre-fill what the database already has while in the edit part. The drop down menu only shows the Employee name. So when I select the name, only the EmpID variable is transferred to the next part. I want all fields pre-filled out by defining variables from the SQL table. So I cant figure out how to call the database, then the table, Find the PRIMARY KEY (which is the EmpID) and create variables from (FirstName, LastName and Pay), but only for the EmpID that was slected before hand. How can I do this? Once I can make those variables, I can use them to pre-fill the text areas.. Thank you in advance. Also if needed I will post the code I have so far. <html> <head> <title>Edit Employee</title> </head> <body> <?php $dbhost = 'localhost'; $dbuser = 'Labor'; $dbpass = '***********'; $conn = mysql_connect($dbhost, $dbuser, $dbpass); // Check connection if(! $conn ) { die('Could not connect: ' . mysql_error()); } if(isset($_POST['save'])) { $EmpID = $_POST['EmpID']; $FirstName = $_POST['FirstName']; $LastName = $_POST['LastName']; $Pay = $_POST['Pay']; $OrgEmpID = $_POST['OrgEmpID']; $sql = "UPDATE Employees SET EmpID='$EmpID', FirstName='$FirstName', LastName='$LastName', Pay='$Pay' WHERE EmpID='$OrgEmpID'"; mysql_select_db('Labor'); $retval = mysql_query( $sql, $conn ); if(! $retval ) { die('Could not update data: ' . mysql_error()); } echo "Updated data successfully\n"; } else { if(isset($_POST['select'])) { $EmpID = $_POST['EmpID']; $FirstName = $_POST['FirstName']; $LastName = $_POST['LastName']; $Pay = $_POST['Pay']; $OrgEmpID = $_POST['EmpID']; echo ' Update Employee <form method="post" action="'.$_PHP_SELF.'"> <table width="400" border="0" cellspacing="1" cellpadding="2"> <tr> <td width="100">Employee ID: </td> <td><input name="EmpID" type="text" id="EmpID" value="'.$EmpID.'"></td> </tr> <tr> <td width="100">First Name: </td> <td><input name="FirstName" type="text" id="FirstName" value="'.$FirstName.'"></td> </tr> <tr> <td width="100">Last Name: </td> <td><input name="LastName" type="text" id="LastName" value="'.$LastName.'"></td> </tr> <tr> <td width="100">Hourly Pay: </td> <td><input name="Pay" type="text" id="Pay" value="'.$Pay.'"></td> </tr> <tr> <td width="100"> </td> <td> </td> </tr> <tr> <td width="100"> </td> <td> <input type="hidden" name="OrgEmpID" value="'.$OrgEmpID.'"> <input name="save" type="submit" id="save" value="Save"> </td> </tr> </table> </form> '; } else { ?> <form method="post" action="<?php $_PHP_SELF ?>"> <table width="400" border="0" cellspacing="1" cellpadding="2"> <tr> <td width="100">Select Employee to Edit</td> <td></td> </tr> <tr> <td width="100"> <select name="EmpID" id="EmpID"> <option value='EmpID' disabled='disabled' selected='selected'>Please select a name</option> <? mysql_select_db('Labor'); $result = mysql_query('SELECT EmpID, FirstName, LastName, Pay FROM Employees'); while ($row = mysql_fetch_array($result)) { echo '<option value="'.$row{"EmpID"}.'">'.$row{"FirstName"}.' '.$row{"LastName"}.'</option>'; } ?> </select> </td> <td> </td> </tr> <tr> <td width="100"> </td> <td> <input name="select" type="submit" id="select" value="Select"> </td> </tr> </table> </form> <?php } } ?> <br> <a href="index.php">Home</a> </body> </html> Quote Link to comment https://forums.phpfreaks.com/topic/288566-make-variables-from-sql-table/ Share on other sites More sharing options...
Ch0cu3r Posted May 17, 2014 Share Posted May 17, 2014 You'd perform a SELECT query returning the record where the EmpID matches the name dropdown menu value submitted by your form. Example if(isset($_POST['EmpID')) { $EmpID = intval($_POST['EmpID']); $result = mysql_query('SELECT EmpID, FirstName, LastName, Pay FROM Employees WHERE EmpID = ' . $EmpID; list($FirstName, $LastName, $Pay) = mysql_fetch_row($result); // output form echo '<form method="post"> Firstname: <input type="text" name="FirstName" value="'.$FirstName.'" /><br /> Lastname: <input type="text" name="LastName" value="'.$LastName.'" /><br /> Pay: <input type="text" name="Pay" value="'.$Pay.'" /><br /> <input type="hidden" value="'.$EmpID.'" name="EmpID" /> <input type="submit" name="submit" value="Update" /> </form>'; } Quote Link to comment https://forums.phpfreaks.com/topic/288566-make-variables-from-sql-table/#findComment-1479873 Share on other sites More sharing options...
Solution Wastedfun Posted May 18, 2014 Author Solution Share Posted May 18, 2014 (edited) Thank you Ch0cu3r, The line, $result = mysql_query("SELECT EmpID, FirstName, LastName, Pay FROM Employees WHERE EmpID = $EmpID"); list($FirstName, $LastName, $Pay) = mysql_fetch_row($result); Resulted in: Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home/EditEmp.php on line 55 I tried mysql fetch array also with no luck EDIT: I added the selection for the database and it worked. mysql_select_db('Labor'); Thanks again! Edited May 18, 2014 by Wastedfun Quote Link to comment https://forums.phpfreaks.com/topic/288566-make-variables-from-sql-table/#findComment-1479926 Share on other sites More sharing options...
ginerjm Posted May 18, 2014 Share Posted May 18, 2014 you have four fields in your selection, but only 3 in your list(). I realize that list will work like that but it's still poor practice and should be giving you a notice error Quote Link to comment https://forums.phpfreaks.com/topic/288566-make-variables-from-sql-table/#findComment-1479934 Share on other sites More sharing options...
Wastedfun Posted May 18, 2014 Author Share Posted May 18, 2014 you have four fields in your selection, but only 3 in your list(). I realize that list will work like that but it's still poor practice and should be giving you a notice error Yes, it messed up the order of my Variables. But when I noticed they were messed up, removing that was an easy fix. Thank you as well ;p Quote Link to comment https://forums.phpfreaks.com/topic/288566-make-variables-from-sql-table/#findComment-1479936 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.