CODEWITHSUNDEEP
phpmysqlsql
user2755541
user2755541

Reputation: 546

Using MAX() in mySQL query is giving problematic results

I'm trying to make multiple queries in order to find the most recent entry in a database by username.
Here's my code:

<?php  
    require_once("../includes/db_connection.php");  

    $userID = $_POST["userID"];
    $returnString = array();

    // Query the max id value of a given key_id (find the most recent upload)
    $query = "SELECT MAX(id) FROM photos WHERE key_id = {$userID}";
    $result = mysqli_query($connection, $query);
    //additional while loop could go here

    //now get the url where from the max id value that we just queried
    $query = "SELECT url FROM photos WHERE id = {$urlID}";
    $result = mysqli_query($connection, $query);

    $returnString['url'] = $urlID;

    mysqli_free_result($result);        
    echo json_encode($returnString);


?>

I think the problem lies in the first query. When I return the result from that, I get:

{"maxID": "current_field":null,"field_count":null,"lengths":null,"num_rows":null,"type":null}

When I create a while loop to capture the array (why I need to do this is beyond me because it will only ever return 1 value):

while($row = mysqli_fetch_assoc($result)) {$returnString[] = $row;}

Then I get this funky result: 

[{"MAX(id)":"30"}]

30 is the correct value, but then I don't know how to use that result in my next mySQL query.

**********UPDATE*************

The query :

SELECT url FROM photos WHERE id = (SELECT MAX(id) FROM photos WHERE key_id = {$userID});

Works perfectly when making the query from within mySQL, but doesn't work from my php script. It returns this weird string:

{"url":{"current_field":null,"field_count":null,"lengths":null,"num_rows":null,"type":null}}

Here's the updated script:

require_once("../includes/db_connection.php");  

$userID = $_POST["userID"];
$returnString = array();

$query = "SELECT url FROM photos WHERE id = (SELECT MAX(id) FROM photos WHERE key_id = {$userID})";
$result = mysqli_query($connection, $query);

mysqli_free_result($result);    

$returnString['url'] = $result; 
echo json_encode($returnString);

Upvotes: 0

Views: 147

Answers (3)

NickC217
NickC217

Reputation: 326

Updated answer: 

$query = "SELECT url FROM photos WHERE id = (SELECT MAX(id) FROM photos WHERE key_id = {$userID})";
$result = mysqli_query($connection, $query);
$row = mysqli_fetch_array($result):
$url = $row['url'];
echo json_encode($url);
mysqli_free_result($result);

Upvotes: 1

bitfiddler
bitfiddler

Reputation: 2115

I think the real problem is in the loop and use of an array for the results.

You should change to a single query like:

SELECT url, MAX(id) as id FROM photos WHERE key_id = {$userID}

MAX(id) as id returns the aggregate column name as id

You don't need to loop with a while if you are only expecting one row. Just change the while to if to test if any row is returned, and assign the values to single variables: 

$id = {$row['id']};
$url = {$row['url']};

The "funky" result is from trying to print the array which is not needed and has stored the column name and value.

Upvotes: 1

Ken
Ken

Reputation: 336

Unless I'm missing something in the schema that's not apparent from code and comments, you can save yourself a roundtrip by combining your SQL commands. 

$query = "SELECT id AS urlID, url FROM photos WHERE id = (SELECT MAX(id) FROM photos WHERE key_id = {$userID})";

Then interface with your results like you normally would.

Upvotes: 2

Related Questions