PHP Array Conversion to String for using in SQL Statement

Question

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

Julie Pelletier · Accepted Answer · 2016-05-07 06:37:53Z

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;

answered May 7, 2016 at 6:37

Julie Pelletier

1,7161 gold badge10 silver badges19 bronze badges

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

2 Comments

Priyanka Over a year ago

No sir,it doesn't work.. it show invalid argument passed in implode @Julie Pelletier

Julie Pelletier Over a year ago

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.

Drone · Accepted Answer · 2016-05-07 06:47:26Z

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

edited May 7, 2016 at 6:47

Drone

1,1342 gold badges13 silver badges31 bronze badges

answered May 7, 2016 at 6:38

Saty

22.5k7 gold badges35 silver badges52 bronze badges

7 Comments

Priyanka Over a year ago

No sir,it doesn't work.. it show invalid argument passed in implode @Saty

Saty Over a year ago

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')

Saty Over a year ago

You have to miss something!! Post what you have done!!

Priyanka Over a year ago

Sir, I have edited my code. Please go through it. Thank you @Saty

Saty Over a year ago

What echo $category; echo also post value of print_r($_POST['category'])

|

Drone · Accepted Answer · 2016-05-07 06:47:56Z

0

You can use implode function with ',' delimiter.

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

edited May 7, 2016 at 6:47

Drone

1,1342 gold badges13 silver badges31 bronze badges

answered May 7, 2016 at 6:37

Emir Šator

313 bronze badges

Collectives™ on Stack Overflow

PHP Array Conversion to String for using in SQL Statement

3 Answers 3

2 Comments

7 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

7 Comments

Comments

Related