Display result by certain id

Databases
,
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…

image
image1920×1080 182 KB

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.

1 Like
58

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.