PHP script, mysqli functions update a record - The MySQL Relational Database

Posted by Deeda (Deeda), 16 October 2006

I'm looking for a php script written with mysqli functions to update or edit a record in my database. I'm using MySQL 5.0.20, Apache 2.0.58, and PHP 5.1.4. I have been able to add, display, and delete a record, but not edit or update.

My goal is to be able to display the record so I can edit or update it and then save the changes.

Any help you can provide will be greatly appreciated.

Thanks,
Deeda

Posted by admin (Graham Ellis), 16 October 2006

You probably want to use a "replace" query. And you can embed it within the appropriate mysqli function call just like any other query. Suggestion - run an appropriate query by hand (via the mysql command line interface) and then develop your PHP code to generate that query.

Posted by Deeda (Deeda), 17 October 2006

I forgot to mention that I am very new to php script writing and I really need an example to completely understand the concept.

Here is an example of some code I found on the web. I would like to know how to write this code with mysqli functions.

<html><head><title></title></head>
<body>
<?
$id=$_POST['id'];
$db="mydatabase";
//mysql_connect(localhost,$_POST['username'],$_POST['pass']);
$link = mysql_connect("localhost");
if (! $link)
die("Couldn't connect to MySQL");

mysql_select_db($db , $link)
or die("Couldn't open $db: ".mysql_error());

$query=" SELECT * FROM birthdays WHERE id='$id'";
$result=mysql_query($query);
$num=mysql_num_rows($result);

$i=0;
while ($i < $num) {
$name=mysql_result($result,$i,"name");
$birthday=mysql_result($result,$i,"birthday");
?>
<table width="300" cellpadding="10" cellspacing="0" border="2">
<tr align="center" valign="top">
<td align="center" colspan="1" rowspan="1" bgcolor="#64b1ff">
<h3>Edit and Submit</h3>
<form action="birthdays_change_record.php" method="post">
<input type="hidden" name="username" value="<?php print $_POST['username']?>">
<input type="hidden" name="pass" value="<?php print $_POST['pass']?>">
<input type="hidden" name="ud_id" value="<? echo "$id" ?>">
Name: <input type="text" name="ud_name" value="<? print "$name"?>"><br>
Birthday: <input type="text" name="ud_birthday" value="<? echo "$birthday"?>"><br>
<input type="Submit" value="Update">
</form>
</td></tr></table>

<?
++$i;
}
?>
</body>
</html>

Posted by admin (Graham Ellis), 17 October 2006

In essence, you'll change all the "mysql" calls to "mysqli" calls and add in an extra parameter - as the first parameter - telling the mysqli functions which database connection is concerned; this replaces the last parameter on the existing calls. Exceptionally, the mysql_connect call returns that connection as a variable. So the two lines

Quote:

$link = mysql_connect("localhost");
and
mysql_select_db($db , $link) ;

become
$link = mysqli_connect("localhost");
and
mysql_select_db($link, $db );

Rather than me go through every line in detail, your best way to sort out the changes are to look up the PHP manual - go to www.php.net and search for each function in your existing code, the look for the same thing with the extra "i" to check what the new version does.

Posted by Deeda (Deeda), 19 October 2006

I have been working through the code like you suggested, but I'm having a problem with the following line:

$name= mysqli_result($result, $i, "name");

I get the following error:

1 row. The ID number is: 16
Fatal error: Call to undefined function mysqli_result() in C:\Program Files\Apache Group\Apache2\htdocs\birthdays_change_forms.php on line 33

What am I doing wrong? I tried using mysqli_fetch_field, mysqli_fetch_array, mysqli_fetch_object, mysqli_fetch_row, but nothing seems to work. I get either undefined function or too many parameters.

<html>
<head>
<title>Birthdays Change Form</title>
</head>
<body>

<?php

$id=$_POST['id'];
$mysqli = mysqli_connect("localhost", "kelly", "ki", "mydatabase");

if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();

} else {
$query = "SELECT * FROM birthdays WHERE id = '$id'";

$result = mysqli_query($mysqli, $query);

$num = mysqli_num_rows($result);
printf("%d row. \n", $num);
printf("The ID number is: ".$id."");

//mysqli_close($mysqli);

}

$i=0;
while ($i < $num) {
$name= mysqli_result($result, $i, "name");
$birthday=mysqli_result($result,$i,"birthday");
?>
<table width="300" cellpadding="10" cellspacing="0" border="2">
<tr align="center" valign="top">
<td align="center" colspan="1" rowspan="1" bgcolor="#64b1ff">
<h3>Edit and Submit</h3>
<form action="birthdays_change_record.php" method="post">
<input type="hidden" name="username" value="<?php print $_POST['username']?>">
<input type="hidden" name="pass" value="<?php print $_POST['pass']?>">
<input type="hidden" name="ud_id" value="<? echo "$id" ?>">
Name: <input type="text" name="ud_name"" value="<? print "$name"?><br>
Birthday: <input type="text" name="ud_birthday" value="<? echo "$birthday"?>"><br>
<input type="Submit" value="Update">
</form>
</td></tr></table>

<?php
++$i;
}
?>
</body>
</html>

Posted by admin (Graham Ellis), 19 October 2006

Quote:

$name= mysqli_result($result, $i, "name");
$birthday=mysqli_result($result,$i,"birthday");

could become

Quote:

$row = mysqli_fetch_assoc($result);
$name= $row[ "name"];
$birthday=$row["birthday"];

You're fetching results row by row, then picking up the individual cells you need. You don't specify the row number as, just like when reading a file, each fetch you do gives you the next result line.

Posted by Deeda (Deeda), 20 October 2006

It worked! Thank you very much.

Posted by Deeda (Deeda), 21 October 2006

Now that the Birthdays Change Form is working, I'm trying to get the Birthdays Change Record to work. The problem I am having is it won't update the record in the birthdays table. I think the problem is with the following line, but I'm not positive. Plus I'm not sure how to fix it .

$sql = ("UPDATE birthdays SET name= '$ud_name', birthday= 'ud_birthday' WHERE id='".$ud_id."'");

When I run the code it appears to have made the change, but when I go to the Birthday Display Records, no change or update was made.

Here is the code for the Birthdays Change Record:
*********************************************
<html>
<head>
<title>Birthdays Change Record</title>
</head>
<body>

<?php

//$user=$_POST['username'];
//$password=$_POST['password'];
$ud_id=@$_POST["ud_id"];
$ud_name=$_POST["ud_name"];
$ud_birthday=$_POST["ud_birthday"];

$mysqli = mysqli_connect("localhost", "kelly", "ki", "mydatabase");

if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();

} else {
$sql = ("UPDATE birthdays SET name= '$ud_name', birthday= 'ud_birthday' WHERE

id='".$ud_id."'");
$res = mysqli_query($mysqli, $sql)
or die(mysqli_error($mysqli));

printf("The ID is: %s<br/>", $ud_id);
printf("The name is: %s<br/>", $ud_name);
printf("The birthday is: %s<br/>", $ud_birthday);

echo "Record Updated";

mysqli_close($mysqli);

}

?>
<form method="POST" action="birthdays_update_form.php">
<input type="hidden" name="username" value="<?php print $_POST['username']?>">
<input type="hidden" name="pass" value="<?php print $_POST['password']?>">
<input type="submit" value="Change Another">
</form><br>

<form method="POST" action="birthdays_dbase_interface.php">
<input type="hidden" name="username" value="<?php print $_POST['username']?>">
<input type="hidden" name="pass" value="<?php print $_POST['password']?>">
<input type="submit" value="Dbase Interface">
</form>

</body>
</html>
*********************************************

Although you may not need this information, I have also included the code for the Birthday Change Form, Birthday Update Form, and Birthday Display Records
*********************************************
<html>
<head>
<title>Birthdays Change Form</title>
</head>
<body>

<?php

$id=$_POST['id'];

$mysqli = mysqli_connect("localhost", "kelly", "ki", "mydatabase");

if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();

} else {
$query = "SELECT * FROM birthdays WHERE id = '$id'";

$result = mysqli_query($mysqli, $query);

$num = mysqli_num_rows($result);
//printf("%d row. \n", $num);
//printf("The ID number is ".$id."");

//mysqli_close($mysqli);

}

$row = mysqli_fetch_assoc($result);
$name = $row["name"];
$birthday=$row["birthday"];

//printf("Name: %s<br/>", $name);
//printf($birthday);
?>
<table width="300" cellpadding="10" cellspacing="0" border="2">
<tr align="center" valign="top">
<td align="center" colspan="1" rowspan="1" bgcolor="#64b1ff">
<h3>Edit and Submit</h3>
<form action="birthdays_change_record.php" method="post">
<input type="hidden" name="username" value="<?php print $_POST['username']?>"
<input type="hidden" name="pass" value="<?php print $_POST['pass']?>"
<input type="hidden" name="ud_id" value="<? echo $_POST['id']" ?>
Name: <input type="text" name="ud_name" value="<?php echo "$name"?>"><br>
Birthday: <input type="text" name="ud_birthday" value="<?php echo "$birthday"?>"><br>
<input type="Submit" value="Update">

</form>
</td></tr></table>
</body>
</html>
*********************************************

<?php
$mysqli = mysqli_connect("localhost", "kelly", "ki", "mydatabase");

//gather the birthdays
$get_birthday_sql = "SELECT id, name, birthday FROM birthdays ORDER BY name";
$get_birthday_res = mysqli_query($mysqli, $get_birthday_sql) or die(mysqli_error($mysqli));

if (mysqli_num_rows($get_birthday_res) < 1) {
//there are no birthdays, so say so
$display_block = "<p><em>No birthdays exist.</em></p>";
} else {
//create the display string
$display_block = "
<table cellpadding=\"3\" cellspacing=\"2\" border=\"1\">
<tr>
<th>ID</th>
<th>Name</th>
<th>Birthday</th>
</tr>";

while ($birthday_info = mysqli_fetch_array($get_birthday_res)) {
$id = stripslashes ($birthday_info['id']);
$name = stripslashes($birthday_info['name']);
$birthday = stripslashes($birthday_info['birthday']);

//get number of birthdays
$get_num_birthday_sql = "SELECT COUNT(id) AS birthday_count FROM birthdays WHERE id = '".$id."'";
$get_num_birthday_res = mysqli_query($mysqli, $get_num_birthday_sql) or die(mysqli_error($mysqli));

while ($birthday_info = mysqli_fetch_array($get_num_birthday_res)) {
$num_birthdays = $birthday_info['birthday_count'];
}

//add to display
$display_block .= "
<tr>
<td align=\"center\">$id <br></td>
<td align=\"center\">$name <br></td>
<td align=\"center\">$birthday <br></td>
</tr>";
}

$display_block .= "</table>";
}

?>
<html>
<head>
<title>Birthday Update Form</title>
</head>
<body>
<h2> Birthday Update Form</h2>
<?php echo $display_block; ?>
<br>
<form method="POST" action="birthdays_change_form.php">
<pre>
Enter ID Number to Edit: <input type="text" name="id" size="5">
<input type="submit" value="Submit"><input type="reset">
</pre>
</form>
</body>
</html>

*********************************************
<?php
$mysqli = mysqli_connect("localhost", "kelly", "ki", "mydatabase");

//gather the birthdays
$get_birthday_sql = "SELECT id, name, birthday FROM birthdays ORDER BY name";
$get_birthday_res = mysqli_query($mysqli, $get_birthday_sql) or die(mysqli_error($mysqli));

if (mysqli_num_rows($get_birthday_res) < 1) {
//there are no birthdays, so say so
$display_block = "<p><em>No birthdays exist.</em></p>";
} else {
//create the display string
$display_block = "
<table cellpadding=\"3\" cellspacing=\"2\" border=\"1\">
<tr>
<th>ID</th>
<th>Name</th>
<th>Birthday</th>
</tr>";

while ($birthday_info = mysqli_fetch_array($get_birthday_res)) {
$id = stripslashes ($birthday_info['id']);
$name = stripslashes($birthday_info['name']);
$birthday = stripslashes($birthday_info['birthday']);

//get number of birthdays
$get_num_birthday_sql = "SELECT COUNT(id) AS birthday_count FROM birthdays WHERE id = '".$id."'";
$get_num_birthday_res = mysqli_query($mysqli, $get_num_birthday_sql) or die(mysqli_error($mysqli));

while ($birthday_info = mysqli_fetch_array($get_num_birthday_res)) {
$num_birthdays = $birthday_info['birthday_count'];
}

//add to display
$display_block .= "
<tr>
<td align=\"center\">$id <br></td>
<td align=\"left\">$name <br></td>
<td align=\"center\">$birthday <br></td>
</tr>";
}

$display_block .= "</table>";

}

?>
<html>
<head>
<title>Birthday List</title>
</head>
<body>
<h2>Birthday List
<?php echo $display_block; ?>
</body>
</html>

Thanks,
Deeda

Posted by admin (Graham Ellis), 22 October 2006

That's one heck of a lot of code - 236 lines! Without me taking a long time looking at what's going on it's impractical for me to do more than guess. However, I guess the update isn'w working.

1. Print out the update string $sql and try running it against the database directly via the MySQL clinet program to ensure you have it right

2. Check the retrun status is you're not doing to already

3. Use MySQL selects to find out the status of the record before and after the update.

Those three will probably point you to the problem. If they don't, come up with a cut down example that fails, showing the update that doesn't work in around 20 or 30 lines, and post that up. Chances are that by the very act of trimming you code back into an easy example, you'll find the problem anyway.

Posted by Deeda (Deeda), 22 October 2006

Graham,

I finally got it to work. I reviewed the update string $sql and it still wasn't working correctly. The bad part about it was I wasn't getting an error code. Then I started reviewing my code. I discover that I had a ">" missing from one of my lines of code. As soon as I entered it in, it worked.

Thanks for all of your help.

Deeda

This page is a thread posted to the opentalk forum at www.opentalk.org.uk and archived here for reference. To jump to the archive index please follow this link.