Is it bad practice to execute this query in a for or foreach-loop?

PHP
1

So I have the following query:

$new = $result[$i]['resclients']; `
$resclients=$mysqli->query("SELECT id,client_name FROM clients WHERE id IN ($new)");`

And I am wondering, is it bad practice to execute the above query in a for or foreach-loop, does it hurt the MySQL server?

Or, is it better to do LEFT JOINS or INNER JOINS or RIGHT JOINS?

Forgot to add, the $result is actually a two dimensional array.

Array
(
    [0] => Array
        (
            [id] => 7
            [resclients] => 6,7,8,9,10,11,12,13,14,15
        )

    [1] => Array
        (
            [id] => 5
            [resclients] => 5
        )

    [2] => Array
        (
            [id] => 4
            [resclients] => 4
        )
)

Just a small portion of it.

1 Like
2

I would guess that if it suits the rest of the code to combine those into a single query, then the database server would probably have less work to do. But if doing so makes it much more complex to read and understand the code, possibly not worth it. All depends on what else is happening with the code, size of the data, size of the server.

1 Like
3

What is the object of the result array or rather what is it you are wanting as a final result? Can you form it into a question? Like, "For the first 10 results (what object are the results?), what are the id and client_name of each resultā€™s resclients? Generally, from what Iā€™ve learned, putting queries in a loop, especially a loop with a lot of iterations, is bad practice.

Scott

1 Like
4

If you do decide to use the loop then convert the code to use prepare/bind instead of query and keep the prepare outside of the loop so that the database can do as much as possible of the processing outside of the loop and only needs to substitute a few variables to execute the code inside the loop.

2 Likes
5

IMHO nesting queries is best avoided if at all possible and is usually in code of those that have trouble putting together more complex queries.

As a simplified pseudocode example, say I have 3 tables

first - id, f_name, last_id 
last - id, l_name, age_id 
age - id, years

And I want to get the first name, last name and age of all ā€œJohnsā€

$result = SELECT f_name, last_id FROM first WHERE f_name LIKE 'John' 
 while ($result) 
  $result2 = SELECT l_name, age_id FROM last WHERE id = $result['last_id'] 
   while ($result2) 
    $result3 = SELECT years FROM age WHERE id = $result2['age_id'] 
     while ($result3) 
      echo $result['f_name'] . $result2['l_name'] . $result3['years']

OK, it may work - BUT - the code is hitting the database hard.

Compare to

$result = SELECT first.f_name, last.l_name, age.years 
          FROM first 
          INNER JOIN last 
           ON first.last_id = last.id 
          INNER JOIN age 
           ON last.age_id = age.id 
          WHERE first.f_name LIKE 'John'

The query is more complex - BUT - the code is hitting the database only once

The first example consumes more PHP memory saving all the ā€œresultā€ variable values.

As long as the database is designed with good indexes, there will be less resource use with the second example.

It depends of course on the size of the tables etc. Inefficient code may work fine on a small scale, but when things get big it could bring things to a crawl.

1 Like
6

You can always add or remove indexes later if necessary without having to change the code.

2 Likes
7

@felgall, @Mittineague, @s_molinari, and @droopsnoot, thanks for your replies.

I want to achive the following ouput:

[0] represents someone
length([resclients]) holds IDs, which are in ASC, and in return order are DESC, so the length becomes 10.

Department A added 10 clients, now to grab data about each client I need to use the query, and somehow loop to get separate outputs.

Department A added 10 clients => [resclients] => 6,7,8,9,10,11,12,13,14,15

Department B added 1 client => [resclients] => 5

Department C added 1 client => [resclients] => 4

Each of these IDs come from the same table, then I am trying to ensure that ā€œDep N added x clientsā€, and then grab each clients name etc.

Not sure if I am on the right track! Thanks a lot folks.

8

I donā€™t know if it is outside of your control, but it looks like the problem may have more to do with poor database design.
Anytime a column holds multiple values like ā€œ3,5,6,8,9ā€ itā€™s a good indication that thereā€™s a good chance it could be improved somehow.

In any case, since IN works with an array, you might be able to do something like

loop 
$new = array_merge($new, [$i]) 
endloop 
WHERE IN $new

instead of

loop 
$new = [$i] 
WHERE IN $new 
endloop
9

I think they are referring to the list of values to be returned. I donā€™t think they mean that the data is stored like that in the database.

What is the actual structure of the table. You are now talking about departments but there has been no mention of departments in the list of fields in the table mentioned so far.

If the department is identified by a field in the table then counting how many rows there are for each is trivial. If it isnā€™t in the table then you canā€™t query data that hasnā€™t been stored in the first place.

1 Like
10

I should clarify this.

IN is not working with an array, but an array value that is a comma separated list

So putting those values into an array would require an implode() to get it back to being a string of the list

1 Like
11

I am still not sure I understand the goal of the query. Iā€™ll try to put it into a question from the given information and you can tell me if I am wrong or right.

How many departments gained new clients and what are the name and id of those clients?

Is that correct? If it is, what determines a new client from an old one?

Scott

12 
CREATE Table department_activity (
  id BIGINT(100) AUTO_INCREMENT PRIMARY KEY,
  department_id BIGINT(100) NOT NULL, 
  object_id BIGINT(100) NOT NULL, 
  object_type VARCHAR(50) NOT NULL,
  action_name VARCHAR(50) NOT NULL, 
  activity_date DATETIME NOT NULL
);

id - Unique Activity Item ID.
department_id - ID of the department who created the activity item.
object_id - Internal ID of the object.
object_type - Type of object.
action_name - The action taken against the object.
activity_date - Timestamp that the action was created.

More information below:

INSERT INTO department_activity (department_id, object_id, object_type, action_name, activity_date)
VALUES ('1197381911108', '3438983', 'client', 'added' '2016-01-17 09:18:43');

1197381911108 - ID of the department.
3438983 - ID of the client.
ā€˜clientā€™ - The type of object.
ā€˜addedā€™ - The action taken.
ā€˜2016-01-17 09:18:43ā€™ - Timestamp when the action was taken.

I am producing it through this query:

FROM 
    (SELECT department_activity.*, date(department_activity.activity_date) groupby_date,
    COUNT(department_activity.id) AS number_of_clients_added,
    GROUP_CONCAT(department_activity.id) AS clients_comma_list 
    FROM department_activity
    INNER JOIN subscriptions 
    ON department_activity.user_id = subscriptions.subscribing_user
    WHERE subscriptions.user_id = '0'
    GROUP BY department_activity.user_id,
             date(department_activity.activity_date)
    ) As department_activities 
GROUP BY object_id,
      groupby_date
ORDER BY activity_date DESC
LIMIT 20;

I hope this information is enough to get us somewhere.

13

Just curious.
Is there a reason id isnā€™t UNSIGNED i.e. negative id values are possible?
The field name object_id suggests itā€™s numeric, is it really a String?

http://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html

Use the smallest integer data type for the AUTO_INCREMENT column that is large enough to hold the maximum sequence value you will need. When the column reaches the upper limit of the data type, the next attempt to generate a sequence number fails. Use the UNSIGNED attribute if possible to allow a greater range. For example, if you use TINYINT, the maximum permissible sequence number is 127. For TINYINT UNSIGNED, the maximum is 255. See Section 11.2.1, ā€œInteger Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINTā€ for the ranges of all the integer types.

14

A little bit off-topic here. Those are minor issues.

15

What if the department went and deleted one or more of the clients they just added?

Are the clients at all connected to the departments other than through the activity table?

What determines an old client from a new one?

Scott

16

@s_molinari,

Then that row on the department_activity table will be removed as well.

Only through the department_activity table.

The department_activity table determines this.

17

Can I use the comma separated values in the same query with a UNION to query more records from a number of tables.

GROUP_CONCAT(department_activity.id) AS clients_comma_list

and use clients_comma_list in a way that would allow me to pull everything in one single query. Just curious.

18

But, then then both ā€œactivitiesā€ are lost. That makes no sense at all, if this is some sort of historical tracking system for auditing purposes.

Then Iā€™d do a join through the department_activity table.

What in the department_activity table determines a new to old client?

Scott

1 Like
19

TIMESTAMP.

How?

20

Are you storing a timestamp somewhere else, in order to determine the new customers? Like last time the query was made? Or somthing to that effect? The timestamp alone doesnā€™t determine ā€œnewā€ from ā€œoldā€.

Scott