CODEWITHSUNDEEP
phparraysloops
G--
G--

Reputation: 33

How to iterate through database results, compare each value to that in an array and return the matching id?

I am trying to build a function that will be given an array. and from this array, iterate through an entire table in the database trying to find a match. If it does find a match. I would like it to echo out the ID of that match from the database table.

If possible I would also like it to say if it found any close matches?

What's making this tricky for me is that it needs to match all values despite their order.

for example, if the function is given this array:

$array = array(1,2,3,4,5)`

and finds this array in the database:

$array = array(2,3,5,1,4)

it should consider this a match

Also, if it finds

array(1,2,3,4,5,6)

It should output this this as a match except for value 6.

Upvotes: 0

Views: 2001

Answers (4)

Nouphal.M
Nouphal.M

Reputation: 6344

You could use any of the options:

option 1: 

$array = array(1,2,3,4,5);
$query = "SELECT id FROM youtable WHERE id IN(".implode(",",$array).")";

option 2:

$array = array(1,2,3,4,5);
$query = "SELECT id FROM yourtable";//Select ids

Now iterate through query results, say results are stored in $query_results,

$result=array();
foreach($query_results as $row){
   if(in_array($row['id'],$array)){
           $result[] = $row['id'];
   }

}

Upvotes: 1

Nic Wortel
Nic Wortel

Reputation: 11423

Let me refrase your question, is this correct?

Based on an array of ID's, return all records whose ID's are in the array.

If so, use the IN operator:

SELECT *
FROM tableName
WHERE columnName IN (value1, value2, value3, etc.)

So first we need to transform the given array into a comma-seperated list:

$comma_seperated = implode(', ', $array);

Now we have a comma-seperated list we can use in our query:

$comma_seperated = implode(', ', $array);

$query = "SELECT *
FROM tableName
WHERE id IN (" . $comma_seperated . ")";

// execute query, don't use mysql_* functions, etc. ;)

Upvotes: 1

FoPi
FoPi

Reputation: 145

Case 1:

Maybe you can select the unique values from the database.

SELECT DISTINCT unique_values_that_need_for_compare_column FROM table

This SQL result will be the $databaseValues variable value.

Than iterate through the array that the function gets.

foreach ($functionArray as $value) {
    if (in_array($value, $databaseValues)) {
        // you have a match
        echo $value;
    }
}

Case 2:

Or you can make a query for every value:

foreach ($functionArray as $value) {
    $query = "SELECT COUNT(*) as match FROM table WHERE unique_values_that_need_for_compare_column = " . intval($value);
    // fetch the result into the $queryResult variable and than check
        if ($queryResult['match']) {
            // you have a match
            echo $value;
        }
    }

Upvotes: 0

souvickcse
souvickcse

Reputation: 7804

You can use array difference to get the result just run the code and you will understand Case 1

$array1 = array(1,2,3,4,5);
$array2 = array(1,2,3,4,5,6);
$result = array_diff($array1, $array2);

print_r($result);

Case 2

$array1 = array(1,2,3,4,5);
$array2 = array(1,2,3,4,5,6);
$result = array_diff($array2, $array1);

print_r($result);

And after that you can the count of $result and put it in your logic.

Upvotes: 0

Related Questions