0

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();
  ?>

3 Answers 3

1

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;
2
  • No sir,it doesn't work.. it show invalid argument passed in implode @Julie Pelletier Commented May 7, 2016 at 7:38
  • That's probably because the information your provided showing two layers of arrays is not right. Try it without the [0] and tell me if it works. Commented May 7, 2016 at 15:48
0

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
7
  • No sir,it doesn't work.. it show invalid argument passed in implode @Saty Commented May 7, 2016 at 7:38
  • No its working my code output is 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') Commented May 7, 2016 at 7:44
  • You have to miss something!! Post what you have done!! Commented May 7, 2016 at 7:45
  • Sir, I have edited my code. Please go through it. Thank you @Saty Commented May 7, 2016 at 7:53
  • What echo $category; echo also post value of print_r($_POST['category']) Commented May 7, 2016 at 7:57
0

You can use implode function with ',' delimiter.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.