Jump to content

UPDATE vs. INSERT...


Jim R

Recommended Posts

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)

Link to comment
Share on other sites

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

 

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

 

 

Hmmm...not it's not inserting. 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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=''

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.