New to mysqli library - Multiple query problem

[johnnc]

Greetings,

 

I am writing a batch program that executes 3 queries on a single page. Using mysql libraries and regular queries works just fine. I am rewriting the page to include a stored procedure. The first query gets several thousand rows from a table, the second query calls a stored procedure which returns a resultset and the third query updates the table with the results from the stored procedure. Queries 2 & 3 are inside a while loop and execute once for every record from query 1.

 

Here is my connection string to the database:

$connect = mysqli_connect("localhost", "username", "password","database") or

die ("The database is unavailable. Please try again later.");

 

Query 1 = $results = mysqli_query($connect,$query)

Query 2 = $results1 = mysqli_query($connect,$query1);

Query 3 = $results2 = mysqli_query($connect,$query2);

 

$query = Select * from table

$query1 = Call stored Procedure

$query2 = Update table set ...

 

The above fails with this error message:

Warning: mysqli_fetch_row() expects parameter 1 to be mysqli_result, boolean given in pagename on line 103

 

If I create 3 connection variables everything works as expected.

$connect = mysqli_connect("localhost", "username", "password","database") or

die ("The database is unavailable. Please try again later.");

$connect1 = mysqli_connect("localhost", "username", "password","database") or

die ("The database is unavailable. Please try again later.");

$connect2 = mysqli_connect("localhost", "username", "password","database") or

die ("The database is unavailable. Please try again later.");

 

Is there a way to accomplish what I am trying to do without creating 3 identical connections to the database?

 

I am a noob to this so go easy on my coding practices. This code actually processes 40+ records per second on an old Dell laptop 

 

thanks in advance

[will35010]

Can you post your code? So we can be more helpful.

[johnnc]

Here is the code...

 

$starttime = date('h:i:s A');

$sqlid = 150000;

$connect = mysqli_connect("localhost", "username", "password","database") or

die ("The database is unavailable. Please try again later.");

/*

$connect1 = mysqli_connect("localhost", "username", "password","database") or

die ("The database is unavailable. Please try again later.");

$connect2 = mysqli_connect("localhost", "username", "password","database") or

die ("The database is unavailable. Please try again later.");

*/

$query = "SELECT * from table where id > " . $sqlid;

$results = mysqli_query($connect,$query)

  or die(mysqli_error($connect));

if (mysqli_num_rows($results) != 0) {

//Set counter variable to see how many records were processed

$i=0;

 

//Start the loop of retrieving the leads to be rated

while ($row = mysqli_fetch_array($results, MYSQL_ASSOC)) {

 

//Get the Age Factor and set the variable

$query1 = "CALL sp_get_rates(" . $row["sqft"] ."," . $row["zip"] . "," . $row["yearbuilt"] . ")";

$results1 = mysqli_query($connect,$query1);

$row1 = mysqli_fetch_row($results1);

extract($row1);

$cov = $row1[0];

$premYes = $row1[1];

$premNo = $row1[2];

 

//Update record with new information

$query2 = "update tableset fireprem = " . $premYes . ", nonfireprem = " . $premNo . ", covfactor = ". $cov . " where id = " . $row["id"];

$results2 = mysqli_query($connect,$query2);

 

//increment counter variable

$i++;

 

 

//Free sql resources

mysqli_free_result($results1);

}

//Determine how many records received a rate

$query3 = "select count(*) as count from aptmailinglist where id > " . $sqlid . " and fireprem <> '0.00'";

$results3 = mysqli_query($connect,$query3);

$row3 = mysqli_fetch_row($results3);

extract($row3);

 

$endtime = date('h:i:s A');

//Print on screen the number of records that were processed

echo "There were " . $i . " records processed<br>";

echo "There were " . $row3[0] . " records rated<br>";

//echo $i - $row3[0] . " records did not receive a rate<br>";

echo "Start Time = " . $starttime . "<br>";

echo "End Time = " . $endtime . "<br>";

//Close connections

mysqli_free_result($results);

//mysqli_free_result($results3);

mysqli_close($connect);

//mysqli_close($connect1);

//mysqli_close($connect2);

}

[johnnc]

Just to clarify, the above code works for the first record in the set but fails for all others. Here are the Warnings I am getting on record 2- end of resultset.

 

Warning: mysqli_query() [function.mysqli-query]: Couldn't fetch mysqli in page.php on line 65

 

Warning: mysqli_fetch_row() expects parameter 1 to be mysqli_result, null given in page.php on line 66

 

Warning: extract() [function.extract]: First argument should be an array in page.php on line 67

 

Warning: mysqli_query() [function.mysqli-query]: Couldn't fetch mysqli in page.php on line 74

[blueman378]

please use

tags!

 

also, would you be able to identify which line is 103?

 

<?php
$starttime = date('h:i:s A');
      $sqlid = 150000;
      $connect = mysqli_connect("localhost", "username", "password","database") or
      die ("The database is unavailable. Please try again later.");
      /*
      $connect1 = mysqli_connect("localhost", "username", "password","database") or
      die ("The database is unavailable. Please try again later.");
      $connect2 = mysqli_connect("localhost", "username", "password","database") or
      die ("The database is unavailable. Please try again later.");
      */
      $query = "SELECT * from table where id > " . $sqlid;
      $results = mysqli_query($connect,$query)
        or die(mysqli_error($connect));
         if (mysqli_num_rows($results) != 0) {
            //Set counter variable to see how many records were processed
            $i=0;
            
            //Start the loop of retrieving the leads to be rated
            while ($row = mysqli_fetch_array($results, MYSQL_ASSOC)) {
               
               //Get the Age Factor and set the variable
               $query1 = "CALL sp_get_rates(" . $row["sqft"] ."," . $row["zip"] . "," . $row["yearbuilt"] . ")";
               $results1 = mysqli_query($connect,$query1);
               $row1 = mysqli_fetch_row($results1);
               extract($row1);
               $cov = $row1[0];
               $premYes = $row1[1];
               $premNo = $row1[2];
               
               //Update record with new information
               $query2 = "update tableset fireprem = " . $premYes . ", nonfireprem = " . $premNo . ", covfactor = ". $cov . " where id = " . $row["id"];
               $results2 = mysqli_query($connect,$query2);
                              
               //increment counter variable
               $i++;
               
               
               //Free sql resources
               mysqli_free_result($results1);
            }
               //Determine how many records received a rate
               $query3 = "select count(*) as count from aptmailinglist where id > " . $sqlid . " and fireprem <> '0.00'";
               $results3 = mysqli_query($connect,$query3);
               $row3 = mysqli_fetch_row($results3);
               extract($row3);
               
            $endtime = date('h:i:s A');
               //Print on screen the number of records that were processed
               echo "There were " . $i . " records processed<br>";
               echo "There were " . $row3[0] . " records rated<br>";
               //echo $i - $row3[0] . " records did not receive a rate<br>";
               echo "Start Time = " . $starttime . "<br>";
               echo "End Time = " . $endtime . "<br>";
            //Close connections   
            mysqli_free_result($results);
            //mysqli_free_result($results3);
            mysqli_close($connect);
            //mysqli_close($connect1);
            //mysqli_close($connect2);
         } 
?>

[johnnc]

Line 103 is

$row1 = mysqli_fetch_row($results1);

[mikesta707]

your query is probably failing. For debugging purposes, add

or die(mysqli_error($connect));

 

and see what the error says

[johnnc]

They queries are working correctly but I added the code to be sure anyway. The first record returns the correct result but each subsequent record produces the following Warnings:

 

Warning: mysqli_fetch_row() expects parameter 1 to be mysqli_result, boolean given in page.php on line 67

 

Warning: extract() [function.extract]: First argument should be an array in page.php on line 68

 

Warning: mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in page.php on line 100

 

Here is line 67

$row1 = mysqli_fetch_row($results1);

 

Here is line 68

extract($row1);

 

Here is line 100

mysqli_free_result($results1);

 

These lines of code are for the stored procedure call and seem to be the problem. Any ideas?

 

thanks again

[Mchl]

Do your stored procedures return any resultsets?

[johnnc]

MCHL, Yes, the sp returns 3 elements in a single row.