UPDATE vs. INSERT...

[Jim R]

I went back to the code I had, without the escapes, as I was getting the same error without the escapes.  It is updating the data base, but it isn't passing the User through to the payment page.  So it appears to be getting stuck on the following code:

 

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
   header( 'Location: /fall-league/payment' );

mysql_close($con)

[Jim R]

I commented out of this code, and it passes the User through to the Payment page.  What does this code do? 

 

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }

 

 

Hmmm...not it's not inserting. 

[JonnoTheDev]

It is your insert query that is causing the error because you are not escaping the post data. If the post data contains any special characters such as ' they will break the query. I do not normally do this but I have cleaned and rewritten your entire script, commenting each section. I strongly advise you learn the basics of php / mysql through a good book.

 

<?php
/*
connect to database
*/
if(!$con = mysql_connect("localhost","jwrbloom_","redcoach")) {
die("Could not connect to database: ".mysql_error());
}
mysql_select_db("jwrbloom_wpMIB", $con);

$nameFirst = $_POST['nameFirst'];
$nameLast = $_POST['nameLast'];
$email = $_POST['email'];
$addressHome = $_POST['addressHome'];
$stateHome = $_POST['stateHome'];
$zipHome = $_POST['zipHome'];
$phoneHome = $_POST['phoneHome'];
$phoneMobile = $_POST['phoneMobile'];
$school = $_POST['school'];
$grade = $_POST['grade'];
$coachSchool = $_POST['coachSchool'];
$feet = $_POST['feet'];
$inshces = $_POST['inches'];

/*
search for existing row
*/
$sql = "SELECT id FROM fallLeague10 WHERE nameFirst='".mysql_real_escape_string($nameFirst)."' AND nameLast='".mysql_real_escape_string($nameLast)."' AND school='".mysql_real_escape_string($school)."'";
if(!$result = mysql_query($sql)) {
die(mysql_error()."<br />Query: ".$sql);	
}
if(mysql_num_rows($result)) {
  $row = mysql_fetch_assoc($result);
  /*
  update existing row
  */
  $sql = "UPDATE fallLeague10 SET 
                  confirm='y', 
                  email='".mysql_real_escape_string($email)."', 
                  addressHome='".mysql_real_escape_string($addressHome)."', 
                  stateHome='".mysql_real_escape_string($stateHome)."', 
                  zipHome='".mysql_real_escape_string($zipHome)."', 
                  phoneHome='".mysql_real_escape_string($phoneHome)."', 
                  phoneMobile='".mysql_real_escape_string($phoneMobile)."', 
                  coachSchool='".mysql_real_escape_string($coachSchool)."', 
                  feet='".mysql_real_escape_string($feet)."', 
                  inches='".mysql_real_escape_string($inches)."' 
                  WHERE id='".$row['id']."'";
if(!$result = mysql_query($sql)) {
	die(mysql_error()."<br />Query: ".$sql);	
}
}
else {
  /*
  insert new row
  */	
  $sql = "INSERT INTO fallLeague10 SET 
			  nameFirst='".mysql_real_escape_string($nameFirst)."',
			  nameLast='".mysql_real_escape_string($nameLast)."',
                  confirm='y', 
                  email='".mysql_real_escape_string($email)."', 
                  addressHome='".mysql_real_escape_string($addressHome)."', 
                  stateHome='".mysql_real_escape_string($stateHome)."', 
                  zipHome='".mysql_real_escape_string($zipHome)."', 
                  phoneHome='".mysql_real_escape_string($phoneHome)."', 
                  phoneMobile='".mysql_real_escape_string($phoneMobile)."', 
                  coachSchool='".mysql_real_escape_string($coachSchool)."', 
                  feet='".mysql_real_escape_string($feet)."', 
                  inches='".mysql_real_escape_string($inches)."' 
                  WHERE id='".$row['id']."'";
if(!$result = mysql_query($sql)) {
	die(mysql_error()."<br />Query: ".$sql);	
}
}

/*
redirect user
*/
header("Location:/fall-league/payment");
exit();
?>

[Jim R]

I really do appreciate your help and your time.  I do.  I have been involved in a lot of topics on here asking for help and giving a little from the experiences I have had.  I never been given the advice of using those escape strings.

 

It was working and worked multiple times, and I tried it on different names, both inserting and updating.  Suddenly it doesn't work. 

 

That said, the code you wrote didn't work.  I got an 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 id=''' at line 14

Query: INSERT INTO fallLeague10 SET confirm='y', nameFirst='Jim', nameLast='Reamer', email='jwr######@gmail.com', addressHome='10######## Drive', stateHome='IN', zipHome='46032', phoneHome='#######', phoneMobile='######5', coachSchool='Heady', feet='6', inches='0' WHERE id=''

[JonnoTheDev]

Sorry, my mistake

 

<?php
/*
connect to database
*/
if(!$con = mysql_connect("localhost","jwrbloom_","redcoach")) {
   die("Could not connect to database: ".mysql_error());
}
mysql_select_db("jwrbloom_wpMIB", $con);

$nameFirst = $_POST['nameFirst'];
$nameLast = $_POST['nameLast'];
$email = $_POST['email'];
$addressHome = $_POST['addressHome'];
$stateHome = $_POST['stateHome'];
$zipHome = $_POST['zipHome'];
$phoneHome = $_POST['phoneHome'];
$phoneMobile = $_POST['phoneMobile'];
$school = $_POST['school'];
$grade = $_POST['grade'];
$coachSchool = $_POST['coachSchool'];
$feet = $_POST['feet'];
$inshces = $_POST['inches'];

/*
search for existing row
*/
$sql = "SELECT id FROM fallLeague10 WHERE nameFirst='".mysql_real_escape_string($nameFirst)."' AND nameLast='".mysql_real_escape_string($nameLast)."' AND school='".mysql_real_escape_string($school)."'";
if(!$result = mysql_query($sql)) {
   die(mysql_error()."<br />Query: ".$sql);   
}
if(mysql_num_rows($result)) {
  $row = mysql_fetch_assoc($result);
  /*
  update existing row
  */
  $sql = "UPDATE fallLeague10 SET 
                  confirm='y', 
                  email='".mysql_real_escape_string($email)."', 
                  addressHome='".mysql_real_escape_string($addressHome)."', 
                  stateHome='".mysql_real_escape_string($stateHome)."', 
                  zipHome='".mysql_real_escape_string($zipHome)."', 
                  phoneHome='".mysql_real_escape_string($phoneHome)."', 
                  phoneMobile='".mysql_real_escape_string($phoneMobile)."', 
                  coachSchool='".mysql_real_escape_string($coachSchool)."', 
                  feet='".mysql_real_escape_string($feet)."', 
                  inches='".mysql_real_escape_string($inches)."' 
                  WHERE id='".$row['id']."'";
   if(!$result = mysql_query($sql)) {
      die(mysql_error()."<br />Query: ".$sql);   
   }
}
else {
  /*
  insert new row
  */   
  $sql = "INSERT INTO fallLeague10 SET 
              nameFirst='".mysql_real_escape_string($nameFirst)."',
              nameLast='".mysql_real_escape_string($nameLast)."',
              confirm='y', 
              email='".mysql_real_escape_string($email)."', 
              addressHome='".mysql_real_escape_string($addressHome)."', 
              stateHome='".mysql_real_escape_string($stateHome)."', 
              zipHome='".mysql_real_escape_string($zipHome)."', 
              phoneHome='".mysql_real_escape_string($phoneHome)."', 
              phoneMobile='".mysql_real_escape_string($phoneMobile)."', 
              coachSchool='".mysql_real_escape_string($coachSchool)."', 
              feet='".mysql_real_escape_string($feet)."', 
              inches='".mysql_real_escape_string($inches)."'";
   if(!$result = mysql_query($sql)) {
      die(mysql_error()."<br />Query: ".$sql);   
   }
}

/*
redirect user
*/
header("Location:/fall-league/payment");
exit();
?>

[Jim R]

It's not recognizing the similar record and just inserting. 

[JonnoTheDev]

The code is 100% correct. 'similar' is incorrect. It is looking for an EXACT match of firstname, lastname and school name. If there is no match it will insert a new record.

[Jim R]

It didn't work.  I went back to the form, entered my first name, my last name and, the same school name to test it, and I changed my address to a previous one.  It didn't update it.  It inserted a new record. 

 

Could there be an issue with the variables in the IF statement?  In what you wrote, they don't appear to match the query. 

[JonnoTheDev]

No the code is correct. Simple test. I am adding this in to print the number of rows returned. It will also print the query to the screen. Look in your database to see if it matches up.

 

<?php
$sql = "SELECT id FROM fallLeague10 WHERE nameFirst='".mysql_real_escape_string($nameFirst)."' AND nameLast='".mysql_real_escape_string($nameLast)."' AND school='".mysql_real_escape_string($school)."'";
if(!$result = mysql_query($sql)) {
   die(mysql_error()."<br />Query: ".$sql);   
}
print "I have found ".mysql_num_rows($result)." matching the following query<br />".$sql;
exit();
?>

 

So the whole thing looks like

<?php
/*
connect to database
*/
if(!$con = mysql_connect("localhost","jwrbloom_","redcoach")) {
   die("Could not connect to database: ".mysql_error());
}
mysql_select_db("jwrbloom_wpMIB", $con);

$nameFirst = $_POST['nameFirst'];
$nameLast = $_POST['nameLast'];
$email = $_POST['email'];
$addressHome = $_POST['addressHome'];
$stateHome = $_POST['stateHome'];
$zipHome = $_POST['zipHome'];
$phoneHome = $_POST['phoneHome'];
$phoneMobile = $_POST['phoneMobile'];
$school = $_POST['school'];
$grade = $_POST['grade'];
$coachSchool = $_POST['coachSchool'];
$feet = $_POST['feet'];
$inshces = $_POST['inches'];

/*
search for existing row
*/
$sql = "SELECT id FROM fallLeague10 WHERE nameFirst='".mysql_real_escape_string($nameFirst)."' AND nameLast='".mysql_real_escape_string($nameLast)."' AND school='".mysql_real_escape_string($school)."'";
if(!$result = mysql_query($sql)) {
   die(mysql_error()."<br />Query: ".$sql);   
}
print "I have found ".mysql_num_rows($result)." matching the following query<br />".$sql;
exit();



if(mysql_num_rows($result)) {
  $row = mysql_fetch_assoc($result);
  /*
  update existing row
  */
  $sql = "UPDATE fallLeague10 SET 
                  confirm='y', 
                  email='".mysql_real_escape_string($email)."', 
                  addressHome='".mysql_real_escape_string($addressHome)."', 
                  stateHome='".mysql_real_escape_string($stateHome)."', 
                  zipHome='".mysql_real_escape_string($zipHome)."', 
                  phoneHome='".mysql_real_escape_string($phoneHome)."', 
                  phoneMobile='".mysql_real_escape_string($phoneMobile)."', 
                  coachSchool='".mysql_real_escape_string($coachSchool)."', 
                  feet='".mysql_real_escape_string($feet)."', 
                  inches='".mysql_real_escape_string($inches)."' 
                  WHERE id='".$row['id']."'";
   if(!$result = mysql_query($sql)) {
      die(mysql_error()."<br />Query: ".$sql);   
   }
}
else {
  /*
  insert new row
  */   
  $sql = "INSERT INTO fallLeague10 SET 
              nameFirst='".mysql_real_escape_string($nameFirst)."',
              nameLast='".mysql_real_escape_string($nameLast)."',
              confirm='y', 
              email='".mysql_real_escape_string($email)."', 
              addressHome='".mysql_real_escape_string($addressHome)."', 
              stateHome='".mysql_real_escape_string($stateHome)."', 
              zipHome='".mysql_real_escape_string($zipHome)."', 
              phoneHome='".mysql_real_escape_string($phoneHome)."', 
              phoneMobile='".mysql_real_escape_string($phoneMobile)."', 
              coachSchool='".mysql_real_escape_string($coachSchool)."', 
              feet='".mysql_real_escape_string($feet)."', 
              inches='".mysql_real_escape_string($inches)."'";
   if(!$result = mysql_query($sql)) {
      die(mysql_error()."<br />Query: ".$sql);   
   }
}

/*
redirect user
*/
header("Location:/fall-league/payment");
exit();
?>

[Jim R]

It was an exact match.  I back up to the registration page, so all the information is the same.  From there I changed street address.  It didn't reflect the change.  It entered a new record. 

[JonnoTheDev]

What does it display on screen with the code I have just given you.

[Jim R]

Here is what it says:

 

I have found 0 matching the following query

SELECT id FROM fallLeague10 WHERE nameFirst='Jim' AND nameLast='Reamer' AND school='Carmel'

 

However, there is an exact match in the database.  I'm looking right at it.

[JonnoTheDev]

Run this query directly in mysql server / phpMyAdmin whatever you use to look at the database records. Does it return a row?

 

SELECT id FROM fallLeague10 WHERE nameFirst='Jim' AND nameLast='Reamer' AND school='Carmel'

[Jim R]

I found the error, and it has nothing to do with what we're talking about. 

 

In what you wrote, the School is missing.  So as I cleared my test record, when it put it in there it didn't insert the school name. 

[JonnoTheDev]

Thank God. I'm going home now!

 

<?php
  /*
  insert new row
  */   
  $sql = "INSERT INTO fallLeague10 SET 
              nameFirst='".mysql_real_escape_string($nameFirst)."',
              nameLast='".mysql_real_escape_string($nameLast)."',
              school='".mysql_real_escape_string($school)."',
              confirm='y', 
              email='".mysql_real_escape_string($email)."', 
              addressHome='".mysql_real_escape_string($addressHome)."', 
              stateHome='".mysql_real_escape_string($stateHome)."', 
              zipHome='".mysql_real_escape_string($zipHome)."', 
              phoneHome='".mysql_real_escape_string($phoneHome)."', 
              phoneMobile='".mysql_real_escape_string($phoneMobile)."', 
              coachSchool='".mysql_real_escape_string($coachSchool)."', 
              feet='".mysql_real_escape_string($feet)."', 
              inches='".mysql_real_escape_string($inches)."'";
   if(!$result = mysql_query($sql)) {
      die(mysql_error()."<br />Query: ".$sql);   
   }
?>

[Jim R]

It works now.  Thanks...I do appreciate it. 

 

Make sure you remove the test echo if should port this over to something you need.  That goes for anyone who would use this.

[MadTechie]

Well done neil.johnson

great job..