INSERTING values from one table into another table

Question

I have this code to select all the fields from the 'jobseeker' table and with it it's supposed to update the 'user' table by setting the userType to 'admin' where the userID = $userID (this userID is of a user in my database). The statement is then supposed to INSERT these values form the 'jobseeker' table into the 'admin' table and then delete that user from the 'jobseeker table. The sql tables are fine and my statements are changing the userType to admin and taking the user from the 'jobseeker' table...however, when I go into the database (via phpmyadmin) the admin has been added by none of the details have. Please can anyone shed any light onto this to why the $userData is not passing the user's details from 'jobseeker' table and inserting them into 'admin' table?

Here is the code:

<?php

include ('../database_conn.php');

$userID = $_GET['userID'];

$query = "SELECT * FROM jobseeker WHERE userID = '$userID'";
$result = mysql_query($query);
$userData = mysql_fetch_array ($result, MYSQL_ASSOC);
$forename = $userData ['forename'];
$surname = $userData ['surname'];
$salt = $userData ['salt'];
$password = $userData ['password'];
$profilePicture = $userData ['profilePicture'];

$sQuery = "UPDATE user SET userType = 'admin' WHERE userID = '$userID'";

$rQuery = "INSERT INTO admin (userID, forename, surname, salt, password, profilePicture) VALUES ('$userID', '$forename', '$surname', '$salt', '$password', '$profilePicture')";

$pQuery = "DELETE FROM jobseeker WHERE userID = '$userID'";


mysql_query($sQuery) or die (mysql_error());
$queryresult = mysql_query($sQuery) or die(mysql_error());


mysql_query($rQuery) or die (mysql_error());
$queryresult = mysql_query($rQuery) or die(mysql_error());

mysql_query($pQuery) or die (mysql_error());
$queryresult = mysql_query($pQuery) or die(mysql_error());


mysql_close($conn);


header ('location:     http://www.numyspace.co.uk/~unn_v002018/webCaseProject/index.php');

?>

Answer 1

Firstly, never use SELECT * in some code: it will bite you (or whoever has to maintain this application) if the table structure changes (never say never).

You could consider using an INSERT that takes its values from a SELECT directly:

"INSERT INTO admin(userID, forename, ..., `password`, ...)
    SELECT userID, forename, ..., `password`, ...
    FROM jobseeker WHERE userID = ..."

You don't have to go via PHP to do this.

(Apologies for using an example above that relied on mysql_real_escape_string in an earlier version of this answer. Using mysql_real_escape_string is not a good idea, although it's probably marginally better than putting the parameter directly into the query string.)

I'm not sure which MySQL engine you're using, but your should consider doing those statements within a single transaction too (you would need InnoDB instead of MyISAM).

In addition, I would suggest using mysqli and prepared statements to be able to bind parameters: this is a much cleaner way not to have to escape the input values (so as to avoid SQL injection attacks).

EDIT 2:

(You might want to turn off the magic quotes if they're on.)

$userID = $_GET['userID'];

// Put the right connection parameters
$mysqli = new mysqli("localhost", "user", "password", "db");

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

// Use InnoDB for your MySQL DB for this, not MyISAM.
$mysqli->autocommit(FALSE);

$query = "INSERT INTO admin(`userID`, `forename`, `surname`, `salt`, `password`, `profilePicture`)"
    ." SELECT `userID`, `forename`, `surname`, `salt`, `password`, `profilePicture` "
    ." FROM jobseeker WHERE userID=?";

if ($stmt = $mysqli->prepare($query)) {
    $stmt->bind_param('i', (int) $userID);
    $stmt->execute();
    $stmt->close();
} else {
    die($mysqli->error);
}

$query = "UPDATE user SET userType = 'admin' WHERE userID=?";

if ($stmt = $mysqli->prepare($query)) {
    $stmt->bind_param('i', (int) $userID);
    $stmt->execute();
    $stmt->close();
} else {
    die($mysqli->error);
}

$query = "DELETE FROM jobseeker WHERE userID=?";

if ($stmt = $mysqli->prepare($query)) {
    $stmt->bind_param('i', (int) $userID);
    $stmt->execute();
    $stmt->close();
} else {
    die($mysqli->error);
}

$mysqli->commit();

$mysqli->close();

EDIT 3: I hadn't realised your userID was an int (but that's probably what it is since you've said it's auto-incremented in a comment): cast it to an int and/or don't use it as a string (i.e. with quotes) in WHERE userID = '$userID' (but again, don't ever insert your variable directly in a query, whether read from the DB or a request parameter).

Answer 2

There's nothing obviously wrong with your code (apart from it being insecure with using non-escaped values directly from $_GET).

I'd suggest you try the following in order to debug:

1. var_dump $userData to check that the values are as you expect
2. var_dump $rQuery and copy and paste it into phpMyAdmin to see if your query is not as you expect

If you don't find your problem then please post back your findings along with the structure of the tables you're dealing with