how can i run the queries without loop in foreach

Question

PHP

I have an question here. I'm using the below query. I'm using the loop to run this query ?

How can i run this query without loop ?

I don't want to run the queries in foreach ? how can i do that ?

Am i using the correct method to run this query ?

It takes time to load. Is there any optimized to achieve this ?

I need perform a multi-query to run them all at once?

<?php
    $year = "2016";
    $start    = new DateTime($year.'-01-01');
    $start->modify('first day of this month');
    $end      = new DateTime($year.'-12-31');
    $end->modify('first day of next month');
    $interval = DateInterval::createFromDateString('1 month');
    $period   = new DatePeriod($start, $interval, $end);

    foreach ($period as $dt) {

       $calcstart_date = $dt->format("Y-m-d").' 00:00:00';
       $calcend_date  = $dt->format("Y-m-t").' 11:59:00';
       $month_name  = $dt->format('M');

       $test_submitted_query = db_query("
            SELECT requester_id, email, username, is_dashboard_user, SUBMITTED_ON 
            FROM testing_a, aspusers a 
            WHERE requester_id=a.user_id 
            AND AD_MAIL_ATTRIBUTE IS NOT NULL 
            AND IS_DASHBOARD_USER = 'y' 
            AND  SUBMITTED_ON >= timestamp '$calcstart_date' 
            AND SUBMITTED_ON <= timestamp '$calcend_date' 
       ");

    }
?>

What are you doing with the query result? You can always stack up the SQL queries and perform a multi-query to run them all at once? — Philip Thomson, Feb 02 '17 at 09:07
I assume the load time is because the SQL query is running inefficiently. Put some execution time checks in to see what parts are running slowly: `$time_start = microtime(true); // Anywhere else in the script echo 'Total execution time in seconds: ' . (microtime(true) - $time_start);` — Philip Thomson, Feb 02 '17 at 09:11
Have a look at [mysqli.multi-query](http://php.net/manual/en/mysqli.multi-query.php) — Philip Thomson, Feb 02 '17 at 09:15
my query is running 12 times in loop.... i need to run the query single time and i need the same result — Question User, Feb 02 '17 at 09:15
^ not necessarily - you're getting fixated on the XY problem : http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem Your problem is one of efficiency - doesn't mean removing the loop is the best or only solution. MySQL often performs more efficiently with lots of simple queries rather than one complex one. — CD001, Feb 02 '17 at 09:16
You should read http://stackoverflow.com/questions/508791/mysql-query-group-by-day-month-year . I think the whole PHP part is unnessesary. — S.Visser, Feb 02 '17 at 09:21
^ or potentially a sub-query, depending on how the tables are indexed — CD001, Feb 02 '17 at 09:24

score 2 · Accepted Answer · answered Feb 02 '17 at 09:27

2

The php part is not nessesary, you could use the date and time functions from mysql.

Example

 SELECT requester_id, email, username, is_dashboard_user, SUBMITTED_ON 
 FROM testing_a, aspusers a 
 WHERE requester_id=a.user_id 
 AND AD_MAIL_ATTRIBUTE IS NOT NULL 
 AND IS_DASHBOARD_USER = 'y' 
 AND YEAR(SUBMITTED_ON) = $year
 GROUP BY MONTH(SUBMITTED_ON)

You should get an result back with 12 rows

answered Feb 02 '17 at 09:27

S.Visser

4,645
1
22
43

This *should* work but it might not solve the underlying efficiency issue if the database itself is poorly optimised. – CD001 Feb 02 '17 at 09:38

how can i run the queries without loop in foreach

1 Answers1