Jump to content

UPDATE vs. INSERT...


Jim R

Recommended Posts

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?

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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. 

 

 

Link to comment
Share on other sites

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)

?>

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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)

?>

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

 

 

Link to comment
Share on other sites

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)

?>

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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]')";
}

Link to comment
Share on other sites

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']."'");
?>

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.