Using mySql to retrieve a certain value based on a condition

Question

I'm currently doing a computing project which requires me to use an id(which is a primary key in one table) as a secondary key for another table. I want to use sessions to keep track of the variable using commands like this

$id=$_SESSION['id'];

However I'm currently having the problem of getting id from the table it's the primary key in. This is the mysql statement I'm using.

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);}
$sql='INSERT INTO user(email,pass) VALUES ("'.$email.'","'.$pass.'")';
if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}
$sql='SELECT id FROM user WHERE user(email)="'.$email'"'

I've done a bit of looking but haven't found any examples of how to do this. The information is being sent to the database and id's are being generated and I will add validation once it all works. However I can't retrieve the specific id's from the database. Also is there a specific variable I have to use (like $result) or do I just do $id=$sql after the query is completed?

[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)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Jan 04 '17 at 18:58
**Never store plain text passwords!** Please use PHP's [built-in functions](http://jayblanchard.net/proper_password_hashing_with_PHP.html) to handle password security. If you're using a PHP version less than 5.5 you can use the `password_hash()` [compatibility pack](https://github.com/ircmaxell/password_compat). Make sure you ***[don't escape passwords](http://stackoverflow.com/q/36628418/1011527)*** or use any other cleansing mechanism on them before hashing. Doing so *changes* the password and causes unnecessary additional coding. — Jay Blanchard, Jan 04 '17 at 18:58

score 1 · Answer 1 · answered Jan 04 '17 at 18:17

Instead of running another query, the mysqli provides a way to get the primary key from the last insert using a property of the connection called insert_id like this

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$sql = "INSERT INTO user(email,pass) VALUES('$email','$pass'");
if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";

    // get the id
    $lastId = $conn->insert_id;
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

score 0 · Answer 2 · answered Jan 04 '17 at 18:15

0

Try this :

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);}
$sql='INSERT INTO user(email,pass) VALUES ("'.$email.'","'.$pass.'")';
if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$lastId = mysqli_insert_id($con);

answered Jan 04 '17 at 18:15

prakash tank

1,269
1
9
15

Do or do not. There is no "try". A ***good answer*** will always have an explanation of what was done and why it was done in such a manner, not only for the OP but for future visitors to SO. – Jay Blanchard Jan 04 '17 at 18:58
@JayBlanchard : Yes you are right sir.. keep that in mind :) thanks for the suggestion. – prakash tank Jan 04 '17 at 19:01

Using mySql to retrieve a certain value based on a condition

2 Answers2