views:

256

answers:

6

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.

+1  A: 

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.

jmz
What about in the first question, when the value is at the beginning of the string? It won't match (doesn't have a , before it). Also don't forget to *sanitise your input*!!
Blair McMillan
hey, thanks! why, how else would you structure it? i just thought it would be easier to save an array for certain tasks.
@Blair: The query matches if the number starts the array, or is the only element in the array.@user317005: You should save each array value as a separate row, like `create table table_name (id int, value int)` each id would have multiple rows, but it's much easier to do queries.
jmz
@jmz Sorry, for whatever reason I didn't notice the OR's in there, so I only saw the first `LIKE`.@user317005 As jmz says, you are effectively storing a MANY-MANY relationship (each 'item' can have many 'values' and each 'value' can belong to many 'items'). There are plenty of articles around for how to do that.
Blair McMillan
A: 

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.

Blair McMillan
A: 

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]
Sadat
'23,4' LIKE '3'. not quite correct
knittl
'23,4' LIKE '3', its not true. '3,4' like '3' its true.
Sadat
um yes. sorry, you were right. my fault
knittl
its ok @knitt :)
Sadat
+2  A: 

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

VolkerK
A: 

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.

codeholic
A: 

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

Mark Baker