Dolamite Jenkins
asked on
Wont update mysql record
I am trying to test if an entry is already present and update that record in MySQL or if its a new record insert it ... my code isn't throwing any errors but its not updating existing records ... it keeps inserting a new duplicate record ... the only notice I am getting is below
<b>Notice</b>: Undefined variable: row in <b>/home/content/15/6807515/html/UpdateData.php</b> on line <b>64</b><br />
What am I doing wrong ? is it my syntax ?<?php
error_reporting(E_ALL);
$dbhost = 'Txxxxx';
$dbuser = 'xxxx';
$dbpass = 'xxxx';
$dbname='xxxxx';
$tbl_name="DataMove"; // Table name
// Connect to server and select database.
$con = mysql_connect("$dbhost","$dbuser","$dbpass");
mysql_select_db($dbname);
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
// Get values from form
$Incident = mysql_real_escape_string($_POST['Incident']);
$Ic_number = mysql_real_escape_string($_POST['Ic_number']);
$Last_Name = mysql_real_escape_string($_POST['Last_Name']);
$First_Name = mysql_real_escape_string($_POST['First_Name']);
$Age = mysql_real_escape_string($_POST['Age']);
$Gender = mysql_real_escape_string($_POST['Gender']);
$sql="SELECT Last_Name
FROM DataMove
Incident='".$Incident."' AND Ic_number='".$Ic_number."'";
$result = mysql_query($sql,$con);
if ($result) {
$row = mysql_fetch_array($result);
}
if ($row)
{
// Update record
$sql="UPDATE $tbl_name
SET Last_Name='".$Last_Name."', First_Name='".$First_Name."', Age='".$Age."',Gender='".$Gender."';
WHERE Incident='".$Incident."' AND Ic_number='".$Ic_number."'";
} else {
echo "Inserted record<br/>";
// Insert record
$sql="INSERT INTO $tbl_name(Incident, Ic_number, Last_Name, First_Name, Age, Gender)VALUES('$Incident', '$Ic_number', '$Last_Name','$First_Name','$Age','$Gender')";
}
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "201";
mysql_close($con)
?>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you
And you're going to need this, fairly soon, sorry to say.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
ASKER
I made the changes and now I am getting error
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE Incident='10995922' AND Ic_number='10001'' at line 3
$sql="SELECT Last_Name
FROM DataMove
WHERE Incident='".$Incident."' AND Ic_number='".$Ic_number."'";
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
URGENT MESSAGE TO AVOID CORRUPTING YOUR DATA!
Ray, the insert query was being run instead of the update one, so there was definitely a problem with the select that determined which one to run. You're correct though that the semi-colon in the update query is a problem too (in fact, it will update all rows, hence my note above!!). It should be:
I wonder if this message has come too late, as your previous message may indicate the update was run on all rows of your data, then it tried to run a subsequent query which would just be:
WHERE Incident='".$Incident."' AND Ic_number='".$Ic_number."' "
(which would fail, but only after updating all rows, unfortunately)
Ray, the insert query was being run instead of the update one, so there was definitely a problem with the select that determined which one to run. You're correct though that the semi-colon in the update query is a problem too (in fact, it will update all rows, hence my note above!!). It should be:
$sql="UPDATE $tbl_name
SET Last_Name='".$Last_Name."', First_Name='".$First_Name."', Age='".$Age."',Gender='".$Gender."'
WHERE Incident='".$Incident."' AND Ic_number='".$Ic_number."'";
I wonder if this message has come too late, as your previous message may indicate the update was run on all rows of your data, then it tried to run a subsequent query which would just be:
WHERE Incident='".$Incident."' AND Ic_number='".$Ic_number."'
(which would fail, but only after updating all rows, unfortunately)
@TerryAtOpus: Hopefully, the developer is using a test data set! I think the failure was coming in the UPDATE query and nothing was being updated because of the semi-colon. Even as dumb as the procedural MySQL extension is, it will not run more than one query at a time through mysql_query(), multiple queries are not supported.
@dolamitejenkins: Here is an example that shows a strategy for query error handling. This will give you more debugging information, eg, making it clear which query failed, what the query contained, etc.
@dolamitejenkins: Here is an example that shows a strategy for query error handling. This will give you more debugging information, eg, making it clear which query failed, what the query contained, etc.
// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$sql = "SELECT id FROM my_table WHERE username='$safe_username'";
$res = mysql_query($sql);
// IF mysql_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
$err
= "QUERY FAIL: "
. $sql
. ' ERRNO: '
. mysql_errno()
. ' ERROR: '
. mysql_error()
;
trigger_error($err, E_USER_ERROR);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED OR THE ERROR WAS HANDLED
ASKER
Thank you
What was wrong with the answers? Please read the grading guidelines and then tell us why you gave a bad grade!
http://support.experts-exchange.com/customer/portal/articles/481419
Thanks, and looking forward to your explanation of what you expected, that we failed at.
Ray
http://support.experts-exchange.com/customer/portal/articles/481419
Thanks, and looking forward to your explanation of what you expected, that we failed at.
Ray
Open in new window