Get integer value from MySQL database

Question

Basically I have a MySQL database and a table named users in which there is a column named credits. How do I get that integer value for the currently logged in user?

My columns are:

user_id
username
email
password
role
credits

Where are you getting the information for the currently logged in user, a session? And what do you have for them, the user_id, username?? — j08691, May 05 '12 at 17:22
the currently logged in user is assigned to the user_id via a session — kilianvde, May 05 '12 at 18:41

score 1 · Answer 1 · edited May 23 '17 at 12:20

I assume you'll first want to authenticate your user. You can use any number of available libraries for that or roll your own. Here are a few places to start:

Once you have the user_id of the user who is authenticated you can build a very simple MySQL query to extract the credits:

// Connect to the database
$connection = new mysqli("localhost", "mysql_user", "mysql_password", "database");
if (mysqli_connect_errno())
{
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

if ($statement = $mysqli->prepare("SELECT credits FROM user WHERE user_id=?"))
{
    // $user_id is the stored value
    $statement->bind_param("i", $user_id);
    $statement->execute();
    $statement->bind_result($credits);
    $statement->fetch();

    echo "User has " . $credits . "credits<br/>";

    $statement->close();
}

/* close connection */
$connection ->close();

Code mostly copy/pasted from http://www.php.net/manual/en/mysqli.prepare.php

score 0 · Answer 2 · answered May 05 '12 at 17:25

0

if you know user_id, username or email of currently logged in user your SQL-queries would be:

"SELECT `credits` FROM `users` WHERE `user_id` = '$user_id'"
"SELECT `credits` FROM `users` WHERE `username` = '$username'"
"SELECT `credits` FROM `users` WHERE `email` = '$email'"

I assumed your users-table named "users"

answered May 05 '12 at 17:25

s.webbandit

16,332
16
58
82

Beware [SQL Injection](http://xkcd.com/327)! – Jonathan Leffler May 05 '12 at 21:17
Yah sure you shuld pay much attention to secure your queries from SQL injections here – s.webbandit May 06 '12 at 05:12

score 0 · Answer 3 · answered May 05 '12 at 17:31

0

Not quite sure about this, since I usually work with PDO, but I guess it should be fine for the beginning

<?php
$db = mysql_connect("localhost", "user", "password");

$sql = "SELECT credits FROM users WHERE user_id = " . mysql_real_escape_string($_SESSION['userid']);

$result = mysql_query($sql);

$row = mysql_fetch_object($result);

$credits = $row->credits;

echo $credits;

answered May 05 '12 at 17:31

Ahatius

4,777
11
49
79

2

no need for `mysql_real_escape_string()` since `user_id` is an integer and has no `'` at the beginning, and you should always use this `if ($result && mysql_num_rows($result) > 0) { ... }` to make sure that there is a result. – Ahmed Jolani May 05 '12 at 17:34
@AhmedJolani Thanks for the info. As I was saying, I usually work with PostGres and PDO, I've never really used these quick & dirty methods of connecting to a db. – Ahatius May 05 '12 at 17:35
@user1377123 You can use session if you want to store credits of logged in user and use to another pages of application. – Dhruvisha May 05 '12 at 17:35
When i echo out the $credits variable it always just says $credits and not the amount, i have a slightly other code – kilianvde May 05 '12 at 19:22
@AhmedJolani: unless you've ensured that the value entered by the user actually was a pure integer, it is more sensible to use `mysql_real_escape_string()`. Or you clean the variable with `$intvar = $_SESSION['userid'] + 0;` or some similar trick that forces a numeric value. If it came from a user (or, perhaps, a script masquerading as a user), then you assume the data is contaminated and dangerous until you've proved otherwise. See [SQL Injection](http://xkcd.org/327). – Jonathan Leffler May 05 '12 at 21:20

Get integer value from MySQL database

3 Answers3