I have a Windows Server 2012 with IIS running MySQL 5.7 and PHP 5.6. I have to build some complex JSON using multiple data sets and loops.
Would it be more efficient for me to do one SQL call and then build my JSON using a number of PHP loops on an array of data?
SQL:
SELECT stuff, item_id
FROM here
WHERE item = 1
OR item = 2
OR item = 3
OR item = 4
PHP:
foreach($sql_results as $item)
{
// build JSON by looking at $item['item_id']
}
Or would it be better for me to do each SQL call individually (in a loop of some kind) and build the JSON as the calls are being made?
SQL:
SELECT stuff, item_id
FROM here
WHERE item = 1
SELECT stuff, item_id
FROM here
WHERE item = 2
SELECT stuff, item_id
FROM here
WHERE item = 3
SELECT stuff, item_id
FROM here
WHERE item = 4
PHP:
$item
// build JSON directly from each call
With a very large amount of calls, either PHP or MySQL could get bogged down... so which one would I go with as my workhorse here?
The db request is definitely more time consuming, so I would choose the first approach with one SQL query and PHP loop.
Definitely, one query will be better solution.
Also, as a side note, you can use item IN(...) instead of using OR condition multiple times