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 />
 

Open in new window

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)
?> 

Open in new window

dolamitejenkinsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Terry WoodsIT GuruCommented:
Your SQL:

$sql="SELECT Last_Name
FROM DataMove
Incident='".$Incident."' AND  Ic_number='".$Ic_number."'";

is missing the word "where". Try:

$sql="SELECT Last_Name
FROM DataMove
WHERE Incident='".$Incident."' AND  Ic_number='".$Ic_number."'";
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Terry WoodsIT GuruCommented:
That also shows that you aren't checking correctly for errors, so you should probably get that fixed too. Something like this:
if ($result) {
	$row = mysql_fetch_array($result);
} else {
        die('Invalid query: ' . mysql_error());
}

Open in new window

0
dolamitejenkinsAuthor Commented:
Thank you
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

dolamitejenkinsAuthor Commented:
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

Open in new window


$sql="SELECT Last_Name
FROM DataMove
WHERE Incident='".$Incident."' AND  Ic_number='".$Ic_number."'"; 

Open in new window

0
Ray PaseurCommented:
I don't think SELECT is the query with the error.  I think the UPDATE query has an extraneous semi-colon embedded in the query string.
0
Terry WoodsIT GuruCommented:
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:
$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."'";

Open in new window


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)
0
Ray PaseurCommented:
@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.

// 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

Open in new window

0
dolamitejenkinsAuthor Commented:
Thank you
0
Ray PaseurCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.