Editing Records In A MySQL Database

el_nino · 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> &nbsp </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>&nbsp </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> &nbsp </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>&nbsp </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

Zane · March 6, 2010

I have you tried.. not using short tags

-----------------------------------***

update

Dennis1986 · March 6, 2010

                echo "</td><td>";
                        echo "<a href='update.php?id=" . $row['PlayerID'] . "'>update</a>";

el_nino · 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

el_nino · March 6, 2010

when i echo the query it seems to show that the value for PlayerID does not seem to be making it

el_nino · 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>&nbsp </p>";

	include ('includes\footer.html');
?>

	<div align="center">
</html>

help?

Dennis1986 · 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;

el_nino · 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

el_nino · 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>&nbsp </p>  
<p><a href="loggedin.php"> Take me back to my member page </a></p> 
<p>&nbsp </p>
<p><a href="logout.php"> Log me out, i'm done </a></br></p>
</br>

</body>
<?

// close connection 
mysql_close();

?>

<?php
	echo "<p>&nbsp </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>&nbsp </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 :confused:

Dennis1986 · 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...
}

el_nino · 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> &nbsp </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>&nbsp </p>";			
?>		

<p>&nbsp </p>  
<p><a href="loggedin.php"> Take me back to my member page </a></p> 
<p>&nbsp </p>
<p><a href="logout.php"> Log me out, i'm done </a></br></p>
</br>

<?php
	echo "<p>&nbsp </p>";

	include ('includes\footer.html');
?>

	<div align="center">
</html>

thanks in advance

el_nino · March 9, 2010

problem solved, forgot to get the id in the previous script

Sign In

Editing Records In A MySQL Database

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information