I receive lists of values like (comma seperator)
D1,D2,D3
and I need to check if each of these values exists in DB or not (check if D1 exists , D2 and D3)
I know I can loop and send "N" queries to DB (but sometimes I receive long list)
is there any automatic way in MySQL to handle this? or in PHP
table to match with:
id data
1 D1
2 D3
3 D4
4 D5
thanks,
To check if it exists you need to query your database for it, either like this:
SELECT data FROM table WHERE `data` IN ('D1','D2','D3')
or in loop, looking for each value separately (depending on logic of your app)
foreach( ... ) {
select ....
}
you can use this query
select * from table where data in ('D1','D2','D3','D4')
select * is a performance issue for large tables.data column so there is no point in querying for the id since he doesn't know what id to look for. If he has index on data column he should be ok.This is a snippet you can use in PHP to retrieve the information needed.
// setup database connection
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
// define the list of values to check for
$values_list = "D1,D2,D3";
// explode list into array
$values = explode(',', $values_list);
// initiate empty array for database values
$db_values = array();
// create the query
$query = "SELECT data FROM table WHERE `data` IN ('" . implode("','", $values) . "')";
// run the query
if ($result = mysqli::query($query)) {
// loop through results and add values to database value array
while($obj = $result->fetch_object()){
$db_values[] = $obj->data;
}
}
$result->close();
// go through given values and check if they are in database value array or not, print result
foreach ($values AS $value) {
if (in_array($value, $db_values)) {
echo "Value $value exists in database.\r\n";
}
else {
echo "Value $value does not exist in database.\r\n";
}
}
SELECT should be enough.