[SOLVED] reading only certain information from a database

[drkshenronx]

Heres my problem, I want to read only data from the database that meets a certain criteria.

 

Here is my Code -

 

<?php
    @mysql_connect($localhost, forums, ps2pctech) or die("ERROR--CAN'T CONNECT TO SERVER");
    @mysql_select_db(games) or die("ERROR--CAN'T CONNECT TO DB");
?>

<?php

    if($_GET['page']) // Is page defined?

    {

        $page = $_GET['page']; // Set to the page defined

    }else{

        $page = 1; // Set to default page 1

    }

$max = 2; // Set maximum to 10



$cur = (($page * $max) - $max); // Work out what results to show



$getdata = mysql_query("SELECT * FROM `games` ORDER BY `game_name` LIMIT $cur, $max") or die(mysql_error()); // select the results

$counttotal = mysql_query("SELECT * FROM `games` ") or die(mysql_error()); // select all records        

$counttotal = mysql_num_rows($counttotal); // count records



$total_pages = ceil($counttotal / $max); // dive the total, by the maximum results to show 

?>
<center>
<?php
if($page > 1){ // is the page number more than 1?

                $prev = ($page - 1); // if so, do the following. take 1 away from the current page number

                echo '<a href="?page=' . $prev . '">« Previous  </a>'; // echo a previous page link

                }



for($i = 1; $i <= $total_pages; $i++) // for each page number

                {

                    if($page == $i) // if this page were about to echo = the current page

                        {

                            echo'<b>' . $i .'</b> '; // echo the page number bold

                                } else {

                            echo '<a href="?page=' . $i . '">' . $i . '</a> '; // echo a link to the page

                        }

                }



if($page < $total_pages){ // is there a next page?

                    $next = ($page + 1); // if so, add 1 to the current

                echo '<a href="?page=' . $next . '">Next »</a>'; // echo the next page link

                    } 
?>
</center>
<?php
$align_center = "center";
$width_97 = "97%";
$url = "website_url";
$font = "font1";
$pad = "4";
$top = "top";
$img_class = "image";
echo("<table align=".$align_center." width=".$width_97." cellpadding=".$pad." cellspacing=".$pad.">");
    
      echo("<tr><td span class=".$font.">Name</td><td span class=".$font.">Type</td><td span class=".$font.">Publisher</td><td span class=".$font.">Website</td><td span class=".$font.">Image</td><tr>");
    while($row = mysql_fetch_array($getdata)){
      echo("<tr><td span class=".$font." valign=".$top.">");
      echo($row["game_name"]);
      echo("</td><td span class=".$font." valign=".$top.">");
      echo($row["type"]);
      echo("</td><td span class=".$font." valign=".$top.">");
      echo($row["publisher"]);
      echo("</td><td valign=".$top.">");
      echo('<a href="'.$row["website_url"].'">'.$row["website_url"].'</a>');
      echo("</td><td>");
      echo('<img class="'.$img_class.'" src="'.$row["images"].'">');
      echo("</td></tr>");
    }

    echo("</table>"); 

?>

 

When I call the information from the database I get something similar to this

 

Game NamePublisherRate (out of 10)

BootsPub 18

BagsPub 25

BigPub 38

BondsPub 17

BotPub 18

 

Now say I only want to see games from a specific publisher (pub 1) so it would just be this

 

Game NamePublisherRate (out of 10)

BootsPub 18

BondsPub 17

BotPub 18

 

How would I go about doing this using my block of code?

[Yesideez]

SELECT * FROM `games` WHERE `publisher`='1'

 

I'll write the query but I won't write the code - you can do that. If you get stuck, come back for help

[BenInBlack]

SQL uses the "WHERE" for criteria

 

so in your case it would be

"SELECT * FROM `games` WHERE publisher = 'Pub 1' ORDER BY `game_name` LIMIT $cur, $max"

[drkshenronx]

Now I have another problem ... When I do this it works fine except ... theres always the except part ... my pagination gets messed up. I think it's still returning the results from the other games that are not listed.

 

<?php
    @mysql_connect($localhost, forums, ps2pctech) or die("ERROR--CAN'T CONNECT TO SERVER");
    @mysql_select_db(games) or die("ERROR--CAN'T CONNECT TO DB");
?>

<?php

    if($_GET['page']) // Is page defined?

    {

        $page = $_GET['page']; // Set to the page defined

    }else{

        $page = 1; // Set to default page 1

    }

$max = 4; // Set maximum to 10



$cur = (($page * $max) - $max); // Work out what results to show



$getdata = mysql_query("SELECT * FROM `games` WHERE publisher='pub1' ORDER BY `game_name` LIMIT $cur, $max") or die(mysql_error()); // select the results

$counttotal = mysql_query("SELECT * FROM `games` ") or die(mysql_error()); // select all records        

$counttotal = mysql_num_rows($counttotal); // count records



$total_pages = ceil($counttotal / $max); // dive the total, by the maximum results to show 

?>
<center>
<?php
if($page > 1){ // is the page number more than 1?

                $prev = ($page - 1); // if so, do the following. take 1 away from the current page number

                echo '<a href="?page=' . $prev . '">« Previous  </a>'; // echo a previous page link

                }



for($i = 1; $i <= $total_pages; $i++) // for each page number

                {

                    if($page == $i) // if this page were about to echo = the current page

                        {

                            echo'<b>' . $i .'</b> '; // echo the page number bold

                                } else {

                            echo '<a href="?page=' . $i . '">' . $i . '</a> '; // echo a link to the page

                        }

                }



if($page < $total_pages){ // is there a next page?

                    $next = ($page + 1); // if so, add 1 to the current

                echo '<a href="?page=' . $next . '">Next »</a>'; // echo the next page link

                    } 
?>
</center>
<?php
$align_center = "center";
$width_97 = "97%";
$url = "website_url";
$font = "font1";
$pad = "4";
$top = "top";
$img_class = "image";
echo("<table align=".$align_center." width=".$width_97." cellpadding=".$pad." cellspacing=".$pad.">");
    
      echo("<tr><td span class=".$font.">Name</td><td span class=".$font.">Type</td><td span class=".$font.">Publisher</td><td span class=".$font.">Website</td><td span class=".$font.">Image</td><tr>");
    while($row = mysql_fetch_array($getdata)){
      echo("<tr><td span class=".$font." valign=".$top.">");
      echo($row["game_name"]);
      echo("</td><td span class=".$font." valign=".$top.">");
      echo($row["type"]);
      echo("</td><td span class=".$font." valign=".$top.">");
      echo($row["publisher"]);
      echo("</td><td valign=".$top.">");
      echo('<a href="'.$row["website_url"].'">'.$row["website_url"].'</a>');
      echo("</td><td>");
      echo('<img class="'.$img_class.'" src="'.$row["images"].'">');
      echo("</td></tr>");
    }

    echo("</table>"); 

?>

 

So heres what happens. I have 5 games in the DB, 4 by pub 1. I have set the max limit to 4 and it displayes 2 pages. The first page has infomation about the games only by pub1. The second page is blank

 

Now I tested a theory and I was right so heres what happened. I added 5 more games to the database all by another publisher and checked the page again. Now there were 3 pages ... the first was the information and 2 blank. So then I removed 2 games. Now only 2 pages (1 information the other blank).

[drkshenronx]

Nevermind Silly me forgot the change my counttotal variable so it would only count the total of games by the publisher 'pub1'

 

See :

 

$counttotal = mysql_query("SELECT * FROM `games` ") or die(mysql_error()); // select all records 

Should be

$counttotal = mysql_query("SELECT * FROM `games` WHERE publisher='pub1' ") or die(mysql_error()); // select pub1 records

 

Thanks for the help and quick responses !!!  Im begining to love this site !!