how to execute in query with mysql pdo

Question

i want to execute a query with mysql pdo

the query is

SELECT * FROM `user_skills` WHERE skills in ('html','css')

see the executed screen below

i'm getting data in this format

    $skills = array('html','css');

            $sql = "SELECT first_name,last_name,mobile_number,prev_comany_name,user_id FROM user_details WHERE  skills=:input";                               
            $stmt = $db->prepare($sql);
            $stmt->bindValue("input", $skills);            
            $stmt->execute();

   /** foreach($skills as $skill){
       echo $skill;
    } **/

You can't input an array directly into prepare like that. Just take your original query, replace the user inputs with your flags then bind them. — IsThisJavascript, Oct 10 '17 at 10:50
@SamerAbuGahgah, yes the `html` , `css` is coming from an `array`, so i tried like that — , Oct 10 '17 at 10:52

Samer Abu Gahgah · Answer 1 · 2017-10-10T11:43:53.730

0

You can't pass an array o the bindValue() function, and you can't test to equality with multiple values in the query instead you need to use the in operator. Then you can implode the array to pass a string to the bindValue() function:

$skills = array('html','css');

            $sql = "SELECT first_name,last_name,mobile_number,prev_comany_name,user_id FROM user_details WHERE  skills IN (:input)";                               
            $stmt = $db->prepare($sql);
            $stmt->bindValue(":input", implode(",", $skills), PDO::PARAM_STR);            
            $stmt->execute();

   /** foreach($skills as $skill){
       echo $skill;
    }

edited Oct 10 '17 at 11:43

answered Oct 10 '17 at 10:55

Samer Abu Gahgah

751
1
9
18

Change `implode(","$skills)` to `implode("," ,$skills)` – Nana Partykar Oct 10 '17 at 10:57
@SamerAbuGahgah, now i'm getting this `error` `Fatal error: Call to a member function prepare() on null in ` – Oct 10 '17 at 11:02
I've edited the answer can you try it now, and tell me if you got same error @EaB – Samer Abu Gahgah Oct 10 '17 at 11:03
@SamerAbuGahgah, `query` is `executing` but i'm getting `blank result` why? despite `html` and `css` are present – Oct 10 '17 at 11:08
@EaB the image you posted shows another table or what, – Samer Abu Gahgah Oct 10 '17 at 11:10
@EaB change $sql to $sql =" SELECT * FROM `user_skills` WHERE skills in (:input)"; and try it out again – Samer Abu Gahgah Oct 10 '17 at 11:11
@SamerAbuGahgah, those `skills` are present in the `posted` table – Oct 10 '17 at 11:12

score 0 · Answer 2 · answered Oct 10 '17 at 12:13

i got the solution referring this thread Can I bind an array to an IN() condition?

note FIND_IN_SET

$skills = array('html','css');
SELECT * FROM table WHERE FIND_IN_SET(id, :array)
$skills = implode(',', $skills); 
$stmt->bindParam('array', $skills);

Farhang Negari · Answer 3 · 2017-10-10T11:24:19.767

-1

change code to :

  $skills = array('html','css');

  $sql = "SELECT first_name,last_name,mobile_number,prev_comany_name,user_id FROM user_details WHERE  skills IN (:input)";                               
  $stmt = $db->prepare($sql);
  $stmt->bindValue(":input", "'".implode("','", $skills)."'");            
  $stmt->execute();

edited Oct 10 '17 at 11:24

answered Oct 10 '17 at 11:16

Farhang Negari

229
1
12

how to execute in query with mysql pdo

3 Answers3