How to get products from table with exact ids from array?

Question

I have a sql table with products and i don't get how to retrieve several products using array with their ids

i know how retrieve all of these products and only one using id

JSON with all products from sql

function loadGoods(){
    $conn = connect(); 
    $sql = "SELECT * FROM PRODUCTS";
    $result = mysqli_query($conn, $sql);

    if (mysqli_num_rows($result) > 0) {
        $out = array();
        // output data of each row
        while($row = mysqli_fetch_assoc($result)) {
            $out[$row["id"]] = $row;
        }
        echo json_encode($out);

    } else {
        echo 0;
    }
    mysqli_close($conn);
}

JSON with one product using its id received in js file from hash

function loadSingleGoods(){
    $id = $_POST['id'];
    $conn = connect(); 
    $sql = "SELECT * FROM PRODUCTS WHERE id='$id'";
    $result = mysqli_query($conn, $sql);

    if (mysqli_num_rows($result) > 0) {
        $row = mysqli_fetch_assoc($result);
        echo json_encode($row);

    } else {
        echo 0;
    }
}

Both of these snippets are working and now i have array with id's but i don't get how retrieve only their properties from sql in JSON file.

I have array with ids like this ["6", "7", "27"] and i need to retrieve from sql PRODUCTS only items with these ids. As i understand i have to do something with this line

 $sql = "SELECT * FROM PRODUCTS WHERE id='$id'";

i tried such code

 $sql = "SELECT * FROM PRODUCTS WHERE in_array("id", $ids, TRUE);

but it didn't work

https://jonsuh.com/blog/convert-loop-through-json-php-javascript-arrays-objects/ use this link for more clarification — Jigar, Mar 23 '19 at 12:26
Warning: [SQL Injection](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — , Mar 23 '19 at 19:52
Always test your queries via console or workbench or SQL Studio for transact SQL before implementing them. That will ensure that your query works in the first place. Then you convert it into the language in use. — yardpenalty.com, Mar 23 '19 at 19:56

score 4 · Accepted Answer · answered Mar 23 '19 at 12:15

4

It would be helpful if you showed us your JavaScript also, but assuming you're feeding the output of the json_encode() into a single variable, you can access each product like so:

console.log(json_variable[0]);

That should give you the first "row". To access a particular cell, try this:

console.log(json_variable[0]['column_name']);

To go thru all of them, try jQuery's each():

$.each( json_variable, function( id, column_name) {
  console.log( id + ": " + column_name);
});

answered Mar 23 '19 at 12:15

dearsina

4,774
2
28
34

Sorry, but as i understand you write about how retrieve data from json and i need to receive data from sql in JSON. I don't have JSON yet. I have only array with ids as this ["6", "7", "27"] – Sergio Iliev Mar 23 '19 at 12:41
If you do a `var_dump($out);exit;`, after the `while()` loop, what do you get? – dearsina Mar 23 '19 at 12:47
array(37) {↵ [1]=>↵ array(7) {↵ ["id"]=>↵ string(1) "1"↵ ["name"]=>↵ string(16) "перчатки"↵ ["img"]=>↵ string(19) "img/other/hands.jpg"↵ ["price"]=>↵ string(3) "550"↵ ["category"]=>↵ string(5) "other"↵ ...etc – Sergio Iliev Mar 23 '19 at 13:12
Great, that's exactly as expected. Now on your JS side, how are you "receiving" the output from PHP? Perhaps you can share your JS code. – dearsina Mar 23 '19 at 13:15
Actually i get it in js and after adding this commands i get error there SyntaxError: Unexpected token a in JSON at position 0 My js quite big and messy so i don't know what to show here exactly – Sergio Iliev Mar 23 '19 at 15:02
Just show the bit where the "output" of the PHP script is received in JS. It should only be a line or two. – dearsina Mar 24 '19 at 05:29

score 3 · Answer 2 · answered Mar 23 '19 at 12:23

You can parse json object using JSON.parse() and access all the value of the object.

var text = '{ "name":"John", "age":"39", "city":"New York"}';
JSON.parse(text, function (key, value) {  
    console.log(value); 
});

Here you can replace text with your json variable.

If you want to read more about json parse & how to access json variable in js click here.

score 0 · Answer 3 · answered Mar 23 '19 at 19:47

0

You can build your query before executing it:

 $sql_array=array("6", "7", "27");
 $first_item=array_values($sql_array)[0];
 $sql="SELECT * FROM `PRODUCTS` WHERE `id`='$first_item'";
 $built_query="";
 foreach ($sql_array as $k => $id) {
   if ($k < 1) continue;
   $built_query=$built_query." OR `id` = '$id'";
}
$built_query=$sql.$built_query;

echo "$built_query";

answered Mar 23 '19 at 19:47

Bigeloj

58
8

1

Warning: [SQL Injection](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – Mar 23 '19 at 19:52

How to get products from table with exact ids from array?

3 Answers3