CODEWITHSUNDEEP
phpmysql
Pamela
Pamela

Reputation: 684

Mysql Query to Select data where, WHERE clause contains both comma separated and individual values

I have a mysql field which may contain single center_id values like AB or csv values like AB,AJ etc..

Currently i do the following in php:

$usercenter = $_SESSION['usercenter'];
$searchForValue = ',';
if( strpos($usercenter, $searchForValue) !== false ) {
$centerid = explode(',', $usercenter);
}
else  {
$centerid = $usercenter;    
}

and in sql query i do:

SELECT id,node
FROM followup
WHERE center_id IN('".implode("','",$centerid)."')

If there are csv values, it works correctly. How can I write the query to check for both csv values and single value ??

Requesting help..

Upvotes: 0

Views: 1702

Answers (3)

Qirel
Qirel

Reputation: 26490

While it doesn't use indexed values (so for huge datasets it can be rather slow), you can use the MySQL function FIND_IN_SET(). This can search for a single value in a comma-separated list.

While this can't search for multiple values at once, from what I understand in the question, you're just looking for a single value at a time.

The function takes the value to search for (case-sensitive!), and then the column to search in.

SELECT id,node
FROM followup
WHERE FIND_IN_SET('AB', center_id)

If its the other way around, and you're looking to use all those values in a WHERE IN (..), then you can just explode() it regardless if it is one or not - the returnvalue will be an array which you can use, it doesn't matter if its 1 or greater length. You should however, use a prepared statement in doing so. You can achieve that by generating a dynamic query based on the length of the array.

MySQLi:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // Set MySQLi to throw exceptions - this allows you to automagically check for errors 

// Create connection, $mysqli = new mysqli(...);

$parts = explode(',', $usercenter); // Split the comma-separated values 
if (empty($parts)) {
    // Handle it
}

// Generate dynamic SQL, execute and fetch
$stmt = $mysqli->prepare("SELECT id, node
                          FROM followup 
                          WHERE center_id IN (".implode(',', array_fill(0, count($parts ), '?')).")");
$stmt->bind_param(str_repeat("s", count($parts)), ...$parts);
$stmt->execute();
$stmt->bind_result($id, $node);
while ($stmt->fetch()) {
    // Use $id and $node for each row
}
$stmt->close();

PDO:

// Create connection, $pdo = new PDO(...);
// You should set PDO to throw exceptions on errors

$parts = explode(',', $usercenter); // Split the comma-separated values 
if (empty($parts)) {
    // Handle it
}

$stmt = $pdo->prepare("SELECT id, node
                          FROM followup 
                          WHERE center_id IN (".implode(',', array_fill(0, count($parts ), '?')).")");
$stmt->execute([...$parts]);
while ($row = $stmt->fetch()) {
    // Use $row['id'] and $row['node'] for each row
}

You might also want to look into filtering out empty values after exploding.

Upvotes: 1

Forge Web Design
Forge Web Design

Reputation: 835

You should use FIND_IN_SET() function returns the position of a string within a list of strings. it is working both comma separated and individual values. try the following query

SELECT id,node
FROM followup
WHERE FIND_IN_SET('center_id','".implode("','",$centerid)."')

Upvotes: 1

Code Spirit
Code Spirit

Reputation: 5061

Given your schema the only solution I can think of is using LIKE.

SELECT id, node FROM followup WHERE center_id LIKE "%AD%";

But this will also select rows with CAD, ADA etc. and can only used to check a single option per condition (so you would have to use AND/OR with multiple LIKE statements.)

Also your solution is kind of an antipattern. Normally you would use a one-to-many relation for this.

Upvotes: 1

Related Questions