How to use an array to query

Question

Is there any way I can use the array from $query and use it in another query like the $query2 below? It works but it only returns the first data of the array...

Already tried these links already but none of them worked in my case Array in SQL Query?

How to query a database with an array? WHERE = 'array()'

$query = mysqli_query($db, "SELECT * FROM patient_details WHERE discharged_date >= '2019-03-01' AND discharged_date < '2019-03-30'");
    while ($row = mysqli_fetch_array($query)){
        $result = $row['hosp_patient_no'];
        $query2 = mysqli_query($db, "SELECT deficiencies_id FROM deficiency_patient_details WHERE hosp_patient_no = '$result'");

}

Unless there is a specific reason, I would look to combine the two queries into 1 SQL statement. — Nigel Ren, May 18 '19 at 15:20
You have to use a column I'm not entirely sure it's possible to compare two arrays in SQL however, you could compare them in PHP using two queries — Intel, May 18 '19 at 15:32
I'm actually going to use the array to query again. After $query2, I'll query again using that array. — Jackie Chan, May 19 '19 at 01:54

score 0 · Answer 1 · answered May 18 '19 at 15:34

0

you are using mysqli_fetch_array which will "Fetches a result row as an associative, a numeric array, or both", but your requirement says it otherwise so you should use mysqli_fetch_all which "Fetches all result rows as an associative array, a numeric array, or both".

so your syntax should look like this

$query = mysqli_query($db, "SELECT * FROM patient_details WHERE discharged_date >= '2019-03-01' AND discharged_date < '2019-03-30'");
while ($row = mysqli_fetch_all($query)){
    $result = $row['hosp_patient_no'];
    $query2 = mysqli_query($db, "SELECT deficiencies_id FROM deficiency_patient_details WHERE hosp_patient_no = '$result'");

}

infact i would suggest you to do this in a single query using subquery.

answered May 18 '19 at 15:34

Pritam Jinal

121
9

`mysqli_fetch_all()` will return all rows in 1 call, which also means `$row['hosp_patient_no']` will be incorrect - `$row[0]['hosp_patient_no']` will be needed to use the value from the first row. – Nigel Ren May 18 '19 at 15:47
yes, i missed that one, actually i copied whole code from question and forgot.. oops. :) – Pritam Jinal May 18 '19 at 15:49
undefined index :/ – Jackie Chan May 19 '19 at 01:50

How to use an array to query

1 Answers1