Display result by certain id

Databases
,
1

hi guys so am thinking of displaying some content but it should only display certain id and it content like for example

genre title
gospel jesus lives
gospel jesus wonderful
gospel love is love

and since the order is random the next one will be

genre title
rap make it now
rap dont care
rap simple and hard

and next random

genre title
rock wide wide
rock who cares
rock love

i hope u guys understand what am trying to say

heres the code i got but it simple display all d result like

rock wide wide
gospel jesus lives
rap simple and hard

// get music catergory random 

$query = "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 ORDER BY RAND() LIMIT 5";


$params = array();
$params[] = array(':time', $NOW, 'int');
$params[] = array(':limit', $system->SETTINGS['lastitemsnumber'], 'int');
$db->query($query, $params);

$i = 0;
while ($row = $db->fetch())
{
    $template->assign_block_vars('cat_random', array(
            'DATE' => ArrangeDateNoCorrection($row['starts'] + $system->tdiff),
            'ID' => $row['id'],
            'ARTISTNAME' => $row['artistname'],
            'TITLE' => $row['title'],
            'CAT_NAME' => $row['cat_name'],
            'ACTIVATE_VID' => $row['activate_vid'],
            'VIDEO_LINK' => $row['video_link'],
            'IMAGE_URL' => (!empty($row['pict_url'])) ? 'getthumb.php?w=' . $system->SETTINGS['thumb_show'] . '&fromfile=' . UPLOAD_FOLDER . $row['id'] . '/' . $row['pict_url'] : 'images/email_alerts/default_item_img.jpg',
            'TITLE' => htmlspecialchars($row['title'])
            ));
    $i++;
}

Thanks a bunch

2

I don’t see any code or where-condition in your SQL that represents this requirement.

3

hi chorn thanks 4 reply theres a where clause

WHERE c.cat_id = u.category ORDER BY RAND

if i do this

WHERE c.cat_id = 208 ORDER BY RAND

i get a specific result but it will only b that want since am manually changing it.

4

If you want a specific ID you have to tell that in the query - the ID may be from direct input, from a variable or from a function like rand() or a subquery.

5

am just trying to display rand row with same cat_id

i tried this

$query = "SELECT * FROM  " . $DBPrefix . "auctions  
WHERE  category IN (SELECT cat_id FROM   " . $DBPrefix . "categories 
WHERE category = category AND cat_id = cat_id ) ORDER BY RAND() LIMIT 3";

but still showing random selection of different id

6

You are still just connecting the records by any cat_id, but you are not actually reducing the records to a specific cat_id. Just have a look at what your query results without the limit. Use a cat_id that is determined by a fixed value, a variable, a function or a subquery.

7

hi thanks chorn a little push to d right direction will be appreciated

thanks

8

If you want to use a specific id from a variable such as $id, you could write: cat_id = " . $id . ") ORDER BY ....

9

HI thanks for the reply, i did that i believe this way

// get music catergory random 
$query = "SELECT * FROM  " . $DBPrefix . "auctions  
WHERE  category IN (SELECT cat_id FROM   " . $DBPrefix . "categories 
WHERE cat_id = " . $id . " ) ORDER BY RAND() LIMIT 3";

but i get know result.

10

HI thanks for the reply, i did that i believe this way

// get music catergory random 
$query = "SELECT * FROM  " . $DBPrefix . "auctions  
WHERE  category IN (SELECT cat_id FROM   " . $DBPrefix . "categories 
WHERE cat_id = " . $id . " ) ORDER BY RAND() LIMIT 3";

but i get know result.

11

this displays no result

WHERE cat_id = '" . $id . "' ) ORDER BY RAND() LIMIT 3";

with this it display random values
WHERE cat_id <> '" . $id . "' ) ORDER BY RAND() LIMIT 3";

12

Are you sure there is a record in your database table that corresponds to your $id?

13

yeap

14

show an example on http://sqlfiddle.com/

15

no idea what am suppose to do there
this is what am simply trying to accomplish

on every random it shows different value with same id

genre             title

208 gospel    jesus lives
208 gospel    jesus wonderful
208 gospel    love is love

when i refresh page shows another

genre             title

211 rap          hard lives
211 rap          wonderful
211 rap          love rare
211 rap          bad girl

when i refresh page shows another
genre             title

231 hippop         lives
231 hippop         fright

this is what am trying to accomplish

16

its an SQL console, just put in your sheme, your data and relevant sql queries.

17

yh i see that, still no clue so not gonna waste time on it , thanks appreciate ur effort

18

any other way i can do this?

19

You had six days to make a minimal example that takes ten minutes (because its just a copy of the data you already have), so that potential helpers dont have to do all the work for you - and you dont even come up with new insights? There is a simple way, you already got necessary answers, all you have to do is to put more effort in finding a solution and to give the people around you a starting point.

20

multiple stuff have been doing not just website stuff so wen am less busy i try build a page for time consume me again

and nope havent been able to get it working