1

I've got a db_table where some cells contain comma separated arrays to link them to all the corresponding rows in a different table column, like [846,1400,1657].

So how do i select all rows where that cell has one matching value in the array?

$result = "";
$searchid = $_POST['id'];

$query = mysql_query("SELECT * FROM Company WHERE City/*<-the array*/ LIKE '%$searchid%'");
$count = mysql_num_rows($query);

if($count == 0){
    $result .= "no companies";
} else {
    while($row = mysql_fetch_array($query)){
        $name = $row['Name'];
        $result .= "<div class=\"result\">$name </div>";

    }
}

echo $result;

This gives all companies with $searchid, but if $searchid = 25, it will return the ones with 25 in that column as well as those with for example 250 and 725. :/

Without the wildcards tho:

$query = mysql_query("SELECT * FROM Company WHERE City/*<-the array*/ = '$searchid'");

will return the rows with only $searchid in that cell, for example if $searchid = 25 a cell with [25] and not one with [25,456] or [12,13,25]

I've goggled for 48 hours and tried everything i found, think this should be an easy one to crack. What have i missed???

esqew
  • 42,425
  • 27
  • 92
  • 132
Kayuzumi Widenroth
  • 15
  • 3
  • You can try using the [`IN Operator`](http://www.mysqltutorial.org/sql-in.aspx) - `WHERE (expr|column) IN ('value1','value2',...)` – Funk Forty Niner Sep 17 '14 at 16:53
  • @Fred-ii- I think you can't combine `LIKE` with the `IN` operator but I may be wrong. – Manolis Agkopian Sep 17 '14 at 16:57
  • Your database design is rather poor. It appears that there is a many-to-many relationship between Company and City entities. In this case, have a third table that would associate each company with the city it is in. Then, join the tables in your query. – DRD Sep 17 '14 at 17:06
  • @DRD The relation is more like this-to-many, julst like with country and cities. – Kayuzumi Widenroth Sep 17 '14 at 17:16
  • It seems that one company could be in many cities and one city could have many companies. No? – DRD Sep 17 '14 at 17:23

3 Answers3

0

You could try using the like statement : 

SELECT * FROM Company WHERE City LIKE "%25%";

Obviously this would select ones with 250 in too but it's a start.

aarcarr
  • 186
  • 1
  • 14
0

You can't do split in mysql, you should have used a transitional table :

city_company
------------
id_city
id_company

Where you could have stored all these relations.

Though you could use like for a quick dirty hack. Here is how you could do it :

$query = mysql_query("SELECT * FROM Company WHERE concat(',',City,',') LIKE '%,$searchid,%'");
Loïc
  • 11,804
  • 1
  • 31
  • 49
0

The FIND_IN_SET function might work for this one. Here's an example:

SELECT * FROM Company WHERE FIND_IN_SET('25', City) > 0

The example assumes that the City column is the one with the comma-separated list; I think that's what your question states.

Ed Gibbs
  • 25,924
  • 4
  • 46
  • 69
  • That did the trick! Thanks a bunch! One question tho, why is the "> 0" necessary? – Kayuzumi Widenroth Sep 17 '14 at 17:28
  • If the value of City is `'13,250,25,40'` then `FIND_IN_SET('25', City)` will return 3 because 25 is the third item in the comma-delimited list. If 25 isn't found in the comma-delimited list, FIND_IN_SET will return zero. So the "> 0" just means '25' was found somewhere in the list. Hope that makes sense :) Also - if this is still working for you could you kindly accept my answer? That way others with similar questions will know that it worked. Thanks! – Ed Gibbs Sep 17 '14 at 18:23
  • Oh, that clarifies it. Sorry, new to this site, accepted the answer. – Kayuzumi Widenroth Sep 17 '14 at 21:27