Php mysql update after insert

quinnd6

Ok I'm trying to put a url of an image into a database using php.
The url is based on the id which is a primary key that increments autmatically so is generated after insertion of the product into the database.
So I need to be able to insert product details into the mysql database and then directly after that update the imageurl field to reflect the product id which was generated during the insert.
So when I try to do it in code the insert happens but the update doesn't and I'm left with a blank image url field.
The versions of php and mysql are
PHP version: 5.4.25
MySQL version: 5.6.13
I can show you some of the code so maybe some of you may know what I'm doing wrong.

<?php 
// Parse the form data and add inventory item to the system
if (isset($_POST['product_name'])) {
	
        $product_name = mysqli_real_escape_string($connection,$_POST['product_name']);
	$price = mysqli_real_escape_string($connection,$_POST['price']);
	$category = mysqli_real_escape_string($connection,$_POST['category']);
	$subcategory = mysqli_real_escape_string($connection,$_POST['subcategory']);
	$details = mysqli_real_escape_string($connection,$_POST['details']);
	// See if that product name is an identical match to another product in the system
	$sql = mysqli_query($connection,"SELECT id FROM products WHERE product_name='$product_name' LIMIT 1");
	$productMatch = mysqli_num_rows($sql); // count the output amount
        if ($productMatch > 0) {
            echo 'Sorry you tried to place a duplicate "Product Name" into the system, <a href="inventory_list.php">click here</a>';
            exit();
	}
	// Add this product into the database now
	$sql = mysqli_query($connection,"INSERT INTO products (product_name, price, details, category, subcategory, date_added) 
        VALUES('$product_name','$price','$details','$category','$subcategory',now());") or die (mysql_error());
        $pid = mysqli_insert_id($connection);
	// Place image in the folder 
	$newname = "$pid.jpg";
	move_uploaded_file( $_FILES['fileField']['tmp_name'], "../inventory_images/$newname");
        $sql = mysqli_query($connection,"UPDATE PRODUCTS SET picurl1='inventory_images/$pid.jpg' WHERE id='$pid';"); 
	header("location: inventory_list.php"); 
        exit();
}
?>

The second Update Products sql statement is not working no matter what I try.

seamus

First off, try sticking some debugging statements in there to see exactly what the code is doing - try to echo $pid to log or even to the screen just for this purpose, make sure you're getting something back.

Also, maybe try replacing the INSERT block. Try this instead.

if(!(mysqli_query($connection,"INSERT INTO products (product_name, price, details, category, subcategory, date_added)  VALUES('$product_name','$price','$details','$category','$subcategory',now())")) {
    die (mysql_error());
}

quinnd6

Nope that didn't work either thanks for trying.

ectoraige

Is the call to move_uploaded_file working? Check your error logs, script might be falling before getting to the update statement.

quinnd6

Yes it is.

cython

I'd suggest you construct the update statement into a variable and, taking example from seamus' suggestion, echo that to the log before trying to execute it from the variable, just in case there is something in it that is failing or invalid that you can't see, or similar. You might find you're generating a different statement than you expect.

EDIT: another thought - mysql on unix can be case-sensitive about table names, and you have a mix of products and PRODUCTS in your insert and updates, os maybe the update is erroring in trying to update a non-existent table?

quinnd6

Using die(mysqli_error($link)) worked and showed me the error and I figured out the problem then.
Thanks guys for the help.

daymobrew

quinnd6 wrote: »

Using die(mysqli_error($link)) worked and showed me the error and I figured out the problem then.
Thanks guys for the help.

Please post the solution so that others that find this thread can learn from it.

ectoraige

The solution was to handle errors correctly - no error handling was being done on the UPDATE, and mysql_error was being used for error reporting on the INSERT statement, but when using mysqli one needs to use mysqli_error instead.

If OP comes back I'm sure the failure was due to some silly SQL mistake such as using incorrect column names or something.

Talisman

Most likely the field that was being updated in the database table didn't exist.

Given the code example it seemed a pointless exercise to update the database record. If the file name of the image is the identity of the product record why do you need to store it in the database?

ID = 1 -> Image name is "1.jpg"

There's no need for two fields to record the identity of the record.

seamus

Talisman wrote: »

Most likely the field that was being updated in the database table didn't exist.

Given the code example it seemed a pointless exercise to update the database record. If the file name of the image is the identity of the product record why do you need to store it in the database?

ID = 1 -> Image name is "1.jpg"

There's no need for two fields to record the identity of the record.

You're kind of tied then to the ID. If you want to move or rename your image files later on, you only need to update the database to point at the correct one. Otherwise you'd have to rewrite your application to break the link between the ID and the file.

Talisman

seamus wrote: »

You're kind of tied then to the ID. If you want to move or rename your image files later on, you only need to update the database to point at the correct one. Otherwise you'd have to rewrite your application to break the link between the ID and the file.

That language is a bit extreme. It's a relatively straight forward process to add a field to the database table and an additional parameter to the relevant queries and templates. I'd like to point out that the OP was also hardcoding the folder path into the update query so moving the files later was not a primary concern.

A more forward thinking solution would have been to generate a unique file name before the database insert and use it for the image upload, this pattern would have eliminated the need for the update query. Using the ID for the filename made little sense.

For example, if you wanted to replace the image on the server you would need to check the database to find the product first.

The first database query in the provided code sample tells us that the product name is unique (e.g. 'Blue Shoes'), so if you slugify the product name you get a unique filename for the image (e.g. 'blue-shoes.jpg'). The file name on the server would be easily recognisable and also provide some SEO benefit if that was a potential future requirement.