How to get the user_id from mysql using procedural php?

Question

I am an android/java developer and Im struggling with php. I've made it as far as inserting a new user in the DB, now I want to get their ID.

What do I do with the result? I want to assign it to a variable.

$query = "SELECT user_id FROM users WHERE user_email = '" . $user_email . "'";
$result =

PS: Im using mysql, not mysqli.

EDIT: Here is what I did:

$query = "SELECT user_id FROM users WHERE user_email = '" . $user_email ."';";
$store_info = mysql_fetch_array(mysql_query($query)); 
$user_id = $store_info['user_id'];
$response["message"] = "User created with id: " . $user_id;
echo json_encode($response);

And the error message after inserting (successfully) the user in the db:

null{"success":3,"message":"User created with id: "}

[Please, stop using mysql_* functions](http://stackoverflow.com/q/12859942/1238019) in new code, they are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Instead of, have a look on [prepared statements](http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html), and use [Mysqli](http://php.net/manual/en/book.mysqli.php) or [PDO](http://php.net/manual/en/book.pdo.php). — zessx, Oct 31 '13 at 08:21
I just wanted to get my api up and running quickly. When I do that I will start learning php and mysqli and pdo. Just wanted quick results so I can motivate myself — Kaloyan Roussev, Oct 31 '13 at 08:24
@J.Kowalski So, you got an empty result from the database. Does user with this email exist in the database? Can you show us the dump? — user4035, Oct 31 '13 at 08:27
INSERT INTO `users` (`user_id`, `user_name`, `user_email`, `user_password`, `user_sex`, `user_avatar`, `user_date_registered`, `user_last_seen`, `user_points`) VALUES (18, 'asd', 'asd2e', 'kirobr', '', '', '2013-10-31 08:22:12', '0000-00-00 00:00:00', 0); — Kaloyan Roussev, Oct 31 '13 at 08:29
well I just inserted some random symbols (the debugging form has no validation) for testing — Kaloyan Roussev, Oct 31 '13 at 08:34

score 6 · Accepted Answer · edited May 23 '17 at 10:25

6

I assume that your are using MySQLi API

$query = "SELECT user_id FROM users WHERE user_email = '$user_email'"; //Your Query

$store_info = mysqli_fetch_array(mysqli_query($connection, $query)); 
//Execute the query, fetch the result, it's just one result so no need for a while loop

echo $store_info['user_id']; //echo id

As per the comments, you requested a mysql_() version so here you go...

$query = "SELECT user_id FROM users WHERE user_email = '$user_email'"; //Your Query

$store_info = mysql_fetch_array(mysql_query($query)); 
//Execute the query, fetch the result, it's just one result so no need for a while loop

echo $store_info['user_id']; //echo id

Still consider using mysqli_() or PDO instead. Why? Because mysql_() is now deprecated, read the red box on the documentation page which says...

enter image description here

Refer this answer for PDO tutorial

edited May 23 '17 at 10:25

Community

1
1

answered Oct 31 '13 at 07:53

Mr. Alien

153,751
34
298
278

@J.Kowalski Remove the `i` from function and connection variable, rest stays the same, but I would like to suggest you to use mysqli or pdo – Mr. Alien Oct 31 '13 at 07:55
@J.Kowalski Better to start of with mysqli as mysql is deprecated. – StBlade Oct 31 '13 at 07:55
1

Added quotes around `$user_email` to fix the syntax typo – Hanky Panky Oct 31 '13 at 07:56
1

@J.Kowalski Did you used my query? Your query has `= =` – Mr. Alien Oct 31 '13 at 08:02
fixed that and now Im getting user ID = FALSE – Kaloyan Roussev Oct 31 '13 at 08:04
1

I guess you are not paying attention to the answer. This answer is different from what you `tried` in your edit. – Hanky Panky Oct 31 '13 at 08:05
but I dont have mysqli installed or whatever is needed for it to be used. And I've setup my connection via mysql not mysqli. I really dont know php :( – Kaloyan Roussev Oct 31 '13 at 08:06
@J.Kowalski Also, you need to check the id you are using to fetch the row, also this is wrong again `$response["user_id"]` – Mr. Alien Oct 31 '13 at 08:07
@J.Kowalski Just remove the `i` from the functions and remove `$connection, ` part, yes the space after the comma too, so it will be `mysqli_query($query)` – Mr. Alien Oct 31 '13 at 08:08
@Mr.Alien since He is cofused about MySQL and MySQLi, I think it will make your answer even better if you add an example for him using `mysql_*` as well – Hanky Panky Oct 31 '13 at 08:10
@Hanky웃Panky actually I was ignoring, that will just spread the disease, I guess I need to add it now.. – Mr. Alien Oct 31 '13 at 08:10

score 3 · Answer 2 · answered Oct 31 '13 at 07:59

3

Here is the PDO variant:

<?php
//credentials
$host = 'localhost';
$user = "user";
$password = '';
$db_name = 'test';
$port = 3306;

//connection to the database
try
{
    $connection = new PDO("mysql:host=$host;port=$port;dbname=$db_name", $user, $password);
    $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch (PDOException $e)
{
    echo 'Connection failed: ' . $e->getMessage();
}

//prepare and execute SELECT statement
$sth = $connection->prepare("SELECT user_id FROM users WHERE user_email = :email");
$sth->execute(array(':email' => $user_email));

$record = $sth->fetch(PDO::FETCH_ASSOC);
print $record["user_id"];

answered Oct 31 '13 at 07:59

user4035

22,508
11
59
94

do I need to install something in order to use PDO? – Kaloyan Roussev Oct 31 '13 at 08:13
@J.Kowalski Usually this library is shipped together with PHP. Does my code run for you? – user4035 Oct 31 '13 at 08:23
@J.Kowalski Look for extension=pdo.so in php.ini – user4035 Oct 31 '13 at 08:25

score 0 · Answer 3 · answered Oct 31 '13 at 08:00

0

If you use mysql (but you shouldn't, it's deprecated) :

$result = mysql_query("SELECT user_id FROM users WHERE user_email = '$user_email'");
$row = mysql_fetch_row($result);

echo $row[0]; // you result (id)

answered Oct 31 '13 at 08:00

onionpsy

1,502
11
15

score 0 · Answer 4 · answered Oct 31 '13 at 08:03

Connection:

define("HOST","localhost");
define("USER","mysql_username");
define("PASS","password");

$conn = mysql_connect(HOST,USER,PASS) or die("<h3>Sorry, could not connect to MySQL. Please Try Again</h3>");
$db = mysql_select_db(DBNAME,$conn) or die("<h3>Sorry, could not connect to Database. Please Try Again</h3>")

Query:

$query = "SELECT user_id FROM users WHERE user_email = = '" . $user_email . "'";
$result = mysql_query($query);
$row=mysql_fetch_assoc($result);

user4035 · Answer 5 · 2013-10-31T08:05:48.620

0

Your error comes because of the error in SQL query: you used = operator twice:

$query = "SELECT user_id FROM users WHERE user_email = = '" . $user_email . "'";

Must be:

$query = "SELECT user_id FROM users WHERE user_email = '" . $user_email . "'";

edited Oct 31 '13 at 08:05

answered Oct 31 '13 at 08:04

user4035

22,508
11
59
94

How to get the user_id from mysql using procedural php?

5 Answers5