Hadley8899 February 2016

Am i being very inefficient ? mysqli call in loop

I have a database with 2 tables, I have a primary key(item_id) and a foreign key(color_id) in the items table. I list the items out with the color as the title as below,

Item details

//item_id

//color_id

//Details

Colors

//Color_id

//color_suffix

//Color_details

I list these items out like Color Suffix(RE) item details item details item details etc etc...

Color Suffix(RT) Item details item details

To do this i loop through the colors table and make an sql call each time it loops

$query = "SELECT * FROM COLORS";
$result = mysqli_query($connection,$query);
while($data= fetch_array($result)){
    echo "<h2>{$data['color_suffix']}</h2>";
    $query2 = "SELECT ... FROM items WHERE color_id = {$data['color_id']}";
    $result2 = mysqli_query($connection,$query);
    while($item = fetch_array($result2)) {
        //List all items
    }
}

This is only kind of sudo code im just wondering if there is a more efficient way. I've been reading up on SQL inner join, Would this be the method i should use ?

I've also read up on prepared statements, But ill be honest i do not really understand the syntax on either.

Should i be doing it like this ? or is it extremely inefficient ? Bear in mind there will be around 30 different colors then about 10 - 50 items each so that a lot of SQL calls.

Could someone provide some very basic sudo code to help me understand how i would achieve this ?

Thank you in advance Ryan

Answers


Sergey Gurevich February 2016

You could do it with a simple inner join:

SELECT * FROM colors, items WHERE colors.color_id = items.color_id

http://dev.mysql.com/doc/refman/5.7/en/join.html


J. A. Streich February 2016

Use joins. Then add a little logic for your groupings.

 $query = "SELECT ... FROM COLORS, ITEMS WHERE items.color_id = colors.color_id order by colors.color_id";
 $stmnt = $db->prepare($query);
 $stmnt->bind_results($color_id, $color_suffix, ...);
 $stmnt->execute();
 $cur_col_id = '';
 while($stmnt->fetch())
 {
   if($color_id !== $cur_col_id)
   {
     echo '<h2>' . $color_suffix . '</h2>';
     $cur_col_id = $color_id;
   }
   //print the items here, no second loop.
 }

Keep in mind the ... shouldn't just be a *, it should be a list of actual fields you will be using. The order has to match that in the bind_results call. You should also add error checking and handling.

Post Status

Asked in February 2016
Viewed 3,631 times
Voted 4
Answered 2 times

Search




Leave an answer