mysql like clause doesnt work against array php

Question

Hello am trying to count how many rows are in location column where there names include any of the names which are in the predefined $uk_array.

mysql prints out that I don't have any value in my column "there are ()" that matches a name from the array although there is one "Dublin, Ireland".

Here is the code:

    <?php
    include'connect.php';
    $uk_array = array('Liverpool','London','London UK','UK','London',
    'Dublin, Ireland','Manchester','Norwich','United Kingdom','Norwich','Duplin','England','ENGLAND',
    'united kingdom');

    $string = '';

    foreach($uk_array as $term=>$i) {
        $string = $string."location LIKE '%".$i."%' AND";
    }
    $string = substr($string, 0, -5);
    $query="SELECT COUNT(location) as location FROM tweets WHERE $string";
    $result = mysql_query($query) or die(mysql_error());
    while($row = mysql_fetch_array($result)){
        echo "There are ". $row['location']."";

    }


    ?>

If you do not partially match locations, use WHERE location IN ('Dublin', 'Liverpool', 'London', ...) instead of LIKE, its faster and cleaner. — ddinchev
– ddinchev, Commented Jul 30, 2012 at 13:18
Also: nonbinary string comparisons are case insensitive by default. So you do not need to have, for example, "United Kingdom" AND "united kingdom" checked — MiDo
– MiDo, Commented Jul 30, 2012 at 13:21

Summoner · Accepted Answer · 2012-07-30 13:16:26Z

4

You should be doing

$string = $string."location LIKE '%".$i."%' OR";

Switch the AND operator with the OR operator, otherwise the field has to contain all of the values in uk_array.

Also, you have a typo - Duplin?

Edit: More errors

foreach($uk_array as $term=>$i) {
    if($term) $string .= ' OR '; // only append OR if it's not the first one
    $string .= "location LIKE '%".$i."%'";
}

answered Jul 30, 2012 at 13:16

Summoner

1,4171 gold badge10 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

anjel Over a year ago

with OR i get this error:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''%united kingdom%' at line 1

Summoner Over a year ago

Copy it again - that was because you had something like OR%united kingdom% in the query. You need spaces, too.

donald123 · Accepted Answer · 2012-07-30 13:19:35Z

0

You may want to use the OR Operator

  // change this
  // $string = $string."location LIKE '%".$i."%' AND";
  // to
   $string = $string."location LIKE '%".$i."%' OR";
 // and 
 // $string = substr($string, 0, -5);
 // to
 $string = substr($string, 0, -3);

answered Jul 30, 2012 at 13:19

donald123

5,7883 gold badges28 silver badges24 bronze badges

Collectives™ on Stack Overflow

mysql like clause doesnt work against array php

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related