PHP mySQL - Combine Multiple Queries into 1

Question

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.

can you post how you get item_ids ? if it is from another table use join query, if it is from array using post or something, then just use itemId in (...) and then loop on recordset. — Sumit Gupta
– Sumit Gupta, Commented Sep 24, 2013 at 9:49
@MarkBaker running 3000 query will always going to be slow and not recommended for a web page. — Sumit Gupta
– Sumit Gupta, Commented Sep 24, 2013 at 9:49
What indexes do you have on your table? What does an EXPLAIN return? — Mark Baker
– Mark Baker, Commented Sep 24, 2013 at 9:49
in my opinion you absolutely have to use ezSQL DB Class for into cache for query. — avalkab
– avalkab, Commented Sep 24, 2013 at 9:52
@SumitGupta: its an array of item_ids(strings), could you post an example of the looping over the record-set using IN(), I was going to try that but am not sure how to keep the price_date(s) linked to their item_id(s) when doing so. — IIIOXIII
– IIIOXIII, Commented Sep 24, 2013 at 9:54

kmas · Accepted Answer · 2013-09-24 10:10:14Z

2

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);

edited Sep 24, 2013 at 10:10

answered Sep 24, 2013 at 9:52

kmas

6,44913 gold badges44 silver badges63 bronze badges

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

6 Comments

IIIOXIII Over a year ago

Could you provide the rest, the code that actually stores the results into the array? Look at my reply to Sumit Gupta above ^

Sumit Gupta Over a year ago

@kmas the ideas are in item_ids variable which is an array only, so he can simple implode them to get ids also query is wrong it should have brackets around ids :).

kmas Over a year ago

@IIIOXIII , you've got the rest of the solution in Mauro's post. ;)

kmas Over a year ago

Anyway, now you have discovered the 'IN' operator. The rest is simple.

IIIOXIII Over a year ago

@kmas: Yep, plan on creating the index, and was going to use IN with join, the main problem is I just don't know how to get the info output from the single query into an array with the same organization as my example: with each item_id as an array, with an array of corresponding price_dates within it.

|

Mauro · Accepted Answer · 2013-09-24 10:11:58Z

1

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();
    }

edited Sep 24, 2013 at 10:11

answered Sep 24, 2013 at 9:54

Mauro

1,4971 gold badge26 silver badges47 bronze badges

8 Comments

pulsar Over a year ago

N.B. join() is an alias of implode()

IIIOXIII Over a year ago

@Mauro: With the above code, in the loop, $i is not changing, $i would need to change in order for the output to be an array of item_id(s) which contain arrays of price_date(s), correct? That is my delema.

pulsar Over a year ago

wasn't advising an edit, just alerting other users in case they wondered what join() did. I had to look it up as I had never seen it before but knew of implode() :)

pulsar Over a year ago

"$i is not changing" - maybe change $iData[$i][0]=$row['item_id']; to $iData[][0]=$row['item_id']; or if you need the key of $iData to be the same use $iData[key($row)][0]=$row['item_id'];

IIIOXIII Over a year ago

@Mauro: I must be doing something wrong, I keep getting 1 as the result for count($iData[$i][1]), $iData[$i][1] should be an array of dates for each separate $iData[$i][0]. The dates and their relative item_ids must stay linked. I want to be able to echo them like: "Here is the item_id: $iData[$i][0]" and "Here are the dates relating to that item_id '$iData[$i][1][0],$iData[$i][1][1],$iData[$i][1][2]", etc. where $iData[$i][1] is an array of the related dates. Did I miss something?

|

Collectives™ on Stack Overflow

PHP mySQL - Combine Multiple Queries into 1

2 Answers 2

6 Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related