Display result by certain id

php
mysql

#43

if u mean this

$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";

its brings out random result of different values


#44

Why did this change?
EDIT: Though it shouldnt matter cause it’s already in the join condition.

Show me records that it’s pulling up.


#45

it still puling same record

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


#46

Right… one problem at a time. Let’s take care of the blanks, and stop changing things you’re not told to change :stuck_out_tongue:

$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";

#47

LOL ok

ok this has no blanks but random values not same values


#48

the above code with WHERE counter > 0 doesnt display same value
it output different values and has space


#49

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";

#50

hi am getting no result

$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";



#51

What is the value of $DBPrefix ? Cause that’s the only difference between our queries at this point, and it works on my mockup of your tables…


#52

song_


#53

Then you missed one.

$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";

#54

what did i miss?


#55

the " . $DBPrefix . " before the categories on the second line.


#56

yh u right let me chk it out again


#57

yeap works well now thanks a millionnnnnnnnnnnnnn m_hutley u done quite alot for me.

thank you.