dynamically created php bind_param not working as expected [duplicate]

  bindparam, mysqli, php

I am in the process of developing an ecommerce basket and am using the Session global variable to hold a record of the items in an array. These items are all the product ID’s, which will then be placed in an SQL prepared statement.

I am using bind_param in an attempt to create this query dynamically, since the amount of items will always vary.

Here is my SQL statement below, as well as the process of converting the array into a string in order to pass it as a variable.

//Convert basket items array to string with seperator for query
    $arrString = implode(",",$_SESSION["basketItems"]);

    //Statement template
    $query = $conn->prepare('SELECT * FROM PRODUCTS WHERE ID in (?)');
    //Bind param dynamically & execute
    $query->bind_param("s",$arrString);
    $query->execute();

    //Check for query failure
    if (false === $query) {

        $output['status']['code'] = "400";
        $output['status']['name'] = "executed";
        $output['status']['description'] = "query failed";  
        $output['data'] = [];

        mysqli_close($conn);

        echo json_encode($output); 

        exit;

    }   

    //Get mysqli result class from query 
    $queryResult = $query->get_result();

    //Store basket items as asso arr
    $basketItems = [];

    //loop through existing rows, push into basket items arr
    while ($row = mysqli_fetch_assoc($queryResult)){
        array_push($basketItems, $row);
    }

    echo json_encode($basketItems);

However, the problem is, even though the imploded array contains several id’s (I have echo’d and checked) the resulting query only shows the first item of the array.

Now I can see where the issue may be – with the single ‘s’ argument in the bind param but technically, would this not count as a single argument string, given the fact that I have imploded it as one? Or does the problem occur elsewhere in the code?

Here is what I get when I echo the arrayString as well as the resulting query

ArrString

1,2,3,6,1,3,7,1,6,4,1

Query Result

[{"id":1,"name":"Anti Aging Serum","description":"This Anti Aging Serum contains vitamin C blends with Botanical Hyaluronic Acid, Vitamin E, Witch Hazel, and Jojoba Oil to promote your skin's response to signs of aging like brightness, wrinkles, & dark spots","price":"25"}]

Source: Ask PHP

LEAVE A COMMENT