insert in database table if not exist

Hello Experts:

I want to insert values in database table where the values doesn't exist... I am trying to write the code using PDO / PHP / mysql.

I am reading values from same database table and inserted in same database...

Due to the limitations of the web application, I can add unique key in the table and use INSERT IGNORE MYSQL technique... I have to go with IF NOT EXIST technique...

Please let me know if anybody has done this before.
LVL 5
CalmSoulAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
Run insert into with on duplicate if insert fails not available.



What is the alternative to the insert I.e. If not exist condition insert
Else do what?

If not exists (select column from table where condition) .....
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CalmSoulAuthor Commented:
Can I have some code examples please?
0
NerdsOfTechTechnology ScientistCommented:
INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c;

Open in new window


If a duplicate key would be created on insert,  rows value c = c (AKA the existing row doesn't change; nothing else is inserted).

Whereas, on a new unique value of a key, a new record is inserted.
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

rjohnsonjrCommented:
Another option would be to do a select sql statement in your PHP code to check for the value.  If it does not exist ...  if it does don't do the insert.  Thoughts?
0
Ray PaseurCommented:
If you try to insert a duplicate data element into a column marked UNIQUE, MySQL will throw error number 1062.  You can catch this number and do something else (retry the insert with a different value, skip the insertion, whatever makes sense for your application).  You can install this and run it to see the effects in action.

<?php // demo/random_unique_string.php
error_reporting(E_ALL);
echo "<pre>";

/**
 * GENERATE A SHORT UNIQUE RANDOM STRING FOR USE AS SOME KIND OF KEY.
 *
 * WE DELIBERATELY OMIT LOOK-ALIKE LETTERS LIKE O and 0, I and 1.
 * IF LOWER CASE LETTERS ARE INCLUDED, IT'S WISE TO OMIT LOWER-CASE L.
 *
 * IN SOME FONTS WE MIGHT WANT TO OMIT S,5 AND Z,2.
 *
 * NOTE THAT THE DATA BASE MUST HAVE THE rand_key FIELD DEFINED AS "UNIQUE"
 * NOTE THAT THE LENGTH ARGUMENT MUST MATCH THROUGHOUT SO WE DEFINE() IT.
 */


define('ARG_LENGTH', 7);


Class Randu
{
    public $keys, $errs, $cnt;
    protected $mytable  = 'myKeys';
    protected $mycolumn = 'rand_key';

    public function __construct()
    {
        $this->keys = array();
        $this->errs = array();
        $this->cnt  = -1;

        // 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
		$this->mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

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


		// IN REAL LIFE, THIS WOULD BE A PERMANENT TABLE
		$len = ARG_LENGTH;
		$sql
		=
		"
		CREATE TEMPORARY TABLE $this->mytable
		( id                 INT                   NOT NULL AUTO_INCREMENT PRIMARY KEY
		, $this->mycolumn    VARCHAR($len)  UNIQUE NOT NULL DEFAULT '?'
		)
		ENGINE=MyISAM DEFAULT CHARSET=ascii
		"
		;
		// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
		if (!$res = $this->mysqli->query($sql))
		{
		    $err
		    = 'QUERY FAILURE:'
		    . ' ERRNO: '
		    . $this->mysqli->errno
		    . ' ERROR: '
		    . $this->mysqli->error
		    . ' QUERY: '
		    . $sql
		    ;
		    trigger_error($err, E_USER_ERROR);
		}
    }

    // FUNCTION TO MAKE A RANDOM STRING
	public function random_string($length)
	{
	    // POSSIBLE COMBINATIONS > HUNDREDS OF MILLIONS IF LENGTH > 6
	    //           1...5...10...15...20...25...30.
	    $alphabet = "ABCDEFGHJKMNPQRSTUVWXYZ23456789";
	    $strlen   = strlen($alphabet);
	    $string   = NULL;
	    while(strlen($string) < $length)
	    {
	        $random = mt_rand(0,$strlen);
	        $string .= substr($alphabet, $random, 1);
	    }
	    return($string);
	}

	// FUNCTION TO ENSURE THE RANDOM STRING IS UNIQUE
	public function make_random_key()
	{
	    $key = NULL;
	    $this->cnt++;

	    // GENERATE A UNIQUE AND RANDOM TOKEN
	    while ($key == NULL)
	    {
	        $key = $this->random_string(ARG_LENGTH);
	        $sql = "INSERT INTO $this->mytable ( $this->mycolumn ) VALUES ( '$key' )";
	        $res = $this->mysqli->query($sql);

	        // IF THERE IS A QUERY ERROR
	        if (!$res)
	        {
	            // THE EXPECTED QUERY ERROR WOULD BE A DUPLICATE VALUE, NULLIFY THE KEY AND START OVER
	            if ($this->mysqli->errno == 1062)
	            {
	                // trigger_error("1062 Duplicate Key Event: $key at " . number_format($this->cnt), E_USER_NOTICE);
	                echo PHP_EOL   . "1062 Duplicate Key Event: $key at " . number_format($this->cnt);
	                $this->errs[$this->cnt] = $key;
	                $key = NULL;
	            }
	            // OTHER UNEXPECTED QUERY ERROR
	            else
	            {
	                $err
	                = 'QUERY FAILURE:'
	                . ' ERRNO: '
	                . $this->mysqli->errno
	                . ' ERROR: '
	                . $this->mysqli->error
	                . ' QUERY: '
	                . $sql
	                ;
	                trigger_error($err, E_USER_ERROR);
	            }
	        }
	    }
	    $this->keys[$this->cnt] = $key;
	    return $key;
    }

}


// MAKE LOTS OF UNIQUE AND RANDOM STRINGS
$num = 0;
$max = 320000;
$ran = new Randu;
$aaa = time();
while ($num < $max)
{
    $thing = $ran->make_random_key();
    $num++;
}

// SHOW THE WORK PRODUCT
$zzz = time();
$lap = $zzz - $aaa;
echo PHP_EOL . "CREATED $max UNIQUE KEYS IN $lap SECONDS" . PHP_EOL;
echo PHP_EOL . number_format(count($ran->errs)) . " DUPLICATE KEYS";
echo PHP_EOL;
print_r($ran->errs);
echo PHP_EOL ;
print_r($ran->keys);

Open in new window

0
arnoldCommented:
in norder for a code examples to fit your need, a clearer, more detailed explanation data example may help illustrate your need.
0
CalmSoulAuthor Commented:
@Arnold: Here is my original question has more details of what I am looking for

http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28626846.html
0
CalmSoulAuthor Commented:
@Ray Paseur - Can you please provide more details on this solution? What this code is doing?

thanks!
0
arnoldCommented:
Unfortunately, the prior question sheds little light in my opinion.

your PHP code will effectively be a set of loops.
loop through data in table 1, perform quries on table 2 and table 3, absence of data in the query, you would generate an insert into the applicable table.

please post the output for the following command for each table:
show create table <tablename>

where <tablename> is a variable that needs to be replaced.
0
CalmSoulAuthor Commented:
show create tables:

CREATE TABLE `test1` (
 `id` int(4) NOT NULL AUTO_INCREMENT,
 `title` varchar(300) CHARACTER SET utf8 NOT NULL,
 `URL` varchar(300) CHARACTER SET utf8 NOT NULL,
 `img` varchar(300) CHARACTER SET utf8 NOT NULL,
 `tagsv` varchar(300) CHARACTER SET utf8 NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=749 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Open in new window



CREATE TABLE `qa_posts` (
 `postid` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `type` enum('Q','A','C','Q_HIDDEN','A_HIDDEN','C_HIDDEN','Q_QUEUED','A_QUEUED','C_QUEUED','NOTE') NOT NULL,
 `parentid` int(10) unsigned DEFAULT NULL,
 `categoryid` int(10) unsigned DEFAULT NULL,
 `catidpath1` int(10) unsigned DEFAULT NULL,
 `catidpath2` int(10) unsigned DEFAULT NULL,
 `catidpath3` int(10) unsigned DEFAULT NULL,
 `acount` smallint(5) unsigned NOT NULL DEFAULT '0',
 `amaxvote` smallint(5) unsigned NOT NULL DEFAULT '0',
 `selchildid` int(10) unsigned DEFAULT NULL,
 `closedbyid` int(10) unsigned DEFAULT NULL,
 `userid` int(10) unsigned DEFAULT NULL,
 `cookieid` bigint(20) unsigned DEFAULT NULL,
 `createip` int(10) unsigned DEFAULT NULL,
 `lastuserid` int(10) unsigned DEFAULT NULL,
 `lastip` int(10) unsigned DEFAULT NULL,
 `upvotes` smallint(5) unsigned NOT NULL DEFAULT '0',
 `downvotes` smallint(5) unsigned NOT NULL DEFAULT '0',
 `netvotes` smallint(6) NOT NULL DEFAULT '0',
 `lastviewip` int(10) unsigned DEFAULT NULL,
 `views` int(10) unsigned NOT NULL DEFAULT '0',
 `hotness` float DEFAULT NULL,
 `flagcount` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `format` varchar(20) CHARACTER SET ascii NOT NULL DEFAULT '',
 `created` datetime NOT NULL,
 `updated` datetime DEFAULT NULL,
 `updatetype` char(1) CHARACTER SET ascii DEFAULT NULL,
 `title` varchar(800) DEFAULT NULL,
 `content` varchar(8000) DEFAULT NULL,
 `tags` varchar(800) DEFAULT NULL,
 `name` varchar(40) DEFAULT NULL,
 `notify` varchar(80) DEFAULT NULL,
 `twitter_done` int(11) DEFAULT '0',
 `fb_pub` tinyint(4) NOT NULL DEFAULT '0',
 `vimeoid` int(11) DEFAULT NULL,
 PRIMARY KEY (`postid`),
 KEY `type` (`type`,`created`),
 KEY `type_2` (`type`,`acount`,`created`),
 KEY `type_4` (`type`,`netvotes`,`created`),
 KEY `type_5` (`type`,`views`,`created`),
 KEY `type_6` (`type`,`hotness`),
 KEY `type_7` (`type`,`amaxvote`,`created`),
 KEY `parentid` (`parentid`,`type`),
 KEY `userid` (`userid`,`type`,`created`),
 KEY `selchildid` (`selchildid`,`type`,`created`),
 KEY `closedbyid` (`closedbyid`),
 KEY `catidpath1` (`catidpath1`,`type`,`created`),
 KEY `catidpath2` (`catidpath2`,`type`,`created`),
 KEY `catidpath3` (`catidpath3`,`type`,`created`),
 KEY `categoryid` (`categoryid`,`type`,`created`),
 KEY `createip` (`createip`,`created`),
 KEY `updated` (`updated`,`type`),
 KEY `flagcount` (`flagcount`,`created`,`type`),
 KEY `catidpath1_2` (`catidpath1`,`updated`,`type`),
 KEY `catidpath2_2` (`catidpath2`,`updated`,`type`),
 KEY `catidpath3_2` (`catidpath3`,`updated`,`type`),
 KEY `categoryid_2` (`categoryid`,`updated`,`type`),
 KEY `lastuserid` (`lastuserid`,`updated`,`type`),
 KEY `lastip` (`lastip`,`updated`,`type`),
 CONSTRAINT `qa_posts_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `qa_users` (`userid`) ON DELETE SET NULL,
 CONSTRAINT `qa_posts_ibfk_2` FOREIGN KEY (`parentid`) REFERENCES `qa_posts` (`postid`),
 CONSTRAINT `qa_posts_ibfk_3` FOREIGN KEY (`categoryid`) REFERENCES `qa_categories` (`categoryid`) ON DELETE SET NULL,
 CONSTRAINT `qa_posts_ibfk_4` FOREIGN KEY (`closedbyid`) REFERENCES `qa_posts` (`postid`)
) ENGINE=InnoDB AUTO_INCREMENT=1965 DEFAULT CHARSET=utf8

Open in new window



REATE TABLE `qa_postmetas` (
 `postid` int(10) unsigned NOT NULL,
 `title` varchar(40) NOT NULL,
 `content` varchar(8000) NOT NULL,
 PRIMARY KEY (`postid`,`title`),
 CONSTRAINT `qa_postmetas_ibfk_1` FOREIGN KEY (`postid`) REFERENCES `qa_posts` (`postid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Open in new window

0
arnoldCommented:
how does data in table1 relates to data in table3?

is id <=> postid
 title <=> title


It looks as though each table repeats some data from other tables.

normalizing the data such that there is a relationship between the tables such that content exists only in one table, and is referenced in others.
0
CalmSoulAuthor Commented:
`test1` has no relationship

 `postid` in qa_post table is same as postid in qa_postmetas
0
Ray PaseurCommented:
Sure.  The code snippet generates a short, random unique string.  However random strings are just that - random, and there may be collisions between strings.  In theory, at least, you could generate something like "abcde" twice.  So the script prevents that unwanted duplication by INSERTing the strings into a table and putting them in a column marked UNIQUE.  The script teaches now to detect the MySQL 1062 error and thereby know that the string was already in the UNIQUE column.

Sorry, I'm not sure if I am helping -- perhaps I do not understand the question.
0
arnoldCommented:
I thought you wanted an example that will go through some table (table1) and will insert if missing..
0
CalmSoulAuthor Commented:
Here are 3 steps:

1) INSERT in table2 from table1 (if not exist)
2) GET POSTIDs from table2 and then
3) INSERT in table3 (if not exist)

like this ...
0
Ray PaseurCommented:
It seems like you may be new to PHP and MySQL.  This article can help you get started with basic questions like this one.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html

It also seems that you're new to the E-E community and you may not yet understand the grading guidelines.   You are expected to explain any grade that is marked down.  Since you've been in "radio silence" for weeks, then gave a bad grade, this is your opportunity to provide that explanation.

The grading guidelines are here:
http://support.experts-exchange.com/customer/portal/articles/481419
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.