0

After pulling in some data from a mysql database saving it to a variable, I'm wondering if it's possible to "query" the variable instead of doing another request to the database? I realise I need to search an array of objects based on key and value. So here is an example of what I have.

<?php
[{"customer":1,"item":1,"bought_at":"2016-12-15 11:41:11"},
{"customer":2,"item":1,"bought_at":"2016-12-15 11:43:21"},
{"customer":3,"item":1,"bought_at":"2016-12-16 13:31:11"},
{"customer":1,"item":2,"bought_at":"2016-12-16 12:12:21"},
{"customer":1,"item":3,"bought_at":"2016-12-17 15:13:58"}]
?>

So lets say I need to search it based on the item number and the date (but not time) when the item was bought. The next step would be to return the result as another array of objects. So if I were to search for item 1 bought at 2016-12-15 it would return.

[{"customer":1,"item":1,"bought_at":"2016-12-15 11:41:11"}, 
{"customer":2,"item":1,"bought_at":"2016-12-15 11:41:21"},]

Is this possible? If so how would I go about doing it? Regards

EDIT: The reason I originally asked this question was because I had a query inside a nested foreach loop which bothered me. It's a piece of code that builds up a a json table at the back-end to pass information to the front end to draw a google line graph. Also I changed the data slightly in my original question to try to make it easier to read. It's also built in Laravel. The complete code is pretty large so I'm just posting the nested foreach loops. The query is in the second loop and is given the variable $activations.

foreach ($timeRange as $time){
        $temp = array();
        $timeTwentyFour = date("G", strtotime($time));
        $temp[] = array('v' => "Date(01,01,2000,$timeTwentyFour)");

        foreach($data as $row){
            $count = 0;
            $activations = DB::table('customer_display')->where('display_id',$row->id)->where(DB::raw('DATE(created_at)'),$day)->get();

            foreach($activations as $activation){
                $timestamp = $activation->created_at;
                $activationTime = explode(" ", $timestamp)[1];

                if (strtotime($activationTime) >= strtotime($time) && strtotime($activationTime) < strtotime($time) + 3600){
                    $count++;
                };
            }
            $temp[] = array('v' => (float) $count);
            //The custom tooltip
            $temp[] = array('v' => $time . ' ' . $row->location . '. ' .  $count . ($count == 1 ? ' Activation' : ' Activations'));
        }
        $rows[] = array('c' => $temp);
    }
Dalek
  • 111
  • 4
  • 12

2 Answers2

0

If those are objects in an array and you only wanted the entries where item is 1 you could use array_filter;

$filtered = array_filter($items, function($item){
    // only return objects where this is true
    return $item->item == 1;
});

If you wanted only items purchased on the 15th use

return date('d', strtotime($item->bought_at)) == 15;

and if you want to see items 1 bought on the 15th you'd use 

$filtered = array_filter($items, function($item){
    return $item->item === 1
        && date('d', strtotime($item->bought_at)) == 15;
});

Also check out this answer on comparing dates for more information on how to better do that.

Community
  • 1
  • 1
castis
  • 8,154
  • 4
  • 41
  • 63
  • Thanks a lot, although I didn't realise it at the time the array is inside an object. So it's actually object ->array->objects – Dalek Dec 15 '16 at 14:35
0

Another database request will be the better approach in most cases. A database is optimized for querying data. It can use indexes, etc. Well known databases like MySQL have a query optimalisation. Doing it by hand will be less efficient. First downloading too much data and then use something like array_filter to linearly search through all the data is far less efficient than just querying the data with the search criteria in the query.

One way to do it is:

//Prepare statement once
$statement = $pdo->prepare("SELECT * FROM table WHERE item = ? AND bought_at = ?");

$statement->execute(array(1, "2016-12-15"));
foreach ($statement->fetchAll() as $array)
    //Do something with $array

//reuse prepared statement with another selection criteria
$statement->execute(array(3, "2016-12-16"));
foreach ($statement->fetchAll() as $array)
    //Do something with $array
Richard Brinkman
  • 317
  • 2
  • 5
  • I'm just concerned that my query is currently in a foreach loop, I thought doing 1 pull from the database and then "querying" the array would be better as it would mean less calls to the database. – Dalek Dec 15 '16 at 14:40
  • Maybe you can rewrite your question with a bit of your code. That makes it easier for outsiders to give answers. Did you look into prepared statements? – Richard Brinkman Dec 15 '16 at 14:58
  • My question would be radically different. I attempted to delete this question to make a new one but it wold not let me :(. Perhaps this will be useful to somebody though, do you think I should make a brand new question and leave this one up? – Dalek Dec 15 '16 at 15:05
  • Just edit it. It will be more meaningful for others as well. – Richard Brinkman Dec 15 '16 at 15:11
  • "Another database request will be the better approach in most cases." Careful, this thinking is an easy way to introduce unnecessary bottlenecks into your software. – castis Dec 15 '16 at 15:16
  • It really depends on the specific scenario. It seems like Dalek's query retrieves a complete table and does say the "where clause" in PHP. This means that the database complexity is linear in the size of the table (it has to send everything) and then the PHP has a linear complexity as well. Doing the where clause inside the database, the database can use all his optimizations (using indexes, partitions, etc) to retrieve the data, which can be constant or logarithmic time (depending on the indexes used). – Richard Brinkman Dec 15 '16 at 15:28
  • I have edited the question Richard, it would be great for any advise. – Dalek Dec 15 '16 at 15:42