PDO - Check if found in 3 tables and if not insert

PHP
1

I need help i need to run 3 different row counts and if all = 0 it inserts the record and if it’s found if the row statement is higher than 0 it then ignores it and moves on.

my head is a bit scrambled any one able to assist with the if statement


                //Check If in Untested
		$check = "SELECT * FROM `server` WHERE `untested` LIKE \\'%".$dbrow."%\\' LIMIT 0, 30 ";
		//Check If in working
		$check = "SELECT * FROM `server` WHERE `working` LIKE \\'%".$dbrow."%\\' LIMIT 0, 30 ";
		//Check If in bad
		$check = "SELECT * FROM `server` WHERE `bad` LIKE \\'%".$dbrow."%\\' LIMIT 0, 30 ";
		
		$check = $DBH->prepare($check);
		$check->execute();
		$count = $check->rowCount();

                // Not In DB and Insert
		$sql = "INSERT INTO server (`untested`) VALUES (:server)";
		$q = $DBH->prepare($sql);
		$q->execute(array(':server'=>$dbrow));
2

Solved it now by the looks of things


//Check If in Untested
		$check = "SELECT * FROM `server` WHERE `untested` LIKE \\'%".$dbrow."%\\' LIMIT 0, 30 ";
		//Check If in working
		$check = "SELECT * FROM `server` WHERE `working` LIKE \\'%".$dbrow."%\\' LIMIT 0, 30 ";
		//Check If in bad
		$check = "SELECT * FROM `server` WHERE `bad` LIKE \\'%".$dbrow."%\\' LIMIT 0, 30 ";
		
		
		$check = $DBH->prepare($check);
		$check->execute();
		$count = $check->rowCount();
		if ($count = <1){
		// Not In DB and Insert
		$sql = "INSERT INTO server (`untested`) VALUES (:server)";
		$q = $DBH->prepare($sql);
		$q->execute(array(':server'=>$dbrow));
		}
3

That will only look at the bad column… (as it was the last query to receive its value).

There are a couple of ways of handling this though. One way would be to loop through the queries you need to run.

$queries = array("SELECT * FROM `server` WHERE `untested` LIKE \\'%:dbrow%\\' LIMIT 0, 30 ",
		"SELECT * FROM `server` WHERE `working` LIKE \\'%:dbrow%\\' LIMIT 0, 30 ",
		"SELECT * FROM `server` WHERE `bad` LIKE \\'%:dbrow%\\' LIMIT 0, 30 ");
$rowcounts = [];
foreach ($queries as $key => $query) // replace with typical for loop, if this doesn't work
{
		$check = $DBH->prepare($query);
		$check->execute(array(':dbrow' => $dbrow));
		$rowcounts[] = $check->rowCount();
}

if (array_sum($rowcounts) == 0)
{
		// Not In DB and Insert
		$sql = "INSERT INTO server (`untested`) VALUES (:server)";
		$q = $DBH->prepare($sql);
		$q->execute(array(':server'=>$dbrow));
}

Another approach, would be to rework your query using UNION and then compare the three results. Something like (this is untested, so there may be a few errors):

SELECT COUNT(*) AS RowCount FROM `server` WHERE `untested` LIKE \\'%:dbrow%\\' LIMIT 0, 30
      UNION ALL SELECT COUNT(*) AS RowCount FROM `server` WHERE `working` LIKE \\'%:dbrow%\\' LIMIT 0, 30
      UNION ALL SELECT COUNT(*) AS RowCount FROM `server` WHERE `bad` LIKE \\'%:dbrow%\\' LIMIT 0, 30

That should give you 3 rows returned, and you can look at the row count column for each row to see if they are 0. You can probably group that query in a sub-query to use SUM() and get back 1 row with 1 column indicating the sum of all three as well…

4

Cheers cpradio :slight_smile: many thanks

have issues with finding errors as host is using ngnix so will hopefully work later.

5

worked a charm!
thank you very very much for your assistance!!

6

You’re very welcome.