Where to place a prepared statement

[timstring]

I am having trouble placing a "myqsli->prepare" Here's the statement:

$stmt = $mysqli->prepare("SELECT IdNum FROM TOAWorkOrdersNew WHERE WorkDate = (?) AND TechNum = (?)")

Here is as much code as I can with a 10,000 character limit:

<?php

session_name("PassTech");
session_start();
set_time_limit (360);





set_include_path('/Library/WebServer/Documents/');

date_default_timezone_set('America/Chicago');



$host="localhost";
$user="root";
$password="g***9";
$Dbname = "calls_to_dispatch";

$mysqli = new mysqli($host, $user, $password, $Dbname);

// Check connection
if ($mysqli->connect_errno) {
    echo "Hey dope!  Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}



$LastDate = '05-30-2013'; //$_SESSION['LastDate'];
$TechNumEntry = 0; // $_SESSION['TechNum'];
$SystemNum = 6; // $_SESSION['SystemNum'];
$SystemNumEntry = $SystemNum;

$iRow = 3;

--snip--

/** Include PHPExcel */
require_once dirname(__FILE__) . '/PHPExcel.php';


// Create new PHPExcel object
echo date('H:i:s') , " Create new PHPExcel object" , EOL;
$objPHPExcel = new PHPExcel();


--snip
//if I put the prepare statement above here, the script returns NULL. {

$stmt = $mysqli->prepare("SELECT IdNum FROM TOAWorkOrdersNew WHERE WorkDate = (?) AND TechNum = (?)");
$stmt2 = $mysqli->prepare("SELECT IdNum FROM CallVolume WHERE WorkDate = (?) AND ANI = (?)");

//}



if ($SystemNumEntry == 0) $Quest = "SELECT * FROM Systems";
else $Quest = "SELECT * FROM Systems WHERE SystemNum = $SystemNum";

$SystemResult = $mysqli->query($Quest) or die('Dipstick  can not query');
		{
		 while ($row = $SystemResult->fetch_assoc()) 
				{
				$SystemNum = $row["SystemNum"];
				$System = $row["System"];
				$LongSystem = $row["LongSystem"];
				$Contractor = $row["Contractor"];


			$EndCol = 'A'. chr($NumMonth+43);
			$strRow = 'A' . $iRow  . ':' . $EndCol . $iRow;

			$fontRow = 'A' . $iRow;

--snip
				if ($TechNumEntry == 0) $Quest = "SELECT * FROM Techs WHERE SystemNum = '$SystemNum'";
				else $Quest = "SELECT * FROM Techs WHERE TechNum = '$TechNumEntry'";

			echo '<br>' . $Quest . '<br>';

				$TechResult = $mysqli->query($Quest);

	 				 $row_cnt = mysqli_num_rows($TechResult);

					 for ($i = 0; $i < $row_cnt; $i++) {
						 $iRow = $iRow + 2; 

						 echo '<br>' . $iRow . '<br>';

						$row = $TechResult->fetch_assoc();
						$TechNum = $row["TechNum"];
						$FirstName = $row["FirstName"];
						$LastName = $row["LastName"];
						$JobDesc = $row["JobDesc"];

						$TechName = $FirstName . ' ' . $LastName;

						echo $TechName;

$EndCol = 'A'. chr($NumMonth+43);
$strRow = 'A' . $iRow  . ':' . $EndCol . ($iRow+1);

//if I put the prepare statement here, the whole computer locks up. {

$stmt = $mysqli->prepare("SELECT IdNum FROM TOAWorkOrdersNew WHERE WorkDate = (?) AND TechNum = (?)");
$stmt2 = $mysqli->prepare("SELECT IdNum FROM CallVolume WHERE WorkDate = (?) AND ANI = (?)");

//}

for ($Count = 0; $Count < $NumMonth; $Count++) {

$date = date_create($StartDate);
date_add ($date, date_interval_create_from_date_string($Count . 'days'));
$IncDate = date_format($date, 'Y-m-d');
			//	$stmt = $mysqli->prepare("SELECT IdNum FROM TOAWorkOrdersNew WHERE WorkDate = ? AND TechNum = ?");

$stmt->bind_param('ss', $IncDate, $TechNum); 
$stmt->execute(); 
$stmt->store_result(); 

$WorkOrders = $stmt->num_rows; 
$SubWorkOrders = $SubWorkOrders + $WorkOrders; 

$stmt->free_result(); 
$stmt->close(); 




$stmt2->bind_param('ss', $IncDate, $CellNum); 
$stmt2->execute(); 
$stmt2->store_result(); 

$CallCount = $stmt2->num_rows; 
$SubCallCount = $SubCallCount + $CallCount; 

$stmt2->free_result(); 
$stmt2->close(); 


	   echo $q . ' qq ' . $Count . ' tn ' . $TechNum . ' cc ' . $CallCount . ' pp ' . $WorkOrders . ' xx ' . $CallCount . '<br>';

$q++;



flush();
ob_flush();


--snip--

			$SubCallCount = 0;
			$SubWorkOrders = 0;

flush(); 
ob_flush(); 
ob_end_flush(); 
ob_start(); 
					 }
			}

	}

Where in all this mess am I supposed to put the prepare statement?

All of the code necessary to export an Excel file through PHP-Excel is immense. I need to post it a paragraph at a time so y'all who understand these things can hep.

[bradgrafelman]

timstring;11030869 wrote:

Where in all this mess am I supposed to put the prepare statement?

Any place before you need to use execute the query(ies), and preferably some place that isn't going to cause them to be repeatedly "prepared" (which might not really be a problem, just rather inefficient).

As for your issue... I myself have rarely used prepared statements, so I'm by no means an expert on them, but have you tried removing the parentheses you have around both ? placeholders? I'm not sure if those would cause a problem, but I do know they are at the very least superfluous.

[timstring]

So, I ended up with this code:

$stmt = $mysqli->prepare("SELECT IdNum FROM TOAWorkOrdersNew WHERE WorkDate = ? AND TechNum = ?");
$stmt2 = $mysqli->prepare("SELECT IdNum FROM CallVolume WHERE WorkDate = ? AND ANI = ?");
$stmt->bind_param('ss', $IncDate, $TechNum);
$stmt2->bind_param('ss', $IncDate, $CellNum);

---loop
    ---loop

    --code and more code.

$stmt->execute();
$stmt->store_result();

$WorkOrders = $stmt->num_rows;
$SubWorkOrders = $SubWorkOrders + $WorkOrders;

$stmt2->execute();
$stmt2->store_result();


$CallCount = $stmt2->num_rows;
$SubCallCount = $SubCallCount + $CallCount;

  -----more code
    }
}

I am getting the proper data now. The problem now is that the script dies through the 81st iteration. The actual count is upwards of 800. Am I running out of memory?

[bradgrafelman]

In what way does it "die" ? If the script execution (or the PHP process itself) is terminated unexpectedly, either the PHP error log or your webserver's general/SAPI error log should contain some clue as to what happened.

Have you tried examining these error logs? Have you verified that PHP's log_errors is set to On and error_reporting is set to E_ALL (or better)?

EDIT: While I have no idea what it is you're trying to do with those two queries, it's also entirely possible that you could optimize the code further by reducing the two separate queries down to one.

[timstring]

AHA. When I added the error reporting, I got this message:

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 24 bytes) in /Users/tim/Sites/classes/PHPExcel/Style.php on line 403

How would I free up the memory allocation? I have every 'dump' command at the end of the script. If I adjust php's memory-limit to the value required to cover the script, I'd take 3/4 of the 2 gigs of ram that I have on this old machine.

[bradgrafelman]

Can you post the full/actual code?

[timstring]

Here's the full code. I have to split it into sections to keep below the board's limit of 10,000 characters. This script needs help. I'm trying to pare down the exsmell code. However, the script is too RAM intensive and too slow, even on my honkin' iMac. I'm taking 74 systems ~20 techs ea 30 days/mo * 400,000 records in the table. This means a whole lot of database calls. I need a way to empty PHP's memory with each iteration. As things stand now, the memory usage increases by a considerable sum every time around.

Here goes:

<?php

session_name("PassTech");
session_start();
set_time_limit (0);
ob_end_clean();

error_reporting(E_ALL);

ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);

set_include_path('/Users/tim/Sites/');

date_default_timezone_set('America/Chicago');



$host="localhost";
$user="root";
$password="g[][][]]9";
$Dbname = "calls_to_dispatch";

$mysqli = new mysqli($host, $user, $password, $Dbname);

// Check connection
if ($mysqli->connect_errno) {
    echo "Hey dope!  Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}



$LastDate = '05-30-2013'; //$_SESSION['LastDate'];
$TechNumEntry = 0; // $_SESSION['TechNum'];
$SystemNum = 6; // $_SESSION['SystemNum'];
$SystemNumEntry = $SystemNum;

$iRow = 3;


$LastDateArray = explode("-", $LastDate);

	$EndMonth = $LastDateArray[0];
	$EndDay = $LastDateArray[1];
	$EndYear = $LastDateArray[2];

	$EndDate = $EndYear . '-' . $EndMonth . '-' . $EndDay;
$WorkDate = '2013-05-30';

echo $WorkDate . ' || ' . $TechNumEntry . '<br>';


$NumMonth = cal_days_in_month(CAL_GREGORIAN, $EndMonth, $EndYear);

			$date = date_create($EndDate);


				$CommonEndDate = date_format($date, "M d, Y");
				$EndMO = date_format($date, 'F, Y');
				$FileMonth = date_format($date, 'm-Y');

			date_sub($date, date_interval_create_from_date_string($NumMonth - 1 . 'days'));
			$StartDate = $EndYear . '-' . $EndMonth . '-01';


			$date = date_create($StartDate);
				$StartDate = date_format($date, 'Y-m-d');
				$CommonStartDate = date_format($date, "M d, Y");


	for ($counter=0; $counter < $NumMonth; $counter++)
		{
			$date = date_create($StartDate);
			date_add($date, date_interval_create_from_date_string($counter . 'days'));

			$W[$counter] = date_format($date, "D");
			$D[$counter] = date_format($date, "n-j");


		}

/** Include PHPExcel */
require_once dirname(__FILE__) . '/PHPExcel.php';


// Create new PHPExcel object
echo date('H:i:s') , " Create new PHPExcel object" , EOL;
$objPHPExcel = new PHPExcel();

// Set document properties
echo date('H:i:s') , " Set document properties" , EOL;
$objPHPExcel->getProperties()->setCreator("Timothy Stringer")
							 ->setLastModifiedBy("Mac the Knife")
							 ->setTitle("Office 2007 XLSX Test Document")
							 ->setSubject("Office 2007 XLSX Test Document")
							 ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
							 ->setKeywords("office 2007 openxml php")
							 ->setCategory("Test result file");

echo date('H:i:s') , " Add some data" , EOL;

//set header
$stmt = $mysqli->prepare("SELECT IdNum FROM TOAWorkOrdersNew WHERE WorkDate = ? AND TechNum = ?");
$stmt2 = $mysqli->prepare("SELECT IdNum FROM CallVolume WHERE WorkDate = ? AND ANI = ?");
	$stmt->bind_param('ss', $IncDate, $TechNum);
	$stmt2->bind_param('ss', $IncDate, $CellNum);


if ($SystemNumEntry == 0) $Quest = "SELECT * FROM Systems";
else $Quest = "SELECT * FROM Systems WHERE SystemNum = $SystemNum";

$SystemResult = $mysqli->query($Quest) or die('Dipstick  can not query');
		{
		 while ($row = $SystemResult->fetch_assoc()) 
				{
				$SystemNum = $row["SystemNum"];
				$System = $row["System"];
				$LongSystem = $row["LongSystem"];
				$Contractor = $row["Contractor"];






			$EndCol = 'A'. chr($NumMonth+43);
			$strRow = 'A' . $iRow  . ':' . $EndCol . $iRow;

			$fontRow = 'A' . $iRow;




			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $iRow, $LongSystem);

			$objPHPExcel->getActiveSheet()->mergeCells($strRow);
			$objPHPExcel->getActiveSheet()->getStyle($strRow)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
			$objPHPExcel->getActiveSheet()->getStyle($strRow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle($fontRow)->getFont()->setSize(14);



$iRow++;
$strRow = 'A' . $iRow  . ':' . $EndCol . $iRow;
$fontRow = 'A' . $iRow;



			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $iRow, $EndMO);



	$objPHPExcel->getActiveSheet()->mergeCells($strRow);
	$objPHPExcel->getActiveSheet()->getStyle($strRow)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle($strRow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle($fontRow)->getFont()->setSize(14);

	$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, $iRow) ->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);	
	$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, $iRow) ->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);


$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $iRow+1, 'Tech Number');


$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $iRow+1, 'Name');

$iRow++;

$fontRow = 'A' . $iRow;



	for ($counter = 0; $counter <$NumMonth; $counter++)
		{
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($counter+3, $iRow, $W[$counter]);
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($counter, $iRow) ->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);	
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($counter, $iRow) ->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);


$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($counter+3, $iRow+1, $D[$counter]);
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($counter, $iRow+1) ->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);	

$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($counter, $iRow+1) ->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);		

}

[timstring]

				if ($TechNumEntry == 0) $Quest = "SELECT * FROM Techs WHERE SystemNum = '$SystemNum'";
				else $Quest = "SELECT * FROM Techs WHERE TechNum = '$TechNumEntry'";

			echo '<br>' . $Quest . '<br>';

				$TechResult = $mysqli->query($Quest);

	 				 $row_cnt = mysqli_num_rows($TechResult);

					 for ($i = 0; $i < $row_cnt; $i++) {
						 $iRow = $iRow + 2; 

						 echo '<br>' . $iRow . '<br>';

						$row = $TechResult->fetch_assoc();
						$TechNum = $row["TechNum"];
						$FirstName = $row["FirstName"];
						$LastName = $row["LastName"];
						$JobDesc = $row["JobDesc"];

						$TechName = $FirstName . ' ' . $LastName;

						echo $TechName;

$EndCol = 'A'. chr($NumMonth+43);
$strRow = 'A' . $iRow  . ':' . $EndCol . ($iRow+1);



for ($Count = 0; $Count < $NumMonth; $Count++) {

$date = date_create($StartDate);
date_add ($date, date_interval_create_from_date_string($Count . 'days'));
$IncDate = date_format($date, 'Y-m-d');

$stmt->execute();  // <--- which one ----- >
//	 $stmt->bind_result($WorkDate, $TechNum);
	$stmt->store_result();

$WorkOrders = $stmt->num_rows;
$SubWorkOrders = $SubWorkOrders + $WorkOrders;

$stmt->free_result();







// 	$stmt2 = $mysqli->prepare("SELECT IdNum FROM CallVolume WHERE WorkDate = ? AND ANI = ?");
//	$stmt2->bind_param('ss', $IncDate, $CellNum);
	$stmt2->execute();
//	 $stmt->bind_result($IncDate, $CellNum);
	$stmt2->store_result();


$CallCount = $stmt2->num_rows;
$SubCallCount = $SubCallCount + $CallCount;



$stmt2->free_result();


	   echo $q . ' qq ' . $Count . ' tn ' . $TechNum . ' cc ' . $CallCount . ' pp ' . $WorkOrders . '<br>';

$q++;




//	flush();





if ($SubCallCount > 0 || $SubWorkOrders > 0) {

	$objPHPExcel->getActiveSheet()->getStyle($strRow)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	$ColorCount = $ColorCount + .5;
	if ($ColorCount == (int) ($ColorCount)) {

		$objPHPExcel->getActiveSheet()->getStyle($strRow)->getFill()->getStartColor()->setARGB('FFA9F5A9');
	}
else {
	$objPHPExcel->getActiveSheet()->getStyle($strRow)->getFill()->getStartColor()->setARGB('FFFFFFFF');
}


$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $iRow, $TechNum);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $iRow, $TechName);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $iRow, 'Calls');


$CellNum = $row["CellNum"];
$AreaCode = substr($CellNum,0,3);
$Exchange = substr($CellNum,3,3);
$PhNum = substr($CellNum,5,4);
$FormatCell = '(' . $AreaCode . ') ' . $Exchange . '-' . $PhNum;


$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $iRow+1, $FormatCell);
	$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(1, $iRow+1) ->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);	
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(1, $iRow+1) ->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);


$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $iRow + 1, 'Work Orders');
	$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(2, $iRow + 1 ) ->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);	
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(2, $iRow + 1) ->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);



$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($Count+3, $iRow, $CallCount);


$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($Count+3, $iRow) ->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);	
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($Count+3, $iRow) ->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);



$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($Count+3, $iRow+1, $WorkOrders);
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($Count+3, $iRow+1) ->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);	
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($Count+3, $iRow+1) ->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);			

	}

}		
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($Count+3, $iRow, $SubCallCount);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($Count+4, $iRow+1, $SubWorkOrders);


			$SubCallCount = 0;
			$SubWorkOrders = 0;

			echo memory_get_usage() . "\n";
			echo memory_get_peak_usage() . '<br>';

//	flush(); 
//	ob_flush(); 
//	ob_end_flush(); 
//    ob_start();

					 }
			}

	}

[timstring]

A 'join' query would make the script faster, but I can't figure out how. Here's what doesn't work:

$stmt = $mysqli->prepare("SELECT 
TOAWorkOrdersNew.WorkDate AS TOAWorkDate,
CallVolume.WorkDate AS CallWorkDate,
TOAWorkOrdersNew.TechNum,
CallVolume.ANI
FROM CallVolume, TOAWorkOrdersNew
  WHERE 
  TOAWorkDate = ? AND 
  TOAWorkOrdersNew.TechNum = ? AND
  CallVolume.ANI = ? AND
  CallWorkDate = TOAWorkDate"); 

$stmt->bind_param('sss', $IncDate, $TechNum, $CellNum); //<--Line 155

The error returned is

"Call to a member function bind_param() on a non-object in /Users/tim/Sites/classes/ImportCSVJoin.php on line 155

[Weedpacket]

timstring wrote:

"Call to a member function bind_param() on a non-object in /Users/tim/Sites/classes/ImportCSVJoin.php on line 155

So [font=monospace]$stmt[/font] is not an object. So [font=monospace]$mysqli->prepare()[/font] failed. So you'd use [font=monospace]$mysqli->error[/font] and/or one of the other error-related properties to find out what its problem was.

[dalecosp]

timstring;11030971 wrote:

Here's the full code. I have to split it into sections to keep below the board's limit of 10,000 characters. This script needs help. I'm trying to pare down the exsmell code. However, the script is too RAM intensive and too slow, even on my honkin' iMac. I'm taking 74 systems ~20 techs ea 30 days/mo * 400,000 records in the table. This means a whole lot of database calls. I need a way to empty PHP's memory with each iteration.

http://php.net/manual/en/mysqli-result.free.php

[timstring]

Err lines 278 and 284?? Here's the offending code:

$stmt = $mysqli->prepare("SELECT IdNum FROM TOAWorkOrdersNew WHERE WorkDate = ? AND TechNum = ?"); 
$stmt->bind_param('ss', $IncDate, $TechNum); 
    ---loop
        ---loop

$stmt->execute();  
//	$stmt->bind_result($IncDate, $TechNum);

$stmt->store_result();

$result = $stmt->get_result();

$WorkOrders = $stmt->num_rows;
$SubWorkOrders = $SubWorkOrders + $WorkOrders;

//	$stmt->free_result();
 mysqli_free_result($result);
        }
    }

When I do this
:

	$result->free_result();

i get

Fatal error: Call to a member function free_result() on a non-object in /Users/tim/Sites/classes/ImportCSV.php on line 278

$stmt->free_result();

the memory keeps getting larger with no error message.

mysqli_free_result($stmt);

or

mysqli_free_result($result);

yields

Warning: mysqli_free_result() expects parameter 1 to be mysqli_result, object given in /Users/tim/Sites/classes/ImportCSV.php on line 279

and the memory usage also increases. This I do not understand.

Anyhoo, I don't know what's going on.