...

View Full Version : Updating MySQL Records



el_nino
03-06-2010, 06:19 PM
Hi

I am in the process of creating a form which will allow the user to edit existing records in a MySQL database. When the user selects a record, the id of the record can be seen in the url at the bottom of the page, and when the user clicks on the update link, they should be taken to the update.php page which should allow then to change the values of the selected record. the problem is that the selected record does not seem to be displayed when the user clicks on the update link.

I have three scripts:
list_records_profiles.php
update.php
update_ac.php

list_records_profiles.php

<html>
<head>
<title>All Profiles</title>
</head>

<div align="center">

<?php
include ('includes/header.html')
?>

<body>


<?php //Connect to Database
//include ('mysql_connect.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 // 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=<?php 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

<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['PlayerID'];

$query = "select * from players WHERE PlayerID ='$id'";

//$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>Edit</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>&nbsp;</td>
<td><input name="PlayerID" type="hidden" id="PlayerID" value="<?php echo $row['PlayerID']; ?>"></td>
<td align="center"><input type="submit" name="Submit" value="Update"></td>
<td>&nbsp;</td>
</tr>
</table>
</td>
<?php echo "</form>"; ?>
</tr>
</table>
<?php
echo "<p>&nbsp </p>";

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

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


update_ac.php

<html>
<head>
<title>Edit</title>
</head>

<div align="center">

<?php
include ('includes/header.html')
?>

<?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 PlayerID='$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";
}

?>

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

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

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

any help would be greatly appreciated

thanks in advance

el nino

bdl
03-06-2010, 06:49 PM
What does your actual markup look like? In other words, do the links look correct and actually work to send a valid `PlayerID` value to the "update.php" script?

If you load "update.php?id=100" (or whatever a valid `PlayerID` value would be, do you see any difference?

What does the "update.php" script show when it is loaded? With or without a query string parameter?


On another note:



<?php
$id=$_GET['PlayerID'];

$query = "select * from players WHERE PlayerID ='$id'";


Scary scary. Make sure you a) validate incoming data (i.e. is it supposed to be a numeric value only?) and b) escape the data and/or use a parameterized query. Never let any user-initiated data to touch your database without a proper sanitization.

You should also make certain to validate data prior to making a database connection. Don't waste resources with the database if the user hasn't passed anything that you actually intend to use to query the database with.

el_nino
03-06-2010, 07:37 PM
the PlayerID which is being sent does seem to be correct in that it does relate to the record in the database. However no matter what id is sent no record is displayed on the next page (update.php)

when the update.php script is loaded it simply shows the headings for the columns but does not show any values

i've echoed the query, and it seems to be that PlayerID is not making it there

bdl
03-06-2010, 08:14 PM
Ah. Ok, now I see this:


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


and this:


$id=$_GET['PlayerID'];


See what's wrong there? You're passing a GET variable named 'id', and trying to retrieve one named 'PlayerID'. In the receiving script, you need to reference $_GET['id'].

el_nino
03-06-2010, 09:22 PM
problem kinda (see below) solved... thank you :)

el_nino
03-07-2010, 01:54 PM
ok, problem kinda solved

i'm able to output the selected record into a table so the user can view the results but i'm unable to output the results into a form so they can then be edited. i've created the following script which should enable me to do this but i just end up with a blank form :mad:

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">&nbsp;</td>
<td align="center">&nbsp;</td>
<td align="center">&nbsp;</td>
<td align="center">&nbsp;</td>
</tr>
<tr>
<td align="center">&nbsp;</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>&nbsp;</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>&nbsp;</td> <td>&nbsp;</td> <td>&nbsp;</td> <td>&nbsp;</td> <td>&nbsp;</td> <td>&nbsp;</td> <td>&nbsp;</td> </tr>
<tr>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</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>&nbsp;</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

el nino

friz
03-07-2010, 06:02 PM
I'm not much of a coder and I haven't checked your code, but this is how I would do it:
(not tested)

<html>
<head>
<title>Edit</title>
</head>

<div align="center">

<?php
include ('includes/header.html')

//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);

//$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;
$numresult = mysql_query($query);
if ( !$numresult ) {
echo "No results...<br>";
}else {
$numrows = mysql_num_rows($numresult);
if ( 0 == $numrows ) {
echo "No data found...";

} else {

echo "<form action=update_ac.php method=post>";
echo "<table><caption>Edit record</caption>";
echo "<thead><tr>
<th scope='col'>Name</th>
<th scope='col'>DOB</th>
<th scope='col'>Club</th>
<th scope='col'>Number</th>
<th scope='col'>Cost</th>
<th scope='col'>Position</th>
<th scope='col'>National Team</th>
</tr></thead>";
for ( $i=0; $i < $numrows; $i++ ) {
$name = mysql_result ( $numresult, $i, 'name');
$DOB = mysql_result ( $numresult, $i, 'DOB');
$club = mysql_result ( $numresult, $i, 'club');
$number = mysql_result ( $numresult, $i, 'number');
$cost = mysql_result ( $numresult, $i, 'cost');
$position = mysql_result ( $numresult, $i, 'position');
$nationalteam = mysql_result ( $numresult, $i, 'nationalteam');

echo "<tr>
<td><input type=text name=name value=$name></td>
<td><input type=text name=dob value=$dob></td>
<td><input type=text name=club value=$club></td>
<td><input type=text name=number value=$number></td>
<td><input type=text name=cost value=$cost></td>
<td><input type=text name=position value=$position></td>
<td><input type=text name=nationalteam value=$nationalteam></td>
</tr>";
}
echo "</table>";
}
echo "<input type=hidden name=id value=$id>
<input type=hidden name=action value=update>
<input type=submit name=Submit value=Update></form>";
}
mysql_close();
?>

bdl
03-07-2010, 06:36 PM
@el_nino> Hopefully by now you've realized that you commented out your call to mysql_query(), and that you've omitted the call to return the resultset, e.g.


$result= mysql_query($query);
while( $row= mysql_fetch_assoc($result) ) {
// handle $row here
}


@friz> Well meant, I'm sure, but that's the worst possible way to iterate over a resultset. Using a single call to mysql_fetch_* to retrieve each record is much better than multiple calls to mysql_result().

friz
03-08-2010, 01:50 AM
@friz> Well meant, I'm sure, but that's the worst possible way to iterate over a resultset. Using a single call to mysql_fetch_* to retrieve each record is much better than multiple calls to mysql_result().

Thx for the tip :thumbsup:

el_nino
03-08-2010, 10:46 AM
bdl -thanks. i got that working this morning before seeing your message.

i can now see the selected record in a form. :)

just need to get it to to update the record now, its giving me an error in the update_ac.php page but i think that may be due to my query being incorrect, i'm gonna have a go at solving that now

el_nino
03-08-2010, 11:56 AM
ok, so i'v now created update_ac.php which should update the selected record in the database, this follows on from update_v2.php

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">&nbsp;</td>
<td align="center">&nbsp;</td>
<td align="center">&nbsp;</td>
<td align="center">&nbsp;</td>
</tr>
<tr>
<td align="center">&nbsp;</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>&nbsp;</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>&nbsp;</td> <td>&nbsp;</td> <td>&nbsp;</td> <td>&nbsp;</td> <td>&nbsp;</td> <td>&nbsp;</td> <td>&nbsp;</td> </tr>
<tr>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</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>&nbsp;</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>

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 "&nbsp;";
echo $query;
echo "&nbsp;";
$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 "&nbsp;";
echo "<p><a href='loggedin.php'> Take me back to my member page</a></p>";
echo "&nbsp;";
echo "<p><a href='list_records_profiles.php'> I want to edit more profiles</a></p>";
echo "&nbsp;";
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>

so the problem is that, when the user changes the value of the selected record and clicks on the update button, they are taken to the next page which displays the message "Successful" or "Error". In each case the Successful message is displayed but the record is not being updated

el_nino
03-09-2010, 09:37 AM
solved that particular problem :)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum