search all the elements of array of strings in mysql using php

Question

I need to search all elements of an array of strings of the database and to return how many values are matched to the existing database entries..

database name = words , table name=collection , word is the coloumn in which array to bi searched

<?php
$array=array('abc','xyz','lmn','pqr');
@ $db=new mysqli('localhost','root','','words');

if(mysqli_connect_errno()) {
    echo 'Error:Could not connect to the database';
} else echo 'connected';

$db->select_db('words');

foreach($array as $s) {
    $query="select * from collection where word = '%".$s."%'";
    $result=$db->query($query);
}

if($result) echo $db->affected_rows;
$db->close();
?>

Btw, if the array is not too big you can generate a single query that will give you the answer in one shot, i.e. WHERE word LIKE ? OR word LIKE ? OR ... — Ja͢ck
– Ja͢ck, Commented Feb 27, 2014 at 9:05

Loïc · Accepted Answer · 2014-02-27 09:41:28Z

2

$result = 0;
foreach($array as $s) {
    $query="select count(*) as number_match from collection where word = '%".$s."%'";
    $row = mysql_fetch_row($db->query($query));
    $result+= $row['number_match'];
}

Edit: Yet better :

$words = implode("%','%",$array);
$query="select count(*) as number_match from collection where word in ('%$words%')";
var_dump(mysql_fetch_row($db->query($query));

edited Feb 27, 2014 at 9:41

answered Feb 27, 2014 at 9:02

Loïc

12k2 gold badges35 silver badges52 bronze badges

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

Comments

Supriya Pansare · Accepted Answer · 2014-02-27 12:15:52Z

-1

its not feasible to run query in for loop so, you can try below solution,

$array=array('abc','xyz','lmn','pqr');     
$query="select word from collection where word LIKE '%".$s."%'";
$result=$db->query($query);
while ( $row = mysql_fetch_array($result) )
 {
   $tblarray[] =  $row['word'];
 }
foreach($tblarray as $k => $v)
{
 foreach($array AS $key => $value)
 {
   if (strpos($v, $value) !== false)
   {
    $finalarray[] = $v; 
   }
 }
}

edited Feb 27, 2014 at 12:15

answered Feb 27, 2014 at 11:48

Supriya Pansare

5791 gold badge4 silver badges11 bronze badges

Collectives™ on Stack Overflow

search all the elements of array of strings in mysql using php

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related