Display result by certain id

php
mysql

#21

How is this fixing your lack of providing some schema and test data for helpers to work with?


#22

yh u right ok so have uploaded a screen shot of my tables

this my auction table

and this is the category table
CATGORYTABLE

// get random value with same cat_id

$query = "SELECT a.*, c.* from " . $DBPrefix . "auctions a
LEFT JOIN " . $DBPrefix . "categories c ON (a.category = c.cat_id)
WHERE a.category = c.cat_id  ORDER BY RAND() LIMIT 3 "

sorry 4 d stress, and thanks 4 ur replies


#23

So, are you saying that you want to run a query that picks a random category, and then displays three of the the entries from the “auction” table with that ID? Although I’m sure it might be possible to do in a single query, I can’t help thinking it might be much quicker to separate it out - run one query to get a random category id, then run another to get some entries for it.

On an entirely separate note which might not be relevant to the original question, why is the other table called “auctions”? It seems to be a list of tunes, and it might make things clearer if the table had a more relevant name. And while you’re at it, I think the artist name should be out in a separate table.


#24

to ur first question yes thats what am trying to accomplish n its been a nitemare what ever ways it gonna work it b better

for d second question am turning it to a tune selling portal will be renaming it later just wanna get somethings working first b4 the renaming

thanks 4 asking


#25

Sort of depends on how you define a ‘single’ query.

Lets start by saying that order by rand is going to be slow if you have a lot of rows. It generally (I wouldnt think) be slower than transmission back and forth between a remote database server and the backend language twice over.

Essentially it will boil down to subqueries inside of queries:
Select a random category ID;
Select 5 (random?) uploads from that category.

The first line would be a subquery of the second. Something to the tune of

SELECT u.*, c.cat_name  
FROM " . $DBPrefix . "categories c
LEFT JOIN " . $DBPrefix . "upload u ON (u.category = c.cat_id)
WHERE c.cat_id = u.category 
AND c.cat_id = (SELECT cat_id 
                FROM categories 
                ORDER BY RAND() 
                LIMIT 1)
ORDER BY RAND() LIMIT 5

Though at this point the question has veered from PHP to more of a general Databases question…


#26

hi result displayed are just random from all id

SELECT a.*, c.cat_name FROM " . $DBPrefix . "categories c
LEFT JOIN " . $DBPrefix . "auctions a ON (a.category = c.cat_id)
WHERE c.cat_id = a.category AND c.cat_id = (SELECT c.cat_id FROM " . $DBPrefix . "auctions a 
                ORDER BY RAND() 
                LIMIT 1)
ORDER BY RAND() LIMIT 5 


#27

If you can explain to me how this subquery makes sense…?
Also the join in the main query is backwards. You want to join categories to auctions, not auctions to categories.


#28

hello am not sure i get wat u asking or telling

i just want to have values of d same id display on random


#29

(SELECT cat_id FROM " . $DBPrefix . "categories WHERE cat_id = " . $id . " ) this has to be the random part.


#30

Why would you need to select cat_id if you’re specifying what the cat_id is by $id ? :wink:


#31

d problem is $id doesnt seem to wanna produce any result thats y maybe cause it on d homepage with any id reading on its index page


#32

skyhigh,

https://www.sitepoint.com/id-html-attribute/


#33

Look very closely at the query that @m_hutley posted in post #25, and you’ll see how it differs from your query. It’s really similar, but not quite. You have a typo in the sub-query.


#34

And when I say the main query’s join is backwards, instead of categories LEFT JOIN auctions, instead auctions LEFT JOIN categories.


#37

okay i tink we getting close on random 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


#38

Do you have categories for which there are no auctions?


#39

yeah and it also seem to display only those with multiple and ignore d once with only 1 in it category


#40

code

$query = "SELECT a.*, c.cat_name FROM " . $DBPrefix . "categories c
LEFT JOIN " . $DBPrefix . "auctions a ON (a.category = c.cat_id)
WHERE c.cat_id = a.category AND c.cat_id = (SELECT a.category FROM " . $DBPrefix . "auctions a 
                ORDER BY RAND() 
                LIMIT 1)
ORDER BY RAND() LIMIT 5 ";

#41

You… still… have the main query backwards. Please read post 34 again.


#42

not so sure whats wrong with d left join here

$query = "SELECT a.*, c.cat_name FROM " . $DBPrefix . "categories c
LEFT JOIN " . $DBPrefix . "auctions a ON (a.category = c.cat_id)
WHERE c.cat_id = a.category AND c.cat_id = (SELECT a.category FROM " . $DBPrefix . "auctions a 
                ORDER BY RAND() 
                LIMIT 1)
ORDER BY RAND() LIMIT 5 ";

shouldnt these b ok? which part is backward cause anything other this query i dont get any result