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

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
LVL 1
rgb192Asked:
Who is Participating?
 
Ray PaseurConnect With a Mentor Commented:
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
 
David L. HansenProgrammer AnalystCommented:
You could just handle the error that it will inevitably give.  Primary keys are there to prevent things like that.
0
 
nemws1Database AdministratorCommented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Ron MalmsteadInformation Services ManagerCommented:
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
 
rgb192Author Commented:
I do not want a new insert to delete saved values on current key row

please verify
0
 
Ron MalmsteadInformation Services ManagerCommented:
If you violate a primary key trying to insert, it will not insert nor will it update the record.
0
 
Ray PaseurCommented:
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
 
David L. HansenProgrammer AnalystCommented:
Having a primary key (as explained above) IS the protection you want.  That is one of the main reasons for having them.
0
 
rgb192Author Commented:
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
 
rgb192Author Commented:
complete example makes question easy to understand insert ignore and what rows are skipped

thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.