multiple mysql query result in single array

Question

how can i add result of multiple queries in a single array so it makes it easier to later output.

for example,

$newArray = array("mike", "john");
while ($newArray !== false){
    $sql = "Select * from accounts where name = '$newArray'";
    $result = mysqli_query($db_connection,$sql);
}

 while ($row = mysqli_fetch_assoc($result)) {
    printf $row['firstName'];
    echo "----";
    printf $row['num_accounts'];
    echo "----";
    prinf $row['location'];
    echo "----";
    echo "<br>";
}

i am sure i am missing something that obvious. but the idea is that i want to setup a loop to read from an array and then execute the query and store the results. each time query executes the condition is different but from same table, , but then keep adding the results to single output, making it easier to output in the end.

I would recommend reading some basic tutorial about MySQL queries to get better acquainted with the technology's capabilities. There are so many things you can do. — i--
– i--, Commented Mar 5, 2018 at 14:48

ScaisEdge · Accepted Answer · 2018-03-05 15:01:41Z

1

you could avoid the while simple using in clause based on list of values you need

$newList = "'mike', 'john'";

$sql = "Select * from accounts where name IN (" .$newList .")";

but you should not use php var in sql ..you are at risk for sqlinjection .. for avoid this you should take a look at your sqldruver for binding param ..

but if you want append the result for different where condition on same table you should take a look at UNION clause

$sql = "Select * from accounts where name IN (" .$newList .") 
        UNION 
        Select * from accounts  where you_col > 10";

edited Mar 5, 2018 at 15:01

answered Mar 5, 2018 at 14:43

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Umair Riaz Over a year ago

thanks for prompt response. just out of curiosity, how will this loop through the array to query for different conditions?

ScaisEdge Over a year ago

If you use the in clause you don't need loop for select result related to the values in the list .. but if you want select the same table with different where clause you should take a look at UNION clause for append several query (with correspondig column data type and number.. answer updated .. with a brief sample

IsThisJavascript Over a year ago

Quite like this solution. It will always be faster executing via SQL rather than php. You can implode(','$newArray) to easily get the data that this query requires.

IsThisJavascript · Accepted Answer · 2018-03-05 15:03:59Z

There's a few things wrong with your code..
1. You are inputting a full array into your query.
2. You're doing no preparations on your query thus it's vulnerable to sql injection if $newArray is human input data.
3. Your logic from looping thru the data, if you managed to get a single query correct, would always loop thru the last query. You need to move the $row = mysqli_fetch_assoc($result) stuff inside your actual loop.

To get this code working it would look like so:

$newArray = array("mike", "john");
foreach($newArray as $name){
    $sql = "Select * from accounts where name = '{$name}'";
    $result = mysqli_query($db_connection,$sql);

    while ($row = mysqli_fetch_assoc($result)) {
        printf $row['firstName'];
        echo "----";
        printf $row['num_accounts'];
        echo "----";
        prinf $row['location'];
        echo "----";
        echo "<br>";
    }
}

Collectives™ on Stack Overflow

multiple mysql query result in single array

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related