[SOLVED] Displaying SELECT results in a formatted table

[rondog]

I was wondering how to show a table with a max limit of 20 results per page. I have various data being pulled and its putting it in a table, but its displayed all in one row. I want to format it so its a 4 column/5 row table. With that I then want the page to generate a numbered list at the bottom that calculates how many pages it takes to show 20 items on each. Here is my code that shows all the results on 1 page in 1 single row so far.

<?php
include 'connect.php';
$c_loc = $_GET['cam'];
$t_loc = $_GET['tape'];

$getvid = mysql_query("SELECT camera,tape,vidname FROM videos WHERE camera = 'cam_$c_loc' AND tape = 'tape_$t_loc'") or die(mysql_error());
echo "<table>";
while ($vidrow = mysql_fetch_array($getvid))
{
echo "<td><div align=\"center\" style=\"padding-right: 20px;\"><a href=\"video.php?cam=$vidrow[camera]&tape=$vidrow[tape]&vid=$vidrow[vidname]\" target=\"_blank\"><img src=\"cam_$c_loc/tape_$t_loc/imgs/$transp.jpg\" alt=\"$transp\" height=\"100\" width=\"100\"/>$vidrow[vidname]</a></div></td>";
}
echo "</table>";
?>

 

Thanks in advance guys!

[cooldude832]

Pagination, about 1 million tutorials on google right now.

[rarebit]

It's been a slow day, i've only seen this question about six time today on here... Someone even posted a link to a tutorial about ten minutes ago...

[rarebit]

http://www.phpfreaks.com/forums/index.php/topic,160936.0.html

[rondog]

I am sorry, but not to be rude, I would have had no clue that a topic with the title of "Help PHP-MySQL" would be talking about what I was looking for. Honestly I didn't even know what to search for. I could describe it but thats about it. Pagination is the first time I've heard this word.

 

I did however take the tutorial that was in that link and got it somewhat working. It is displaying the first 20 results with the links and correct number of pages, however when I click them it says "Nothing to display" which is echoed out in this line:

<?php
if(mysql_num_rows($result) == 0) {
echo("nothing to display!");
}

?>

 

is maybe this statement wrong?

<?php
$query = "SELECT camera,tape,vidname FROM videos WHERE camera = 'cam_$c_loc' AND tape = 'tape_$t_loc' LIMIT $limitvalue, $limit";
?>

 

Ive never done a WHERE, AND, & LIMIT in the same call

 

This is what that call echoes:

SELECT camera,tape,vidname FROM videos WHERE camera = 'cam_a' AND tape = 'tape_1' LIMIT 0, 20

[cooldude832]

try adding an or die(mysql_error()); on the query part, but it looks fine it is looking for all records matching

 

tape is equal to tape_1

camera is equal to cam_a

 

and only producing the first 20 records

(with the LIMIT flag the first number is where to start in the match rows, and the second is the number, if the first is omiited it will default to 0 i.e LIMIT 0, 20 is the same as LIMIT 20)

[rondog]

I added the mysql_error and got nothing, but then realized, that my links were wrong! Since I had get attributes at the top my page link in the url bar read:

http://movfactory.com/army/newrotc/video/list.php?tape=1&cam=a

 

and my 2,3,4 ect links read

 

http://movfactory.com/army/newrotc/video/list.php?page=2

http://movfactory.com/army/newrotc/video/list.php?page=3

http://movfactory.com/army/newrotc/video/list.php?page=4

 

So now I click the links and I don't get that problem of "Nothing to display," however it is still returning results 0-20 on every page. Its like $limitvalue and $limit are not increasing by 20. Here is my pagination.php

 

<?php
include 'connect.php';
$c_loc = $_GET['cam'];
$t_loc = $_GET['tape'];
$limit = 20;
$query_count = "SELECT camera,tape,vidname FROM videos WHERE camera = 'cam_$c_loc' AND tape = 'tape_$t_loc'";
$result_count = mysql_query($query_count);
$totalrows = mysql_num_rows($result_count);

if(empty($page)) {
$page = 1;
}
$limitvalue = $page * $limit - ($limit);
$query = "SELECT camera,tape,vidname FROM videos WHERE camera = 'cam_$c_loc' AND tape = 'tape_$t_loc' LIMIT $limitvalue, $limit" or die(mysql_error());
echo($query);
$result = mysql_query($query) or die(mysql_error());
if(mysql_num_rows($result) == 0) {
echo("nothing to display!");
}
$bgcolor = "#E0E0E0";
echo("<table>");
while($row = mysql_fetch_array($result)) {
if($bgcolor == "#E0E0E0") {
	$bgcolor = "#FFFFFF";
} else {
	$bgcolor = "#E0E0E0";
}
echo("<tr bgcolor=\"".$bgcolor."\"><td>");
echo($row["vidname"]);
echo("</td><td>");
echo($row["camera"]);
echo("</td></tr>");
}
echo("</table>");

if($page != 1){ 
        $pageprev = $page--;
        echo("<a href=\"$PHP_SELF&tape=$t_loc&cam=$c_loc&page=$pageprev\">PREV".$limit."</a> "); 
    }else{
        echo("PREV".$limit." ");
    }
    $numofpages = $totalrows / $limit; 
    for($i = 1; $i <= $numofpages; $i++){
        if($i == $page){
            echo($i." ");
        }else{
            echo("<a href=\"$PHP_SELF?tape=$t_loc&cam=$c_loc&page=$i\">$i</a> ");
        }
    }
    if(($totalrows % $limit) != 0){
        if($i == $page){
            echo($i." ");
        }else{
            echo("<a href=\"$PHP_SELF?tape=$t_loc&cam=$c_loc&page=$i\">$i</a> ");
        }
    }
    if(($totalrows - ($limit * $page)) > 0){
        $pagenext = $page++;
        echo("<a href=\"$PHP_SELF?tape=$t_loc&cam=$c_loc&page=$pagenext\">NEXT".$limit."</a>"); 
    }else{
        echo("NEXT".$limit); 
    }
    mysql_free_result($result); 
?>

 

See anything that raises a flag as to why the limits aren't increasing?

[GingerRobot]

I expect register_globals is turned off (which is a good thing) and so $page is undefined. Try changing the top of your script to:

 

<?php
include 'connect.php';
$c_loc = $_GET['cam'];
$t_loc = $_GET['tape'];
$page = $_GEt['page'];

 

Edit: And yes, the reason for the large number of topics on the subject is simpy because until you've done it, you wouldn't know what it's called - people seem to forget that.

[cooldude832]

yes $page should be defined in that empty line you left before the if and a good test also is to see is_numerc if not report back to page 1

 

Whats funny is pagination is defined in most dictionaries now too.

[GingerRobot]

yes $page should be defined in that empty line you left before the if and a good test also is to see is_numerc if not report back to page 1

 

Whats funny is pagination is defined in most dictionaries now too.

 

Still does't help you find the term in the first place!

 

And yes, you will want some error checking on the page number. Indeed, you'll want some on all of the variables you're getting from the user. At present, you're querying your database with information straight from the user, which is a large security risk. Consider using the mysql_real_escape_string() function on user data.

[rondog]

Oh sweet that worked! But ughhh now another 'minor' problem.

 

As you click the numbers, the one you are currently on is suppose to just be text. What it does is it makes the PREVIOUS number text as well as the "NEXT20" link links to page1 every time and on then something else is screwy with the "PREV20" link as well. Something is screwy with that logic. It was copied directly from that tutorial so I dont understand.

 

Its better if I just show you.

 

http://movfactory.com/newarmy/ (removed)

 

click camera A tape 1(dont mind the html, my boss built this with some web generator. Im redoing the html/css after I finish the back end. Thanks guys

[cooldude832]

the        $pageprev = $page--; and $page++; dont' work it will only alter teh value of page not $pageprev say

$pageprev= $page-1;

$pagenext = $page+1; and try that.

[rondog]

Thank you cooldude. That fixed the problem

 

@reply#9

yes $page should be defined in that empty line you left before the if and a good test also is to see is_numerc if not report back to page 1

 

Whats funny is pagination is defined in most dictionaries now too.

 

Still does't help you find the term in the first place!

 

And yes, you will want some error checking on the page number. Indeed, you'll want some on all of the variables you're getting from the user. At present, you're querying your database with information straight from the user, which is a large security risk. Consider using the mysql_real_escape_string() function on user data.

 

Trying to understand this here..

 

so I would do

<?php
$query = sprintf("SELECT camera,tape,vidname FROM videos WHERE camera = 'cam_$c_loc' AND tape = 'tape_$t_loc' LIMIT $limitvalue, $limit", mysql_real_escape_string($c_loc),mysql_real_escape_string($t_loc),mysql_real_escape_string($limitvalue));
?>

sorry bare with me im new  :-\

[cooldude832]

That works, but it is disgusting. Instead work the variables out side the query string and then make the query srting one variable and use it

example

<?php
$var1 = $_GET['var1'];
$var2 = $_GET['var2'];
if(empty($var1) || !is_numeric($var1) || $var1 <1){
$var1 = 1;
}
if(empty($var2) || is_numeric($var2) || strlen($var2) >1){
$var2 = a;
}
$q = "Select Stuff from `table` where Var1 = '".$var1."' and var2 = '".$var2'" Order by something limit 0,20";
$r = mysql_query($q) or die(mysql_error());
?>

Make sense?

[rondog]

I am getting an error with that code. My echoed select statement and then the mysql_error.

 

SELECT camera,tape,vidname FROM videos WHERE camera = cam_a AND tape = tape_1;

 

Unknown column 'cam_a' in 'where clause'

 

The error says unknown column cam_a..The field name is camera and cam_a is whats in the field. Anyway here is what I have right now:

<?php
include 'connect.php';
$c_loc = $_GET['cam'];
$t_loc = $_GET['tape'];
$page = $_GET['page'];
if(empty($t_loc) || !is_numeric($t_loc) || $t_loc <1){
$t_loc = 1;
}
if(empty($c_loc) || is_numeric($c_loc) || strlen($c_loc) >1){
$c_loc = a;
}
$limit = 20;
$query_count = "SELECT camera,tape,vidname FROM videos WHERE camera = cam_".$c_loc." AND tape = tape_".$t_loc.";";
?>

[rondog]

wait my bad im an idiot..I didnt use any single quotes.

 

So I can see that it is working with those if statements, but what exactly are they doing? If its empty make it 1, if its not numeric make it 1 or if its less than 1 make it. Is that about right?

 

Im gonna set this to solved since I have no more problems. Thanks guys for the help. It was GREATLY appreciated!

[cooldude832]

The problem is that you want to use GET so pages can be bookmarked for later viewing (And so you can use hyperlinks over a form system), and GET is an insecure method of sending data by design, because it sends in the url which the user can easily edit.  So you want to make sure the user doesn't put nasty data in that to make it generate errors (such as saying $limit*a)  I use a as a representation of the user edited get variable.  This would cause an error when u use that value in the LIMIT portion of mysql, and you don't want that.  So instead of telling them they messed up just fix it.