Looping through an array from SQL.

[quorthorn]

Ok, how I've always done this in the past is used a different query for each record set. This is probably not the most efficient way, so how do I use the same array for several loops? For instance:

 

$result = $db->query("SELECT type, name FROM table");

$query = $result->fetch_all(MYSQLI_ASSOC);

 

Ok, so now I can loop through $query and pick out the necessary records.

 

How would I loop through the records with ONLY type = 1? Is it possible to do this without writing this into the query or checking for type=1 within the loop?

 

I could do this:

 

foreach($query as &$item)

{

    if($item['type'] == 1) { display }

}

 

However it seems inefficient. Is there a way to check for type=1 BEFORE the foreach loop WITHOUT a "WHERE type=1" inside the query?

 

The idea is that I need to be able to list all of type=1, then type=2, type=3 etc. The actual script is a lot more complex than this but the answer to this question will be enough for what I need to do.

 

Thanks!

[.josh]

No, there are no other ways to filter it out except via the query or in the loop.

[quorthorn]

Ok, it was worth a shot. Thanks!

[mac_gyver]

you could create a view in your database with that condition, then the query would use the name of the view as the table name.

[quorthorn]

The idea was to have just the one query that I could pull everything from using PHP. I'm just trying to optimise some existing code since the SQL load is becoming a problem so I need to cut back on queries.

 

Checking within the loop should be fine - I can't see the server load becoming a problem anytime soon.

[ginerjm]

I would think that making your query more specific would lessen the load on your database.  Why query all the records when you only want a subset? 

[mac_gyver]

i think you have generalized this to the point that we can only give general answers.

 

so, in general, you should run one or as few queries as possible that gets the data you want in the order that you want it. the reason for getting the data in the order you need it, is so you can in most cases simply loop through the result set once and use the data the way you need it.

 

if your host is complaining about database usage, the biggest problem is running queries inside of loops, using data from one query to feed a query inside the loop. the solution is to write one joined query that does it all at once.

 

if you show how you are using these different type values, and even if you need all or just some of the possible type values, someone can provide more specific help. optimization takes knowing the full scope, as just looking at one small part may micro-optimize that one small part, while missing things that could result in major improvements.

Edited January 25, 2014 by mac_gyver

[quorthorn]

The reason why I generalised it and didn't explain the complete script was because I already knew what I wanted to do. I just wanted to know if it was possible to do what I explained in the original post - one query with all records and loop through the query several times selecting ONLY the records that I want (yes, all records will be used at some point in the script). Since it isn't possible to do exactly what I wanted I am having to re-code the script.

 

Since I can't do this, the next most efficient way would be to grab all of the records and loop through once, sorting the records into various strings which will be used throughout the script. This is for an inventory system in an online game and it is running dedicated server getting over a thousand sql hits per second so you can imagine the need for optimisation. No worries though - this method is probably more efficient than my original approach, it just requires a fair bit of re-coding.

 

Thanks for the responses guys!

[mac_gyver]

you could GROUP BY the type in the query, group concatenating the selected data. this will give one row per type. depending on what and how you are using this data, you could use the concatenated string for each type directly, or simply explode it into an array for each type.

 

again, without knowing what the data is and how it is being used, cannot offer exact solutions that will work best for you.

Edited January 25, 2014 by mac_gyver

[mac_gyver]

it just requires a fair bit of re-coding.

 

 

there's no reason a single query couldn't produce the same exact program variables that you have now. the only re-coding is to consolidate your multiple type=1, type=2 queries into one query that gets all the type information at once.

[quorthorn]

For the basics of this script, it's for an inventory system in an online RPG. All of the "equipment" data is stored in a single table. The field "type" determines what type of equipment. The script lists weapons, first by "equipped=1", another field, then unequipped (equipped=0), to get the following output.

 

Weapons

---

Weapon 3 (Equipped)

Weapon 1
Weapon 2
Weapon 4

 

Armor

---

Armor 2 (Equipped)

Armor 1

Armor 3

 

etc...

 

There is more to it than this, and several other systems in the game use a similar approach currently (I have no idea who coded this).

 

My plan was to just select all of the data, then create all of the strings in one loop rather than looping for each output. I'd prefer to have a separate loop for each equipment type as it is easier to read (and I assume 1 loop of 100 items is not significantly faster than 10 loops of 10?) and wouldn't require much editing of the existing code (since I just remove the queries and edit the existing loops to work from the query of all items). Would grouping by item type and sorting by equipped then name solve this issue? 

[Barand]

The data

mysql> SELECT * FROM equipment;
+----+----------+---------+----------+
| id | name     | type    | equipped |
+----+----------+---------+----------+
|  1 | Weapon 1 | Weapons |        0 |
|  2 | Weapon 2 | Weapons |        0 |
|  3 | Weapon 3 | Weapons |        1 |
|  4 | Weapon 4 | Weapons |        0 |
|  5 | Armour 1 | Armour  |        0 |
|  6 | Armour 2 | Armour  |        1 |
|  7 | Armour 3 | Armour  |        0 |
|  8 | Armour 4 | Armour  |        1 |
|  9 | Armour 5 | Armour  |        0 |
+----+----------+---------+----------+

The code

$sql = "SELECT type, name, equipped
        FROM equipment
        ORDER BY type, equipped DESC, name";
$res = $db->query($sql);
$curType='';
while (list($type,$name,$equip) = $res->fetch_row()) {
    if ($curType != $type) {
        echo "<h4>$type</h4>";
        $curType = $type;
    }
    $eText = $equip ? '(equipped)':'';
    echo "$name $eText<br>";
}

The results

Armour

Armour 2 (equipped)
Armour 4 (equipped)
Armour 1 
Armour 3 
Armour 5 

Weapons

Weapon 3 (equipped)
Weapon 1 
Weapon 2 
Weapon 4