0

I have to execute a PHP code just one time to make some changes in my database to have better tables. The code is too heavy and send more than 190000 queries to database (both select and insert).

Now the problem is when I execute it on the browser, form the result on the database and words that I've set to echo to check if it is going in correct way, I can find that it is executed partly. No error/warning/notice appears on the page.

I set max execute time and all the related settings to 1hour but it just take 30minuts and stop the for loop at once.

this is a sample of a part of my code:

    for ($i=9;$i<=19909; $i++){
        $j=0;
        $select= "select synonym from synonym where x_=". $i ."";
        //echo $select;
        //echo "</br>";
        $select_result= mysqli_query($connection,$select);
        if (mysqli_num_rows($select_result)>0) {
            $row = mysqli_fetch_assoc($select_result);
            $arra = explode("،",$row["synonym"]);
        }
        while ($arra[$j]){
            $text=trim($arra[$j]);
            $search="select word from semiresult where word='". $text ."'";
            $search_result= mysqli_query($connection,$search);
            if (mysqli_num_rows($search_result)==0) {
                $insert="insert into semiresult (x, word, synonym) values ('','". $text."','".$syn."')";
            }elseif (mysqli_num_rows($search_result)>0){
                 $repeat="UPDATE semiresult SET synonym=CONCAT(synonym,'".$syn2."') where word like'".$arra[$j]."'";
                 $repeat_result=mysqli_query($connection,$repeat) or die ('request "Could not execute SQL query" '.$repeat);

}

shekoufeh
  • 559
  • 2
  • 5
  • 11
  • 1
    What is your question? – Kuya Sep 09 '15 at 07:19
  • 1
    What is your question and can you show this "code"? – Darren Sep 09 '15 at 07:21
  • I think you may be over complicating your code. Give us an example of your code... say 20 lines or so. Then we will be better able to help you. – Kuya Sep 09 '15 at 07:27
  • the question is how to execute it – shekoufeh Sep 09 '15 at 07:29
  • Code please... without code we cannot do anything to help you. – Kuya Sep 09 '15 at 07:31
  • I add a sample code. I 've tried to optimize it and make is as simple as possible. – shekoufeh Sep 09 '15 at 07:34
  • Why not just run it directly in phpMyAdmin instead of a browser? – Kuya Sep 09 '15 at 07:36
  • because some changes should be maked in php. I think the problem is number of records I have in the table because I execute it on a sample table with 23 records successfully. with 19000 records it take too long. – shekoufeh Sep 09 '15 at 07:37
  • How many lines of code do you have? – Kuya Sep 09 '15 at 07:44
  • What do you mean by *"some changes should be maked in php"*? Any sql code running correctly on a web page (in a browser) will run correctly in phpMyAdmin. – Kuya Sep 09 '15 at 07:55
  • Why do ~19000 selects rather than just doing *SELECT synonym FROM synonym WHERE x_ BETWEEN 9 AND 19909* ? You could even break up the synonym field in SQL and directly join that against semiresult if you wanted. – Kickstart Sep 09 '15 at 09:25

2 Answers2

1

With work you could avoid doing much of this in php. Whether it is worthwhile would depend on the data and table declares.

For example, assuming that word has a unique index on it in the semiresult table and that the synonym field has at most 1000 delimited fields you could do something like this (untested):-

INSERT INTO semiresult (x, word, synonym)
SELECT '',
        '". $text."',
        SUBSRTING_INDEX(SUBSRTING_INDEX(t1.synonym, '،', (hundreds.aCnt * 100 + tens.aCnt * 10 + units.aCnt + 1)), '،', -1)
FROM synonym t1
CROSS JOIN
(
    SELECT 0 aCnt UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) AS units
CROSS JOIN
(
    SELECT 0 aCnt UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) AS tens
CROSS JOIN
(
    SELECT 0 aCnt UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) AS hundreds
WHERE t1.x_ BETWEEN 9 AND 19909
AND LENGTH(t1.synonym) - LENGTH(REPLACE(t1.synonym, '،', '')) > (hundreds.aCnt * 100 + tens.aCnt * 10 + units.aCnt)
ON DUPLICATE KEY UPDATE synonym=CONCAT(synonym, VALUES(synonym))
Kickstart
  • 21,403
  • 2
  • 21
  • 33
0

after all I solve it!!
After changing time limitations like max execute time and any other time limitations to two hours in php settings I add this line of code at the begining of my code then it execute until the end of the code.

set_time_limit(0);

This code was planned to execute one time only otherwise I have not do this changes.

shekoufeh
  • 559
  • 2
  • 5
  • 11