UPDATE vs. INSERT...

[Jim R]

I'm pulling information from a form that INSERTS into my database.  I think I have the UPDATE syntax down for when a User enters information that is already in the database.  UPDATE....WHERE....FormA = ColumnA...AND...FormB = ColumnB.

 

What I need is help with is if it's not updating an existing User's information, getting it to insert a new row.  Do I set it up the same way as the update, just with INSERT...WHERE...FormA == ColumnA AND FormB == ColumnB?

 

 

[bh]

The "Replace into" statement helps you.

[JonnoTheDev]

You need a SELECT prior to your INSERT / UPDATE.

Run a SELECT to see if the record already exists. If it does then you perform an UPDATE query using the primary key. If it doesn't then you perform an INSERT query to add a new row.

The REPLACE INTO function can be slow compared to the above.

[Jim R]

@BH, I'll look more into that for sure.

 

 

@Neil, do I just run a Select * or just the fields I want to check (nameFirst, nameLast, school)?  I guess this is the direction that confuses me. 

[Jim R]

After looking at this a little more, I would rather not use Replace.  It will increase the number of rows I have with every entry.  I like to keep track of how many individuals are actually in my database, so I can compare from year to year.  If it gets right down to it, I could use it, but I'd prefer not.

 

 

Neil,

 

I think I can figure out the query syntax if I understand more of what you're talking about.  If I SELECT to see if the record already exists, how do I create conditions?  Are we talking about IF statements or WHERE statements within the query?

 

My query would look like:

 

SELECT * from TableA

 

UPDATE SET (column information)

WHERE TableA = $_POST['value']

 

[JonnoTheDev]

<?php
// find record
$result = mysql_query("SELECT id FROM users WHERE name='Neil'");
// record found
if(mysql_num_rows($result)) {
  $row = mysql_fetch_assoc($result);
  // update existing row
  $result = mysql_query("UPDATE users SET name='Neil Johnson' WHERE id='".$row['id']."'");
}
else {
// no record found - insert a new row
$result = mysql_query("INSERT INTO users SET name='Neil Johnson'");
}
?>

[Jim R]

So instead of

 

SELECT id FROM users WHERE name='Neil'  

 

It should be????

 

....WHERE nameFirst='$_POST['nameFirst']' && nameLast='$_POST['nameLast']' && school=$_POST['school']

 

 

I don't get how using the ID helps me, and I'm trying to match information from form not another table. 

 

 

[Jim R]

Here is what I have so far, realizing I still need the IF and ELSEIF statements, as well as the UPDATE.

 

<?php
$con = mysql_connect("localhost","db_name","db_password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

$nameFirst=$_POST['nameFirst'];
$nameLast=$_POST['nameLast'];
$school=$_POST['school'];

mysql_select_db("db_table_name", $con);

$result = mysql_query(
"SELECT * FROM fallLeague10 
WHERE nameFirst='$nameFirst'
AND nameLast='$nameLast'
AND school='$school'
");

// IF statement?

//UPDATE code

//ELSEIF


$sql="INSERT INTO fallLeague09reg(nameFirst,nameLast,email,addressHome,stateHome,zipHome,phoneHome,phoneMobile,school,grade,coachSchool,feet,inches)
VALUES
('$_POST[nameFirst]','$_POST[nameLast]','$_POST[email]','$_POST[addressHome]','$_POST[stateHome]','$_POST[zipHome]','$_POST[phoneHome]','$_POST[phoneMobile]','$_POST[school]','$_POST[grade]','$_POST[coachSchool]','$_POST[feet]','$_POST[inches]')";

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

mysql_close($con)

?>

[JonnoTheDev]

yep, just add it in. you have marked the correct place. your insert query goes within the else (not elseif) condition.

[Jim R]

Any  help on what the IF statement should look like?  What you have above isn't registering well in my head right now.  Not sure about 'rows' and I'm not using ID's.  Sometimes I have mental blocks when it comes to coding. 

[JonnoTheDev]

Im not getting your database structure. Why would you have 2 tables containing the same fields and data?

You are selecting from 'fallLeague10' and then inserting into 'fallLeague09reg'. If there is a record found in 'fallLeague10' what table do you want to update and how are you obtaining the records' primary key?

[Jim R]

Crap...I'm not.  My fault.   

 

When I put that PHP page online, it will all be fallLeague10. 

[JonnoTheDev]

Your code isn't going to work unless you have the correct database structure

[Jim R]

I know, but the insert code was left over from last year's entries. 

 

Here is the correct version:

 

<?php
$con = mysql_connect("localhost","db_name","db_password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

$nameFirst=$_POST['nameFirst'];
$nameLast=$_POST['nameLast'];
$school=$_POST['school'];

mysql_select_db("db_table_name", $con);

$result = mysql_query(
"SELECT * FROM fallLeague10 
WHERE nameFirst='$nameFirst'
AND nameLast='$nameLast'
AND school='$school'
");

$sql="INSERT INTO fallLeague10(nameFirst,nameLast,email,addressHome,stateHome,zipHome,phoneHome,phoneMobile,school,grade,coachSchool,feet,inches)
VALUES
('$_POST[nameFirst]','$_POST[nameLast]','$_POST[email]','$_POST[addressHome]','$_POST[stateHome]','$_POST[zipHome]','$_POST[phoneHome]','$_POST[phoneMobile]','$_POST[school]','$_POST[grade]','$_POST[coachSchool]','$_POST[feet]','$_POST[inches]')";

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

mysql_close($con)

?>

[JonnoTheDev]

OK so if you are running this query first to check for an existing record:

<?php
$result = mysql_query("SELECT * FROM fallLeague10 WHERE nameFirst='$nameFirst' AND nameLast='$nameLast' AND school='$school'");
?>

If a result does exist, what data do you want to update as opposed to inserting a new record.

[Jim R]

I have the SELECT part already, as well as the INSERT portion after an ELSEIF.  My question gets to how do I set up the IF portion?  I read what you posted above, but I'm not sure what applies to me or what is mostly general recommendation.

 

 

[Jim R]

Would this work:  (I need to change the echo part of the IF statement into my UPDATE code)

 

<?php
$con = mysql_connect("localhost","jwrbloom_","redcoach");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

$nameFirst=$_POST['nameFirst'];
$nameLast=$_POST['nameLast'];
$school=$_POST['school'];

mysql_select_db("jwrbloom_wpMIB", $con);

$result = mysql_query(
"SELECT * FROM fallLeague10 
WHERE nameFirst='$nameFirst'
AND nameLast='$nameLast'
AND school='$school'
");

$aff_rows = mysql_affected_rows($result);
if( $aff_rows === 1 ) {
     
 echo 'One row was updated.'; // Or do something else, whatever . . .
} 

else {
     

$sql="INSERT INTO fallLeague10(nameFirst,nameLast,email,addressHome,stateHome,zipHome,phoneHome,phoneMobile,school,grade,coachSchool,feet,inches)
VALUES
('$_POST[nameFirst]','$_POST[nameLast]','$_POST[email]','$_POST[addressHome]','$_POST[stateHome]','$_POST[zipHome]','$_POST[phoneHome]','$_POST[phoneMobile]','$_POST[school]','$_POST[grade]','$_POST[coachSchool]','$_POST[feet]','$_POST[inches]')";

}

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

mysql_close($con)

?>

[fenway]

Did you try?

[JonnoTheDev]

You still haven't answered my question:

 

From your initial SELECT query, if a result does exist, what data do you want to update as opposed to inserting a new record.

 

The issue here is that you are not making it clear what you are trying to do. I don't think you know yourself. This makes it incredibly difficult to help you even though what you are trying to do is so simple.

 

Here is something that will help you write your code. Use PSEUDO code first before writing any PHP! This makes it much easier to get the logic in the right order.

http://www.minich.com/education/wyo/stylesheets/pseudocode.htm

 

Example:

 

1.  Connect to database
2.  Query the database for an existing record by using the POST data
2a. If a record exists update something
2b. If no record exists, insert a new record with POST data
3.  Close database connection
4.  Redirect user

 

Now write your PHP code over the pseudo code, completing each stage.

[Jim R]

Neil,

 

When you look at the Select vs. what I would otherwise Insert I have showed what I'm trying to do.  I'm trying to Update the rest of it into an already existing record.  My issue is getting it do differentiate between when to Insert vs. when to Update.  I know how to use Select, Insert and Update separately.  I'm just trying to figure out the syntax of the IF, ELSE (ELSEIF).

 

I've asked twice before about the syntax of the IF and establishing the right condition.

[JonnoTheDev]

I've asked twice before about the syntax of the IF and establishing the right condition.

The exact syntax has been posted in my second reply to this thread. When you perform the initial SELECT you do not want to select all fields (*). You only want to select the primary key i.e id. If returned you use it in your UPDATE query. Look again at my second post. The solution is right there.

[Jim R]

Ok...I used the IF statement as you described it.  It's Inserting just fine if doesn't find a match.  If it does find a match, I'm getting an error.  It's looking for a matching first name, last name and school, and if found, it's supposed to Update the information. 

 

Here is the 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 '1' at line 1

 

Here is the code I'm using:

 

<?php
$con = mysql_connect("localhost","jwrbloom_","redcoach");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

$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'];

mysql_select_db("jwrbloom_wpMIB", $con);

$result = mysql_query(
"SELECT id FROM fallLeague10 
WHERE nameFirst='$nameFirst'
AND nameLast='$nameLast'
AND school='$school'
");

if(mysql_num_rows($result)) {
  $row = mysql_fetch_assoc($result);
  // update existing row
  $result = mysql_query("UPDATE fallLeague10 SET confirm='y', email='$email', addressHome='$addressHome', stateHome='$stateHome', zipHome='$zipHome', phoneHome='$phoneHome', phoneMobile='$phoneMobile', coachSchool='$coachSchool', feet='$feet', inches='$inches'  
   WHERE id='".$row['id']."'");
}

else {
     

$result="INSERT INTO fallLeague10(confirm,nameFirst,nameLast,email,addressHome,stateHome,zipHome,phoneHome,phoneMobile,school,grade,coachSchool,feet,inches)
VALUES
('y','$_POST[nameFirst]','$_POST[nameLast]','$_POST[email]','$_POST[addressHome]','$_POST[stateHome]','$_POST[zipHome]','$_POST[phoneHome]','$_POST[phoneMobile]','$_POST[school]','$_POST[grade]','$_POST[coachSchool]','$_POST[feet]','$_POST[inches]')";
}

[JonnoTheDev]

You should always escape data that comes from URLS or forms before querying or updating database records. It would also help to print your query to the screen so you can see where the error is. Use mysql_real_escape_string() on all data as follows:

 

<?php
$result = mysql_query("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']."'");
?>

[Jim R]

I'm getting the same 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 '1' at line 1

[Jim R]

And since changing to the escape string, it won't Insert either.