Link to home
Start Free TrialLog in
Avatar of Dolamite Jenkins
Dolamite JenkinsFlag for United States of America

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

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

ASKER CERTIFIED SOLUTION
Avatar of Terry Woods
Terry Woods
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

Avatar of Dolamite Jenkins

ASKER

Thank you
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

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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)
@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

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