$query = "SELECT a.*, c.cat_name FROM " . $DBPrefix . "auctions a
LEFT JOIN " . $DBPrefix . "categories c ON (a.category = c.cat_id)
WHERE a.category = a.category AND a.category = (SELECT a.category FROM " . $DBPrefix . "auctions a
ORDER BY RAND()
LIMIT 1)
ORDER BY RAND() LIMIT 5";
it displays same values and also displays empty result from time to time
and then it displays another same value and then displays empty result
again to n fro like that
Right⌠one problem at a time. Letâs take care of the blanks, and stop changing things youâre not told to change
$query = "SELECT a.*, c.cat_name FROM " . $DBPrefix . "auctions a
LEFT JOIN " . $DBPrefix . "categories c ON (a.category = c.cat_id)
WHERE a.category = (SELECT cat_id FROM " . $DBPrefix . "categories
WHERE counter > 0
ORDER BY RAND()
LIMIT 1)
ORDER BY RAND() LIMIT 5";
Ok, the queryâs running away and running independently across every row, which is what droopsnoot was concerned about. So we restructure to run it as a mock table.
$query = "SELECT a.*, c.cat_name, b.rand_id FROM auctions a
LEFT JOIN categories c ON (a.category = c.cat_id)
LEFT JOIN (SELECT cat_id AS rand_id FROM categories
WHERE counter > 0
ORDER BY RAND()
LIMIT 1) AS b ON 1=1
WHERE a.category = b.rand_id
ORDER BY RAND() LIMIT 5";
$query = "SELECT a.*, c.cat_name, b.rand_id FROM " . $DBPrefix . "auctions a
LEFT JOIN categories c ON (a.category = c.cat_id)
LEFT JOIN (SELECT cat_id AS rand_id FROM " . $DBPrefix . "categories
WHERE counter > 0
ORDER BY RAND()
LIMIT 1) AS b ON 1=1
WHERE a.category = b.rand_id
ORDER BY RAND() LIMIT 5";
$query = "SELECT a.*, c.cat_name, b.rand_id FROM " . $DBPrefix . "auctions a
LEFT JOIN " . $DBPrefix . "categories c ON (a.category = c.cat_id)
LEFT JOIN (SELECT cat_id AS rand_id FROM " . $DBPrefix . "categories
WHERE counter > 0
ORDER BY RAND()
LIMIT 1) AS b ON 1=1
WHERE a.category = b.rand_id
ORDER BY RAND() LIMIT 5";