Solved

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

Posted on 2013-05-23
10
449 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
  • 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 M
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
 

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 M
ID: 39194476
If you violate a primary key trying to insert, it will not insert nor will it update the record.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 108

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 108

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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
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 look for a specific file type in a local or remote server directory using PHP.

760 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now