1.11M Members

MySQL Db wont update from html form with php script

 
0
 

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'); 
}
?>
 
0
 

You're mixing mysqli and mysql

 
0
 

Am i?

Please explain

 
0
 

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.

 
0
 

OK thanks,

Wat is the code for connect using mysql?

 
0
 

Try the php.net manual:

http://uk1.php.net/manual/en/function.mysql-select-db.php

BUT mysqli is better. Don't go back to mysql. For mysqli functions - see the relevant pages in the manual.

 
0
 

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

 
0
 

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

 
0
 

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.

 
0
 

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'); 
}
?>
 
0
 

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.

 
0
 

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

 
0
 

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());
}
 
0
 

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

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

maybe that?

 
0
 

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

Ah, forgot he was using class.

 
0
 

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

 
0
 

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.

 
0
 

This is what i get when i make those changes:

Connected successfullyTable 'ccm3414.studentinfo' doesn't exist

 
0
 

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

You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: