PHP / SQL - A lot of SQL Queries

Question

On my website, I want to use a lot of different data from my database. Currently, I'm using four queries to gather different data. But is there a way to make it more efficient and put them into one big query? And how would I do that?

Edit: So the answer was to simply put all queries together into one and use as much data manipulation as possible in the database queries, and not in php.

$qry = "SELECT COUNT(*) cnt,
                        AVG(level) avg_lvl,
                        SUM(IF(onlinestatus=1, 1, 0)) online_cnt,
                        (SELECT Max(time) FROM refreshes) refresh_time
                        FROM players";
                foreach ($db->query($qry) as $row){
                    $amount_total = $row['cnt'];
                    $average_level = floor($row['avg_lvl']);
                    $online_amount = $row['online_cnt'];
                    $milliseconds = $row['refresh_time'] + 1800000;
                    $update_time = DateTime::createFromFormat('U', intval($milliseconds / 1000));
                }

If there is a common key between the rookstayers and refreshes tables then you could create the sql as a left outer join between the tables and do what you want with one query. Without that it looks like this could be done in 2 queries because select * from rookstayers should be enough to manipulate the data nad perform whatever calculations you want in php — Professor Abronsius
– Professor Abronsius, Commented Dec 26, 2015 at 17:08
So basically just SELECT * FROM rookstayers on the first 3 queries? Then one for refreshes ? But the thing is, I also have some stuff like WHERE something = something wouldn't it screw up the query for the ones that doesnt use WHERE?. You got any example on a left outer join ? — Tibia Player
– Tibia Player, Commented Dec 26, 2015 at 17:10
Yes, 1st query could replace first 3 queries, the where clause can be tested for in php and then a separate query ( unless you can join the tables ) for the refreshes table data — Professor Abronsius
– Professor Abronsius, Commented Dec 26, 2015 at 17:17
Now get rid of foreach; there will be exactly one row returned. — Rick James
– Rick James, Commented Dec 27, 2015 at 2:18

trincot · Accepted Answer · 2015-12-26 19:06:05Z

1

You could combine all queries into one, like this:

$qry = "SELECT COUNT(*) cnt,
           AVG(level) avg_lvl,
           SUM(IF(onlinestatus=1, 1, 0)) online_cnt,
           (SELECT Max(time) FROM refreshes) refresh_time
    FROM   rookstayers";
foreach ($db->query($qry) as $row){
    $amount_total = $row['cnt']
    $level = $row['avg_lvl'];
    $online_amount = $row['online_cnt'];
    $milliseconds = $row['refresh_time'] + 1800000;
    $update_time = DateTime::createFromFormat('U', intval($milliseconds / 1000));
}

The last query you have seems to assume there is only one record in the result, as the loop would overwrite the previous result in each iteration. And as there is no order by in that query, it would be a bit of a gamble what the outcome would be. So I have taken the most recent time from the table in case there are multiple records there.

Note that that the above loop only executes once, as there is a guarantee to get exactly one result from the query.

edited Dec 26, 2015 at 19:06

answered Dec 26, 2015 at 17:25

trincot

357k38 gold badges282 silver badges338 bronze badges

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

2 Comments

Tibia Player Over a year ago

Invalid argument supplied for foreach() in C:\xampp\htdocs\new\index.php on line 32 Total Rookstayers: Notice: Undefined variable: amount_total in C:\xampp\htdocs\new\index.php on line 40 Average Level: Notice: Undefined variable: average_level in C:\xampp\htdocs\new\index.php on line 41 Rookstayers Online: Notice: Undefined variable: online_amount in C:\xampp\htdocs\new\index.php on line 42 Next Update: Notice: Undefined variable: update_time in C:\xampp\htdocs\new\index.php on line 43 Fatal error: Call to a member function format() on null in C:\xampp\htdocs\new\index.php on line 43

trincot Over a year ago

This is hard to understand, as the above code does not have these line numbers. The Undefined variable errors should come from your code, not mine.

Gordon Linoff · Accepted Answer · 2015-12-26 17:45:20Z

0

The first and third queries can be combined into one:

select count(*) as num, sum(onlinestatus = 1) as numOnline
from rookstayers;

The second should be an aggregation:

select level, count(*) as cnt
from rookstayers
group by level;

The fourth is also an aggregation; I'm not sure exactly what the data looks like, but it seems to be something like:

select sum(time + 1800000)
from refreshes;

In general, you should do as much data manipulation in the database as you can. That is what databases are designed for.

EDIT:

The first, second, and third can be combined into:

select count(*) as num, sum(onlinestatus = 1) as numOnline,
       avg(level) as avgLevel
from rookstayers;

edited Dec 26, 2015 at 17:45

answered Dec 26, 2015 at 17:20

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

2 Comments

Tibia Player Over a year ago

how would I access those variables though? You named them "sum" and "num", "numOnline" etc... $row['num'] ? or what. Fourth one is a date/time in milliseconds. Just an integer number

Gordon Linoff Over a year ago

@TibiaPlayer . . . The same way you access level and time.

Collectives™ on Stack Overflow

PHP / SQL - A lot of SQL Queries

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related