CODEWITHSUNDEEP
phpmysql
IIIOXIII
IIIOXIII

Reputation: 1010

PHP mySQL - Combine Multiple Queries into 1

Here is my problem:

I have a mySQL database with 3 columns (for simplicities sake - there are other irrelevant columns) and 3000 rows of data (again for simplicities sake), on which I am executing the loop below:

$iCount = count($item_ids);
for($i=0;$i<$iCount;$i++){
    $q="SELECT item_id, value_date FROM $dataTable WHERE value_date >= $sDate AND value_date <= $eDate AND price_disc <> 'NA' AND item_id = '".$item_ids[$i]."' ORDER BY value_date";
    $q=$myConn->query($q);
    if($q->num_rows>0){
        $x=0;
        while($row=$q->fetch_assoc()){
            $iData[$i][0]=$row['item_id'];
            $iData[$i][1][$x]=$row['value_date'];
            $x++;
        }
        $q->free();
    }
    set_time_limit(30);
}

to fill a PHP array (as you can see).

The above code is working, though VERY slowly as it currently iterates approx 3000 times (one for each item_id).

I am looking for a way to gather the data into an array in the same format as it is currently (with the item_id being array[$i][0] and the value_date being array[$i][1][$x]), but in a single query rather than 3000.

Note: I do not want to have to re-organize the database unless completely necessary.

I am sure there is a simple way to do this. Thanks in advance.

EDIT: I know that I can use IN('.join(item_ids).') to make it into one query string, I just don't know how to get the result in an array with the same dimensions as the one I have described.

Upvotes: 0

Views: 122

Answers (2)

Mauro
Mauro

Reputation: 1487

You are doing 3000 queries in a simple page request... too much

Store all your items id's in to an array

$stringItemIds = implode(',',$arrayItemsId);

$q="SELECT item_id, value_date FROM $dataTable WHERE value_date >= $sDate AND value_date <= $eDate AND price_disc <> 'NA' AND item_id IN ($stringItemIds) ORDER BY value_date";

After that iterate with the query:

    $q=$myConn->query($q);
    if($q->num_rows>0){
        $x=0;
        while($row=$q->fetch_assoc()){
            $iData[] = array(
                '0' => $row['item_id'],
                '1' => array('x' => $row['value_date'])
            );
        }
        $q->free();
    }

Upvotes: 1

kmas
kmas

Reputation: 6439

Use IN

$ids = " ('" . implode(item_ids, "','") . "')" ;

$q ="SELECT item_id, value_date ";
$q.=" FROM $dataTable ";
$q.=" WHERE value_date >= $sDate AND value_date <= $eDate AND price_disc <> 'NA' ";
$q.=" AND item_id IN ".$ids ." ORDER BY value_date";
$stmt=$myConn->query($q);
if($stmt->num_rows>0){
    $x=0;
    $i=0;
    while($row=$stmt->fetch_assoc()){
        $iData[$i][0]=$row['item_id'];
        $iData[$i++][1][$x]=$row['value_date'];
        $x++;
    }
    $stmt->free();
}

If your request is still slow, then you have to create an index on your column.

CREATE INDEX idx_item_id  ON YOUR_TABLE(item_id);

Upvotes: 2

Related Questions