1,105,409 Community Members

MySQL Db wont update from html form with php script

Member Avatar
Amicallef1991
Newbie Poster
22 posts since Jan 2013
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
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'); 
}
?>
Member Avatar
diafol
Where are my eyes?
12,977 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,848 [?]
Skill Endorsements: 92 [?]
Moderator
Featured
Sponsor
 
0
 

You're mixing mysqli and mysql

Member Avatar
Amicallef1991
Newbie Poster
22 posts since Jan 2013
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Am i?

Please explain

Member Avatar
diafol
Where are my eyes?
12,977 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,848 [?]
Skill Endorsements: 92 [?]
Moderator
Featured
Sponsor
 
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.

Member Avatar
Amicallef1991
Newbie Poster
22 posts since Jan 2013
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

OK thanks,

Wat is the code for connect using mysql?

Member Avatar
diafol
Where are my eyes?
12,977 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,848 [?]
Skill Endorsements: 92 [?]
Moderator
Featured
Sponsor
 
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.

Member Avatar
Amicallef1991
Newbie Poster
22 posts since Jan 2013
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
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

Member Avatar
diafol
Where are my eyes?
12,977 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,848 [?]
Skill Endorsements: 92 [?]
Moderator
Featured
Sponsor
 
0
 

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

Member Avatar
rholdbataller
Newbie Poster
4 posts since Jan 2013
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
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.

Member Avatar
Amicallef1991
Newbie Poster
22 posts since Jan 2013
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
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'); 
}
?>
Member Avatar
diafol
Where are my eyes?
12,977 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,848 [?]
Skill Endorsements: 92 [?]
Moderator
Featured
Sponsor
 
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.

Member Avatar
Amicallef1991
Newbie Poster
22 posts since Jan 2013
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
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

Member Avatar
diafol
Where are my eyes?
12,977 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,848 [?]
Skill Endorsements: 92 [?]
Moderator
Featured
Sponsor
 
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());
}
Member Avatar
Amicallef1991
Newbie Poster
22 posts since Jan 2013
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
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

Member Avatar
Biiim
Posting Pro
504 posts since Oct 2011
Reputation Points: 85 [?]
Q&As Helped to Solve: 86 [?]
Skill Endorsements: 9 [?]
 
0
 
die('Invalid query: ' . $conn->error);

maybe that?

Member Avatar
diafol
Where are my eyes?
12,977 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,848 [?]
Skill Endorsements: 92 [?]
Moderator
Featured
Sponsor
 
0
 

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

Ah, forgot he was using class.

Member Avatar
Amicallef1991
Newbie Poster
22 posts since Jan 2013
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
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

Member Avatar
pritaeas
mod_pritaeas
11,315 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,835 [?]
Skill Endorsements: 155 [?]
Moderator
Featured
Sponsor
 
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.

Member Avatar
Amicallef1991
Newbie Poster
22 posts since Jan 2013
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

This is what i get when i make those changes:

Connected successfullyTable 'ccm3414.studentinfo' doesn't exist

Member Avatar
pritaeas
mod_pritaeas
11,315 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,835 [?]
Skill Endorsements: 155 [?]
Moderator
Featured
Sponsor
 
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: