SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    if else statement in query

    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:
    Code:
    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:
    Code:
     <cfif structKeyExists( URL, "gallery_id" )>
    WHERE
                   gallery_id = URL.gallery_id
    </cfif>
    How would I do that in PHP?

    Thank you in advance.
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Assuming you'll have the gallery id in the query string of each menu link, you'd use $_GET['galleryid'] in the query.

  3. #3
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Guido, I tried what you said
    Code:
            
    <?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
    Code:
    $("#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
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  4. #4
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,438
    Mentioned
    45 Post(s)
    Tagged
    13 Thread(s)
    Hi donboe,

    Try this in your PHP script:
    PHP Code:
    $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:
    HTML Code:
    <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>
    Code JavaScript:
    $("#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. #5
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi fretburner! This is working great What can life be easy if you know to walk the right roads!

    Thank you so much
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •