Hi,

I have a mysql database with some student info records. I am trying to Update a record in the db using a html form and a php script however it wont update.

It doesnt give me any errors.

Can anyone assit me please. Here is the code.

editingRecord.php

<?php
$conn = @new mysqli('localhost', 'root', '', 'ccm3413');
$conn->query("SET NAMES 'utf8'");
$recordID = $_GET['recordID'];
$query_str = "SELECT * FROM StudentInfo WHERE $recordID = recordID"; 
$result = $conn->query($query_str);
$row_data = $result->fetch_assoc();
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>adding a new data record</title>
</head>
<body>
<form id="form1" name="form1" method="post" action="updateRecord.php">
<p>
<label>Student Number:
<input name="StudentNumber" type="text" id="StudentNumber" size="10" value="<?=$row_data['StudentNumber'] ?>" />
</label>
</p>
<p>
<label>First Name:
<input name="FirstName" type="text" id="FirstName" size="20" value="<?=$row_data['FirstName'] ?>" />
</label>
</p>
<p>
<label>Last Name:
<input name="LastName" type="text" id="LastName" size="20" value="<?=$row_data['LastName'] ?>" />
</label>
</p>
<p>
<label>Email Address:
<input name="EmailAddr" type="text" id="EmailAddr" size="50" value="<?=$row_data['EmailAddr'] ?>" />
</label>
</p>
<p>
<label>Telephone:
<input name="PhoneNumber" type="text" id="PhoneNumber" size="20" value="<?=$row_data['PhoneNumber'] ?>" />
</label>
</p>
<p>
<input name="recordID" type="hidden" id="recordID" value="<? $row_data['recordID'] ?>" />
<input type="submit" name="Submit" id="Submit" value="Submit" />
</p>
</form>
</body>
</html>

updateRecord.php

<?php
//connect to the database
$conn = @new mysqli('localhost', 'root', '', 'CCM3413');
if (mysqli_connect_errno() != 0)
{
$errno = mysqli_connect_errno();
$errmsg = mysqli_connect_error();
echo "Connect Failed with: ($errno) $errmsg<br/>\n";
exit;
}
$conn->query("SET NAMES 'utf8'");
//get values from HTML page
$StudentNumber = $_POST["StudentNumber"];
$FirstName = $_POST["FirstName"];
$LastName = $_POST["LastName"];
$EmailAddr = $_POST["EmailAddr"];
$PhoneNumber = $_POST["PhoneNumber"];

$recordID = $_GET['recordID'];
//add a new record into the table StudentInfo
$query_str = "UPDATE StudentInfo SET StudentNumber='$StudentNumber', FirstName='$FirstName', LastName='$LastName', EmailAddr='$EmailAddr', PhoneNumber=
'$PhoneNumber' WHERE recordID= '$recordID'";
$result = @$conn->query($query_str);
if(mysql_query($query_str)){
echo "updated";}
else{
echo "fail";}
if ($result === FALSE)
{
echo "Connection Failed <br/>";
$conn->close();
exit;
}
else
{
header('Location: displaying.php'); 
}
?>
Member Avatar

diafol

You're mixing mysqli and mysql

Member Avatar

diafol

For example:

$result = @$conn->query($query_str);
if(mysql_query($query_str)){

Your connection is based on:

$conn = @new mysqli('localhost', 'root', '', 'CCM3413');

which is mysqli, but you're trying to use the old mysql_* functions to process the query. There may be other issues with the code, but that's the first thing that struck me.

OK thanks,

Wat is the code for connect using mysql?

Im not fussed about using mysqli functions. Its just for a miniproject for work that im trying to do so mysql will do. I really just want to get it working at this stage.

Any ideas on how to get thsi functional?

Thanks

Member Avatar

diafol

Yes, look at the manual as I mentioned - with the link. Replace all mysqli functions with mysql ones.

Replace $_GET with $_POST in $recordID = $_GET['recordID']; since you use the method POST and placed the control that holds the value of recordID inside that form or else it will not pass any value to the variable $recordID. This is the reason why your update doesn't work in your updateRecord.php.

I have made some changes to the file but now when i click update all i get is "Connection failed" !!!

Please help. code below

editingRecord.php

<?php
$conn = @new mysqli('localhost', 'root', '', 'CCM3413');
$conn->query("SET NAMES 'utf8'");
$rcID = $_GET['recordID'];
$query_str = "SELECT * FROM StudentInfo WHERE RecordID=$rcID"; 
$result = $conn->query($query_str);
$row_data = $result->fetch_assoc();
?>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>adding a new data record</title>
</head>
<body>
<form id="form1" name="form1" method="post" action="updateRecord.php">
<p>
<label>Student Number:
<input name="StudentNumber" type="text" id="StudentNumber" size="10" value="<?=$row_data['StudentNumber'] ?>" />
</label>
</p>
<p>
<label>First Name:
<input name="FirstName" type="text" id="FirstName" size="20" value="<?=$row_data['FirstName'] ?>" />
</label>
</p>
<p>
<label>Last Name:
<input name="LastName" type="text" id="LastName" size="20" value="<?=$row_data['LastName'] ?>" />
</label>
</p>
<p>
<label>Email Address:
<input name="EmailAddr" type="text" id="EmailAddr" size="50" value="<?=$row_data['EmailAddr'] ?>" />
</label>
</p>
<p>
<label>Telephone:
<input name="PhoneNumber" type="text" id="PhoneNumber" size="20" value="<?=$row_data['PhoneNumber'] ?>" />
</label>
</p>
<p>
<input name="recordID" type="hidden" id="recordID" value="<? $row_data['RecordID'] ?>" />
<input type="submit" name="Submit" id="Submit" value="Submit" />
</p>
</form>
</body>
</html>

updateRecord.php

<?php
//connect to the database
$conn = @new mysqli('localhost', 'root', '', 'CCM3413');
if (mysqli_connect_errno() != 0)
{
$errno = mysqli_connect_errno();
$errmsg = mysqli_connect_error();
echo "Connect Failed with: ($errno) $errmsg<br/>\n";
exit;
}
$conn->query("SET NAMES 'utf8'");
//get values from HTML page
$StudentNumber = $_POST["StudentNumber"];
$FirstName = $_POST["FirstName"];
$LastName = $_POST["LastName"];
$EmailAddr = $_POST["EmailAddr"];
$PhoneNumber = $_POST["PhoneNumber"];
$RecordID = $_POST["recordID"];
//add a new record into the table StudentInfo
$query_str = "UPDATE StudentInfo SET StudentNumber='$StudentNumber', FirstName='$FirstName',
LastName='$LastName', EmailAddr='$EmailAddr', PhoneNumber='$PhoneNumber' WHERE recordID=$RecordID";
$result = @$conn->query($query_str);

if ($result === FALSE)
{
echo "Connection Failed <br/>";
$conn->close();
exit;
}
else

{
header('Location: displaying.php'); 
}
?>
Member Avatar

diafol

Why "connection failed?", surely it's "query failed"? Have an or die() clause on the query line to tell you exactly why the query is failing.

Alternatively,echo the query string to the screen, copy and paste it into phpMyAdmin and run it in the SQL window. See what happens.

I have ammeneded the code to help troubleshooting.

<?php
//connect to the database
$conn = @new mysqli('localhost', 'root', '', 'CCM3413');
if (mysqli_connect_errno() != 0)
{
$errno = mysqli_connect_errno();
$errmsg = mysqli_connect_error();
echo "Connection to DB Failed with: ($errno) $errmsg<br/>\n";
exit;
}
//get values from HTML page
$StudentNumber = $_POST["StudentNumber"];
$FirstName = $_POST["FirstName"];
$LastName = $_POST["LastName"];
$EmailAddr = $_POST["EmailAddr"];
$PhoneNumber = $_POST["PhoneNumber"];
$RecordID = $_POST["recordID"];

echo "Values Passed <br/>";

//add a new record into the table StudentInfo
$query_str = "UPDATE StudentInfo SET StudentNumber=$StudentNumber, FirstName=$FirstName,
LastName=$LastName, EmailAddr=$EmailAddr, PhoneNumber=$PhoneNumber WHERE recordID=$RecordID";
$result = @$conn->query($query_str);

echo "Query Passed <br/>";

if ($result === FALSE)
{
echo "Query Failed <br/>";
$conn->close();
exit;
}
else

{
header('Location: displaying.php'); 
}
?>

I have added some echo lines to see when it fails. However when i run this i get:

Values Passed
Query Passed
Query Failed

and thats it. Any ideas anyone? I really just want to get this updating correctly.

Thanks

Member Avatar

diafol

This won't work:

$result = @$conn->query($query_str);
echo "Query Passed <br/>";
if ($result === FALSE)
{
echo "Query Failed <br/>";
$conn->close();
exit;
}

For one thing, you've suppressed errors with @
Query passed will be echoed every time as you don't provide a test.

Try:

$result = $conn->query($query_str);
if (!$result) {
    die('Invalid query: ' . mysql_error());
}

Ok,

I have added the above code and now i get the error message

Invalid Query:

I dont get any error message as per : ' . mysql_error());

Please help

die('Invalid query: ' . $conn->error);

maybe that?

Member Avatar

diafol

die('Invalid query: ' . $conn->error);

Ah, forgot he was using class.

I have started to rewrite the code and i cant get the query to run in my first page.

Pease advise:

Displaying.php

<?php
//connect to the database

$link = mysql_connect('localhost', 'root', '');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($link);

mysql_query('SELECT * FROM ccm3414.StudentInfo');
if (false){ 
    die ('Unable to Select that DB : ' . mysql_error());
}

$result = mysql_query ("SELECT * FROM StudentInfo");
if (false){ 
    die ('Unable to run Query : ' . mysql_error());
}

{
echo <<<EOM
<table border='1'>
<tr>
<th>Student Number</th>
<th>First Name</th>
<th>Last Name</th>
<th>Email Address</th>
<th>Phone Number</th>
<th>Actions</th>
</tr>
EOM;


while($row = mysql_fetch_array($result))
  {

echo <<<EOM
<tr>
<td>{$row['StudentNumber']}</td>
<td>{$row['FirstName']}</td>
<td>{$row['LastName']}</td>
<td>{$row['EmailAddr']}</td>
<td>{$row['PhoneNumber']}</td>
<td><a href="editingRecord.php?recordID={$row['recordID']}">Edit</a> <a href="deletingRecord.php?recordID={$row['recordID']}">Delete</a></td>
</tr>
EOM;
}
echo <<<EOTABLE
</table>
EOTABLE;

}
// finally, clean up the connection.

mysql_close();

?>

My problem is i cant get the code right for displaying the info from the db in the table. The table is created but no info is present. I get

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in displaying.php on line 40

Remove line 9.

Also this:

mysql_query('SELECT * FROM ccm3414.StudentInfo');
if (false) { die ('Unable to Select that DB : ' . mysql_error()); }

doesn't work, use this:

$result = mysql_query('SELECT * FROM ccm3414.StudentInfo') or die (mysql_error());

Lines 16-19 can be removed too.

This is what i get when i make those changes:

Connected successfullyTable 'ccm3414.studentinfo' doesn't exist

So the database (ccm3414) is wrong and/or the table (StudentInfo).

My mistake. it is ccm3413. what an ars*

This is now working! Thanks

That page then passed the record info over to this page to be edited.

editingRecord.php

<?php
//connect to the database

$link = mysql_connect('localhost', 'root', '')or die ("Connection Failed");
mysql_select_db("CCM3413")or die("Connection Failed");
echo 'Connected successfully';

//$rcID = $_GET['recordID']
//$query = ('SELECT * WHERE RecordID=$rcID');

$rcID = $_GET['recordID'];
//$query_str = "SELECT * FROM StudentInfo WHERE recordID=$rcID";
$result = "SELECT * WHERE recordID=$rcID";
//$result = $link->query($query_str);

//$row_data = ($result);
//$row_data = ($result);


$row_data = mysql_fetch_array($result);


?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Editing an existing record</title>
</head>
<body>
<form id="form1" name="form1" method="post" action="updateRecord.php">
<p>
<label>Student Number:
<input name="StudentNumber" type="text" id="StudentNumber" size="10" value="<?=$row_data['StudentNumber'] ?>" />
</label>
</p>
<p>
<label>First Name:
<input name="FirstName" type="text" id="FirstName" size="20" value="<?=$row_data['FirstName'] ?>" />
</label>
</p>
<p>
<label>Last Name:
<input name="LastName" type="text" id="LastName" size="20" value="<?=$row_data['LastName'] ?>" />
</label>
</p>
<p>
<label>Email Address:
<input name="EmailAddr" type="text" id="EmailAddr" size="50" value="<?=$row_data['EmailAddr'] ?>" />
</label>
</p>
<p>
<label>Telephone:
<input name="PhoneNumber" type="text" id="PhoneNumber" size="20" value="<?=$row_data['PhoneNumber'] ?>" />
</label>
</p>
<p>
<input name="recordID" type="hidden" id="recordID" value="<? $row_data['recordID'] ?>" />
<input type="submit" name="Submit" id="Submit" value="Submit" />
</p>
</form>
</body>
</html>

I get the error:

Warning: mysql_fetch_array() expects parameter 1 to be resource, string given in C:\xampp\htdocs\ccm3413\andrew2\editingRecord.php on line 20

The input boxes are displayed but no info inside them.

Any ideas?

Line 13 is missing mysql_query().

Like this:

$result = mysql_query("SELECT * WHERE recordID=$rcID");

Now FROM StudentInfo is missing.

Thanks.

Error is gone. But the infor from recordID is still not put into the boxes???

Member Avatar

diafol

Few things:

$row = mysql_fetch_array($result);

Note the ; at the end

Also add

print_r($row);

after the

$row = mysql_fetch_array($result);

to prove that the data is in $row.

<input name="recordID" type="hidden" id="recordID" value="<?= $row['RecordID'] ?>" />

You need to add the = to the short tag. With regard to short tags, they are not a good idea - convert them all from

<?= ... >

to

<?php echo ...; ?>

Thanks

This is now working.

No doubt more errors to come :)

Ok, so now i am trying to update the record.

updateRecord.php

<?php
//Connect to the database

$link = mysql_connect('localhost', 'root', '')or die ("Connection Failed");
mysql_select_db("CCM3413")or die("Connection Failed");
echo 'Connected successfully';

//get values from HTML page
$StudentNumber = $_POST['StudentNumber'];
$FirstName = $_POST['FirstName'];
$LastName = $_POST['LastName'];
$EmailAddr = $_POST['EmailAddr'];
$PhoneNumber = $_POST['PhoneNumber'];
$RecordID = $_POST["recordID"];

//Update Record
$query_str = "UPDATE StudentInfo SET StudentNumber=$StudentNumber, FirstName=$FirstName,
LastName=$LastName, EmailAddr=$EmailAddr, PhoneNumber=$PhoneNumber WHERE recordID=$RecordID";

$result = mysql_query($query_str);
if (!$result) {
    die('Invalid query: ' . mysql_error());

mysql_close();
exit;
}
else

{
header('Location: displaying2.php'); 
}
?>

This should update the record selected in the DB. It diverts back to displaying2.php but the record is not updated.

Any ideas?

EDIT
Code ammended. Error is now
Invalid query: You have an error in your SQL syntax

You are missing single quotes around the values.