ORDER BY RAND() for one id

I am using the following query to display a background image for each page based on the page ID:

public function get_background($page_id)
{
    $sql = "SELECT background
                 , description
	          FROM page_backgrounds
             WHERE page_id = ?";
					   
    $stmt = $this->pdo->prepare($sql);
    $stmt->execute(array($page_id));
		
    return $stmt->fetch();
}

That works well the only thing is for the index page (page_id = 1) I have mulptiple image in the database. I would like to display a random image for the index page, but still want to be able to use the same query for the other pages as well. So I need a ORDER BY RAND() clause that only will fire when page_id = 1. How do I do that?

Thank you in advance

Add the order by part to the $sql only if page id = 1 :wink:

And how do I do that? That’s why I ask.

http://php.net/manual/en/control-structures.if.php

http://php.net/manual/en/language.operators.string.php

1 Like

Hi Guido. I am still not sure how to approach this?

If I try to add

$pagina_id = NULL;

In front of the query and this afterwards:

if ($pagina_id == 1)
{
	$sql	.= " ORDER BY RAND
			        LIMIT 1";	
}

The entire query isn’t working any longer. Or do I understand you wrong?

The if looks good (mind you I’m not sure about the sql syntax :wink: )
but I don’t think the $pagina_id = NULL is a good idea, I think that’s why it isn’t working anymore.

Please try without, and if it still doesn’t work, post the modified code here and any sql error you might get.

Hi Guido. I tried it without the $pagina_id = NULL.Now the background from all pages is working, except the index page :(. This is the complete methode:

public function get_background($page_id)
{
	$page_id;
    $sql = "SELECT background
                 , description
	          FROM page_backgrounds
             WHERE page_id = ?";
			 
	if ($page_id == 1)
	{
		$sql .=  " ORDER BY RAND()
		              LIMIT 1
	}
					   
    $stmt = $this->pdo->prepare($sql);
    $stmt->execute(array($page_id));
		
    return $stmt->fetch();
}

stop and think about it for a minute

the other pages only have one image, right?

what happens if you ran ORDER BY RAND() LIMIT 1 for one of those pages?

in other words, checking for “only will fire when page_id = 1” is not needed

Hi Rudi. Here I am loosing you. maybe stupid but I don’t understand what you mean. Yes all other pages have just 1 image. The page_id’s are identified in the controller:

$this->pageId    = 1;
$background      = $this->page->get_background($this->pageId);

what i’m saying is –

if pageID is equal to 1, there are multiple images, so ORDER BY RAND() LIMIT 1 will return one of them at random

if pageID is not equal to 1, there’s only one image, so ORDER BY RAND() LIMIT 1 will return it

in ~both~ cases, you don’t actually have to test for pageID = 1

2 Likes

Hi Rudi, you’re absolutely right hahaha, and I was struggling for hours :frowning: Thank you so much

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