Can't insert or update data above 1000 in the database.

[amith_ami]

Hi all, I am trying to insert 1000 above data into a table but after inserting 500 something data its showing an "Internal Server Error". I tried after changing max_execution_time in php.ini and some other methods but no use. But this works fine in localhost. Given below is my code.

$submitted = !empty($_POST);

if($submitted==true)
{
    $date = date("Y-m-d",strtotime($_POST["add_date"]));

//select cash data for selected date    
 $cond_data = "WHERE date_added ='$date'";
 $sql_data=$objA->select_rows(TABLEPREFIX.'cash_data','SERIES,SYMBOL',$cond_data); 
 if(mysql_num_rows($sql_data)>0){
    while($row_data=mysql_fetch_array($sql_data)){

        $series = $row_data["SERIES"];
        $symbol = $row_data["SYMBOL"];

        //to find exist data
         $cond_exist = "WHERE SERIES='$series' AND SYMBOL='$symbol' AND date_added ='$date'";
         $sql_exist=$objA->select_rows(TABLEPREFIX.'cash_stock_scanning','count(*) as nm',$cond_exist);
         $row_exist = mysql_fetch_array($sql_exist);

$sql_avg=mysql_query("SELECT  AVG(CASE WHEN RowNumber <= 200 THEN Close_price  END) AS avg200,
        AVG(CASE WHEN RowNumber <= 100 THEN Close_price  END) AS avg100,
        AVG(CASE WHEN RowNumber <= 50 THEN Close_price  END) AS avg50,
        AVG(CASE WHEN RowNumber <= 25 THEN Close_price  END) AS avg25    
FROM    (   SELECT  @i:= @i + 1 AS RowNumber, Close_price 
            FROM    cash_data,
                    (SELECT @i:=0) AS i
           WHERE SERIES='$series' AND SYMBOL='$symbol' AND date_added <= '$date' ORDER BY date_added DESC
        ) AS Data");

        $row_avg=mysql_fetch_array($sql_avg);

        //to last 25 days closeprice sum and average get data of SYMBOL 
        $avg25 = $row_avg["avg25"];
        //to last 50 days closeprice sum and average get data of SYMBOL 
        $avg50 = $row_avg["avg50"];
        //to last 100 days closeprice sum and average get data of SYMBOL 
        $avg100 = $row_avg["avg100"];
        //to last 200 days closeprice sum and average get data of SYMBOL 
        $avg200 = $row_avg["avg200"];

        //saving data
    if($row_exist["nm"]>0){ //if exist update data 
    $fields= "200_MDA='$avg200',100_MDA='$avg100',50_MDA='$avg50',25_MDA='$avg25'";
    $pass_add=$objA->update_rows(TABLEPREFIX.'cash_stock_scanning',$fields,$cond_exist);

         }else{     //insert data 
  $fields="SERIES,SYMBOL,200_MDA,100_MDA,50_MDA,25_MDA,date_added";
//echo $fields="SERIES,SYMBOL,200_MDA,100_MDA,50_MDA,25_MDA,15_MDA,5_MDA,7_MDA,21_MDA,MACD_9,   MACD_0,RSI_30,RSI_70,STOCHASTIC_20,STOCHASTIC_80,date_added";
$values = "'$series','$symbol','$avg200','$avg100','$avg50','$avg25','$date'"; 
    $pass_add=$objA->insert_fields(TABLEPREFIX.'cash_stock_scanning',$fields,$values);

     }
 }//end while

    if($pass_add)
    {
        $msg='Content has been added sucessfully!';
        $_SESSION['sess_Msg']=$msg;
        header('location:index.php');
        exit();
    }
    else
    {
        $msg="Error in updating content!";
        $_SESSION['sess_Msg']=$msg;

    }

}else{

    $msg = "No Cash Data in the table."; 
    $_SESSION['sess_Msg']=$msg;
    }

}

[diafol]

The timeout may be an Apache timeout not PHP's

[amith_ami]

Hi diafol, is there anyother option to sort out this issue.?

[diafol]

If it is an Apache timeout issue, I don't think there's a way of changing it if you're on a shared host. They will have set it for maximum performance. If you have a dedicated host, then you should be able to.

Another option would be to optimize your SQL. It looks horrendously complicated and in a loop!

[amith_ami]

ok, please show me an example to optimize SQL.
Thanks

[pritaeas]

Hard to tell definitely from the above code, but it might be possible to create a INSERT INTO () SELECT which does all that work at once. If you create a SqlFiddle with some sample data and your actual queries people might be able to help you out.

[diafol]

SELECT  AVG(CASE WHEN RowNumber <= 200 THEN Close_price  END) AS avg200,
        AVG(CASE WHEN RowNumber <= 100 THEN Close_price  END) AS avg100,
        AVG(CASE WHEN RowNumber <= 50 THEN Close_price  END) AS avg50,
        AVG(CASE WHEN RowNumber <= 25 THEN Close_price  END) AS avg25
FROM    (   SELECT  @i:= @i + 1 AS RowNumber, Close_price
            FROM    cash_data,
                    (SELECT @i:=0) AS i
           WHERE SERIES='$series' AND SYMBOL='$symbol' AND date_added <= '$date' ORDER BY date_added DESC
        ) AS Data

This looks like a bit of a monster.
Maybe:

SELECT Close_price FROM cash_data WHERE SERIES='$series' AND SYMBOL='$symbol' AND date_added <= '$date' ORDER BY date_added DESC LIMIT 200

Do a fetchColumn (PDO) and place into $columns and then calc avg using PHP:

function getAverage($data,$cnt,$limit)
{
    if($cnt < $limit) $limit = $cnt;
    return array_sum(array_slice($data, 0, $limit))/$limit;
}


$stmt = $pdo->prepare("SELECT Close_price FROM cash_data WHERE SERIES=? AND SYMBOL=? AND date_added <= ? ORDER BY date_added DESC LIMIT 200");
$stmt->execute([$series,$symbol,$date]);
$columns = $stmt->fetchColumn();
$cnt = count($columns);

$avg25 = getAverage($columns,$cnt,25);
$avg50 = getAverage($columns,$cnt,50);
$avg100 = getAverage($columns,$cnt,100);
$avg200 = getAverage($columns,$cnt,200);

However, array functions are notoriously slow. I'd time each method to see which was quickest.
You can do that with a microtime(true) at the start and the end of the script. Substract the numbers and you get the time.

[jkon]

Arrays functions in PHP is a big failure till now (that is sad for a language that depends so much in associative arrays). In fact creating a simple implementation (like a static method in a arrays Util class) over an array function might make it 10 to 1000 times faster and more efficient (less memory + CPU usage) (it is very intresting why this is the case).

The queries here seems too compicated for the task that they perform. It would be better if the question started with “I have the X schema” tables , and then what is needed to be done. So @priteas suggestion about sharing schema and a bit of data in an SqlFeedle or something similar that will include also the desired output would be great.

[diafol]

Arrays functions in PHP is a big failure

I agree. Ridiculous.

[Taywin]

N/A. I just misread it. Similar idea as @diafol.

[amith_ami]

thanks for all replies. I am following procedural way of coding and I never used PDO yet.
Is there any way to do it with procedural coding.

[diafol]

Not PDO. You do realise that you.re using a dbh object already (objA)?