fredbear Posted December 21, 2009 Share Posted December 21, 2009 Not sure if this should go here or in the MySQL forum, but if I'm wrong could a mod move it please? Thanks! Anyhow, I'm trying to create a HTML table where I can quickly manipulate some data in a MySQL table. I've got it almost working, but when I hit the Update button it doesn't seem to do anything but place 0's in the emp_name field of my MySQL table. I'm hoping someone could take a look? This isn't a high pri emergency, just a small project I created for myself at work to pass the time while we're waiting for the new year and something to help me get more practice with PHP. Here's the code for the table: <html> <body> <?php $username="uname"; $password="pass"; $database="db"; mysql_connect(localhost,$username,$password); @mysql_select_db($database) or die( "Unable to select database"); $query="SELECT departments.dep_name, employees.emp_name, employees.emp_pager, employees.emp_id FROM departments INNER JOIN employees ON departments.dep_id = employees.emp_dep_id ORDER BY departments.dep_name, employees.emp_name"; $result=mysql_query($query); $num=mysql_numrows($result); mysql_close(); ?> <table border="1" cellspacing="2" cellpadding="2"> <tr> <th>ID</th> <th>Name</th> <th>Pager</th> <th>Department</th> </tr> <form action="update.php" method="post"> <?php $i=0; while ($i < $num) { $f1=mysql_result($result,$i,"emp_id"); $f2=mysql_result($result,$i,"emp_name"); $f3=mysql_result($result,$i,"emp_pager"); $f4=mysql_result($result,$i,"dep_name"); ?> <tr> <td><input type="text" name="emp_id" readonly="readonly" size="5" value="<?php echo $f1; ?>" /></td> <td><input type="text" name="emp_name" size="30" value="<?php echo $f2; ?>" /></td> <td><input type="text" name="emp_pager" size="30" value="<?php echo $f3; ?>" /></td> <td><?php echo $f4; ?></td> </tr> <?php $i++; } ?> <tr> <td><input type="submit" value="Update" /></td> </tr> </table> </form> </body> </html> And here's the update script: <?php $con = mysql_connect("localhost","name","pass"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("db", $con); $sql="UPDATE employees SET `emp_name` = '" . $_POST['emp_name'] . "' AND `emp_pager` = '" . $_POST['emp_pager'] . "' WHERE `emp_id` = '" . $_POST['emp_id'] . "'"; if (!mysql_query($sql,$con)) { die('Error: ' . mysql_error()); } echo "Record changed. <a href='table.php'>Click here to return</a>"; mysql_close($con); ?> Oh, and my database just consists of two tables, an employee table and a department table. Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted December 21, 2009 Share Posted December 21, 2009 The best thing to do here is look at the update statement just prior to handing it off to the database: <?php echo '<pre>' . print_r( $sql, true ) . '</pre>'; ?> Does the statement look correct? Quote Link to comment Share on other sites More sharing options...
fredbear Posted December 22, 2009 Author Share Posted December 22, 2009 OK, well I think I've got it partly figured out (thanks to your suggestion roopurt18 ), but not completely. When I did the test as suggested I found that it was picking data from the last row in the table. I think that had to do with I was prefilling all my data into input text boxes and trying to update by making changes and hitting submit, I think it didn't know exactly what row I wanted to update, so it picked the last one. So I've remedied that by not loading everything up into text boxes and creating one text box under each column and creating a radio button that gets it's value from the primary key of the table (I've attached a heavily redacted screen shot so you know what I'm talking about). When I select the row I want to edit with the radio button and enter my changes the SQL statement on the other side appears to be correct, but does not update the table. All of the information stays the same. I feel like I'm getting close, but I must be missing some little piece. Here is my updated code: <html> <body> <?php $username="pagers"; $password="password"; $database="pagers"; mysql_connect(localhost,$username,$password); @mysql_select_db($database) or die( "Unable to select database"); $query="SELECT departments.dep_name, employees.emp_name, employees.emp_pager, employees.emp_id FROM departments INNER JOIN employees ON departments.dep_id = employees.emp_dep_id ORDER BY departments.dep_name, employees.emp_name"; $result=mysql_query($query); $num=mysql_numrows($result); mysql_close(); ?> <table border="1" cellspacing="2" cellpadding="2"> <tr> <th>ID</th> <th>Name</th> <th>Pager</th> <th>Department</th> </tr> <form action="testupdate.php" method="post"> <?php $i=0; while ($i < $num) { $f1=mysql_result($result,$i,"emp_id"); $f2=mysql_result($result,$i,"emp_name"); $f3=mysql_result($result,$i,"emp_pager"); $f4=mysql_result($result,$i,"dep_name"); ?> <tr> <td><center><input type="radio" name="id" value="<?php echo $f1; ?>" /></center></td> <td><?php echo $f2; ?></td> <td><?php echo $f3; ?></td> <td><?php echo $f4; ?></td> </tr> <?php $i++; } ?> <tr> <td><input type="submit" value="Update" /></td> <td><input type="text" name="name" size="30" value="" /></td> <td><input type="text" name="pager" size="30" value="" /></td> </tr> </table> </form> <?php $con = mysql_connect("localhost","pagers","password"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("pagers"); $sql = "SELECT dep_id,dep_name FROM departments"; $results=mysql_query($sql) or die(mysql_error()); ?> <table border=1> <th>Add</th> <tr> <td> <form action="insert.php" method="post"> Department: <select name="emp_dep_id" /> <?php while($nt=mysql_fetch_array($results)){//Array or records stored in $nt echo "<option value=".$nt[dep_id].">".$nt[dep_name]."</option>"; /* Option values are added by looping through the array */ } echo "</select>";?> </select> </td> </tr> <tr> <td> Name: <input type="text" name="emp_name" /> </td> </tr> <tr> <td> Pager Address: <input type="text" name="emp_pager" /> </td> </tr> <tr> <td> <input type="submit" /> </td> </tr> </form> </table> </body> </html> Oh, and I combined forms so that my add form is on this sheet as well. That appears to be working as it should. [attachment deleted by admin] Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted December 22, 2009 Share Posted December 22, 2009 Whats the code for testupdate.php? Quote Link to comment Share on other sites More sharing options...
fredbear Posted December 22, 2009 Author Share Posted December 22, 2009 Oh sorry, actually it's pretty much the same as update.php from the original post, but has that little debugging bit you suggested: <?php $con = mysql_connect("localhost","pagers","password"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("pagers", $con); $sql="UPDATE employees SET `emp_name` = '" . $_POST['name'] . "' AND `emp_pager` = '" . $_POST['pager'] . "' WHERE `emp_id` = '" . $_POST['id'] . "'"; if (!mysql_query($sql,$con)) { die('Error: ' . mysql_error()); } echo '<pre>' . print_r( $sql, true ) . '</pre>'; echo '<p><a href="table.php">Return</a></p>'; mysql_close($con); ?> Quote Link to comment Share on other sites More sharing options...
fredbear Posted December 22, 2009 Author Share Posted December 22, 2009 Aha! I found it. The AND in my sql statement should be a comma: $sql="UPDATE employees SET `emp_name` = '" . $_POST['name'] . "', `emp_pager` = '" . $_POST['pager'] . "' WHERE `emp_id` = '" . $_POST['id'] . "'"; I knew it was something simple that I was overlooking. Thank you for your help! Quote Link to comment 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.