How to set result SQL distinct query to one or different variables?

Question

I'm creating a mobile library app, and for one function of the app I am trying to receive the bookID for all books checked out by a certain user. I would like to be able to echo back the results from the query in a string format (preferably with spaces in between each separate book id) so I can deal with the data later on within the app.

Many of the answers I have found online have simply shown how to execute the query, but not how to use the data afterwards. Sorry if this is a simple question to answer, I am a huge novice.

<?php
require "conn.php";

$email = $_POST["email"];
$mysql_qry = "SELECT * FROM user_data WHERE email like '$email'";

$mysql_qry2 = "SELECT DISTINCT(bookID) AS bookID FROM books_checked_out 
WHERE userID LIKE $user_id ORDER BY bookID DESC";

$result = mysqli_query($conn, $mysql_qry);

if(mysqli_num_rows($result) > 0) {
    $row = mysqli_fetch_assoc($result);
    $user_id = $row["user_id"];
    $result2 = mysqli_query($conn, $mysqlqry2); 
}
else
{
    echo "Error, user name not found";
}

$conn->close;
?>

Answer 1

You could append your results into an array and display values using implode():

<?php
require "conn.php";
$email = $_POST["email"]; // You may test here : if (isset($_POST['email']))
$mysql_qry = "SELECT * FROM user_data WHERE email = '$email'";
$result = mysqli_query($conn, $mysql_qry);
if(mysqli_num_rows($result) > 0)
{
    $row = mysqli_fetch_assoc($result);
    $user_id = $row["user_id"];

    $mysql_qry2 = "SELECT DISTINCT(bookID) AS bookID FROM books_checked_out
                   WHERE userID = $user_id ORDER BY bookID DESC";

    $result2 = mysqli_query($conn, $mysql_qry2);
    if(mysqli_num_rows($result2) > 0)
    {
        $ids = [];
        while ($row = mysqli_fetch_assoc($result2)) {
            $ids[] = $row['bookID'] ;
        }
        echo implode(" ", $ids) ; // print list of ID
    }
    else
    {
        echo "No books checked out!";
    }
}
else
{
    echo "Error, user name not found";
}
$conn->close;

NB: I used your code here, but, you should have to look to parameterized queries to prevent SQL injections.

1. Your query $mysql_qry2 should be defined after to get $user_id.

2. Your LIKE $user_id could be replaced by =.

Answer 2

First thing first, always sanitize your data:

$email = filter_var( $_POST['email'], FILTER_SANITIZE_EMAIL );
$user_id = preg_replace( "#[0-9]#", '', $row['user_id'] );

Use

DISTINCT bookID instead of DISTINCT(bookID)

From your query: $mysql_qry2 = "SELECT DISTINCT(bookID) AS bookID FROM books_checked_out WHERE userID LIKE $user_id ORDER BY bookID DESC";

If you're not getting any result or the returned result is empty but the user_id does exist, then I think the query format is wrong.

What you should do instead

1. Change the ORDER BY: The query may be correct but mysql returned an empty result because the result order does not match.

Try this

"SELECT DISTINCT bookID AS bookID FROM books_checked_out WHERE userID LIKE $user_id ORDER BY userID DESC";

"SELECT DISTINCT bookID AS bookID FROM books_checked_out WHERE userID LIKE $user_id ORDER BY `primary_key_here` DESC";

Replace <strong>`primary_key_here`</strong> with the primary key name.

Run the query without conditionals and inspect the result

$query = mysqli_query( $conn, "SELECT bookID FROM books_checked_out DESC" );

var_dump( $query );

Use the result to inspect the rest of the query.

Answer 3

Rather than using your own protocol/format use something like JSON or xml in your response to the request.

This will give you better maintainability in the long run and allow you to easily handle the response in the browser with javascript, and most browsers will give you a nice display of JSON objects in the dev console.

You'll have to extract the user id from the result of the first query or you could do a joined query instead.

$email = validate($POST['email']); //where validate() will try to prevent sql injection 
//joined query
$query = 
  " SELECT bookID FROM user_data 
    INNER JOIN books_checked_out on user_data.user_id = books_checked_out.userID
    WHERE user_data.email='$email'
  ";
//not sure whether that should be user_id or userID looks like you have mixed conventions
//books_checked_out.userID vs user_data.user_id ... check your database column names

//loop through results
// may be empty if user email doesn't exist or has nothing checked out
$result = $conn->query($query);
while($row = $result->fetch_assoc()){
    $response[] = ['bookID'=>$row['bookID']];
}
echo json_encode($response);

When receiving the result in php you can use json_decode() or in javascript/ajax it will automatically be available in your result variable.

if things aren't working as expected it can be a good idea to echo the actual sql. In this case
echo 'SQL IS: '.$query;
and test it against your database directly (phpmyadmin/MySQL-Workbench) to see if you get any results or errors.