CODEWITHSUNDEEP
phpmysqlexplode
JonasT
JonasT

Reputation: 616

Read mysql table until first comma

I have this query:

$sql = "SELECT * FROM tutorials WHERE ( device LIKE '%".$device."%' AND version LIKE '%".$version."%' AND firmware LIKE '%".$firmware."%' ) LIMIT 1";

The table:

device | a, b, c, d
version | 2, 3, 4
firmware | 5

Now I want to have the first string from device(a),version(2) and firmware(5) til the first comma, or if there is no comma the whole text.

I thought of explode(), but I dont really know. I would prefer php, since I want to echo the numbers.

Upvotes: 1

Views: 357

Answers (1)

Dor
Dor

Reputation: 7494

MySQL solution:

SELECT  SUBSTRING_INDEX(`device`, ',', 1),
    SUBSTRING_INDEX(`version`, ',', 1),
    SUBSTRING_INDEX(`firmware`, ',', 1)
FROM `tutorials` WHERE `id`=123 LIMIT 1 /*
    Why LIMIT? `id` should be unique */

PHP solution:

function first_value($str) {
    $pos = strpos($str, ',');
    if ($pos !== false)
        return substr($str, 0, $pos);
    return $str;
}
$q = mysql_query('SELECT * FROM `tutorials` WHERE `id`='. (int)$id .' LIMIT 1 ');
$arr = mysql_fetch_array($q);

$arr['device'] = first_value($arr['device']); // etc

But as being said by Kerrek SB, your should really normalize your table.

Upvotes: 2

Related Questions