Returning auto increment id in previous query into next query

PHP
1

I have a script that is uploading a photo, writing photo file names to a database table and creating an automatic ID (primary key).

Then, in the same script, there is a query to create an entry in the ‘activity’ table - at present it writes user data and image data but in order for the image to be linked to and work within the rest of my web app I need to pull in the id that was automatically created by the query before it and to write it into the activity table’s activity_relatedid column.

This is the working code, before I started to add the image id to activity table;

<?php
include_once 'dbconfig.php';
if(isset($_POST['btn-upload']))
{    
     
	$file = rand(1000,100000)."-".$_FILES['file']['name'];
    $file_loc = $_FILES['file']['tmp_name'];
	$file_size = $_FILES['file']['size'];
	$file_type = $_FILES['file']['type'];
	$folder="gallery-photos/";
	
	// new file size in KB
	$new_size = $file_size/1024;  
	// new file size in KB
	
	// make file name in lower case
	$new_file_name = strtolower($file);
	// make file name in lower case
	
	$final_file=str_replace(' ','-',$new_file_name);
	
	if(move_uploaded_file($file_loc,$folder.$final_file))
	{
		// Write photo details to DB
		$sql="INSERT INTO photos(user_id,username,photo_file,photo_type,photo_size) VALUES('$_POST[user_id]','$_POST[username]','$final_file','$file_type','$new_size')";
		mysql_query($sql);
	?>


	<?php 

		// Generate URLs for activity feed
		//$profileurl = 'girl.php?' . $_POST[username] . '&uid=' . $_POST[user_id];
		//$imgurl = 'viewphoto.php?' . $_POST[username] . '&uid=' . $_POST[user_id] .'&img=' . $final_file;

		// Write activity details to DB
		$sql="INSERT INTO user_activity(user_id,user_name,activity_type,activity_meta,activity_relatedlink,activity_relatedid) VALUES('$_POST[user_id]','$_POST[username]','gallery-photo','$_POST[username] added a new photo to their gallery!','$final_file','$activity_relatedid')";
		mysql_query($sql);

	?>

and these are the changes I’ve been trying to make to capture and insert the id - it works but creates a duplicate record in the photos table;

<?php
include_once 'dbconfig.php';
if(isset($_POST['btn-upload']))
{    
     
	$file = rand(1000,100000)."-".$_FILES['file']['name'];
    $file_loc = $_FILES['file']['tmp_name'];
	$file_size = $_FILES['file']['size'];
	$file_type = $_FILES['file']['type'];
	$folder="gallery-photos/";
	
	// new file size in KB
	$new_size = $file_size/1024;  
	// new file size in KB
	
	// make file name in lower case
	$new_file_name = strtolower($file);
	// make file name in lower case
	
	$final_file=str_replace(' ','-',$new_file_name);
	
	if(move_uploaded_file($file_loc,$folder.$final_file))
	{
		// Write photo details to DB
		$sql="INSERT INTO photos(user_id,username,photo_file,photo_type,photo_size) VALUES('$_POST[user_id]','$_POST[username]','$final_file','$file_type','$new_size')";
		mysql_query($sql);


	if (mysqli_query($conn, $sql)) {
    $last_id = mysqli_insert_id($conn);
    //echo "New record created successfully. Last inserted ID is: " . $last_id;

    // Write activity details to DB
		$query="INSERT INTO user_activity(user_id,user_name,activity_type,activity_meta,activity_relatedlink,activity_relatedid) VALUES('$_POST[user_id]','$_POST[username]','gallery-photo','$_POST[username] added a new photo to their gallery!','$final_file','$last_id')";
		mysql_query($query);

} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

?>

for info my connection script (dbconfig.php) is;

<?php
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "";
$dbname = "lxv2";
mysql_connect($dbhost,$dbuser,$dbpass) or die('cannot connect to the server'); 
mysql_select_db($dbname) or die('database selection problem');

// Create connection
$conn = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
?>

So effectively, I have the code almost spot on but how can’t see what is creating the dublicate record in the first query.

2

you are aware that you are mixing mysql functions and mysqli functions and creating a hell of a confusion and a rich environment for bugs with that?

3

I wasn’t - is that what is causing the duplication issue?

4

might be. but I would rather eliminate the mysql functions ASAP since they are outdated, don’t support prepared statements, and are removed from PHP 7. (not to mention the implicit globals they create …)

5

Ok… so that is pretty much the whole upload script right?

6

there are mysql functions in both scripts.

7

In your other topic you are using PDO. How about you just stick to using that?
mysql isn’t even a thing anymore, you can use mysqli, but be consistent and stick to one API. PDO is best IMO.

8

OK… most of it is in PDO. The upload script was code I already had. Shouldn’t cut corners I guess :wonky:

9

I’ve reworked it into PDO… Posting here for for fullness.

New query - which now writes to both tables and is cleaner;

<?php
include_once '../includes/db.config.php';
if(isset($_POST['btn-upload']))
{    
     
	$file = rand(1000,100000)."-".$_FILES['file']['name'];
    $file_loc = $_FILES['file']['tmp_name'];
	$file_size = $_FILES['file']['size'];
	$file_type = $_FILES['file']['type'];
	$folder="gallery-photos/";
	
	// new file size in KB
	$new_size = $file_size/1024;  
	// new file size in KB
	
	// make file name in lower case
	$new_file_name = strtolower($file);
	// make file name in lower case
	
	$final_file=str_replace(' ','-',$new_file_name);
	
	if(move_uploaded_file($file_loc,$folder.$final_file))
	{


		$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

	    $sql = "INSERT INTO photos(user_id,username,photo_file,photo_type,photo_size)
	    VALUES('$_POST[user_id]','$_POST[username]','$final_file','$file_type','$new_size')";

	    // use exec() because no results are returned
	    $db->exec($sql);

	    $last_id = $db->lastInsertId();

	    //echo "New record created successfully. Last inserted ID is: " . $last_id;

	    $sql = "INSERT INTO user_activity(user_id,user_name,activity_type,activity_meta,activity_relatedlink,activity_relatedid)
	    VALUES('$_POST[user_id]','$_POST[username]','gallery-photo','$_POST[username] added a new photo to their gallery!','$final_file','$last_id')";

	    // use exec() because no results are returned
	    $db->exec($sql);
	    
	    }

?>

Connection code - again, reworked in PDO and cleaner - also means I’m now only maintaining one config file, not multiple;

<?php

// These variables define the connection information for your MySQL database 
    $username = "root"; 
    $password = ""; 
    $host = "localhost"; 
    $dbname = "lxv2";

    $options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8');
    $db = new PDO("mysql:host={$host};dbname={$dbname};charset=utf8", $username, $password, $options);
?>
10

You are aware that you’re wide open to SQL injection?

11

No. How would I fix that?

12

Use prepared statements for any user input.
Also a good idea to validate and sanitise the input too.

13

Like binding paramaters etc? Do you know a good resource that I can find how to do that?

14

That’s it, as seen in your other topic:-

A few here:-

https://www.sitepoint.com/re-introducing-pdo-the-right-way-to-access-databases-in-php/
https://phpdelusions.net/pdo

15

Thanks for the links.

Unfortunately, I didn’t develop the login system - it was a github fork, I am adding to it and trying to learn as I go to build a small web app.

16

just be prepared (for security) from the first step, no matter how big your project might get in the future. it’s really simple

$pdo = new PDO(...);
$stmt = $pdo->prepare('update foo set bar = ?');
$stmt->execute($_POST['bar']);

you may want to write a wrapper class that accepts the statement and the vars in one step, but you best prepare every input, no matter if it’s from you or your users.

17

Thanks for the advice.

Is this the preparing of every input - I’d do that for every input and these values will be inserted where the ? correct?

$stmt->execute($_POST[‘bar’]);

18

Correct but with one little correction, values have to be sent in the form of array, so it should be

$stmt->execute(array($_POST['bar']));

or, for a little more complex example,

$stmt = $pdo->prepare('update foo set bar = ? WHERE id = ?');
$stmt->execute(array($_POST['bar'], $_POST['id']));
19

And that is the same for insert and select queries too…

For example if i had multiple fields being posted would it be;

$stmt->execute(array($_POST['bar'], $_POST['value2'], $_POST['value3'], $_POST['id']));

etc…

20

Or, you could use named placeholders for the parameters, which I personally prefer.

$stmt = $pdo->prepare("update foo set bar = :value where id = :id");
$stmt->bindParam(":value", $_POST['bar']);
$stmt->bindParam(":id", $_POST['id']);
$stmt->execute();

I’m not saying it’s better, and I probably only prefer it because that’s the way I learned to do prepared statements.

2 Likes