Php Mysqli -- Database Connection Failure

[nodirtyrockstar]

Is it possible to make two consecutive calls to a MYSQL DB in one trip to the server? Tables are powered by MYISAM (because fatcow doesn't support InnoDB). PHP 5.3. MYSQL 5.0.91

 

In case you need more information, my site is broken down into header, footer, main content, and side bar, and they are all separate PHP docs. The main content is made up of a few different pages, one of which is called depending on the user's selections. One of the content pages is a catalog. The script accesses the database and loads the current available inventory. When a user adds item to the cart, the whole page reloads (starting with a DB call to load the catalog) and then populates the cart summary in the sidebar, constituting the second DB call for that single trip to the server. Will the second call potentially be attempted when the table is still locked?

 

I ask because I am experiencing connection failures.

 

Please let me know if you need more information. Thank you in advance for your consideration.

[Psycho]

Yes, you can make many queries to the DB on a single script execution. Are you sure you aren't closing the connection prematurely or referencing the wrong link to the DB?

 

When a user adds item to the cart, the whole page reloads (starting with a DB call to load the catalog) and then populates the cart summary in the sidebar, constituting the second DB call for that single trip to the server. Will the second call potentially be attempted when the table is still locked?

 

For the single page execution that would never happen. One query would finish before the next is executed.

[nodirtyrockstar]

I have checked and rechecked all the login and DB credentials. I even created a separate login with different privileges since the first call is SELECT, and the second call is INSERT. I then checked and rechecked all of those credentials.

 

The first script closes the DB connection and the second script attempts to start a new connection. Can two separate scripts share the same connection? Or is keeping them separate the right way to do it?

 

I'm going to update with my code....

 

Catalog page called by main script:

 

require_once 'functions.php';
require_once 'pvt/cfg.php';

//check for catalog type
$dbType = $_SESSION['dbType'];

//Start form & table
$format = "<form action='%s.php?page=distro' method='post'>
 <table id='merch'>
	 <thead>
		 <tr>";
printf($format, $pgVer);

//connect to database
$mysqli = new mysqli($dbhost, $dbuserS, $dbpassS, $db);
if ($mysqli->connect_error) {
 die('Connect Error (' . $mysqli->connect_errno . ') '
		 . $mysqli->connect_error);
}

//prepare query relative to page requested
if($dbType == 'distro'){
 $query = 'SELECT `id`,`artist`,`title`,`artwork`,`label`,`year`,`price` FROM `products` WHERE (`qty` <> 0) AND (`agedOff` <> 1)';
}
else{
 $query = 'SELECT `id`,`artist`,`title`,`artwork`,`year`,`price` FROM `products` WHERE (label = "cobra cabana records") AND (`qty` <> 0) AND (`agedOff` <> 1)';
}
if($stmt=$mysqli->prepare($query)){
 $stmt->execute();
 if($dbType == 'distro'){
	 $stmt->bind_result($id, $artist, $title, $artwork, $label, $year, $price);
	 $colTitle = array('Product ID', 'Artist', 'Title', 'Artwork', 'Label', 'Release Date', 'Price');
 }
 else{
	 $stmt->bind_result($id, $artist, $title, $artwork, $year, $price);
	 $colTitle = array('Product ID', 'Artist', 'Title', 'Artwork', 'Release Date', 'Price');
 }
 $format = "<th><a id='%d' href='#' onclick='sortCol(this)'>%s</a></th>";
 $x=0;
 foreach ($colTitle as $title) {
	 printf($format, $x, $colTitle[$x]);
	 $x++;
 }
 echo "<th><img src='../img/addCart.gif' height='10px' width='10px' /></th>
	 <th><img src='../img/removeCart.gif' height='10px' width='10px' /></th>
	 <th>Qty</th>
 </tr>
				 </thead>";
 if($dbType == 'distro'){
	 while($stmt->fetch()){
		 $colArr = array ($id, $artist, $title, $artwork, $label, $year, $price);
		 echo "<tr>";
		 $format = "<td>%s</td>";
		 $y = 0;
		 while($y < 7){
			 printf($format, $colArr[$y], $colArr[$y], $colArr[$y], $colArr[$y], $colArr[$y], $colArr[$y]);
			 $y++;

		 }
		 $addCartFormat = "<td><input id='%d' type='checkbox' name='addCart[]' value='%s' /></td><td><input id='%d' type='checkbox' name='removeCart[]' value='%s' /></td><td><input id='%d' type='text' name='addMulti[]' value='1' size='2' /></td></tr>";
		 printf($addCartFormat, $pgVer, $y, $pgVer, $y, $pgVer);
	 }
 }
 else{
	 while($stmt->fetch()){
		 $colArr = array ($id, $artist, $title, $artwork, $year, $price);
		 echo "<tr>";
		 $format = "<td>%s</td>";
		 $y = 0;
		 while($y < 6){
			 printf($format, $colArr[$y], $colArr[$y], $id);
			 $y++;   
		 }
		 $addCartFormat = "<td><input id='%d' type='checkbox' name='cart[]' value='%s' /></td><td><input id='%d' type='checkbox' name='removeCart[]' value='%s' /></td><td><input id='%d' type='text' name='addMulti[]' value='1' size='2' /></td></tr>";
		 printf($addCartFormat, $pgVer, $y, $pgVer, $y, $pgVer);
	 }
 }
 echo"</table><input type='submit' name='myCart' value='Submit' /></form>";
 $stmt->free_result();
 $stmt->close();
}
else{
 echo $mysqli->error;
}
mysqli_close($mysqli);

 

This next page is required by main script and printed out in the sidebar.

 

require_once 'pvt/cfg.php';
$n = 0;
if(!isset($_POST) && !array_key_exists('addCart', $_POST)){
$_POST['addCart'] = 0;
}
else if(isset($_POST['addCart'])){
$cart = $_POST['addCart'];
$n = count($cart);
$i = 0;
if ($n === 0){
echo "You did not put anything into the cart.";
}
else{
while($i < $n){
$id = session_id();
$date = new DATETIME();
$expDate = $date->add(new DateInterval('PT3H'));
$comp = 0;
$i++;

//connect to database
 $mysqli = new mysqli($dbhost, $dbuserI, $dbpassI, $db);
 if ($mysqli->connect_error) {
	 die('Connect Error (' . $mysqli->connect_errno . ') '
			 . $mysqli->connect_error);
 }

 //prepare query relative to action
 $query = ' INSERT INTO `sessions` ( `id` , `created` , `expired` , `completed` , `price` )
VALUES (`$id`, `$date`, `$expDate`, `$comp`, `$price`) ON DUPLICATE KEY UPDATE';
 if($stmt=$mysqli->prepare($query)){
 if($stmt->execute()){
//add values to cart////////////////////////////////
 }
$stmt->free_result();
 $stmt->close();
 }
}
 }
}
if($n === 1){
$cartTxt = "Item";
}
else{
$cartTxt = "Items";
}
if($n > 0){
$imgLnk = "fullCart.gif";
}
else{
$imgLnk = "cart.gif";
}
$cartLnk = "#";
$cartReadout = "<img src='../img/%s' width='40' height='30' />
<br />
<a id='cartLnk' href='%s'>%d %s In Cart</a>";
printf($cartReadout,$imgLnk,$cartLnk,$n,$cartTxt);
$n = 0;

Edited October 3, 2012 by nodirtyrockstar

[Christian F.]

This bit here closes the MySQL connection, just like Psycho stated in his first post.

mysqli_close($mysqli);

 

[nodirtyrockstar]

I know that I closed the connection. I then reopen it in the following script. I don't even use the same login for the second script.

 

If you have advice for me, I need you to be more specific.

 

Thanks.

[Psycho]

Open the connection once, then run all your queries before closing the connection. In fact, you don't really need to close the connection at all - that will be done automatically when the script ends.

[PFMaBiSmAd]

Your second FILE (pages in a web setting are something else) of posted code is making the connection inside of a loop. Is there any chance the connection error was worded something like "too many connections?"

 

You should be making ONE connection, before the start of your loop. You should also prepare the INSERT query ONCE, before the start of the loop, and then simply populate the variables with the data for each loop and ->execute() the query inside of the loop. Also, since you are not using place-holders in the prepared query statement for the data values, you are not realizing any benefit from using a prepared query.

 

Edit: Here's the posted code, formatted so that it is readable -

 

<?php
require_once 'pvt/cfg.php';
$n = 0;
if(!isset($_POST) && !array_key_exists('addCart', $_POST)){
   $_POST['addCart'] = 0;
} else if(isset($_POST['addCart'])){
   $cart = $_POST['addCart'];
   $n = count($cart);
   $i = 0;
   if ($n === 0){
       echo "You did not put anything into the cart.";
   } else {
       while($i < $n){
           $id = session_id();
           $date = new DATETIME();
           $expDate = $date->add(new DateInterval('PT3H'));
           $comp = 0;
           $i++;

           //connect to database
           $mysqli = new mysqli($dbhost, $dbuserI, $dbpassI, $db);
           if ($mysqli->connect_error) {
               die('Connect Error (' . $mysqli->connect_errno . ') '
               . $mysqli->connect_error);
           }

           //prepare query relative to action
           $query = ' INSERT INTO `sessions` ( `id` , `created` , `expired` , `completed` , `price` )
               VALUES (`$id`, `$date`, `$expDate`, `$comp`, `$price`) ON DUPLICATE KEY UPDATE';
           if($stmt=$mysqli->prepare($query)){
               if($stmt->execute()){
                   //add values to cart////////////////////////////////
               }
               $stmt->free_result();
               $stmt->close();
           }
       }
   }
}
if($n === 1){
   $cartTxt = "Item";
} else {
   $cartTxt = "Items";
}
if($n > 0){
   $imgLnk = "fullCart.gif";
} else {
   $imgLnk = "cart.gif";
}
$cartLnk = "#";
$cartReadout = "<img src='../img/%s' width='40' height='30' />
<br />
<a id='cartLnk' href='%s'>%d %s In Cart</a>";
printf($cartReadout,$imgLnk,$cartLnk,$n,$cartTxt);
$n = 0;

 

edit2: You also (apparently) have back-ticks around the data values in the query.

Edited October 3, 2012 by PFMaBiSmAd

[nodirtyrockstar]

@PFMaBiSmAd

 

You should also prepare the INSERT query ONCE, before the start of the loop, and then simply populate the variables with the data for each loop and ->execute() the query inside of the loop.

 

When I was writing the script I was very unhappy with the redundancy of the queries and knew that I was not taking full advantage of prepared statements. I couldn't figure out the pattern that I needed to make the script more succinct, so I moved forward figuring I might come across the answer. Can you help me get started by directing me toward a resource or giving me a hint as to what that looks like?

Edited October 4, 2012 by nodirtyrockstar

[PFMaBiSmAd]

The following is the php.net stmt->execute() example, modified to loop over data -

 

<?php
      $query = "INSERT INTO myCity (Name, CountryCode, District) VALUES (?,?,?)";
       $stmt = $mysqli->prepare($query);

       $stmt->bind_param("sss", $val1, $val2, $val3);

       while(your_while_condition_test_here){
           // set the bound variables from your actual data source
           $val1 = some_statement_that_references_the_actual_dynamic_data_value;
           $val2 = some_statement_that_references_the_actual_dynamic_data_value;
           $val3 = some_statement_that_references_the_actual_dynamic_data_value;

           /* Execute the statement */
           $stmt->execute();
       }
       $stmt->close();

 

The only thing that should be inside of the loop are statements needed to reference dynamic data values and to call the ->execute() method.. A number of the values you show in your query are static (don't change for the duration of the loop) and should be set before the start of the loop.

 

Also, your data source is apparently an array. You should probably use a foreach(){} loop instead of a while(){} loop.

[nodirtyrockstar]

Yes, I want to pull things out of the loop. Maybe I am being dense, but I got hung up on the fact that one queries seven fields, and the other only six. So if I use your

 

$query = "INSERT INTO myCity (Name, CountryCode, District) VALUES (?,?,?)";

 

Then how do I get around a variable number of placeholders?

[PFMaBiSmAd]

I'm assuming that question is referring to the ON DUPLICATE KEY UPDATE part of your query? Since the INSERT query in the code you originally posted is cut off, I don't know exactly what you are referring to. However, if you need to reference any of the INSERT values in the UPDATE part of the query, see this post - http://forums.phpfreaks.com/topic/269074-prepared-statement-on-duplicate-help/#entry1382663

[nodirtyrockstar]

Oh I was referring to the first script. I guess that was confusing.

 

The following queries:

$query = 'SELECT `id`,`artist`,`title`,`artwork`,`label`,`year`,`price` FROM `products` WHERE (`qty` <> 0) AND (`agedOff` <> 1)';


$query = 'SELECT `id`,`artist`,`title`,`artwork`,`year`,`price` FROM `products` WHERE (label = "cobra cabana records") AND (`qty` <> 0) AND (`agedOff` <> 1)';

 

only have two differences. I would like to figure out how to create them dynamically.

[PFMaBiSmAd]

I recommend solving one problem at a time.

[nodirtyrockstar]

Good idea. Now that the connection errors are done screaming at me, I can work through what should be happening inside the loop.

 

I'll post more specific questions about what I am dealing with now. Thanks for your help everyone.