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
Solved

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

Posted on 2013-05-23
10
473 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 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 109

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 109

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
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 …

838 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