Random Output In a Query

You can randomize your output in a query using the ORDER BY rand()

Example:

SELECT DISTINCT a.f_user_id, c.f_username,
...
AND b.f_bid_transaction_id is not null
ORDER BY rand()
LIMIT 30

WHERE NOT EXISTS

In this godly world of MySQL, there’s exist this thing called WHERE NOT EXISTS who helped the stupid ‘ol me to select the things that weren’t there. Literally. This, all thanks to my guru of a colleague, Drew.

Example:
SELECT DISTINCT a.f_user_id, b.f_username, b.f_firstname,
b.f_lastname, b.f_added,
(SELECT f_raffle_number
FROM t_model_raffle
WHERE f_user_id = a.f_user_id
AND f_date >= '2007-12-10'
AND f_date <= '2007-12-21'
LIMIT 0,1)
AS f_raffle_number,
(SELECT f_date
FROM t_model_raffle
WHERE f_user_id = a.f_user_id limit 0,1)
AS f_date, b.f_birthdate, b.f_sex
FROM t_model_raffle a
INNER JOIN t_user_profile b
ON b.f_user_id = a.f_user_id
WHERE NOT EXISTS
(SELECT b.f_user_id
FROM t_buy_transaction c
WHERE a.f_user_id = c.f_user_id)

3 Tables in 1 Query

You can get data from 3 tables at the same time all in 1 query.

SELECT DISTINCT a.f_user_id, c.f_username, a.f_raffle_number
FROM t_model_raffle a
INNER JOIN t_buy_transaction b
ON b.f_user_id = a.f_user_id
INNER JOIN t_user_profile c
ON c.f_user_id = a.f_user_id
WHERE b.f_status = 'Completed';

Additional Array Field

You can add a new array field in the existing array result of the query using PHP.

Original Array Result:

[0] => Array (
[f_user_id] => 20250
[f_username] => yoshikihayashi
[f_firstname] => Yoshiki
[f_lastname] => Hayashi
[f_added] => 2006-08-25 16:39:00
[f_raffle_number] => 1543833569
[f_date] => 2007-12-18 13:00:59
[f_birthdate] => 1965-11-20
[f_sex] => M
)
[1] => Array (
[f_user_id] => 1026318
[f_username] => gacktcamui
[f_firstname] => Gackt
[f_lastname] => Camui
[f_added] => 2007-12-09 18:16:48
[f_raffle_number] => 1215771422
[f_date] => 2007-12-18 13:00:59
[f_birthdate] => 1968-07-04
[f_sex] => M
)

Procedure:

for($i=0;$i<count($pagevars['winnersbuy']);$i++):
$bday = $pageVars['winnersBuy'][$i]['f_birthdate'];
$age = $this->$birthday("$bday");
$pageVars['winnersBuy'][$i]['f_age'] = "$age";
endfor;

Output:

[0] => Array (
[f_user_id] => 20250
[f_username] => yoshikihayashi
[f_firstname] => Yoshiki
[f_lastname] => Hayashi
[f_added] => 2006-08-25 16:39:00
[f_raffle_number] => 1543833569
[f_date] => 2007-12-18 13:00:59
[f_birthdate] => 1965-11-20
[f_sex] => M
[f_age] => 42
)
[1] => Array (
[f_user_id] => 1026318
[f_username] => gacktcamui
[f_firstname] => Gackt
[f_lastname] => Camui
[f_added] => 2007-12-09 18:16:48
[f_raffle_number] => 1215771422
[f_date] => 2007-12-18 13:00:59
[f_birthdate] => 1968-07-04
[f_sex] => M
[f_age] => 35
)