el_nino Posted March 6, 2010 Share Posted March 6, 2010 Hi I have created a script which should let me edit selected records in the database. the scripts i have created are: list_records_prfiles.php update.php update_ac.php what seems to be the problem is that the code <a href="update.php?id=<? echo $row['PlayerID']; ?>">update</a> does not seem to be working. When i hover over the update link on the webpage the ['PlayerID'] does not seem to be replaced with the actual ID. this means that when the user clicks on the update link no records are displayed, only the headings of the table are displayed. list_records_prfiles.php <html> <head> <title>All Profiles</title> </head> <div align="center"> <?php include ('includes/header.html') ?> <body> <?php //Connect to Database //connect to your database mysql_connect("****","****","****"); //(host, username, password) //specify database mysql_select_db("****") or die("Unable to select database"); //select which database we're using ?> <?php // Build SQL Query $query = "select * from players order by Club"; $numresults=mysql_query($query); $numrows=mysql_num_rows($numresults); $result = mysql_query($query) or die("Couldn't execute query. MySQL Said: ".mysql_error()); echo "<p>   </p>"; echo "<h1>All Profiles</h1></br>"; echo "<table border='1'>"; echo "<tr> <th>Name</th> <th>DOB</th> <th>Club</th> <th>Number</th> <th>Cost</th> <th>Position</th> <th>National Team</th> <th>Edit</th> </tr>"; // keeps getting the next row until there are no more to get while($row = mysql_fetch_array( $result )) { // Print out the contents of each row into a table echo "<tr><td>"; echo $row['Name']; echo "</td><td>"; echo $row['DOB']; echo "</td><td>"; echo $row['Club']; echo "</td><td>"; echo $row['Number']; echo "</td><td>"; echo $row['Cost']; echo "</td><td>"; echo $row['Position']; echo "</td><td>"; echo $row['NationalTeam']; echo "</td><td>"; ?> <a href="update.php?id=<? echo $row['PlayerID']; ?>">update</a> <?php echo "</td></tr>"; } echo "</table>"; echo "<p>  </p>"; ?> </body> <div align="center"> <?php include ('includes\footer.html'); ?> </html> update.php <?php //connect to your database mysql_connect("****","****","****"); //(host, username, password) //specify database mysql_select_db("****") or die("Unable to select database"); //select which database we're using); $PlayerID=$_GET['PlayerID']; $query = "select * from players WHERE PlayerID ='$PlayerID'"; $numresults=mysql_query($query); $numrows=mysql_num_rows($numresults); ?> <?php $result = mysql_query($query) or die("Couldn't execute query. MySQL Said: ".mysql_error()); echo "<p>   </p>"; echo "<h1>All Profiles</h1></br>"; echo "<table border='1'>"; echo "<form name='form1' method='post' action='update_ac.php'>"; echo "<tr> <th>Name</th> <th>DOB</th> <th>Club</th> <th>Number</th> <th>Cost</th> <th>Position</th> <th>National Team</th> </tr>"; // keeps getting the next row until there are no more to get while($row = mysql_fetch_array( $result )) { // Print out the contents of each row into a table echo "<tr><td>"; echo $row['Name']; echo "</td><td>"; echo $row['DOB']; echo "</td><td>"; echo $row['Club']; echo "</td><td>"; echo $row['Number']; echo "</td><td>"; echo $row['Cost']; echo "</td><td>"; echo $row['Position']; echo "</td><td>"; echo $row['NationalTeam']; echo "</td></tr>"; } echo "</table>"; echo "<p>  </p>"; ?> <table> <tr> <td> </td> <td><input name="PlayerID" type="hidden" id="PlayerID" value="<? echo $row['PlayerID']; ?>"></td> <td align="center"><input type="submit" name="Submit" value="Submit"></td> <td> </td> </tr> </table> </td> <?php echo "</form>"; ?> </tr> </table> update_ac.php <?php //connect to your database mysql_connect("****","****","****"); //(host, username, password) //specify database mysql_select_db("****") or die("Unable to select database"); //select which database we're using); // update data in mysql database $sql="UPDATE players SET name='$Name', dob='$DOB', club='$Club' number='$Number', cost='$Cost' position='$Position', nationalteam='$NationalTeam' WHERE id='$PlayerID'"; $result=mysql_query($sql); // if successfully updated. if($result){ echo "Successful"; echo "<BR>"; echo "<a href='list_records_profiles.php'>View result</a>"; } else { echo "ERROR"; } ?> help? thanks el nino Quote Link to comment https://forums.phpfreaks.com/topic/194330-editing-records-in-a-mysql-database/ Share on other sites More sharing options...
Zane Posted March 6, 2010 Share Posted March 6, 2010 I have you tried.. not using short tags -----------------------------------*** update Quote Link to comment https://forums.phpfreaks.com/topic/194330-editing-records-in-a-mysql-database/#findComment-1022290 Share on other sites More sharing options...
Dennis1986 Posted March 6, 2010 Share Posted March 6, 2010 echo "</td><td>"; echo "<a href='update.php?id=" . $row['PlayerID'] . "'>update</a>"; Quote Link to comment https://forums.phpfreaks.com/topic/194330-editing-records-in-a-mysql-database/#findComment-1022294 Share on other sites More sharing options...
el_nino Posted March 6, 2010 Author Share Posted March 6, 2010 Zanus - I made the change you suggested and now I can see the ID in the url when i place the cursor over the update link but the selected record is still not being displayed on the following page where the user could change the values Quote Link to comment https://forums.phpfreaks.com/topic/194330-editing-records-in-a-mysql-database/#findComment-1022295 Share on other sites More sharing options...
el_nino Posted March 6, 2010 Author Share Posted March 6, 2010 when i echo the query it seems to show that the value for PlayerID does not seem to be making it Quote Link to comment https://forums.phpfreaks.com/topic/194330-editing-records-in-a-mysql-database/#findComment-1022465 Share on other sites More sharing options...
el_nino Posted March 7, 2010 Author Share Posted March 7, 2010 ok, i've kinda solved the problem in that i am able to output the selected record into a table so the user can view it, but when i attempt to output it into a form so it can be edited i just end up with a blank form. the script i created to output the selected record into a form is: update_v2.php <html> <head> <title>Edit</title> </head> <div align="center"> <?php include ('includes/header.html') ?> <?php //connect to your database mysql_connect("","","") or die("cannot connect"); //(host, username, password) //specify database mysql_select_db("") or die("Unable to select database"); //select which database we're using); ?> <?php //$id=$_GET['id']; $id = (is_numeric($_GET['id'])) ? $_GET['id'] : 0; //$PlayerID = ((isset($_GET['PlayerID'])) ? $_GET['PlayerID'] : 0); $query = "select * from players WHERE PlayerID ='$id'"; echo $query; //$numresults=mysql_query($query); //$numrows=mysql_num_rows($numresults); ?> <body> <h1>Edit Record</h1> <table width="400" border="0" cellspacing="1" cellpadding="0"> <tr> <form name="form1" method="post" action="update_ac.php"> <td> <table width="100%" border="0" cellspacing="1" cellpadding="0"> <tr> <td align="center"> </td> <td align="center"> </td> <td align="center"> </td> <td align="center"> </td> </tr> <tr> <td align="center"> </td> <td align="center"><strong>Name</strong></td> <td align="center"><strong>DOB</strong></td> <td align="center"><strong>Club</strong></td> <td align="center"><strong>Number</strong></td> <td align="center"><strong>Cost</strong></td> <td align="center"><strong>Position</strong></td> <td align="center"><strong>National Team</strong></td> </tr> <tr> <td> </td> <td align="center"><input name="Name" type="text" size="15" value="<?php echo $row['Name']; ?>"></td> <td align="center"><input name="DOB" type="date" size="20" value="<?php echo $row['DOB']; ?>"></td> <td align="center"><input name="Club" type="varchar" size="15" value="<?php echo $row['Club']; ?>"></td> <td align="center"><input name="Number" type="int" size="15" value="<?php echo $row['Number']; ?>"></td> <td align="center"><input name="Cost" type="text" size="15" value="<?php echo $row['Cost']; ?>"></td> <td align="center"><input name="Position" type="text" size="15" value="<?php echo $row['Position']; ?>"></td> <td align="center"><input name="NationalTeam" type="text" size="15" value="<?php echo $row['NationalTeam']; ?>"></td> </tr> <tr><td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> </tr> <tr> <td> </td> <td> </td> <td> </td> <td><input name="id" type="hidden" id="id" value="<?php echo $row['PlayerID']; ?>"></td> <td align="center"><input type="submit" name="Submit" value="Update"></td> <td> </td> </tr> </table> </td> </form> </tr> </table> </body> <? // close connection mysql_close(); ?> <?php echo "<p>  </p>"; include ('includes\footer.html'); ?> <div align="center"> </html> help? Quote Link to comment https://forums.phpfreaks.com/topic/194330-editing-records-in-a-mysql-database/#findComment-1022663 Share on other sites More sharing options...
Dennis1986 Posted March 8, 2010 Share Posted March 8, 2010 $query = "select * from players WHERE PlayerID ='$id'"; echo $query; You are forgetting to actually query the string. $query = "select * from players WHERE PlayerID ='$id'"; $result = mysql_query($query) or die("Couldn't execute query. MySQL Said: ".mysql_error()); $row = mysql_fetch_assoc($result); echo $query; Remember that there's a difference in using mysql_fetch_array() and mysql_fetch_assoc(). _array() returns the row(s) with numbers as index while _assoc() returns named indexes (in this case field names). mysql_fetch_array(): $row[0] = value; mysql_fetch_array(object, MYSQL_ASSOC): $row['key'] = value; mysql_fetch_assoc(): $row['key'] = value; Quote Link to comment https://forums.phpfreaks.com/topic/194330-editing-records-in-a-mysql-database/#findComment-1022891 Share on other sites More sharing options...
el_nino Posted March 8, 2010 Author Share Posted March 8, 2010 thank you just need to get the record to update now, i'll let you know how that goes. getting an error message at the moment Quote Link to comment https://forums.phpfreaks.com/topic/194330-editing-records-in-a-mysql-database/#findComment-1022942 Share on other sites More sharing options...
el_nino Posted March 8, 2010 Author Share Posted March 8, 2010 so i have update_v2.php which will allow me view the selected record in a form so that the user can edit it. update_v2.php <html> <head> <title>Edit</title> </head> <div align="center"> <?php include ('includes/header.html') ?> <?php //connect to your database mysql_connect("","","") or die("cannot connect"); //(host, username, password) //specify database mysql_select_db("") or die("Unable to select database"); //select which database we're using); ?> <?php //$id=$_GET['id']; $id = (is_numeric($_GET['id'])) ? $_GET['id'] : 0; //$PlayerID = ((isset($_GET['PlayerID'])) ? $_GET['PlayerID'] : 0); //$query = "select * from players WHERE PlayerID ='$id'"; //echo $query; $query = "select * from players WHERE PlayerID ='$id'"; $result = mysql_query($query) or die("Couldn't execute query. MySQL Said: ".mysql_error()); $row = mysql_fetch_assoc($result); //echo $query; //$numresults=mysql_query($query); //$numrows=mysql_num_rows($numresults); ?> <body> <h1>Edit Record</h1> <table width="400" border="0" cellspacing="1" cellpadding="0"> <tr> <form name="form1" method="post" action="update_ac.php"> <td> <table width="100%" border="0" cellspacing="1" cellpadding="0"> <tr> <td align="center"> </td> <td align="center"> </td> <td align="center"> </td> <td align="center"> </td> </tr> <tr> <td align="center"> </td> <td align="center"><strong>Name</strong></td> <td align="center"><strong>DOB</strong></td> <td align="center"><strong>Club</strong></td> <td align="center"><strong>Number</strong></td> <td align="center"><strong>Cost</strong></td> <td align="center"><strong>Position</strong></td> <td align="center"><strong>National Team</strong></td> </tr> <tr> <td> </td> <td align="center"><input name="Name" type="text" size="15" value="<?php echo $row['Name']; ?>"></td> <td align="center"><input name="DOB" type="date" size="10" value="<?php echo $row['DOB']; ?>"></td> <td align="center"><input name="Club" type="varchar" size="15" value="<?php echo $row['Club']; ?>"></td> <td align="center"><input name="Number" type="int" size="2" value="<?php echo $row['Number']; ?>"></td> <td align="center"><input name="Cost" type="text" size="15" value="<?php echo $row['Cost']; ?>"></td> <td align="center"><input name="Position" type="text" size="15" value="<?php echo $row['Position']; ?>"></td> <td align="center"><input name="NationalTeam" type="text" size="15" value="<?php echo $row['NationalTeam']; ?>"></td> </tr> <tr><td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> </tr> <tr> <td> </td> <td> </td> <td> </td> <td><input name="id" type="hidden" id="id" value="<?php echo $row['PlayerID']; ?>"></td> <td align="center"><input type="submit" name="Submit" value="Update"></td> <td> </td> </tr> </table> </td> </form> </tr> </table> <p>  </p> <p><a href="loggedin.php"> Take me back to my member page </a></p> <p>  </p> <p><a href="logout.php"> Log me out, i'm done </a></br></p> </br> </body> <? // close connection mysql_close(); ?> <?php echo "<p>  </p>"; include ('includes\footer.html'); ?> <div align="center"> </html> and the script update_ac.php which is supposed to update the record in the database. update_ac.php <html> <head> <title>Edit</title> </head> <div align="center"> <?php include ('includes/header.html') ?> <?php $Name = trim($_POST['Name']); $DOB = trim($_POST['DOB']); $Club = trim($_POST['Club']); $Number = trim($_POST['Number']); $Cost = trim($_POST['Cost']); $Position = trim($_POST['Position']); $NationalTeam = trim($_POST['NationalTeam']); //connect to your database mysql_connect("","","") or die("cannot connect"); //(host, username, password) //specify database mysql_select_db("") or die("Unable to select database"); //select which database we're using); // update data in mysql database $sql="UPDATE players SET Name='$Name', DOB='$DOB', Club='$Club', Number='$Number', Cost='$Cost', Position='$Position', NationalTeam='$NationalTeam' WHERE PlayerID='$id'"; $result = mysql_query($sql) or die("Couldn't execute query. MySQL Said: ".mysql_error()); echo " "; echo $query; echo " "; $result=mysql_query($sql); // if successfully updated. if($result){ echo "<h1>Record Successfully Updated</h1>"; echo "<BR>"; echo "<h3><a href='show_all.php'>View Updated Record</h3></p>"; echo " "; echo "<p><a href='loggedin.php'> Take me back to my member page</a></p>"; echo " "; echo "<p><a href='list_records_profiles.php'> I want to edit more profiles</a></p>"; echo " "; echo "<p><a href='logout.php'> Log me out, i'm done</a></p>"; } else { echo "<h1>ERROR</h1>"; echo "<a href='list_records_profiles.php'>Click here to go back and try again</a>"; echo "<a href='loggedin.php'> Take me back to my member page </a>"; } ?> <?php echo "<p>  </p>"; include ('includes\footer.html'); ?> <div align="center"> </html> the message output by the script above seems to indicate that the record has been successfully updated but this does not seem to be the case Quote Link to comment https://forums.phpfreaks.com/topic/194330-editing-records-in-a-mysql-database/#findComment-1022969 Share on other sites More sharing options...
Dennis1986 Posted March 8, 2010 Share Posted March 8, 2010 You are missing $id=$_POST["id"]; in the second file where you set all the variables at top. And your query is called $sql so when you echo $query you are echoing the wrong variable. And last, you're doing query twice on the same sql. $result = mysql_query($sql) or die("Couldn't execute query. MySQL Said: ".mysql_error()); echo " "; echo $query; echo " "; $result=mysql_query($sql); You only need to do it once. $result = mysql_query($sql) or die("Couldn't execute query. MySQL Said: ".mysql_error()); echo " "; echo $sql; And instead of checking to see if the query was successful then maybe check to see if anything was updated at all with mysql_affected_rows (http://php.net/manual/en/function.mysql-affected-rows.php) if (mysql_affected_rows()) { // Sucess... } else { // Not a sucess... } Quote Link to comment https://forums.phpfreaks.com/topic/194330-editing-records-in-a-mysql-database/#findComment-1023086 Share on other sites More sharing options...
el_nino Posted March 9, 2010 Author Share Posted March 9, 2010 ok that makes sense and solved that issue. i have one more issue and then i'll be gone, i promise lol Once the record has been updated, i have a link which will allow the user to view the updated record. I am using the same method as the one used when allowing the user to select a profile to edit but this time the selected record will be displayed in a table rather than a form. I'm having the same problem that i initially had in that the record is not being displayed. No error messages are being returned, only the headings of the table are. updated.php <html> <head> <title>Updated Profile</title> </head> <div align="center"> <?php include ('includes/header.html') ?> <?php //connect to your database mysql_connect("","","") or die("cannot connect"); //(host, username, password) //specify database mysql_select_db("") or die("Unable to select database"); //select which database we're using); ?> <?php //$id=$_GET['id']; $id = (is_numeric($_GET['id'])) ? $_GET['id'] : 0; //$PlayerID = ((isset($_GET['PlayerID'])) ? $_GET['PlayerID'] : 0); $query = "select * from players WHERE PlayerID ='$id'"; $result = mysql_query($query) or die("Couldn't execute query. MySQL Said: ".mysql_error()); $row = mysql_fetch_assoc($result); //$numresults=mysql_query($query); //$numrows=mysql_num_rows($numresults); ?> <?php $result = mysql_query($query) or die("Couldn't execute query. MySQL Said: ".mysql_error()); echo "<p>   </p>"; echo "<h1>Updated Record</h1></br>"; echo "<table border='1'>"; echo "<tr> <th>Name</th> <th>DOB</th> <th>Club</th> <th>Number</th> <th>Cost</th> <th>Position</th> <th>National Team</th> </tr>"; // keeps getting the next row until there are no more to get while($row = mysql_fetch_array( $result )) { // Print out the contents of each row into a table echo "<tr><td>"; echo $row['Name']; echo "</td><td>"; echo $row['DOB']; echo "</td><td>"; echo $row['Club']; echo "</td><td>"; echo $row['Number']; echo "</td><td>"; echo $row['Cost']; echo "</td><td>"; echo $row['Position']; echo "</td><td>"; echo $row['NationalTeam']; echo "</td></tr>"; } echo "</table>"; echo "<p>  </p>"; ?> <p>  </p> <p><a href="loggedin.php"> Take me back to my member page </a></p> <p>  </p> <p><a href="logout.php"> Log me out, i'm done </a></br></p> </br> <?php echo "<p>  </p>"; include ('includes\footer.html'); ?> <div align="center"> </html> thanks in advance Quote Link to comment https://forums.phpfreaks.com/topic/194330-editing-records-in-a-mysql-database/#findComment-1023466 Share on other sites More sharing options...
el_nino Posted March 9, 2010 Author Share Posted March 9, 2010 problem solved, forgot to get the id in the previous script Quote Link to comment https://forums.phpfreaks.com/topic/194330-editing-records-in-a-mysql-database/#findComment-1023470 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.