On the example of an online store. If you go to the Products section in the admin area, then there will be a checkbox next to each product from the list. Select 70 products out of 100 and click Скрыть товар so that it does not appear on the site.

In theory, it is 70 requests. For example, in the visible cell, replace 1 with 0 with each of 70 id.

 UPDATE product SET visible=0 WHERE id=1 UPDATE product SET visible=0 WHERE id=2 UPDATE product SET visible=0 WHERE id=3

and so on to id=70 ...

And yes, I know that nobody does this, or rather I’m almost sure) Since this is done so as not to create such a monstrous load on the database?

I also had an idea about all the id which, for example, should not be displayed on the site, write to a single cell through the feed or a point, and then use the explode parse the line, but it seems even crazier to me.

Well, idea # 3, to create such one big request for an update, but wouldn't it be too big?

PS I hope clearly painted

  • five
    the maximum that comes to mind is to compile the implode id list and give the update product set visible=0 where id in(1,2,3,...) - Mike
  • @Mike, a request with such a design has never been used before, it looks very easy, thanks - emtecif

3 answers 3

It all depends on the conditions. If the ID is not in order, but judging by the fact that the user input is, then not in order, then this:

 UPDATE product SET visible = 0 WHERE id IN(1,2,3,4...);

About the limit on the number of parameters in IN() you can not worry. The default is 4194304 and is set via the max_allowed_packet setting judging by the manual . I have seen in my life well, sooooo long requests with thousands of IDs.

Collecting such a request in php is not a problem - either through implode () or through foreach ()

    Try to use

     UPDATE product SET visible=0 WHERE id<70

      If id goes in a row without gaps, use between :

       UPDATE product SET visible=0 WHERE id between 0 and 70

      Or simple inequality:

       UPDATE product SET visible=0 WHERE id >= 0 and id <= 70
      • I thought about it, but did not go in succession, are there any other options?) - emtecif