Function where ID not in other function result

[donboe]

In a widget where I show just the last two or three gallery photos added to the database I use the lightbox functionality where you can indeed show the last two or three results (based on the query offcource). and the other results are actually hidden using the data-remote functionality. So when you click on one of the results shown, the gallery opens and all images are present… So I need basically two functions The first one with the two or three last photos which I have allready:

public function get_recente_photos()
{
	$sql = "SELECT *
		      FROM `gallery_photos`
		  ORDER BY `photo_id` DESC
			 LIMIT 3";		  
		$stmt = $this->pdo->query($sql);
		return $stmt->fetchAll();	
}

So what I now basically need is a second function with the rest results from the same table but where the id’s are not in the other function. Something like:

public function get_other_photos()
{
	$sql = "SELECT *
		      FROM `gallery_photos`
		    WHERE `photo_id` NOT IN **?**;		  
		$stmt = $this->pdo->query($sql);
		return $stmt->fetchAll();	
}

But I am stucked at the question mark. How do I declare that the photo_id should not be the same as the id’s from the function get_recente_photos()

Thank you in advance

[Klav]

It would make more sense for you to have one function/method that retrieves all of the photo ids, then modify get_recente_photos() to retrieve from key 0 to 2 and modify get_other_photos() to retrieve keys >= 3, if any.

Perhaps use array_slice($data, 0, 3) for the recent ids and array_slice($data, 3) for the rest, where $data is your returned array of contents from the gallery_photos table.

http://php.net/manual/en/function.array-slice.php

[donboe]

@Klav. Fair enough… Maybe I should have ask the question differently. . Ok, lets say I use the slice method, how would I use that in the view?

For example:. This would be the loop for the images in view:

<?php foreach ($gallery_photos as $gallery_photo): ?>
<a href="/images/gallery_photos/photos/<?=$gallery_photo['photo'];?>" data-toggle="lightbox" data-gallery="hidden-images" class="col-4">
    <img src="/images/gallery_photos/grid/<?=$gallery_photo['photo'];?>" class="img-fluid">
</a>	
<?php endforeach; ?>

Like i said in the opening post are the other images not showing because of the data-remote tag

[code]

[/code] so If I would use just one function. How should I loop over the remaining images?

Thank you in advance

[droopsnoot]

If you implode() the three photo_id values that you get from the first query into a comma-separated string, could you use that value for the ? in your second query?

[donboe]

@droopsnoot Excuse my ignorance. My PHP knowledge is not that good that I understand this. Again sorry.How would that look like in the example I gave before?

hank you in advance

[droopsnoot]

I’d be thinking something like this, where you call the first function

$photos = get_recente_photos();
$pids = array();
foreach ($photos as $photo) { 
  $pids[] = $photo['photo_id'];
  }
$leave_out = implode(",",$pids);

That will give you a comma-separated string containing the ids of the photos that the first query returns, which you can use in the second query either by passing it into the second function as a parameter, or if it’s in a class you could probably access $leave_out directly.

[donboe]

@droopsnoot. Thank you for the responce. Sometimes the mind is not going as fast as I would like to. Both functions are within the same class (class Model_Page). I have the feeling that I understand what you say but can not bring it into practise Which is very frustrating. Is something like this is what you mean?

public function get_recente_photos()
{
	$sql = "SELECT *
		      FROM `gallery_photos`
		  ORDER BY `photo_id` DESC
			 LIMIT 3";		  
		$stmt = $this->pdo->query($sql);
		return $stmt->fetchAll();	
}

public function get_other_photos()
{
	$photos = get_recente_photos();
	$pids = array();
	foreach ($photos as $photo) { 
		$pids[] = $photo['photo_id'];
	}
	$leave_out = implode(",",$pids);
	$sql = "SELECT *
		      FROM `gallery_photos`
		     WHERE `photo_id` NOT IN $leave_out;		  
		$stmt = $this->pdo->query($sql);
		return $stmt->fetchAll();	
}

Or do I misunderstand you. Thank you in advance

[droopsnoot]

That would probably do it, as long as you close the quotes on the end of the query. I’m not that experienced in PHP classes, so I wasn’t sure where you were calling the functions from. I’m also not sure off the top of my head whether you need to surround the list of values in your “NOT IN” clause with brackets () to make it work.

[donboe]

I saw that with the closing quotes I am gonna give it a try and let you know. Thank you so much for the input sofar

[TeNDoLLA]

Yes the parenthesis around NOT IN values are required so the code works as expected. Without them it can throw error or just return blank result set.

$sql = "SELECT *
		      FROM `gallery_photos`
		     WHERE `photo_id` NOT IN $leave_out";

→

$sql = "SELECT *
		      FROM `gallery_photos`
		     WHERE `photo_id` NOT IN ($leave_out)";

[donboe]

@TeNDoLLA. Thanks for the additional info. I noticed that Thank you for the input.
@droopsnoot and @TeNDoLLA . It is working great now… Thank you both for the input. Very much appreciated