SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Member
    Join Date
    Jan 2009
    Location
    Visalia CA
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQLi Prepared Statement Does not Insert Database Record

    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:
    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

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Try
    PHP Code:
    $sql 'Insert into `OpenJobs`(`restaurantid`,`jobid`) Values(?, ?)'
    See example # 2 in the manual: http://www.php.net/manual/en/mysqli-stmt.bind-param.php

  3. #3
    SitePoint Member
    Join Date
    Jan 2009
    Location
    Visalia CA
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,998
    Mentioned
    100 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    mysqli_stmt_bind_param($stmt,"ss",$restaurantid,$jobtype); 
    You was missing the $stmt (the statment object).
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  5. #5
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    SitePoint Member
    Join Date
    Jan 2009
    Location
    Visalia CA
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    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

  7. #7
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by webdbapps View Post
    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'])) {}.

  8. #8
    SitePoint Member
    Join Date
    Jan 2009
    Location
    Visalia CA
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @StarLion,
    Thanks for the suggestion. I've modified the code as follows:
    Code:
    <?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

  9. #9
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by webdbapps View Post
    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?

  10. #10
    SitePoint Member
    Join Date
    Jan 2009
    Location
    Visalia CA
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  11. #11
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    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 (?,?)

  12. #12
    SitePoint Member
    Join Date
    Jan 2009
    Location
    Visalia CA
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  13. #13
    SitePoint Member
    Join Date
    Jan 2009
    Location
    Visalia CA
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •