$a = mysql_query("SELECT * FROM catalog_items where page_id='84'") or die(mysql_error());
while ($row = mysql_fetch_array($a)){
$base_item = $row['item_ids'];
mysql_query("DELETE FROM items_rooms where base_item = '".$base_item."'")
or die(mysql_error());
}
How can I run this script on PHP only with querys in mysql?
I am having problems in php with TIMEOUT , so I thought of executing in mysql directly, I just do not know how ...
From what I understand you want to optimize the script and obviously the performance. It really is not necessary to go all that way.
See an example of DELETE with JOIN.
DELETE 'items_rooms' FROM 'items_rooms' AS T1 LEFT JOIN 'catalog_items' AS T2
ON T2.item_ids = T1.base_item
WHERE T2.page_id='84'
I recommend that you back up before running because I can not guarantee the integrity of this because it is foreign to the structure of your system. But I think it should not cause any problems.
I also do not guarantee that it will increase performance. This depends on the state of the structures, indexes, keys, etc.
There are other ways to solve such as by using multiple select. If this example does not change performance a lot, try testing other media, review how the data structure is, etc.
One note, in older versions of MySQL, the use of the alias in DELETE expressions was not recognized. But I do not remember at the moment which version. Something close to 5.3 or 5.1.
What you can do is increase the PHP processing timeout. Before this processing, add with the set_time_limit($tempo)
.
<?php
set_time_limit(0); //para um tempo infinito
set_time_limit($segundos); // o tempo que vc quizer em segundos
The following query will give you the same result (assuming the field names in your table are consistent):
DELETE FROM items_rooms where base_item IN
(SELECT base_item FROM catalog_items where page_id='84')
The query will exclude from a list of base_item that was selected within SELECT and does not even need PHP to be executed.