CODEWITHSUNDEEP
phparrays
Priyanka
Priyanka

Reputation: 75

PHP Array Conversion to String for using in SQL Statement

How can i change an array [["English","French","Spanish","German"]] in a String using for IN clause in Sql Statement.

e.g.s 

 "SELECT a.ad_id FROM `category_rel` a INNER JOIN tbl_category_master b ON a.category_id= b.fld_category_id WHERE b.fld_category_name IN ('English', 'French', "Spanish', 'German')";

I have store this array in $category= [["English","French","Spanish","German"]] variable and use in below SQL Statement.

 "SELECT a.ad_id FROM `category_rel` a INNER JOIN tbl_category_master b ON a.category_id= b.fld_category_id WHERE b.fld_category_name IN ('$category')"

Please Help me out. thank you.

this is my php code:

    <?php

  $con = mysqli_connect("localhost","root","","db");
  $city = $_POST['city'];
  $category=json_encode(array($_POST['category']));

   echo $category;
  $cat= "'".implode("','", $category[0])."'";
    echo $cat;

 $con->query("SET NAMES 'utf8'");

     $sql="SELECT c.ad_id FROM `category_rel` c INNER JOIN tbl_category_master d ON c.category_id= d.fld_category_id WHERE d.fld_category_name IN ($cat)";
    $result1=$con->query($sql);

  while ($row = mysqli_fetch_assoc($result1)) {
    $output[] = $row;
 }
   echo json_encode(array("result"=>$output));
  $ids[]=json_encode(array($output));


      $stmtsearch="SELECT a.title, a.phone, a.description, a.email, a.post_date FROM `ad_master` a INNER JOIN tbl_city_master b ON a.city_id = b.fld_city_id WHERE b.fld_city_name = '$city' AND a.id IN ('2','11','28') ORDER BY id" ;

  $resultsearch=$con->query($stmtsearch);
     $json=array();
    while($row=mysqli_fetch_array($resultsearch)){

   array_push($json, 
  array('title'=> $row[0],
      'phone'=> $row[1],
       'description'=> $row[2],
      'email'=>$row[3],
      'post_date'=>$row[4]
     ));
    }
   echo json_encode(array("result"=>$json));
  $con->close();
  ?>

Upvotes: 0

Views: 61

Answers (3)

Emir Šator
Emir Šator

Reputation: 31

You can use implode function with ',' delimiter.

$categoryCondition = implode(",", $category);

Upvotes: 0

Saty
Saty

Reputation: 22532

You can use $category array into IN clause query by using implode() function as

 $category= [["English","French","Spanish","German"]];
 $cat= "'".implode("','", $category[0])."'";// return 'English','French','Spanish','German'
 $query="SELECT a.ad_id FROM `category_rel` a INNER JOIN tbl_category_master b ON a.category_id= b.fld_category_id WHERE b.fld_category_name IN ($cat)"; // pass your cat here

Upvotes: 0

Julie Pelletier
Julie Pelletier

Reputation: 1716

Here it is:

<?php

$category = [["English","French","Spanish","German"]];

$inCategory = '"' . implode('","', $category[0]) . '"';

$sql = "SELECT a.ad_id FROM `category_rel` a INNER JOIN tbl_category_master b ON a.category_id= b.fld_category_id WHERE b.fld_category_name IN ($inCategory)";

echo $sql;

Upvotes: 1

Related Questions