MySQL (exploding/matching array)

Question

Question1:

MySQL table

id | array
1 | 1,2,3
2 | 2
3 | 2,3
4 | 4,5,6

$_GET['id'] = 2;
$a = mysql_query("SELECT * FROM `table` WHERE `array` ??? '$_GET[id]'");

In this step, I want to run through the entire array and see if it matches with the $_GET['id'], so it should output:

ids: 1,2,3

Question2:

MySQL table

id | array
1 | 4,5,6
2 | 3,4,7

$_GET['id'] = 4;
$a = mysql_query("SELECT * FROM `table` WHERE `array` ??? '$_GET[id]'");

In this step, I only want to match against the first element in the array, so it should output:

id: 4

I can only think of using PHP to do this, but I'd rather do all that just within the MySQL query, if that is even possible.

$a = mysql_query("SELECT * FROM `table`");
while($b = mysql_fetch_assoc($a))
{
    $elements = explode(',', $b['array']);
    foreach($elements as $element)
    {
        if($element == $_GET['id'])
        {
            echo $b['id'].'<br />';
        }
    }
}

or

$a = mysql_query("SELECT * FROM `table`");
while($b = mysql_fetch_assoc($a))
{
    $array = $b['array'];

    if(in_array($_GET['id'], $array))
    {
        echo $b['id'].'<br />';
    }
}

that would look just awful.

Answer 1

That you can/should structure your database differently has already been mentioned (see http://en.wikipedia.org/wiki/Database_normalization). But....

See FIND_IN_SET()

mysql> SELECT FIND_IN_SET('b','a,b,c,d');
-> 2

e.g.

<?php
$mysql = init();    
bar($mysql, 1);
bar($mysql, 2);
bar($mysql, 3);
bar($mysql, 4);


function bar($mysql, $x) {
  $sql_x = mysql_real_escape_string($x, $mysql);
  $result = mysql_query("SELECT id, foo FROM soTest WHERE FIND_IN_SET('$sql_x', foo)", $mysql) or die(mysql_error());

  echo "$x:\n";
  while( false!==($row=mysql_fetch_array($result, MYSQL_ASSOC)) ) {
    echo $row['id'], ' ', $row['foo'], "\n";
  }
  echo "----\n";
}

function init() {
  $mysql = mysql_connect('localhost', 'localonly', 'localonly') or die(mysql_error());
  mysql_select_db('test', $mysql) or die(mysql_error());
  mysql_query('CREATE TEMPORARY TABLE soTest (id int auto_increment, foo varchar(64), primary key(id))', $mysql) or die(__LINE__.' '.mysql_error());
  mysql_query("INSERT INTO soTest (foo) VALUES ('1,2,3'), ('2,4'), ('3'), ('2,3'), ('1,2')", $mysql) or die(__LINE__.' '.mysql_error());
  return $mysql;
}

prints

1:
1 1,2,3
5 1,2
----
2:
1 1,2,3
2 2,4
4 2,3
5 1,2
----
3:
1 1,2,3
3 3
4 2,3
----
4:
2 2,4
----

MySQL can't use indices to perform this search, i.e. the query results in a full table scan, see Optimizing Queries with EXPLAIN

---

edit:
For your second question you only have to change the WHERE-clause to
WHERE FIND_IN_SET('$sql_x', foo)=1

Answer 2

Your data structure in the DB is not optimal for querying the way you want it.

For the first question:

mysql_query("SELECT * FROM table WHERE array LIKE '%,$_GET[id],%' OR array LIKE '$_GET[id],%' OR array LIKE '%,$_GET[id]' OR array = '$_GET[id]'");

For the second:

mysql_query("SELECT id, SUBSTR(array, 1, POSITION(',' IN array) - 1) AS array FROM table WHERE array LIKE '$_GET[id],%' OR array = '$_GET[id]'");

As you can see, these queries aren't pretty, but they'll do what you want.

Answer 3

Untested, but you should be able to use:

Question 1:

SELECT * FROM table WHERE array REGEXP '(^|,)?(,|$)';
// Match either the start of the string, or a , then the query value, then either a , or the end of the string

Question 2:

SELECT * FROM table WHERE array REGEXP '^?(,|$)';
// Match the start of the string, then the query value, then either a , or the end of the string

Where ? is replaced with your $_GET value. No idea on the performance of this.

Answer 4

First One:

SELECT * FROM table WHERE array LIKE '$_GET[id],%' OR array LIKE '%,$_GET[id],%' OR array LIKE '%,$_GET[id]' OR array = '$_GET[id]

Second One:

SELECT * FROM table WHERE array LIKE '$_GET[id],%' OR array = '$_GET[id]

Explanation:

• '$_GET[id],%' will match, if array is start with $_GET[id]
• '%,$_GET[id],%' will match, if $_GET[id] is between any two of array items
• '%,$_GET[id]' will match, if array is end with $_GET[id]
• array = '$_GET[id]' match, if the array contains only one item equal to $_GET[id]

Answer 5

I'd recommend you to bring your database to the first normal form, e. g.

CREATE TABLE t_master (
    id INT PRIMARY KEY AUTO_INCREMENT
);

CREATE TABLE t_array (
    id INT PRIMARY KEY AUTO_INCREMENT,
    master_id INT NOT NULL,
    value INT,
    CONSTRAINT fk_array_master_id FOREIGN KEY (master_id) REFERENCES t_master (id)
);

Then you can find records in t_master that have a specific value with

$q = 'SELECT m.* ' .
    'FROM t_master AS m INNER JOIN t_array AS a ON a.master_id = m.id ' .
    "WHERE a.value = '" . mysql_real_escape_string($_GET['id'], $db) . "' " .
    'GROUP BY m.id';

The most important advantage is that if you have a lot of values, you can add an index to find them much faster:

ALTER TABLE t_array ADD INDEX idx_value (value);

A less evident, but not the last advantage is that your queries become more logical and structured.

Answer 6

If you can't normalise your schema (which is the best option:

SELECT * 
  FROM table 
 WHERE ','+array+',' LIKE '%,$_GET[id],%' 

But if you need to access the records by id, then you really should normalise