Running sql query inside loop

Question

I store the array into session for easily to retrieve and work.

$responses = session('get_all_response');

$responses contains 30 records maximum.

I aiming to make the pushing of data into the array more fast. Because if I have 10 records in $responses (array) it takes 30secs to load all the possible info regarding each content of that array (But the real thing is. The count of records in an array is more likely 30 maximum)

I loop inside the array

foreach($responses as $res)
{
    $bo_images = DB::select('SELECT 
    image.bo_hotel_code, 
    image.bo_image_type_code, 
    image.bo_path, 
    imagetypes.bo_content_imagetype_description 

    FROM 

    bo_images AS image

    RIGHT JOIN bo_content_imagetypes AS imagetypes
    ON imagetypes.bo_content_imagetype_code = image.bo_image_type_code

    WHERE image.bo_hotel_code = "'.$res['code'].'" AND image.bo_image_type_code = "COM" LIMIT 1');

    if($bo_images != null)
    {
        foreach($bo_images as $row)
        {
            $responses[$res['code']]['information']['bo_images'] = array(
                'image_type_code' => $row->bo_image_type_code,
                'image_path' => 'http://photos.hotelbeds.com/giata/'.$row->bo_path,
                'image_type_description' => $row->bo_content_imagetype_description,
            );
        }
    }

    $bo_categories = DB::select('SELECT 

    a.category_code,
    b.bo_content_category_description

    FROM 

    bo_hotel_contents AS a 

    RIGHT JOIN bo_content_categories AS b
    ON b.bo_content_category_code = a.category_code

    WHERE a.hotel_code= "'.$res['code'].'"');

    if($bo_categories != null)
    {
        foreach($bo_categories as $row)
        {

            $responses[$res['code']]['information']['rating'] = array(
                'description' => $row->bo_content_category_description,
            );
        }
    }
}

In every loop, there is a code in there that will hold the key to get the contents inside the database.
then after that, it will push the content into that array that equal to the index of the array.

Otherwise. It is a success. But I know this is not the proper way of doing it. I know there is much better to do this.

Any help is so much appreciated

You may want to use WHERE IN. Example: WHERE image.bo_hotel_code IN (code1,code2,code3). And for security reasons, you may want to switch to using PDO's prepared statements. Or otherwise verify that $res['code'] is the type of data you want (integer I'm assuming). — Reed
– Reed, Commented Mar 25, 2019 at 4:47
I'm not sure I understand your problem though. You're trying to add the query results to $responses, it looks like. If DB::select(....) is returning an array, then that should be working, far as I can tell. — Reed
– Reed, Commented Mar 25, 2019 at 4:50
yes your idea is correct. Sorry i didnt say what am aiming for. — Mike
– Mike, Commented Mar 25, 2019 at 4:51
I aim to make the pushing of data into array more fast. Because if i have a 10 records in $responses (array) it takes 30secs to load all the posible info regarding to each content of that array.. — Mike
– Mike, Commented Mar 25, 2019 at 4:52

Reed · Accepted Answer · 2019-03-25 05:02:54Z

1

I'm not familiar with Laravel, so I don't know if prepared statements work with it, but you should do something to clean &/or verify the $res['code'] to make sure it is an integer, assuming that's what it's supposed to be.

First, prepare a string for an WHERE IN clause.

$str = "";
foreach ($responses as $res){
    $str .= ','.$res['code'];
}  
$str = substr($str,1); // to remove the comma

Then you'll need to change your query to use the IN statement.

WHERE a.hotel_code IN({$str})

I'm guessing image.bo_hotel_code refers to $res['code']. But in case it doesn't, you could modify your SELECT statement (if memory serves):

$code = $res['code'];
SELECT {$code} as code, 
    image.bo_hotel_code, 
    image.bo_image_type_code, 
    ...

Then you'll loop over the results and put them into the array in the same manner, where $row['code'] would refer to the code used to select it. It should be MUCH faster than running repeated queries, and there should be one row for each code in the IN statement.

answered Mar 25, 2019 at 5:02

Reed

15k8 gold badges74 silver badges114 bronze badges

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

1 Comment

Mike Over a year ago

Thank you for help. I will test it first.

Collectives™ on Stack Overflow

Running sql query inside loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related