Jump to content

Check if exists


SkyRanger

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.

Link to comment
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";
		}

 

Link to comment
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)

Link to comment
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

Link to comment
Share on other sites

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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.