想要使用一个MySQL表来影响多个页面

Question

So right now, I've got a "gallery" system on my homepage of my site. Take a look:

<?php
        $objConnect = mysql_connect("mydb.db","hello","mypass") or die(mysql_error());
        $objDB = mysql_select_db("mydb");
        $pic2 = "SELECT * FROM gallery";
        if (!isset($_GET['Page']))  $_GET['Page']='0';
        $pic1 = mysql_query($pic2);
        $Num_Rows = mysql_num_rows($pic1);
        $Per_Page = 16;   // Per Page
        $Page = $_GET["Page"];
        if(!$_GET["Page"])
        {$Page=1;}
        $Prev_Page = $Page-1;
        $Next_Page = $Page+1;
        $Page_Start = (($Per_Page*$Page)-$Per_Page);
        if($Num_Rows<=$Per_Page)
        {$Num_Pages =1;}
        else if(($Num_Rows % $Per_Page)==0)
        {$Num_Pages =($Num_Rows/$Per_Page) ;}
        else
        {$Num_Pages =($Num_Rows/$Per_Page)+1;
            $Num_Pages = (int)$Num_Pages;}
        $pic2 .=" order  by GalleryID ASC LIMIT $Page_Start , $Per_Page";
        $pic1  = mysql_query($pic2);
$cell = 0;
$link2 = "SELECT * FROM gallery";
$link1 = mysql_query($link2);
$link = mysql_fetch_array($link1);
$alt2 = "SELECT * FROM gallery";
$alt1 = mysql_query($alt2);
$alt = mysql_fetch_array($alt1);
echo '<div id="tablediv"><table border="0" cellpadding="17" cellspacing="0" class="table"><tr>';
while($pic = mysql_fetch_array($pic1))
{if($cell % 4 == 0) {
    echo '</tr><tr>';}
if($cell == 2) {
    echo '<td>reserved cell, ignore this</td>';
} elseif ($cell == 3) {
    echo '<td>reserved cell, ignore this</td>';
} else {
    echo '
    <td><a href="/' . $link["link"] . '.php"><div class="image"><img src="https://s3.amazonaws.com/images/' . $pic["pic"] . '" alt="' . $alt["alt"] . ' /></div></a></td>'; }
    $cell++;
}
echo '</tr></table></div>';
    ?>

Anyhow... as you can see, with this system, whenever I insert a new record, it automatically updates my gallery. Now my question is how can I make it so when I insert a new record, it doesn't just affect my homepage gallery, it affects the galleries on the other subsections of my website as well.

Say my site is called site.com . I also have a site.com/nature . My site.com/nature is only for nature photos, but I don't want to manually update /nature by creating a whole new set of table and update that manually. Rather I'd rather take an easier route, so in my gallery table, I can specify whether or not I want it in /nature.

I presume I would need another column (obviously) for specifying what other folders do I want my record to appear in, or maybe some conditional statements to determine which subfolder should my record also appear in and not just my homepage. Unfortunately, I'm a nooblet, so I'm asking if fellow stackers can help me with this. Thanks!

Answer 1

I'm not sure of the exact schema you're using but if your gallery is sub divided into categories like nature, animation. its best to have one extra column ("category") probably a varchar or string (sorry not familiar with exact types in mysql) that specifies the category, so in the nature category you would go like:

select *
from gallery g
where g.category = 'nature'

So if you have another category like pokemon you would go:

select *
from gallery g
where g.category = 'pokemon'

This way you can have many different categories for you site.

but in the main page where you want all the pictures just have:

select *
from gallery g

Also, its best that instead of returning * you return only the fields you actually need. And possible use select DISTINCT filndName so you're not getting repeats the same tuple (entry) when grabbing it from sql