trying to retrieve the last id of a record inserted

davids_media · May 12, 2012

I have two pages - add_product.php and show.php

here is the code for add_product.php

<?php

ini_set('display_errors',1);
error_reporting(-1);

require_once ('./includes/config.inc.php');

require_once (MYSQL);

$add_cat_errors = array();

if ($_SERVER['REQUEST_METHOD'] == 'POST')
{
// Check for a name:
if (empty($_POST['product'])) {
	$add_cat_errors['product'] = 'Please enter the name!';
}

// Check for a description:
if (empty($_POST['prod_descr'])) {
	$add_cat_errors['prod_descr'] = 'Please enter the description!';
}

// Check for a category:
if (!isset($_POST['cat']) || !filter_var($_POST['cat'], FILTER_VALIDATE_INT, array('min_range' => 1))) {
	$add_cat_errors['cat'] = 'Please select a category!';
}

// Check for a price:
if (empty($_POST['price']) || !filter_var($_POST['price'], FILTER_VALIDATE_FLOAT) || ($_POST['price'] <= 0)) {
	$add_cat_errors['price'] = 'Please enter a valid price!';
}

// Check for a category:
if (!isset($_POST['directory']) || !filter_var($_POST['directory'], FILTER_VALIDATE_INT, array('min_range' => 1))) {
	$add_cat_errors['directory'] = 'Please select a directory!';
}

// Check for a stock:
if (empty($_POST['stock']) || !filter_var($_POST['stock'], FILTER_VALIDATE_INT, array('min_range' => 1))) {
	$add_cat_errors['stock'] = 'Please enter the quantity in stock!';
}

if (empty($add_cat_errors))
{
$query = "INSERT INTO product (product, prod_descr, catID, price, dirID, stock) VALUES (?, ?, ?, ?, ?, ?)";
	// Prepare the statement:
$stmt = mysqli_prepare($dbc, $query);
// For debugging purposes:
	// if (!$stmt) echo mysqli_stmt_error($stmt);

	// Bind the variables:
mysqli_stmt_bind_param($stmt, 'sssssi', $name, $desc, $_POST['cat'], $_POST['price'], $_POST['directory'], $_POST['stock']);

	// Make the extra variable associations:
	$name = strip_tags($_POST['product']);
	$desc = strip_tags($_POST['prod_descr']);
	// Execute the query:
	mysqli_stmt_execute($stmt);

	if (mysqli_stmt_affected_rows($stmt) == 1) { // If it ran OK.

		// Print a message:
		echo '<h4>The product has been added!</h4>';

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

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


	} else { // If it did not run OK.
		trigger_error('The product could not be added due to a system error. We apologize for any inconvenience.');
	}

} // End of $errors IF.

} else { // Clear out the session on a GET request:
} // End of the submission IF.

require_once ('./includes/form_functions.inc.php');
?>

<form enctype="multipart/form-data" action="add_image.php?prodID={$row['prodID']}" method="post" accept-charset="utf-8">

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

		Product<br /><?php create_form_input('product', 'text', $add_cat_errors); ?>
            
            Description<br /><?php create_form_input('prod_descr', 'textarea', $add_cat_errors); ?>
            
Category<br /><select name="cat"<?php if (array_key_exists('cat', $add_cat_errors)); ?>>
		<option>Select One</option>
		<?php // Retrieve all the categories and add to the pull-down menu:
		$q = 'SELECT catID, cat FROM category ORDER BY cat ASC';		
		$r = mysqli_query ($dbc, $q);
			while ($row = mysqli_fetch_array ($r, MYSQLI_NUM)) {
				echo "<option value=\"$row[0]\"";
				// Check for stickyness:
				if (isset($_POST['cat']) && ($_POST['cat'] == $row[0]) ) echo ' selected="selected"';
				echo ">$row[1]</option>\n";
			}
		?>
		</select><?php if (array_key_exists('cat', $add_cat_errors)) echo $add_cat_errors['cat']; ?>
            
            Price<br /><?php create_form_input('price', 'text', $add_cat_errors); ?>
            
            Directory<br /><select name="directory"<?php if (array_key_exists('directory', $add_cat_errors)); ?>>
		<option>Select One</option>
		<?php // Retrieve all the categories and add to the pull-down menu:
		$q = 'SELECT dirID, directory FROM directory ORDER BY directory ASC';		
		$r = mysqli_query ($dbc, $q);
			while ($row = mysqli_fetch_array ($r, MYSQLI_NUM)) {
				echo "<option value=\"$row[0]\"";
				// Check for stickyness:
				if (isset($_POST['directory']) && ($_POST['directory'] == $row[0]) ) echo ' selected="selected"';
				echo ">$row[1]</option>\n";
			}
		?>
		</select><?php if (array_key_exists('directory', $add_cat_errors)) echo $add_cat_errors['directory']; ?>
</select>

         				<br />

		Stock<br /><?php create_form_input('stock', 'text', $add_cat_errors); ?>


<input type="submit" value="Add This Product" class="button" />


</fieldset>

</form>

and here is the code for show.php

<?php

ini_set('display_errors',1);
error_reporting(-1);

require_once ('./includes/config.inc.php');

require_once (MYSQL);

$add_cat_errors = array();

if($id = isset($_GET['prodID']))
{
$q = "SELECT `prodID`, `product`, `prod_descr`, `catID`, `dirID`, `price`, `stock` FROM product WHERE `prodID`='{$_GET['prodID']}'";
$r = mysqli_query($dbc, $q);
while($row = mysqli_fetch_array($r))
{
echo $row['product'];
echo "<br />";
echo $row['prod_descr'];
echo "<br />";
echo $row['catID'];
echo "<br />";
echo $row ['dirID'];
echo "<br />";
echo $row['price'];
echo "<br />";
echo $row['stock'];
}
}

What I want is to try and retrieve the id of the last record inserted in add_product and pass it into show.php, but I get this error message.

An error occurred in script 'C:\Users\David Morgan\Desktop\WEBSITES\hairz_&_graces\site\admin\add_image.php' on line 16:
mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given

I am struggling to find out where I am going wrong at the moment, if anyone could please help me that would be really appreciated.

.josh · May 12, 2012

Your "if" condition will always be true, because it is evaluating the assignment of something to a variable, not the value of the variable. Try echoing out $_GET['prodID'] to see if it is really set.

davids_media · May 12, 2012

i've tried that getting absolutely nowhere whatsoever

.josh · May 12, 2012

that's not a very helpful response.. remember, we don't have the benefit of sitting in front of your computer to see what is actually happening, so you need to be more detailed...well when you tried it, what happened? is the prodID echoing out or not?

Eiolon · May 12, 2012

I may be blind, but I don't see mysql_insert_id after your insert query on add_product.php.

http://php.net/manual/en/function.mysql-insert-id.php

ManiacDan · May 12, 2012

1) Add.php doesn't contain the text "show.php," so I don't see where it's forwarding at all. All I see is add_image.php

2) The error on show.php means your query is wrong, echo mysql_error() to get the actual mysql error message.

3) Josh was right about your IF statement being wrong.

4) This line refers to $row but $row is not available at that time. Again, answering Josh's question would have fixed this:

You are most likely not getting the variable that was pointed out in your first reply.

davids_media · May 13, 2012

1) Add.php doesn't contain the text "show.php," so I don't see where it's forwarding at all. All I see is add_image.php

2) The error on show.php means your query is wrong, echo mysql_error() to get the actual mysql error message.

3) Josh was right about your IF statement being wrong.

4) This line refers to $row but $row is not available at that time. Again, answering Josh's question would have fixed this:

<form enctype="multipart/form-data" action="add_image.php?prodID={$row['prodID']}" method="post" accept-charset="utf-8">

You are most likely not getting the variable that was pointed out in your first reply.

1. My own mistake, the name of the file being redirected is actually add_image.php not show.php which I soon realised. But they both have the same code regardless.

2. Believe or not, I have often chosen not to use mysqli_error() on much of my code, so should I implement a or die(mysqli_error()) after line 16 on add_image.php (what I originally stated as show.php).

3. Should I just remove the IF statement, as I tried that (I think I misunderstood what he meant in his post)?

4. My querystring, I guess I would have to remove the $row from it but if I plan to use mysql_insert_id(), what two parameters should I use inside it and then, how would I be able to retrieve that using the querystring from the form action URL?

floridaflatlander · May 13, 2012

I use one parameter for mysqli_insert_id

$lastid = mysqli_insert_id($dbc);

place it directly below $r = mysqli_query($dbc, $q);

floridaflatlander · May 13, 2012

Why don't you just enter your info in a db table.

Assign a $last_id.

Use a redirect to www.mysite.com/show.php?item=$last_id

ManiacDan · May 13, 2012

2. Believe or not, I have often chosen not to use mysqli_error() on much of my code, so should I implement a or die(mysqli_error()) after line 16 on add_image.php (what I originally stated as show.php).

Do that at least for the time being, I bet your query is wrong. What you should be doing is something more like:

$result = mysql_query($sql);
if ( !$result ) {
  echo "Error with MySQL query:<br /><i>{$sql}</i><br />MySQL returned: " . mysql_error() . "<P />";
}

3. Should I just remove the IF statement, as I tried that (I think I misunderstood what he meant in his post)?

Your IF statement is an assignment. You need simply if ( isset($thevar) )

4. My querystring, I guess I would have to remove the $row from it but if I plan to use mysql_insert_id(), what two parameters should I use inside it and then, how would I be able to retrieve that using the querystring from the form action URL?

This response doesn't make much sense, but what I'm saying here is you use $row in the URL for the form, but $row doesn't exist. You're printing a form with an action of "add_image.php?prodID=". I think that's the cause of your whole issue. You insert a row above where you print the form. Use msyql_insert_id to fetch the inserted ID, and use that in your form.

davids_media · May 13, 2012

I have followed everything that ManiacDan has suggested, the implementation of the mysqli_error() and the mysqli_insert_id() after the $r variable but to no avail, I get no error message but it does not write to the database.

ManiacDan · May 13, 2012

You get NO error message on an INSERT statement? And the insert fails? Are you sure it's even running? It's pretty deep into some control structures. Echo the query before you execute it.

PFMaBiSmAd · May 14, 2012

after the $r variable

^^^ Since your INSERT query posted in this thread is using a mysqli prepared statement and there's no $r variable present in that specific code, it would probably be a good idea to post your current code. I suspect you are trying to get the last insert id someplace other than immediately after the INSERT query was executed (that's the only place it is available.)

PFMaBiSmAd · May 14, 2012

After the point where your INSERT query successfully executes, you can use either one of the following statements to get the last insert id -

<?php
$last_id = mysqli_stmt_insert_id($stmt);

$last_id = mysqli_insert_id($dbc);

davids_media · May 14, 2012

Following PFMaBiSmAd's advice, I have applied the mysqli_insert_id() statement after my insert query statement, but whilst it writes perfectly fine to the database, I now would like if is possible to direct to add_image.php and retrieve the ID of the last record inserted. here is my code at present;

<?php

ini_set('display_errors',1);
error_reporting(-1);

require_once ('./includes/config.inc.php');

require_once (MYSQL);

$add_cat_errors = array();

if ($_SERVER['REQUEST_METHOD'] == 'POST')
{
// Check for a name:
if (empty($_POST['product'])) {
	$add_cat_errors['product'] = 'Please enter the name!';
}

// Check for a description:
if (empty($_POST['prod_descr'])) {
	$add_cat_errors['prod_descr'] = 'Please enter the description!';
}

// Check for a category:
if (!isset($_POST['cat']) || !filter_var($_POST['cat'], FILTER_VALIDATE_INT, array('min_range' => 1))) {
	$add_cat_errors['cat'] = 'Please select a category!';
}

// Check for a price:
if (empty($_POST['price']) || !filter_var($_POST['price'], FILTER_VALIDATE_FLOAT) || ($_POST['price'] <= 0)) {
	$add_cat_errors['price'] = 'Please enter a valid price!';
}

// Check for a category:
if (!isset($_POST['directory']) || !filter_var($_POST['directory'], FILTER_VALIDATE_INT, array('min_range' => 1))) {
	$add_cat_errors['directory'] = 'Please select a directory!';
}

// Check for a stock:
if (empty($_POST['stock']) || !filter_var($_POST['stock'], FILTER_VALIDATE_INT, array('min_range' => 1))) {
	$add_cat_errors['stock'] = 'Please enter the quantity in stock!';
}

if (empty($add_cat_errors))
{
$query = "INSERT INTO product (product, prod_descr, catID, price, dirID, stock) VALUES (?, ?, ?, ?, ?, ?)";
	// Prepare the statement:
$stmt = mysqli_prepare($dbc, $query);
// $id = mysqli_insert_id($dbc, $query);
// For debugging purposes:

	//if (!$stmt) echo mysqli_stmt_error($stmt);

	// Bind the variables:
mysqli_stmt_bind_param($stmt, 'sssssi', $name, $desc, $_POST['cat'], $_POST['price'], $_POST['directory'], $_POST['stock']);

$last_id = mysqli_stmt_insert_id($stmt);

$last_id = mysqli_insert_id($dbc);

	// Make the extra variable associations:
	$name = strip_tags($_POST['product']);
	$desc = strip_tags($_POST['prod_descr']);
	// Execute the query:
	mysqli_stmt_execute($stmt);

	if (mysqli_stmt_affected_rows($stmt) == 1) { // If it ran OK.

		// Print a message:
		echo '<h4>The product has been added!</h4>';

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

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


	} else { // If it did not run OK.
		trigger_error('The product could not be added due to a system error. We apologize for any inconvenience.');
	}

} // End of $errors IF.

} else { // Clear out the session on a GET request:
} // End of the submission IF.

require_once ('./includes/form_functions.inc.php');
?>

<form enctype="multipart/form-data" action="add_product2.php" method="post" accept-charset="utf-8">

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

		Product<br /><?php create_form_input('product', 'text', $add_cat_errors); ?>
            
            Description<br /><?php create_form_input('prod_descr', 'textarea', $add_cat_errors); ?>
            
Category<br /><select name="cat"<?php if (array_key_exists('cat', $add_cat_errors)); ?>>
		<option>Select One</option>
		<?php // Retrieve all the categories and add to the pull-down menu:
		$q = 'SELECT catID, cat FROM category ORDER BY cat ASC';		
		$r = mysqli_query ($dbc, $q);
			while ($row = mysqli_fetch_array ($r, MYSQLI_NUM)) {
				echo "<option value=\"$row[0]\"";
				// Check for stickyness:
				if (isset($_POST['cat']) && ($_POST['cat'] == $row[0]) ) echo ' selected="selected"';
				echo ">$row[1]</option>\n";
			}
		?>
		</select><?php if (array_key_exists('cat', $add_cat_errors)) echo $add_cat_errors['cat']; ?>
            
            Price<br /><?php create_form_input('price', 'text', $add_cat_errors); ?>
            
            Directory<br /><select name="directory"<?php if (array_key_exists('directory', $add_cat_errors)); ?>>
		<option>Select One</option>
		<?php // Retrieve all the categories and add to the pull-down menu:
		$q = 'SELECT dirID, directory FROM directory ORDER BY directory ASC';		
		$r = mysqli_query ($dbc, $q);
			while ($row = mysqli_fetch_array ($r, MYSQLI_NUM)) {
				echo "<option value=\"$row[0]\"";
				// Check for stickyness:
				if (isset($_POST['directory']) && ($_POST['directory'] == $row[0]) ) echo ' selected="selected"';
				echo ">$row[1]</option>\n";
			}
		?>
		</select><?php if (array_key_exists('directory', $add_cat_errors)) echo $add_cat_errors['directory']; ?>
</select>

         				<br />

		Stock<br /><?php create_form_input('stock', 'text', $add_cat_errors); ?>


<input type="submit" value="Add This Product" class="button" />


</fieldset>

</form>

however, I have tried using this querystring in my form

<form action="add_image.php?prodID=$lastid method="post">

but it does not output the product ID. also I did contemplate using headers (to redirect) butI thought it was not a good idea especially since I am writing and posting data from a HTML form.

also, here is my add_image.php code at present (the page which should then be redirected to upon submission)

<?php

ini_set('display_errors',1);
error_reporting(-1);

require_once ('./includes/config.inc.php');

require_once (MYSQL);

$add_cat_errors = array();

if($id = isset($_GET['prodID']))
{
$q = "SELECT `prodID`, `product`, `prod_descr`, `catID`, `dirID`, `price`, `stock` FROM product WHERE `prodID`='{$_GET['prodID']}'";
$r = mysqli_query($dbc, $q);
$id = mysqli_insert_id($dbc);
if (!$r) {
  echo "Error with MySQL query:<br /><i>{$sql}</i><br />MySQL returned: " . mysql_error() . "<P />";
}
while($row = mysqli_fetch_array($r))
{
echo $row['product'];
echo "<br />";
echo $row['prod_descr'];
echo "<br />";
echo $row['catID'];
echo "<br />";
echo $row ['dirID'];
echo "<br />";
echo $row['price'];
echo "<br />";
echo $row['stock'];
}
}

PFMaBiSmAd · May 15, 2012

You would use one or the other of the statements I posted, not both. You also need to execute the statement AFTER you execute the INSERT query, not before.

Sign In

trying to retrieve the last id of a record inserted

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information