[SOLVED] Only showing allowed pictures from database

[er0x]

OK, so heres my problem. I have a page to list images from my database. it will give the image, description, and link to it then it links to a page with more info about the picture etc.. not really important.

 

On this page i only want to show pictures with the field named 'show' that reads 'yes'

 

Only problem is im trying to integrate it with a pagination script and its not pulling it out of the database correctly. im pretty sure the line thats wrong is the following:

    // use pager values to fetch data 
    $query = "select * from ctboxes  limit $offset, $limit" WHERE SHOW='YES' ; 
    $result = mysql_query($query); 

 

i have used a few different variations of the code above. none have worked.

 

when the image is inserted into the db, the show field is left blank. then i go back and add yes too it when i see it.

 

it also may be that i dont have it set up right in the db. for this field its a TEXT NOT NULL. and thats all i filled out.

 

here is the full code if you want to test it.

 

here is a link to the page its running on.

 

http://er0x.byethost6.com/test/index.php?page=ctboxes

 

<?php 

    $host = "sql1.byethost6.com";
    $username = "b6_903966";
    $password = "batman123";
    $db = "b6_903966_main2";
    mysql_connect($host,$username,$password) or die ("error");
    mysql_select_db($db) or die("error");

if($_GET['action'] == "view") {
//Display a single result.
$id = $_GET['id'];
//The MySQL query. Select all from the table news where the ID equals the id sent in URL.

$query = "SELECT * FROM ctboxes WHERE ID='$id'";
//Executing the query.
$result = mysql_query($query) or die(mysql_error());
//Displaying the results of the query.
while ($row = mysql_fetch_array($result)) {

echo "<font size=\"3\" color=\"#FFFFFF\"><i><b>".$row["title"]."</b></i></font><br />Mood: ".$row["mood"]."<br /><br />".$row["content"]."<br /><font size=\"2\" color=\"#FFFFFF\"> Added on ".$row["date"]." by ".$row["author"]."<br />";
}
}else {

    //// save the script as pager.php 
    // create a database names sam_test
    // create a table MyTable with field name and email
    // that's all u need to run the script 
     
    ///////////////////////////////////////////////
    // getPagetData Function
    // parameter -- 
    // 			$numHits = total no of elements , 
    //			$limit   = no of elements per page,
    //      $ctboxpage    = current page no
    ///////////////////////////////////////////////	
    
    function getPagerData($numHits, $limit, $ctboxpage) 
    { 
           $numHits  = (int) $numHits; 
           $limit    = max((int) $limit, 1); 
           $ctboxpage     = (int) $ctboxpage; 
           $numPages = ceil($numHits / $limit); 

           $ctboxpage = max($ctboxpage, 1); 
           $ctboxpage = min($ctboxpage, $numPages); 

           $offset = ($ctboxpage - 1) * $limit; 

           $ret = new stdClass; 

           $ret->offset   = $offset; 
           $ret->limit    = $limit; 
           $ret->numPages = $numPages; 
           $ret->ctboxpage     = $ctboxpage; 

           return $ret; 
    } 
   
    // connect with mysql database 
        

  	
    // get page no from user to move user defined page    
    $ctboxpage = $_GET['ctboxpage']; 
    $id = $_GET['id'];
    
    // no of elements per page 
    $limit = 5; 
    
    // simple query to get total no of entries
    $result = mysql_query("select count(*) from ctboxes"); 
    $total = mysql_result($result, 0, 0); 

    // work out the pager values 
    $pager  = getPagerData($total, $limit, $ctboxpage); 
    $offset = $pager->offset; 
    $limit  = $pager->limit; 
    $ctboxpage   = $pager->ctboxpage; 

    // use pager values to fetch data 
    $query = "select * from ctboxes WHERE SHOW='YES' limit $offset, $limit"; 
    $result = mysql_query($query); 

$color = array("#353535","#3a3a3a");
$tcolor = #008aff
$num_colors = count($color)-1;
$i = 0;
echo "<table>";
while($row = mysql_fetch_array($result)){
//Resets the color counter to 0 if it gets too high
    if($i > $num_colors){$i = 0;}
    echo "<tr bgcolor=\"".$color[$i]."\">";
    echo "  <td width=\"375\"><font size=\"3\" color=\"#FFFFFF\"><a href=\"?page=ctboxes&action=view&id=".$row["ID"]."\">".$row["cat"]."</a><br></font><font color=\"008AFF\">  ".$row["kwords"]."<br> <font size=\"2\" color=\"#FFFFFF\"></font></td></tr>";
$i++;
}
echo "</table>";
    // use $result here to output page content 

    // output paging system (could also do it before we output the page content) 
    if ($ctboxpage == 1) // this is the first page - there is no previous page 
        ; 
    else            // not the first page, link to the previous page 
        echo "<a href=\"?page=ctboxes&ctboxpage=" . ($ctboxpage - 1) . "\"> << </a>"; 

    for ($i = 1; $i <= $pager->numPages; $i++) { 
        echo " | "; 
        if ($i == $pager->ctboxpage) 
            echo "$i"; 
        else 
            echo "<a href=\"?page=ctboxes&ctboxpage=$i\">$i</a>"; 
    } 
        echo " | ";

    if ($ctboxpage == $pager->numPages) // this is the last page - there is no next page 
        ; 
    else            // not the last page, link to the next page 
        echo "<a href=\"?page=ctboxes&ctboxpage=" . ($ctboxpage + 1) . "\"> >></a>"; 
}
?> 

 

any help or suggestions is appreciated. Also, if you see anything else i could make better in the script, please let me know:) thanks all

[pocobueno1388]

Change this:

$query = "select * from ctboxes  limit $offset, $limit" WHERE SHOW='YES' ;

To

$query = "select * from ctboxes WHERE show='yes' LIMIT $offset, $limit";