Check if exists

[SkyRanger]

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.

[Barand]

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
}

 

[SkyRanger]

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";
		}

 

[Barand]

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)

[SkyRanger]

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

[Barand]

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 June 10, 2019 by Barand