If else statement in query

PHP
1

Due to circumstances after years I have too make a site in PHP. But I am a bit lost! Right now I am working on a gallery page that exists of different galleries e.a. general, food, restaurant etc. To accomplish this I have two tables in the database involved galleries and gallery_photos:


CREATE TABLE IF NOT EXISTS `galleries` (
  `gallery_id` smallint(2) NOT NULL auto_increment,
  `gallery` varchar(128) NOT NULL,
  PRIMARY KEY  (`gallery_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `gallery_photos` (
  `photo_id` smallint(2) unsigned NOT NULL auto_increment,
  `gallery_id` smallint(2) NOT NULL,
  `photo` varchar(255) NOT NULL,
  `description_dut` varchar(255) default NULL,
  PRIMARY KEY  (`photo_id`),
  FOREIGN KEY (`gallery_id`) REFERENCES `galleries` (`gallery_id`) ON DELETE CASCADE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

On the gallery page I have on the left the gallery and on the right a menu with the different galleries. I am looking for a way that when you land on the gallery page all photos are there but when you click on one of the menu items only the photos from that gallery are displayed. In the scripting language I normally use (Coldfusion) I could do something like this within the query:


 <cfif structKeyExists( URL, "gallery_id" )>
WHERE
               gallery_id = URL.gallery_id
</cfif>

How would I do that in PHP?

Thank you in advance.

2

Assuming you’ll have the gallery id in the query string of each menu link, you’d use $_GET[‘galleryid’] in the query.

3

Hi Guido, I tried what you said

        
<?php
  $gallery_id = $_GET['gallery_id'];
  $qryGallery = "SELECT * FROM gallery_photos WHERE gallery_id = $gallery_id";	

  if ($result = $mysqli->query($qryGallery)) {
    while ($row = $result->fetch_assoc()) {
      echo "<li><a href='gallery/photos/{$row['photo']}'><img src='gallery/thumbnails/{$row['photo']}'></a></li>";
    }
  }
?>

but I can’t get it to work as I would like to have it. Here you can see a test page! As you will see when you come to the page there are no photos. When you click on a link the photos corresponding with that gallery are showing. I know that that has to do with the

$("#menu a").on("click", function(e){

but I don’t know how to approach it a different way. Like I said I would to show all photos from the gallery when you land on the page and only when you click on one of the links the photos belonging to that gallery should show! I hope this is clear and that you can help me.

Thank you in advance

4

Hi donboe,

Try this in your PHP script:


$gallery_id = filter_input(INPUT_GET, 'gallery_id', FILTER_SANITIZE_NUMBER_INT);
$qryGallery = "SELECT * FROM gallery_photos";	

if ($gallery_id) {
	$qryGallery .= " WHERE gallery_id = $gallery_id";
}

if ($result = $mysqli->query($qryGallery)) {
	while ($row = $result->fetch_assoc()) {
		echo "<li><a href='gallery/photos/{$row['photo']}'><img src='gallery/thumbnails/{$row['photo']}'></a></li>";
	}
}

Here, if the gallery_id is present in the query string, then the ‘WHERE’ clause is appended to the SQL statement. If no gallery_id is passed, the query should just return all photos.

Note that I’ve also used filter_input to make sure only numeric values will be passed through to our SQL statement, this is good practice to prevent SQL injection attacks.

Also, it’s possible to simplify your HTML and JS a little:


<ul id="menu">
    <li><a href="photos.php?gallery_id=1">Restaurant</a></li>
    <li><a href="photos.php?gallery_id=2">Eten</a></li>
</ul>


$("#menu").on("click", "a", function(e) {
    e.preventDefault();
    $(".gallery").load(this.href);
});

Rather than using data attributes, the correct URL can be put in each link’s href attribute and used in the click handler function (as you’re already calling preventDefault() in the handler, you don’t need to include javascript:void(0) in the link hrefs). You can also use jQuery’s .load() function as a shortcut for your previous AJAX call.

5

Hi fretburner! This is working great :slight_smile: What can life be easy if you know to walk the right roads!

Thank you so much