Is using PHP to loop a mySQL query efficient?

Question

I have a HTML table full of check-boxes on page 1 with the name attribute set like this:

input type="checkbox" name="A_1" input type="checkbox" name="B_2"

On the following page, I loop through my $_POST array in PHP with explode to make an array like this:

Array ( [A_1] => on [A_2] => on [B_2] => on [B_5] => on [C_5] => on [submit] => Submit )

Into two arrays:

row_Array = Array ( [0] => 1 [1] => 2 [2] => 2 [3] => 5 [4] => 5 )

column_Array = Array ( [0] => A [1] => A [2] => B [3] => B [4] => C )

This is my SQL query:

for($i=0; $i < count($column_array); $i++) {
    $sql =  "SELECT {$column_array[$i]} FROM table WHERE num IN ({$row_array[$i]})";
    $result = mysqli_query($connection, $sql);
    while ($data = mysqli_fetch_assoc($result)) {
        $result_array[] = $data[$column_array[$i]];
    }
}

Here is an example of my SQL table:

  A   B   C
1 cat cot cet
2 rat rot ret
3 hat hot het
4 lat lot let
5 bat bot bet

The individual SQL queries from my loop look like this and return the correct values:

SELECT A FROM table WHERE num IN (1) returns cat
SELECT A FROM table WHERE num IN (2) returns rat
SELECT B FROM table WHERE num IN (2) returns rot
SELECT B FROM table WHERE num IN (5) returns bot
SELECT C FROM table WHERE num IN (5) returns bet

I am quite new to sql and php. This works but it seems like a terrible way to do this. Any advice or help would be greatly appreciated.

Where did the original numbers come from anyway? It might need to be converted to a join. — Jessica
– Jessica, Commented Jan 10, 2014 at 22:32

Leeish · Accepted Answer · 2014-01-10 22:27:30Z

6

I believe it's a better practice to run a single query and loop through the data returned rather than looping and then running multiple queries.

However, depending on your data structure and application layout, UI, etc, that may not be possible.

answered Jan 10, 2014 at 22:27

Leeish

5,2132 gold badges20 silver badges48 bronze badges

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

Comments

Andy Gee · Accepted Answer · 2014-01-10 22:29:21Z

3

I would aim for something like SELECT A,B,C,num FROM table WHERE num IN (1,2,5)

then maybe

foreach($result as $r){
    $data[$r['num']] = $r;
}

answered Jan 10, 2014 at 22:29

Andy Gee

3,3812 gold badges35 silver badges48 bronze badges

Comments

thpl · Accepted Answer · 2014-01-10 22:39:18Z

1

Let's try just running one query and loop trough it. It's pretty much @Andy Gee 's answer expressed in code.

$cols = implode(',', array_unique($column_array));
$rows = implode(',', array_unique($row_array));

$sql =  "SELECT {$cols} FROM table WHERE num IN ({$rows})";
$result = mysqli_query($connection, $sql);
while ($data = mysqli_fetch_assoc($result)) {
    //Do your stuff here
}

answered Jan 10, 2014 at 22:39

thpl

5,9183 gold badges31 silver badges44 bronze badges

Comments

user1759572 · Accepted Answer · 2014-01-10 22:42:51Z

Something like, it's realy bad idea but..:

$columns = implode(', ', $column_array);
$num_values = implode(', ', $row_array);
$sql =  "SELECT {$columns}, num FROM table WHERE num IN ({$num_values})";
$result = mysqli_query($connection, $sql);
while ($data = mysqli_fetch_assoc($result)) {
    $result_array[] = $data[$column_array[$i]];
}

Other way is to build one long Query with UNION

SELECT A as value, num FROM table WHERE num = 1
UNION
SELECT A as value, num FROM table WHERE num = 2
UNION
SELECT B as value, num FROM table WHERE num = 2
UNION
SELECT B as value, num FROM table WHERE num = 5
UNION
SELECT C as value, num FROM table WHERE num = 5

returns:

array(
   array("num" => 1, "value" => 'cat'),
   array("num" => 1, "value" => 'rat'),
   ...
);

Collectives™ on Stack Overflow

Is using PHP to loop a mySQL query efficient?

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related