Loop through array and pass value to mysql query php

Question

I have the following JSON object

[
  {
    "var1": "ID001",
    "var2": "ROY",
    "var3": "16"
  },
  {
    "var1": "ID002",
    "var2": "MARK",
    "var3": "15"
  },
  {
    "var1": "ID003",
    "var2": "PETER",
    "var3": "15"
  }
]

I want to loop through the JSON and pass a value to following MySQL query

$select_data=mysqli_query($connsow, "select * from salary where col1 = '$var1' and col2 = '$var2'");

Below is my code, I used a foreach loop but it always selects the first value in the JSON object. I can't loop through entire JSON and pass the value to query

foreach($rows as $item) { 
    $cust_ord_no = $item['var1'];
    $cont_n = $item['var2'];
    $select_data=mysqli_query($connsow, "select * from test where col1 = '$var1' and col2 = '$var2'");
}

Do `json_decode($json, true)` to convert into array. then loop on the array. — Madhur Bhaiya, Oct 01 '18 at 07:23
Also, your query is open to SQL injection. Please use prepared statements — Madhur Bhaiya, Oct 01 '18 at 07:24
Hey first build Where clause in the for-each loop then append that where clause to the SQL outside of the for-each. — Gajanan Kolpuke, Oct 01 '18 at 07:29
@kanishka I have added the answer, kindly refer that, Actually you are trying to access $var1 direct which is not defined. — Gajanan Kolpuke, Oct 01 '18 at 07:59
Incidentally, executing a query inside a loop is often a rather inefficient idea. — Strawberry, Oct 01 '18 at 08:16

score 0 · Answer 1 · answered Oct 01 '18 at 07:26

0

First convert the JSON to array usng json_decode function with second parameter set to true. Then, you can loop through the array.

Do the following:

$rows = json_decode($rows, true);

Most importantly, your query is Open to SQL injection. Please use Prepared statements.

answered Oct 01 '18 at 07:26

Madhur Bhaiya

28,155
10
49
57

@kanishka what do you mean by same result. After running the select query, I don't see you doing anything with the query result set – Madhur Bhaiya Oct 01 '18 at 07:32
query execute only for the first json. the last two json ID - ID002 and ID ID003 i dont get the result – kanishka Oct 01 '18 at 07:37
I hope you are using correct variable in query.It should be `$cust_ord_no` instead of `$var1` and so on – Jimish Gamit Oct 01 '18 at 07:39

Gajanan Kolpuke · Answer 2 · 2018-10-01T08:06:06.177

0

you are trying to access key with name, instead of that, you have to use a variable which you have defined just before SQL.

$rows = json_decode($strJSON, true);

$select_data = [];
foreach( $rows  as $item ) {
    $cust_ord_no = $item['var1'];
    $cont_n = $item['var2'];
    $strSQL = "select * from test where col1 = '$cust_ord_no' and col2 = '$cont_n'";
    $select_data[] =mysqli_query($connsow, $strSQL);
}

edited Oct 01 '18 at 08:06

answered Oct 01 '18 at 07:58

Gajanan Kolpuke

155
1
1
14

sorry that was typp im using the variable name but still same only get result from the first json value – kanishka Oct 01 '18 at 08:04
$select_data[] = mysqli_query($connsow, $strSQL); And $select_data = [] array at the top of foreach. – Gajanan Kolpuke Oct 01 '18 at 08:05

Loop through array and pass value to mysql query php

2 Answers2