Solved

insert, but if row is already there, then do nothing

Posted on 2013-05-23
10
494 Views
Last Modified: 2013-06-02
email_doc2 (unix_timestamp) is int primary key auto_increment unique key

INSERT ignore INTO email_doc2 (unix_timestamp,from_email,body,sms_type) VALUES ('1335401250','J','nice. ',1);


want to insert many rows using php for loop
but if row is already there, then do nothing
0
Comment
Question by:rgb192
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 39192310
You could just handle the error that it will inevitably give.  Primary keys are there to prevent things like that.
0
 
LVL 23

Expert Comment

by:nemws1
ID: 39192564
Well, the INSERT IGNORE should handle that just fine.  You may need to create a compound index if unix_timestamp is *always* different and sometimes from_email, body, and sms_type are the same.

ALTER TABLE yourtablename
ADD UNIQUE IX_from_email_body_sms_type (from_email, body, sms_type);

Open in new window

0
 
LVL 25

Expert Comment

by:Ron Malmstead
ID: 39192643
I agree with sl8rz,

Handle the error.
If you violate a primary key, it will do nothing anyway.  No reason to "double check" if the record exists.  That would have to be evaluated before the insert function is attempted.  All formulas, functions, methods, calculations, "arithmetic"..always work from the inside out.  For example.. nesting an, INSERT INTO ...WHERE <fieldname> NOT IN(SELECT <fieldname> FROM Tablename), ..the 'WHERE' would have to be evaluated first.  Either prevent multiple attempts at the same record, or handle the error.  Prevention would be preferable though I understand sometimes circumstances may not permit.
0
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 

Author Comment

by:rgb192
ID: 39193405
I do not want a new insert to delete saved values on current key row

please verify
0
 
LVL 25

Expert Comment

by:Ron Malmstead
ID: 39194476
If you violate a primary key trying to insert, it will not insert nor will it update the record.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39195051
PRIMARY KEY columns are, by definition, UNIQUE.  An attempt to insert a duplicate value into a UNIQUE index will cause MySQL to throw error #1062.  You can catch that error and ignore it, handling other errors with the appropriate programmatic response.

This is a teaching example and should be upgraded to use something other than the MySQL extension.  But that said, the principle it illustrates is going to be the same.

<?php // RAY_mysql_UNIQUE.php
error_reporting(E_ALL);


// DEMONSTRATE HOW TO HANDLE UNIQUE COLUMNS WITH ERRNO = 1062


// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php

// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES



// HOW TO INSERT A UNIQUE VALUE AND HANDLE DUPLICATE VALUES
// ESCAPING THE EXTERNAL DATA
$safe_unique_thing = mysql_real_escape_string($external_unique_thing);

// CONSTRUCTING THE QUERY
$isql   = "INSERT INTO table ( my_unique_thing ) VALUES ( '$safe_unique_thing' )";

// RUN THE QUERY AND TEST FOR SUCCESS
$err    = FALSE;
if (!$i = mysql_query("$isql"))
{
    // IF ERROR IS NOT 1062, THIS IS A BAD THING
    $err = mysql_errno();
    if ($err != 1062)
    {
       /* HANDLE MYSQL ERROR CONDITION */
    }

    // IF ERROR IS 1062, THIS IS A DUPLICATE KEY
    else
    {
       echo htmlentities($external_unique_thing) . ' ALREADY EXISTS IN THE TABLE';
    }
}
if (!$err)
{
    echo htmlentities($external_unique_thing) . ' HAS BEEN INSERTED INTO THE TABLE';
}

Open in new window

Best regards, ~Ray
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 39195368
Having a primary key (as explained above) IS the protection you want.  That is one of the main reasons for having them.
0
 

Author Comment

by:rgb192
ID: 39203539
by: Ray_PaseurPosted on 2013-05-24 at 13:27:25ID: 39195051

Notice: Undefined variable: external_unique_thing in

// HOW TO INSERT A UNIQUE VALUE AND HANDLE DUPLICATE VALUES
// ESCAPING THE EXTERNAL DATA
$safe_unique_thing = mysql_real_escape_string($external_unique_thing);
0
 
LVL 110

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 39204307
Of course -- it's a teaching example!  You are encouraged to experiment with it, defining and adding your own variables. Through the experimentation you can observe different inputs and outputs to learn how the code works and what it will do with your data.

Here is an upgraded version showing the MySQLi extension.

<?php // RAY_mysqli_UNIQUE.php
error_reporting(E_ALL);
echo '<pre>' . PHP_EOL;


// DEMONSTRATE HOW TO HANDLE UNIQUE COLUMNS WITH ERRNO = 1062


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQLI
// MAN PAGE: http://php.net/manual/en/mysqli.overview.php
// MAN PAGE: http://php.net/manual/en/class.mysqli.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-stmt.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-result.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-warning.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-sql-exception.php <-- DID NOT WORK PHP 5.3+, MySQL 5.1+
// MAN PAGE: http://php.net/manual/en/mysqli.construct.php
// MAN PAGE: http://php.net/manual/en/mysqli.real-escape-string.php
// MAN PAGE: http://php.net/manual/en/mysqli.query.php
// MAN PAGE: http://php.net/manual/en/mysqli.errno.php
// MAN PAGE: http://php.net/manual/en/mysqli.error.php
// MAN PAGE: http://php.net/manual/en/mysqli.insert-id.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.num-rows.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-array.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-object.php


// CREATE AN ARRAY OF NAMES TO USE FOR TEST DATA (NOTE COLLISIONS ON SOME NAMES)
$test_names_arrays = array
( array( "fname" => "Walter" , "lname" => "Williams" )
, array( "fname" => "Ray"    , "lname" => "Paseur"   )
, array( "fname" => "Bill"   , "lname" => "O'Reilly" )
, array( "fname" => "Ray"    , "lname" => "Capece"   )
, array( "fname" => "John"   , "lname" => "Paseur"   )
)
;

// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACTIVATE THIS TO SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id    INT                NOT NULL AUTO_INCREMENT PRIMARY KEY
, fname VARCHAR(24) UNIQUE NOT NULL DEFAULT ''
, lname VARCHAR(24) UNIQUE NOT NULL DEFAULT ''
)
"
;

// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACTIVATE THIS TO SHOW THE RESULTS OF THE QUERY
// var_dump($res);


// LOADING OUR DATA INTO THE TABLE
foreach ($test_names_arrays as $person)
{
    // ESCAPE THE DATA FOR SAFE USE IN A QUERY
    $safe_fn  = $mysqli->real_escape_string($person['fname']);
    $safe_ln  = $mysqli->real_escape_string($person['lname']);

    // CONSTRUCT THE QUERY USING THE ESCAPED VARIABLES
    $sql = "INSERT INTO my_table ( fname, lname ) VALUES ( '$safe_fn', '$safe_ln' )";

    // RUN THE QUERY TO INSERT THE ROW
    $res = $mysqli->query($sql);

    // IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
    if (!$res)
	{
	    // IS THIS A DUPLICATE UNIQUE KEY?
	    if ($mysqli->errno == 1062)
	    {
	        echo "MySQLI DID NOT INSERT A ROW CONTAINING <b>$safe_fn $safe_ln</b> - DUPLICATE KEY ERROR" . PHP_EOL;
	        continue;
	    }
	    $err
	    = 'QUERY FAILURE:'
	    . ' ERRNO: '
	    . $mysqli->errno
	    . ' ERROR: '
	    . $mysqli->error
	    . ' QUERY: '
	    . $sql
	    ;
	    trigger_error($err, E_USER_ERROR);
	}


    // GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED
    $id  = $mysqli->insert_id;
    echo "MySQLI INSERTED A ROW CONTAINING <b>$safe_fn $safe_ln</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
echo PHP_EOL;


// SHOW WHAT A QUERY FAILURE LOOKS LIKE (NOT A UNIQUE KEY ERROR)
$sql = "SELECT oopsie FROM my_table";
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err /* USE THIS FLAG TO CAUSE FATAL ERROR: , E_USER_ERROR  */);
}

// SHOW THE RESULTS OF THE QUERY
var_dump($res);
var_dump($mysqli);

Open in new window

0
 

Author Closing Comment

by:rgb192
ID: 39213976
complete example makes question easy to understand insert ignore and what rows are skipped

thanks
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses how to implement server side field validation and display customized error messages to the client.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question