CODEWITHSUNDEEP
phpmysqlselect
aborted
aborted

Reputation: 4541

MySQL - Select differently

I have this query, which selects a distinct value for a column, but I need something else in that query too. I need it to fetch a different row associated with the main select.

Let me illustrate...

This is my query:

$sql = 'SELECT DISTINCT user_id FROM ' . DONATION_SECURITY_TABLE;
$result = mysql_query($sql);

$rows = mysql_fetch_assoc($result);
mysql_free_result($result);

return $rows;

As you see it returns this query returns the DISTINCT of user_id.

My table

If I use a function like this in a double foreach loop created using the return of the query above:

public function get_donor_status($user_id)
{
    global $db;

    $sql = 'SELECT payment_status FROM ' .DONATION_SECURITY_TABLE .
           " WHERE user_id = '" . (int) $user_id . "'";
    $result = $db->sql_query($sql);

    $row = $db->sql_fetchrow($result);
    $payment_status = $row['payment_status'];

    $db->sql_freeresult($result);

    return $payment_status;
}

This function would return Completed for user_id 2, but I want it to say Pending instead. How would I change my query so it returns the last value for the corresponding user_id?

If I'm not clear enough, please let me know so I can reexplain.

Upvotes: 2

Views: 77

Answers (3)

Aleks G
Aleks G

Reputation: 57316

You can actually get everything in one SQL statement, no need for a double loop:

SELECT
    user_id, payment_status
FROM
    DONATION_SECURITY_TABLE t1
WHERE
    donation_id = (select max(donation_id) from DONATION_SECURITY_TABLE t2 WHERE t2.user_id = t1.user_id)
ORDER BY
    user_id

Upvotes: 0

Gogol
Gogol

Reputation: 3072

How about this?

$sql = 'SELECT payment_status FROM ' .DONATION_SECURITY_TABLE .
           " WHERE user_id = '" . (int) $user_id . "' ORDER BY donation_id DESC LIMIT 1";

Upvotes: 0

noj
noj

Reputation: 6759

Just select the last row for the user:

"SELECT payment_status FROM " . DONATION_SECURITY_TABLE . " WHERE user_id = '" . (int) $user_id . "' ORDER BY donation_id DESC LIMIT 1"

Upvotes: 5

Related Questions