how to split a string into substring using php

Question

I have a string like this 'dubai,sharjah,' and I want to make it like this 'dubai','sharja',

I am passing the value dubai,sharjah, in the URL using ajax and my code

$city=$_GET['city'];

$sql  = "SELECT * FROM filter where isdeleted = 0 ";

if ($city !="" && $city !="Empty" ){
     $sql.=" and twon in ('".$citydata."')";                  
}

when I print the query, it's like this

SELECT * FROM filter where isdeleted = 0 and twon in ('dubai,sharjah,')

but I want it like this

SELECT * FROM filter where isdeleted = 0 and twon in ('dubai','sharja')

Can anyone guide me on how to do this using PHP?

Answer 1

Here's how I would approach it. I'm going to use PDO instead of mysqli because trying to get an array into mysqli_stmt::bind_param is just a pain.

First, create an array of cities, removing any empty values

$params = array_filter(explode(',', $city), function($c) {
    return !empty($c) && $c !== 'Empty';
});
$paramCount = count($params);

$query = 'SELECT * FROM filter where isdeleted = 0';

Now generate a placeholder string for your prepared statement.

if ($paramCount) {
    $placeholders = implode(',', array_fill(0, $paramCount, '?');
    // looks something like '?,?'

    $query .= " AND twon IN ($placeholders)";
}

Now, prepare a statement

// assuming you have a PDO instance in $pdo created with something like
// $pdo = new PDO('mysql:host=localhost;dbname=your_db;charset=utf8', 'username', 'password', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

$stmt = $pdo->prepare($query);

Execute and fetch values :)

$stmt->execute($params);
$filters = $stmt->fetchAll(PDO::FETCH_ASSOC);

Answer 2

$cities = explode(",", $_GET['city']);

//escape!
foreach ($cities as $citykey => $city) {
    $cities[$citykey] = "'" . mysql_real_escape_string($city) . "'";
}

$sql = "SELECT * FROM `filter` where `isdeleted` = 0";
if (!empty($cities)) {
    $sql .= ' and `twon` in (' . implode(',', $cities) . ')';
}

Answer 3

An alternative is to use FIND_IN_SET(). No PHP code change needed.

$sql.=" and FIND_IN_SET(town, '".$citydata."')";   

Answer 4

You just have to explode and implode here. Rest is the problem with extra , in your string at the end.

$str = "dubai,sharjah,";
$citydata = implode("','",explode(',',rtrim($str,',')));
echo "'$citydata'";

test

Answer 5

After 6 answers I gotta add a 7th:

$sql.=" and twon in ('".str_replace(",","','",$citydata)."')";

Answer 6

you can try to explode the string

$cityparts = explode(",",$city);

and you can use

$cityparts[0] and $cityparts[1] in your query 

array explode ( string $delimiter , string $string [, int $limit ] )

you can find more information on [http://www.php.net/explode]

hope this helps!!

Answer 7

You can do this.

$string = 'dubai,sharjah';
$cities = explode(',', $string);

echo $cities[0]; //dubai
echo $cities[1]; //sharjah

Answer 8

try this

$str = "dubai,sharjah,";
$arr = explode(",", $str);
$arr_temp = array()
foreach($arr as $s)
{
   if($s!="")
   {
      $arr_temp[] = "'".$s."'";
   }
}

$new_str = implode(",", $arr_temp); 
echo $new_str;  // output 'dubai','sharjah'

Now your Sql will be

$sql = "SELECT * FROM filter where isdeleted = 0 and twon in ($new_str) ";

Answer 9

You can use it in $city I fix two values with, you can use here by $_GET ;

$city = "dubai,sharjah,";
$query_string ="";
$words = explode(",",$city);
for($i=0;$i<count($words);$i++){$query_string .= "'".$words[$i]."',";}
$query_string = substr($query_string,0,strlen($query_string)-4);

then use your query

SELECT * FROM filter where isdeleted = 0 and twon in ($query_string)

Answer 10

if ($city !="" && $city !="Empty" )
{
     $city_exp = explode(',' $city);
     $sql .= " and twon in ('".implode("', '", $city_exp)."')"; 
}

What we are basically doing here is putting the two values in an array by using explode and then separating each item in that array by using implode

DEMO