using a value of a field from a table inside a query

Question

I have a simple question. how can I use data stored in a field as a variable in a query?

in this example I want to use crypt and need the value inside password field of my database. how should I manage it.

$myusername=strtolower($myusername);
$query='SELECT * FROM auth '
      ."WHERE uname='$myusername' "
      ."and pword=crypt('$mypassword','pword')";
$result=  mysqli_query($connection, $query);
if(mysqli_num_rows($result)>0)......

pword and uname are my field names inside the auth table. this is my first script in PHP and SQL.

there should be a space after `auth` or before `WHERE` otherwise You will get `authWHERE` and syntax or missing table error. — Gustek, Jul 23 '13 at 02:53

Barmar · Answer 1 · 2013-07-23T02:48:00.880

2

If you want to refer to a field in the database, don't quote it:

$myusername = $connection->real_escape_string(strtolower($myusername));
$mypassword = $connection->real_escape_string($mypassword);
$query='SELECT * FROM auth'
      ."WHERE uname='$myusername'"
      ."and pword=crypt('$mypassword',pword)";

edited Jul 23 '13 at 02:48

answered Jul 23 '13 at 02:30

Barmar

741,623
53
500
612

Sorry, should have been `$connection`. – Barmar Jul 23 '13 at 02:48

score 1 · Answer 2 · edited May 23 '17 at 10:32

I suggest to use prepared statements instead:

$myusername = strtolower($myusername);

$sql = "SELECT * FROM auth HERE uname = ? and pword = crypt(?, 'pword')";

// prepare statement
$stmt = mysqli_prepare($connection, $sql);

if(!$stmt)die('can not prepare statement');

// supply parameter values:    
mysqli_stmt_bind_param($stmt, 'ss', $myusername, $mypassword);

// execute
if(!mysqli_stmt_execute($stmt))die('can not execute statement');

// if there are rows...
if(mysqli_stmt_num_rows($stmt)){

    // bind to variables
    mysqli_stmt_bind_result($stmt, $col1, $col2 /* , colN */);

    // output
    while(mysqli_stmt_fetch($stmt)) {
        var_dump($col1, $col2 /* , colN */);
    }

}

// free statement
mysqli_stmt_close($stmt);

I suggest to read further:

Erdo Dirgagautama · Answer 3 · 2013-07-23T03:38:50.900

Assuming $connection is your database connection :

$connection = new mysqli("localhost", "my_db_user", "my_db_password", "my_table");

And assuming you want to get field user_id and the crypted password :

$myusername = $connection->real_escape_string(strtolower($myusername));

$query="SELECT user_id, crypt('{$mypassword}','pword') AS my_password FROM `auth`
      WHERE uname='{$myusername}'
      and pword=crypt('{$mypassword}','pword')";
$result = $connection->query($query);

if ($result !== false && $result->num_rows > 0) {
    $row = $result->fetch_object();
    $myUserID = $row->user_id;
    $myPassword = $row->my_password;
}

Notes :

Enclose PHP variables inside string with {} for better practices.
You could enclosed query string within a pair of "" eventhough those string take more than one rows.

Hopefully this help.

using a value of a field from a table inside a query

3 Answers3