Optimizating by changing to single query instead of putting it in to a loop

Question

I have a table in data base. It contains 3 columns: ID, name, type. I have an array in my PHP script with some of those IDs (not in any order, maybe duplicates). I want to echo out those items from the table, that I have IDs in the array. I tried doing this with a for loop:

$items = array(1, 8, 5, 3, 2, 1,  5, 5);
for ($i=0; $i < count($items); $i++) {
    $query = mysqli_query($connect, "SELECT * FROM weapons WHERE id='$items[$i]'");
    $row = $query->fetch_assoc();

    echo $row['name'] . ', ' . $row['type'];
}

I think its not good practice to use call query in a loop, because if the loop gets too big, it may slow that the process. What can I achieve the same result, without putting query in a loop?

You can use `IN` instead of `=`. Check this http://stackoverflow.com/questions/16240041/sql-search-multiple-values-in-same-field — Aman Rawat, Sep 24 '16 at 05:46
@AmanRawat This method doesn't work with duplicates. If I have the same thing more than once in the IN selector, it would just get once. What should I change? — StanUdachi, Sep 24 '16 at 19:24

score 3 · Accepted Answer · answered Sep 24 '16 at 05:14

3

Use IN keyword for this

$query = mysqli_query($connect, "SELECT * FROM weapons WHERE id IN (1, 8, 5, 3, 2,5) ");

answered Sep 24 '16 at 05:14

Gulmuhammad Akbari

1,986
2
13
28

This is a very nice way to do it without looping unnecessarily. – DanielTheGeek Sep 24 '16 at 05:21
This doesn't work for duplicates. If I have for example number 5 more than one time, the query just return it once. – StanUdachi Sep 24 '16 at 17:41
@StanUdachi, Why would someone run the same query twice? – M Reza Saberi Jan 03 '18 at 06:51

score 0 · Answer 2 · answered Sep 24 '16 at 05:16

0

$query = mysqli_query($connect, "SELECT * FROM weapons WHERE id IN (1, 8, 5, 3, 2,5) ");
while($row = $query->fetch_assoc())
{
echo $row['name'] . ', ' . $row['type'];
}

answered Sep 24 '16 at 05:16

Sathiya saravana Babu

70
9

Optimizating by changing to single query instead of putting it in to a loop

2 Answers2