?
Solved

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

Posted on 2013-05-23
10
Medium Priority
?
517 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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 

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 111

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 111

Accepted Solution

by:
Ray Paseur earned 2000 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

WordPress Tutorial 2: Terminology

An important part of learning any new piece of software is understanding the terminology it uses. Thankfully WordPress uses fairly simple names for everything that make it easy to start using the software.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
The viewer will learn how to count occurrences of each item in an array.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

801 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