MySQLi Prepared Statement Does not Insert Database Record

[webdbapps]

Hi,

I'm attempting to insert a record into my openjobs table which is comprised of openjobid, jobid, DateOpened, DateFilled, isactive.

The goal is to have the user select one or more checkboxes for the job types they want to post and select the restaurant from the dropdown list. The submit button should execute the mysqli_prepare statement. The page loads correctly but no record is inserted. Here's my code:

<form id="frmSelStore" method="post" action ="<?php 
$restaurantid = $_POST['ddlStore'];
$jobtype = $_POST['jobs'];
$stmt = mysqli_stmt_init($mysqli);
$sql = 'Insert into `OpenJobs`(`restaurantid`,`jobid`) Values($restaurantid,$jobtype)';
if(mysqli_stmt_prepare($stmt,$sql))
{
	mysqli_stmt_bind_param("ss",$restaurantid,$jobtype);
	mysqli_stmt_execute($stmt);
	printf("%d Row Inserted.\n", mysqli_stmt_affected_rows($stmt));
	mysqli_stmt_close($stmt);
}

?>">
<fieldset><?php 
	$query="SELECT restaurantid,location from restaurant";
	$result = mysqli_query($mysqli,$query);
	echo '<select name="ddlStore">';
	while($row=mysqli_fetch_array($result))
	{
		echo '<option value="' . htmlspecialchars($row['restaurantid']) . '">' .
		 htmlspecialchars($row['location']) . 
		'</option>';
	}
	echo '</select>';
?>
<h2>Step 2:  Please Select the Jobs to be Posted</h2>
<input type="checkbox" name='[jobs]'  value="2" id="cbJobs1">Host/Hostess
</input><br/>
<input type="checkbox" name='[jobs]' value="4" id="cbJob2">Bartender</input><br/>
<input type="checkbox" name='[jobs]' value="1" id="cbJob3">Server</input><br/>
<input type="checkbox" name='[jobs]' value="3" id="cbJobs4">Cook</input><br/>

<input type="submit" id="submit"/>
</fieldset>
</form>

How should I modify the above code to ensure that the prepared statement is processed correctly?

Thanks,
Sid

[guido2004]

Try

$sql = 'Insert into `OpenJobs`(`restaurantid`,`jobid`) Values(?, ?)'; 

See example # 2 in the manual: http://www.php.net/manual/en/mysqli-stmt.bind-param.php

[webdbapps]

Hi Guido,

I made the change, and have been patterning my code using the manual example per your suggestion. After uploading the revised file, when I browse the page I now receive the following error:
The requested URL /Admin/<br /><b>Warning</b>: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, string given in <b>/home/tgitcorp/public_html/Admin/admin_index.php</b> on line <b>25</b><br />0 Row Inserted. was not found on this server.

Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request.

Can you help me further troubleshoot this issue?

Thanks,
Sid

[SpacePhoenix]

mysqli_stmt_bind_param($stmt,"ss",$restaurantid,$jobtype); 

You was missing the $stmt (the statment object).

[StarLion]

How about the fact that you've jam sandwiched this particular block of code into the middle of a form's action attribute, and arnt echoing out anything resembling a URL?

I think perhaps you have bigger problems than your mysqli code not executing.

[webdbapps]

How about the fact that you've jam sandwiched this particular block of code into the middle of a form's action attribute, and arnt echoing out anything resembling a URL?

I think perhaps you have bigger problems than your mysqli code not executing.

Could you elaborate further as to how I might remedy this? If the action attribute is not the appropriate place for this code block, should I create a separate file and reference that filename as the form's action? Just needing some solid suggestions on ways to improve.

Thanks

[StarLion]

Could you elaborate further as to how I might remedy this? If the action attribute is not the appropriate place for this code block, should I create a separate file and reference that filename as the form's action? Just needing some solid suggestions on ways to improve.

That is one way to do it, yes, or have the action point at the same page, and put the code block at the top of the page, nestled inside a if(isset($_POST['ddlStore'])) {}.

[webdbapps]

@StarLion,
Thanks for the suggestion. I've modified the code as follows:

<?php 
include_once '/home/tgitcorp/includes/dbconn.php';
if(isset($_POST['ddlStore']))
{
	
	$restaurantid = $_POST ['ddlStore'];
	$jobtype = $_POST ['jobs'];
	$stmt = mysqli_stmt_init ( $mysqli );
	$sql = 'Insert into `OpenJobs`(`restaurantid`,`jobid`) Values(?,?)';
	if (mysqli_stmt_prepare ( $stmt, $sql )) {
		mysqli_stmt_bind_param ( $stmt, "ss", $restaurantid, $jobtype );
		mysqli_stmt_execute ( $stmt );
		printf ( "%d Row Inserted.\n", mysqli_stmt_affected_rows ( $stmt ) );
		mysqli_stmt_close ( $stmt );
	}
}
?>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title>Tricorp Job Listing Admin Panel</title>
<link rel="stylesheet" href="../css/style.css" type="text/css" />
</head>

<body>
<h1>Job Listing Administration</h1>
<h2>Step 1: Please Select Your Restaurant</h2>
<form id="frmSelStore" method="post" action="admin_index.php">
<fieldset><?php 
	$query="SELECT restaurantid,location from restaurant";
	$result = mysqli_query($mysqli,$query);
	echo '<select name="ddlStore">';
	while($row=mysqli_fetch_array($result))
	{
		echo '<option value="' . htmlspecialchars($row['restaurantid']) . '">' .
		 htmlspecialchars($row['location']) . 
		'</option>';
	}
	echo '</select>';
?>
<h2>Step 2:  Please Select the Jobs to be Posted</h2>
<input type="checkbox" name='[jobs]'  value="2" id="cbJobs1">Host/Hostess
</input><br/>
<input type="checkbox" name='[jobs]' value="4" id="cbJob2">Bartender</input><br/>
<input type="checkbox" name='[jobs]' value="1" id="cbJob3">Server</input><br/>
<input type="checkbox" name='[jobs]' value="3" id="cbJobs4">Cook</input><br/>

<input type="submit" id="submit"/>
</fieldset>
</form>
</body>

</html>

Despite adding $stmt as the first argument in the mysqli_stmt_bind_param, I still receive the following error when browsing the page:
The requested URL /Admin/<br /><b>Warning</b>: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, string given in <b>/home/tgitcorp/public_html/Admin/admin_index.php</b> on line <b>25</b><br />0 Row Inserted. was not found on this server.

Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request.

Is there a better way to structure the $stmt variable?

Thanks,
Sid

[StarLion]

Despite adding $stmt as the first argument in the mysqli_stmt_bind_param, I still receive the following error when browsing the page:
The requested URL /Admin/<br /><b>Warning</b>: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, string given in <b>/home/tgitcorp/public_html/Admin/admin_index.php</b> on line <b>25</b><br />0 Row Inserted. was not found on this server.

Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request.

Is there a better way to structure the $stmt variable?

Thanks,
Sid

Line 25? There's no way that command is on line 25 anymore, based on what you posted - did you save/upload the file after making the changes?

[webdbapps]

Here's the revised script:

if(isset($_POST['ddlStore']))
{

$restaurantid = $_POST ['ddlStore'];
$jobtype = $_POST ['jobs'];
$stmt = mysqli_prepare($mysqli, "Insert into OpenJobs Values (?,?)");
mysqli_stmt_bind_param($stmt,'ss',$restaurantid,$jobtype);
$restaurantid = $_POST['ddlStore'];
$jobtype = $_POST['jobs'];
mysqli_stmt_execute($stmt);
printf("%d Row Inserted.\n", mysqli_stmt_affected_rows($stmt));
mysqli_stmt_close($stmt);
}

This is now uploaded to the site, the associated error messages are as follows:
Warning: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, boolean given in /home/tgitcorp/public_html/Admin/admin_index.php on line 9

Warning: mysqli_stmt_execute() expects parameter 1 to be mysqli_stmt, boolean given in /home/tgitcorp/public_html/Admin/admin_index.php on line 12

Warning: mysqli_stmt_affected_rows() expects parameter 1 to be mysqli_stmt, boolean given in /home/tgitcorp/public_html/Admin/admin_index.php on line 13
0 Row Inserted.
Warning: mysqli_stmt_close() expects parameter 1 to be mysqli_stmt, boolean given in /home/tgitcorp/public_html/Admin/admin_index.php on line 14

[StarLion]

Warning: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, boolean given in /home/tgitcorp/public_html/Admin/admin_index.php on line 9

Now see, this makes more sense to me. And it tells me: mysql_prepare returned FALSE.

try giving the field names in your query....
Insert into OpenJobs(field1,field2) Values (?,?)

[webdbapps]

It looks like the insertion is failing silently now. When I choose a restaurant and tick one or more checkbox, pressing the submit button yields the message -1 Inserted at top of the page.

[webdbapps]

I'm wondering if my database schema is a bit too complex?
The idea is to insert open jobs into the openjobs table. However, I also created a jobdesc table that contains the jobid, Title and description of each of the 4 positions.
My intial thought was to just get the insertion working into the openjobs table. Ultimately, the user needs to select which position they're hiring, modify the existing job description and then insert into the openjobs table. The jobdesc table has jobid set as follows: 1=Server 2= Host/Hostess 3=Cook and 4=Bartender.

So, to streamline things and keep the end result in mind, I just added a jobdesc field varchar(200) to my openjobs table.

Should I restructure the form to allow retrieval of the canned job description as well as insertion of the new job? Or, is this trying to do too much in one form? If two forms are advisable, would the first form simply retrieve the canned description and the second form handle the insertion into openjobs?