Assigning PHP variable to result of SQL

Question

This is an elementary question, I know, however I can't seem to crack it with my code.

I have my login session username assigned to $username - which inserts perfectly, however, I want $id to be assigned the user ID which is located in the 'users' table.

I currently have 2 tables, users and trips.

Users has the following columns:

id (PK,AI), 
username, 
email, 
password, 
trn_date

Trips has the following columns:

id (PK,AI), 
user (FK_users.ID), 
name, 
from, 
to, date, space, email, telephone, comments

Here is my code:

<?php
session_start();
include("auth.php"); 
include("db.php"); 
?>              
<?php

// SERVER AND DATABASE DETAILS --- WORKING
$servername = "<redacted>";
$username1 = "<redacted>";
$password = "<redacted>";
$dbname = "<redacted>";



//sets session variable username --- WORKING
$username = $_SESSION['username'];

// sets variable to result of sql to determine user_id - NOT WORKING
$id = mysqli_query("SELECT id from `users` where username = '$username'");


// Create connection --- WORKING
$conn = new mysqli($servername, $username1, $password, $dbname);

// Check connection --- WORKING
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
 // Insert script when adding trip --- WORKING
$sql = "INSERT INTO `trips` (`user`,`name`, `from`, `to`, 
`date`, `space`, `email`, `telephone`, `comments`) VALUES   ('$id','$username','".$_POST["from"]."','".$_POST["to"]."','".$_POST["datetime"]."','".$_POST["space"]."','".$_POST["email"]."','".$_POST["telephone"]."','".$_POST["comments"]."')";

// Alert pop-up confirming when insert is successful --- WORKING
if ($conn->query($sql) === TRUE) {
   echo '<script type="text/javascript">'; 
    echo 'alert("Your trip was successfully added!");'; 
    echo 'window.location.href = "/index.php";';
    echo '</script>';

    // Error when sql insert fails
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
    }

$conn->close();
?>

The first parameter to `mysqli_query()` is the connection to the database, which is the next line in your code. — Nigel Ren, Jun 04 '18 at 14:57
In such cases, you may use var_dump($id) in order to inspect your function return results and make sure which part of the code is correct, and which is not. That will help you discover the root issue yourself as well. — Glorious Kale, Jun 04 '18 at 14:59
@IvanIvković - var_dump returns NULL, which seems impossible. The user.ID column has values — Dez-Luan Pieterse, Jun 04 '18 at 15:21
Your code is vulnerable to sql injections. Please check https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 And try to use prepared statements when work with mysqli. — Alex, Jun 04 '18 at 15:31
@Dez-LuanPieterse because the value is null, this means you did not get the result with your function. How do you extract rows from a query with PHP? — Glorious Kale, Jun 04 '18 at 17:29

Alex · Accepted Answer · 2018-06-04T15:45:05.533

replace this line

$id = mysqli_query("SELECT id from `users` where username = '$username'");

with this

$conn = new mysqli($servername, $username1, $password, $dbname); //since I am not sure where did you initiate the mysqli connection
$res = mysqli_query($conn, "SELECT id from `users` where username = '$username'");
$id = mysqli_fetch_assoc ( $res ) ['id'] ;

I guess that will solve you problem.

mysqli_query function returns not the value you expect but mysqli result http://php.net/manual/en/mysqli.query.php

So you need to fetch results from that object first and then extract the value from the result.

In my example I use mysqli_fetch_assoc to fetch data as associative array and then ['id'] to get particular column value.

Assigning PHP variable to result of SQL

1 Answers1