PHP/MySQL: After inserting the value into a table the value of the variable gets lost and cannot print it out at the end of the file?

Question

I want to do more than one database queries in the same file:

Create a user, select the UID of that newly created user, and assign to that same user a specific role.

After I get the UID from the newly created user I save that value into the $userID variable, but at the end of the file, the variable value gets lost.

Why? (PS: I'm not taking into account security at the moment).

//Create User
$email = strip_tags($_POST['email']);
$conectar = mysqli_connect(HOST, USER, PASS, DATABASE);
$query = "INSERT INTO usuarios 
            (userEmail) 
          VALUES 
            ('$email')";
$insertarBase = mysqli_query($conectar,$query);
mysqli_close($conectar);

//look for the UID of the newly created user
$conectar2 = mysqli_connect(HOST, USER, PASS, DATABASE);
$buscarUsuario = "SELECT userID, userEmail 
                  FROM usuarios 
                  WHERE userEmail='$email'                  
                    ";
$resultadoBusqueda = mysqli_query($conectar2,$buscarUsuario);   
    $row = mysqli_fetch_array($resultadoBusqueda);
    $userID = $row['userID'];
mysqli_close($conectar2);

//assign a role to the newly created user
$conectar3 = mysqli_connect(HOST, USER, PASS, DATABASE);
$asignarRol = "INSERT INTO rolesUsuarios 
            (userID, nombreRol) 
          VALUES 
            ('$userID', 'registered')
          ";
$asignarRolenBase = mysqli_query($conectar3,$asignarRol);
mysqli_close($conectar3);

echo $userID; //Here the content of $userID is gone, nothing gets printed out

Edited:

For some weird reason, $userID = mysqli_insert_id($conectar); returns zero. The creation of the usuarios table statement is this:

CREATE TABLE usuarios(
    userID int unsigned not null auto_increment primary key,
    userEmail char(50) not null);

Also, echo $asignarRol; returns:

INSERT INTO rolesUsuarios (userID, nombreRol) VALUES ('0', 'noAutorizado')

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! [Don't believe it?](http://stackoverflow.com/q/38297105/1011527) — Jay Blanchard, Jul 25 '16 at 21:25
`strip_tags` does nothing to stop a SQL injection. Also why close and open to connection multiple times? — chris85, Jul 25 '16 at 21:28
you're simply ASSUMING nothing ever goes wrong. even if the sql is 100% syntactically perfectly, there's any number of OTHER reasons for it to fail. Never **EVER** assume success with a db operation. always assume failure, check for failure, and treat success as a pleasant surprise. Plus, why do you continually re-connect to the database. You're not going anything that would require multiple connections. — Marc B, Jul 25 '16 at 21:28
Please insert "echo $asignarRol" and edit your question to show what is output. — Ben Shoval, Jul 25 '16 at 21:28
Have you checked your error logs? You're making an assumption the query is working. Add error reporting to the top of your file(s) right after your opening `))` to your queries. Or you can find the issues in your current error logs. — Jay Blanchard, Jul 25 '16 at 21:29
As a side note, there's no need to query the database to get the ID of the record you just inserted. Use mysqli_insert_id http://php.net/manual/en/mysqli.insert-id.php — rjdown, Jul 25 '16 at 21:29

score 1 · Answer 1 · answered Jul 25 '16 at 21:49

i tried to tidy up your code and delete superfluous code.

//Create User
$email = $_POST['email']; // you have to verify if this is an email or html etc.
$conectar = new mysqli(HOST, USER, PASS, DATABASE);

$query = "INSERT INTO usuarios 
        (userEmail) 
      VALUES 
        (?)";
$stmt = $conectar->prepare($query);
$stmt->bind_param('s',$email);
$stmt->execute();
$userID = $stmt->insert_id;

$stmt->close();//close statement

//assign a role to the newly created user

$query = "INSERT INTO rolesUsuarios 
        (userID, nombreRol) 
      VALUES 
        (?, 'registered')";
$stmt = $conectar->prepare($query);
$stmt->bind_param('i',$userID);
$stmt->execute();
$stmt->close();
$conectar->close();
echo $userID; //Here the content of $userID

First of all , you don't have to create a new db-connection for each statement. Second: please prepare your statements - for security purposes.

If $userID is empty, make an error_log($userID); after you $userID gets it value, if it's empty , there might be something else wrong.

score 1 · Answer 2 · answered Jul 25 '16 at 21:50

First as other said to you use prepared statement for SQL injection and second the SQL connection not need to repeat so many time. Too many code and select not need please check the follow.

<?php
$conn = new mysqli(HOST, USER, PASS, DBNAME);

$insert_usuarios = $conn->prepare(" INSERT INTO usuarios ( userEmail ) VALUES ( ? ) ");
$insert_usuarios->bind_param( "s", $userEmail);

$insert_rolesUsuarios = $conn->prepare(" INSERT INTO rolesUsuarios ( userID, nombreRol ) VALUES ( ?, ? ) ");
$insert_rolesUsuarios->bind_param( "is", $userID, $nombreRol);

if(isset($_POST['email'])) {
    $userEmail = $_POST['email'];
    if (!$insert_usuarios->execute()) { // ERROR
        echo('Error'); // OR ACTION THAT YOU LIKE
    } else { // SUCCESS
        $userID = $insert_usuarios->insert_id; // LAST ID INSERT
        $nombreRol = 'REGISTERED';

        if (!$insert_rolesUsuarios->execute()) { // ERROR
            echo('Error'); // OR ACTION THAT YOU LIKE
        } else { // SUCCESS
            echo('Done!');
        }

    }
}

?>

Cheers!!!

spirit · Answer 3 · 2016-07-26T09:50:22.063

1

Yet another cleanup of your code, following your code style and convention =)

//Create User
$conectar = mysqli_connect(HOST, USER, PASS, DATABASE);

$email = strip_tags($_POST['email']);

$query = 'INSERT INTO usuarios (userEmail) VALUES (?)';
$stmt = mysqli_prepare($conectar, $query);
mysqli_stmt_bind_param($stmt, 's', $email);

mysqli_stmt_execute($stmt); //execute query
$userID = mysqli_insert_id($conectar);

//assign a role to the newly created user
$query = "INSERT INTO rolesUsuarios (userID, nombreRol) VALUES (?, 'registered')";
$stmt = mysqli_prepare($conectar, $query);
mysqli_stmt_bind_param($stmt, 's', $userID);
mysqli_stmt_execute($stmt); //execute query

var_dump($userID);

tell me, what did you will get in the end?

edited Jul 26 '16 at 09:50

answered Jul 25 '16 at 21:53

spirit

3,265
2
14
29

Thanks for your help! mysqli_insert_id() return zero for some reason... About the expected result, it should be the user saved in the database with his email and his role (defined in another table). – Rosamunda Jul 25 '16 at 23:05
@Rosamunda, show me your `CREATE TABLE ...` statement (in your question) of table `usuarios` – spirit Jul 25 '16 at 23:11
@Rosamunda, I've missed one thing. Fixed it now. Can you try? – spirit Jul 26 '16 at 09:19
Tested with `5.3.29` (fcgi) and `5.6.22` (cli) works smoothly. MySQL - 5.6. If it will not work for you, than pls, show your new code. – spirit Jul 26 '16 at 09:56
@Rosamunda, are you there? – spirit Jul 26 '16 at 19:52

PHP/MySQL: After inserting the value into a table the value of the variable gets lost and cannot print it out at the end of the file?

Edited:

3 Answers3