Jump to content
SkyRanger

Check if exists

Recommended Posts

Trying to check if if entries exist before inserting database.

This is what I have so far:

	  	$kuemname = isset($_POST['input_array_name']) ? $_POST['input_array_name'] : "" ;
		$kuemaddy = isset($_POST['input_array_email']) ? $_POST['input_array_email'] : "" ;
		$kuemqueue = isset($_POST['input_array_queue']) ? $_POST['input_array_queue'] : "" ;
	  	
	 foreach($kuemname as $emkey=>$value){
$kudoemcheck = $wpdb->get_results( "SELECT * from $tableemname where kuemaddy='$kuemaddy[$emkey]'  and kuemqueue='$kuemqueue[$emkey]'" );
 }
        $rowcount = $wpdb->num_rows;
        
        if ($rowcount > 0) {
			echo $kuemaddy[$emkey]. " with " .$kuemqueue[$emkey]. " already exists<br/>";
		} else {
			echo "insert stuff here";
		}

So what I am trying to do is for example:

email@account.com    Helpdesk  - Exists
email2@account.com Accounting - Not exists
email3@account.com  Tech Support - Exists

The one that exists will echo that those entries exist while the one does not exist will insert in to database and states entry entered in to database.  So far I can get the last one in the list to say it does not exist but unsure how to display both or more stating exists and how to get the one that does not exist to insert in to database.

Share this post


Link to post
Share on other sites

Your foreach() loop runs 3 queries. You then check and process the results of the final query.

Put the rowcount checking and output inside the loop too, so it is performard for each query. IE

foreach (...) {
    query
    check result
    output depending on check
}

 

Share this post


Link to post
Share on other sites

Perfect. Thanks Barand works pefectly.

foreach($kuemname as $emkey=>$value){
$kudoemcheck = $wpdb->get_results( "SELECT * from $tableemname where kuemaddy='$kuemaddy[$emkey]'  and kuemqueue='$kuemqueue[$emkey]'" );

 $rowcount = $wpdb->num_rows;
        
        if ($rowcount > 0) {
			echo $kuemaddy[$emkey]. " with " .$kuemqueue[$emkey]. " already exists<br/>";
		} else {
			echo "insert stuff here";
		}

 

Share this post


Link to post
Share on other sites

The efficient way ...

Put a UNIQUE CONSTRAINT on addy and queue columns so they cannot be duplicated

CREATE TABLE `skyranger` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `addy` varchar(50) DEFAULT NULL,
  `queue` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `addyq` (`addy`,`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

We now start off with your two existing records

SELECT * FROM skyranger;
+----+--------------------+--------------+
| id | addy               | queue        |
+----+--------------------+--------------+
|  1 | email@account.com  | Helpdesk     |
|  2 | email3@account.com | Tech Support |
+----+--------------------+--------------+

Now we attempt to insert all three of your new inputs (ignoring duplicate key errors)

mysql> INSERT IGNORE INTO skyranger (addy, queue) values
    -> ('email@account.com', 'Helpdesk'),
    -> ('email2@account.com', 'Accounting'),
    -> ('email3@account.com', 'Tech Support');
Query OK, 1 row affected, 2 warnings (0.12 sec)
Records: 3  Duplicates: 2  Warnings: 2

mysql> SELECT * FROM skyranger;
+----+--------------------+--------------+
| id | addy               | queue        |
+----+--------------------+--------------+
|  3 | email2@account.com | Accounting   |
|  2 | email3@account.com | Tech Support |
|  1 | email@account.com  | Helpdesk     |
+----+--------------------+--------------+

Just the email2@account.com (id 3) record has been inserted, which is what was required. (All done with a single query. No looping of queries to check if a record already exists and then performing separate inserts)

Share this post


Link to post
Share on other sites

Thanks Barand but I need to ensure I can still use the same email address for another queue in case they want to be notified on multiple queues when email notifications are sent out.

ie:

email1@account.com  Accounting
email2@account.com Tech Support
email1@account.com Tech Support

Share this post


Link to post
Share on other sites
Posted (edited)

Not a problem

Adding your 3 new records...

mysql> INSERT IGNORE INTO skyranger (addy, queue) VALUES
    -> ('email1@account.com', 'Accounting'),
    -> ('email2@account.com', 'Tech Support'),
    -> ('email1@account.com', 'Tech Support');
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM skyranger ORDER BY addy, queue;
+----+--------------------+--------------+
| id | addy               | queue        |
+----+--------------------+--------------+
|  6 | email1@account.com | Accounting   |
|  1 | email1@account.com | Helpdesk     |
|  8 | email1@account.com | Tech Support |
|  3 | email2@account.com | Accounting   |
|  7 | email2@account.com | Tech Support |
|  2 | email3@account.com | Tech Support |
+----+--------------------+--------------+
6 rows in set (0.00 sec)

Because the constraint is on the two columns then it is combinations of those columns that are unuque. So, for example, you cannot have two "email2/Accounting" records.

Edited by Barand

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.