Prepared Statement Problem

tescgo · Aug 20, 2010

Hi,

I have been working on this for over 2 hours now and its driving me nuts!

I am trying to create a page that will allow my client to add products to their database.

I have one set of prepared statments on line 94 - 97 that work fine, however, further down the page when I try to use another prepared statement to add info to another table it simply wont work.

THe errors i'm getting are:

Warning: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, boolean given in /home/animport/public_html/add_item.php on line 126

Warning: mysqli_stmt_execute() expects parameter 1 to be mysqli_stmt, boolean given in /home/animport/public_html/add_item.php on line 127

Warning: mysqli_stmt_affected_rows() expects parameter 1 to be mysqli_stmt, boolean given in /home/animport/public_html/add_item.php on line 133

Your submission could not be processed due to a system error.

Warning: mysqli_stmt_close() expects parameter 1 to be mysqli_stmt, boolean given in /home/animport/public_html/add_item.php on line 149

My Database connection is in a seperate file and reads:

$dbc = @mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) OR die ('Error connecting to mysql: '.mysqli_connect_error());

The php file for add_item.php page is as follows:

<?php # Script 17.1 - add_print.php
// This page allows the administrator to add a print (product).

require_once ('../mysqli_connect_bath.php');

if (isset($_POST['submitted'])) { // Handle the form.

// Validate the incoming data...
$errors = array();

// Check for a print name:
if (!empty($_POST['product_name'])) {
	$pn = mysqli_real_escape_string($dbc, trim($_POST['product_name']));
} else {
	$errors[] = 'Please enter the product\'s name!';
}

	// Check for the quantity (not required):
if (!empty($_POST['product_name'])) {
	$qty = mysqli_real_escape_string($dbc, trim($_POST['quantity']));

} else {
	$errors[] = 'Please enter the quantity!';
}


// Check for an image:
if (is_uploaded_file ($_FILES['image']['tmp_name'])) {

	// Create a temporary file name:
	$temp = '../uploads/' . md5($_FILES['image']['name']);

	// Move the file over:
	if (move_uploaded_file($_FILES['image']['tmp_name'], $temp)) {

		echo '<p>The file has been uploaded!</p>';

		// Set the $i variable to the image's name:
		$i = $_FILES['image']['name'];

	} else { // Couldn't move the file over.
		$errors[] = 'The file could not be moved.';
		$temp = $_FILES['image']['tmp_name'];
	}

} else { // No uploaded file.
	$errors[] = 'No file was uploaded.';
	$temp = NULL;
}




// Check for a category (not required):
$c = (!empty($_POST['category'])) ? trim($_POST['category']) : NULL;

	// Check for a category (not required):
$sc = (!empty($_POST['category2'])) ? trim($_POST['category2']) : NULL;


// Check for a price:
if (is_numeric($_POST['price'])) {
	$p = (float) $_POST['price'];
} else {
	$errors[] = 'Please enter the print\'s price!';
}

// Check for a description (not required):
$d = (!empty($_POST['description'])) ? trim($_POST['description']) : NULL;




// Validate the artist...
if (isset($_POST['artist']) && ($_POST['artist'] == 'new') ) {
	// If it's a new artist, add the artist to the database...



	// Check for a manufacturer...
	if (!empty($_POST['last_name'])) {

		$ln = trim($_POST['last_name']);

		// Add the artist to the database:
		$q = 'INSERT INTO manufacturer (manufacturer_name) VALUES (?)';
		$stmt = mysqli_prepare($dbc, $q);
		mysqli_stmt_bind_param($stmt, 's', $ln);
		mysqli_stmt_execute($stmt);

		// Check the results....
		if (mysqli_stmt_affected_rows($stmt) == 1) {
			echo '<p>The artist has been added.</p>';
			$mid = mysqli_stmt_insert_id($stmt); // Get the artist ID.
		} else { // Error!
			$errors[] = 'The new artist could not be added to the database!';
		}

		// Close this prepared statement:
		mysqli_stmt_close($stmt);

	} else { // No last name value.
		$errors[] = 'Please enter the artist\'s name!';
	}

} elseif ( isset($_POST['artist']) && ($_POST['artist'] == 'existing') && ($_POST['existing'] > 0) ) { // Existing artist.
	$mid = (int) $_POST['existing'];
} else { // No artist selected.
	$errors[] = 'Please enter or select the print\'s artist!';
}

if (empty($errors)) { // If everything's OK.

	// Add the print to the database:
	$q = 'INSERT INTO products (manufacturer_id, product_name, price, description, image_name, stock_level, category, sub-category) VALUES (?, ?, ?, ?, ?, ?)';
	//$stmt = mysqli_prepare($dbc, $q);
	$stmt = $dbc->prepare($q);
	mysqli_stmt_bind_param($stmt, 'isdssi', $mid, $pn, $p, $d, $i, $qty);
	mysqli_stmt_execute($stmt);




	// Check the results...
	if (mysqli_stmt_affected_rows($stmt) == 1) {

		// Print a message:
		echo '<p>The print has been added.</p>';

		// Rename the image:
		$id = mysqli_stmt_insert_id($stmt); // Get the print ID.
		rename ($temp, "../uploads/$id");

		// Clear $_POST:
		$_POST = array();

	} else { // Error!
		echo '<p style="font-weight: bold; color: #C00">Your submission could not be processed due to a system error.</p>'; 
	}

	mysqli_stmt_close($stmt);

} // End of $errors IF.

// Delete the uploaded file if it still exists:
if ( isset($temp) && file_exists ($temp) && is_file($temp) ) {
	unlink ($temp);
}

} // End of the submission IF.

// Check for any errors and print them:
if ( !empty($errors) && is_array($errors) ) {
	echo '<h1>Error!</h1>
	<p style="font-weight: bold; color: #C00">The following error(s) occurred:<br />';
	foreach ($errors as $msg) {
		echo " - $msg<br />\n";
	}
	echo 'Please reselect the print image and try again.</p>';
}

// Display the form...
?>
<h1>Add a Print</h1>
<form enctype="multipart/form-data" action="add_item.php" method="post">

<input type="hidden" name="MAX_FILE_SIZE" value="524288" />

<fieldset><legend>Fill out the form to add a print to the catalog:</legend>

<p><b>Product Name:</b> <input type="text" name="product_name" size="30" maxlength="60" value="<?php if (isset($_POST['product_name'])) echo htmlspecialchars($_POST['product_name']); ?>" /></p>



<p><b>Image:</b> <input type="file" name="image" /></p>

<div><b>Manufacturer:</b> 
<p><input type="radio" name="artist" value="existing" <?php if (isset($_POST['artist']) && ($_POST['artist'] == 'existing') ) echo ' checked="checked"'; ?> /> Existing =>
<select name="existing"><option>Select One</option>
<?php // Retrieve all the artists and add to the pull-down menu.
$q = "SELECT manufacturer_id, CONCAT_WS(' ', manufacturer_name) FROM manufacturer ORDER BY manufacturer_name ASC";		
$r = mysqli_query ($dbc, $q);
if (mysqli_num_rows($r) > 0) {
	while ($row = mysqli_fetch_array ($r, MYSQLI_NUM)) {
		echo "<option value=\"$row[0]\"";
		// Check for stickyness:
		if (isset($_POST['existing']) && ($_POST['existing'] == $row[0]) ) echo ' selected="selected"';
		echo ">$row[1]</option>\n";
	}
} else {
	echo '<option>Please add a new artist.</option>';
}
mysqli_close($dbc); // Close the database connection.
?>
</select></p>

<p><input type="radio" name="artist" value="new" <?php if (isset($_POST['artist']) && ($_POST['artist'] == 'new') ) echo ' checked="checked"'; ?> /> New =>
Add New Manufacturer: <input type="text" name="last_name" size="10" maxlength="40" value="<?php if (isset($_POST['last_name'])) echo $_POST['last_name']; ?>" /></p>
</div>

<p><b>Price:</b> <input type="text" name="price" size="10" maxlength="10" value="<?php if (isset($_POST['price'])) echo $_POST['price']; ?>" /> <small>Do not include the dollar sign or commas.</small></p>

<p><b>Quantity:</b> <input type="text" name="quantity" size="4" maxlength="5" value="<?php if (isset($_POST['quantity'])) echo ($_POST['quantity']); ?>" /> </p>

<p><b>Category:</b> <input type="text" name="category" size="30" maxlength="60" value="<?php if (isset($_POST['category'])) echo htmlspecialchars($_POST['category']); ?>" /> (optional)</p>
<p><b>Sub Category:</b> <input type="text" name="category2" size="30" maxlength="60" value="<?php if (isset($_POST['category2'])) echo htmlspecialchars($_POST['category2']); ?>" /> (optional)</p>

<p><b>Description:</b> <textarea name="description" cols="40" rows="5"><?php if (isset($_POST['description'])) echo $_POST['description']; ?></textarea> (optional)</p>

</fieldset>

<div align="center"><input type="submit" name="submit" value="Submit" /></div>
<input type="hidden" name="submitted" value="TRUE" />

</form>

bradgrafelman · Aug 20, 2010

Why do you have the '@' error suppressor in front of the call to mysqli_connect()?

tescgo · Aug 21, 2010

To be honest with you i'm not sure. I've been using this connect file for years andkinda forgot about it because i've never had connection issues before. I'll take the @ off and try it again later.

However, as the database had already been connected to (because the first prepared statement functions ok) do you think that that would be the cause of my troubles?

Bjom · Aug 22, 2010

The @ is not a problem but isn't helpful since it suppress info that you could use for debugging. Also see my sig about the "or die"

About your errors:

This is the line that fails:

$stmt = mysqli_prepare($dbc, $q);

Your $stmt will contain the boolean value "false" and accordingly the following bind_param will be handed over the wrong variable type.

Check the values of $dbc and $q to find out why the prepare fails.

Bjom

tescgo · Aug 22, 2010

Hi,

THanks for your reply but I am still really struggling. The code I have used to prepare the query is exactly the same (except the insert statement as I used earlier in my code. What could have changed in the $dbc variable or the $q variable that would cause a failure?

I apologise for sounding a little stupid but this is the first time i've used preapred statements and I didn't expect so many error to be thrown up. Could it be because of the number of variables that are being inserted into the database?

Mark

Bjom;10961514 wrote:
The @ is not a problem but isn't helpful since it suppress info that you could use for debugging. Also see my sig about the "or die"

About your errors:

This is the line that fails:
$stmt = mysqli_prepare($dbc, $q);
Your $stmt will contain the boolean value "false" and accordingly the following bind_param will be handed over the wrong variable type.

Check the values of $dbc and $q to find out why the prepare fails.

Bjom

NogDog · Aug 22, 2010

Try something like this to get some debug info:

$stmt = mysqli_prepare($dbc, $q); 
if($stmt == false) {
   die(mysqli_error($dbc) . "<br />\n$q");
}

You can change the die() to something more graceful for the final version, maybe using error_log() and some kind of user-friendly error message that does not reveal details about your database.

Bjom · Aug 23, 2010

Can't really scrutinize your code atm. Here is a sample that I used and that works. Maybe use it as a starting point.

About testing what and why the query fails: print out the sql and the parameter, copy and paste it together like it will be sent to the DB and use it in any tool like mysql admin, phpmyadmin or the shell.

code snippet:

$dbConn = new mysqli($dbHost, $dbUser, $dbPwd, $dbDb);
$sql = sprintf('INSERT INTO myTbl (myField) VALUES(?)');
$stmt = $dbConn->prepare($sql);
$stmt->bind_param('s',$param);
$stmt->execute();

Prepared Statement Problem

Ttescgo

Bbradgrafelman

Ttescgo

BBjom

Ttescgo

NogDog

BBjom