[RESOLVED] Database Retrieval Formatting

[mpar612]

Hello,

I am trying to format the results from my database query.

I have tried several things on my own and my logic just isn't working out. I currently retrieve data from a database and it returns the results in one long column. I would like to change it to where it returns the first 4 results in one row, then the next 4 in a new row and so on.

Ultimately, I would like it to return the first 20 results on the first page and then the next 20 results on the next page, etc...

Could anyone give me any advice that will point me in the right direction to get this accomplished?

Thanks!

Here is my current code that works perfectly, but it only returns one long column of results:

$rows = $db->getAll('SELECT upc, artist_name, album_title, image FROM lounge_products ORDER BY artist_name ASC, upc ASC');

foreach ($rows as $row) {
$artist_name = stripslashes($row[artist_name]);
$album_title = stripslashes($row[album_title]);
    				print "<tr><td rowspan=\"3\"><img src=\"../$row[image]\" width=\"60\" height=\"60\"></td>
				   <td colspan=\"2\">UPC: $row[upc]</td></tr>
				   <tr><td colspan=\"2\">Artist: $artist_name</td></tr>
				   <tr><td colspan=\"2\">Album: $album_title</td></tr>				   
				   <tr><tr><td></td><td><a href=\"WMRL_Del.php?upc=$row[upc]&artist_name=$row[artist_name]&album_title=$row[album_title]\">Delete</a>&nbsp;
				   <a href=\"WMRL_Update.php?upc=$row[upc]&artist_name=$row[artist_name]&album_title=$row[album_title]\">Update</a></td></tr>

[lozza1978]

mpar612 wrote:

Ultimately, I would like it to return the first 20 results on the first page and then the next 20 results on the next page, etc...

What your describing is called pagination.. It works where you query the db with the "SELECT * FROM db_table" then you use "mysql_num_rows()" ok its slightly confusing. but heres a basic pagination script for you to modify at will..

<?php 

@mysql_connect("localhost","root","lozza1978") or die("ERROR--CAN'T CONNECT TO SERVER"); 
@mysql_select_db("db_table") or die("ERROR--CAN'T CONNECT TO DB"); 

$limit          = 5;                
$query_count    = "SELECT * FROM table";     
$result_count   = mysql_query($query_count);     
$totalrows      = mysql_num_rows($result_count);  

$PHP_SELF	=	$_SERVER['PHP_SELF'];

if(!isset($_GET['page'])){ // Checks if the $page variable is empty (not set)
    $page = 1; 	// If it is empty, we're on page 1 
} else{

	$page = $_GET['page'];
}



$limitvalue = $page * $limit - ($limit);  
// Ex: (page2 * 5(items per page) = 10) - 5 = 5 <- data starts at 5 

$query  = "SELECT * FROM table LIMIT $limitvalue, $limit";         
$result = mysql_query($query) or die("Error: " . mysql_error());  

if(mysql_num_rows($result) == 0){ 
    echo("Nothing to Display!"); 
} 

$bgcolor = "#E0E0E0"; // light gray 

echo("<table>"); 

while($row = mysql_fetch_array($result)){ 
    if ($bgcolor == "#E0E0E0"){ 
        $bgcolor = "#FFFFFF"; 
    }else{ 
        $bgcolor = "#E0E0E0"; 
    } 

echo("<tr bgcolor=".$bgcolor."><td>"); 
echo($row["photo_filename"]); 
echo("</td><td>"); 
echo($row["photo_filename"]); 
echo("</td></tr>"); 
} //closing while loop

echo("</table>"); 

if($page != 1){  
    $pageprev = $page - 1; //decrementing $page-- does not work on all php configurations. 


    echo("<a href=\"$PHP_SELF?page=$pageprev\">PREV".$limit."</a> ");  
}else{ 
    echo("PREV".$limit." "); 
} 

$numofpages = $totalrows / $limit;  


#echo "<br>", $totalrows;
   #exit;

for($i = 1; $i <= $numofpages; $i++){ 
	/* This for loop will add 1 to $i at the end of each pass until $i is greater  
	than $numofpages. */ 
    if($i == $page){ 
        echo($i." "); 
    }else{ 
        echo("<a href=\"$PHP_SELF?page=$i\">$i</a> "); 
    } 
} //code above has been decoded lozza 


if(($totalrows % $limit) != 0){ 
    if($i == $page){ 
        echo($i." "); 
    }else{ 
        echo("<a href=\"$PHP_SELF?page=$i\">$i</a> "); 
    } 
} 

if(($totalrows - ($limit * $page)) > 0){ 
    $pagenext = $page + 1; //incrementin $page like $page++ does not work in all enviroments

    echo("<a href=\"$PHP_SELF?page=$pagenext\">NEXT".$limit."</a>");  
}else{ 
    echo("NEXT".$limit);  
} 

mysql_free_result($result); 

?> 

HTH
lozz

[bradgrafelman]

More info about pagination can be find by searching the board as well.

[mpar612]

Thanks! I didn't know the proper terminology. I just did a search and there is a ton of information out there on this.

On a slightly different note, this code is excellent for pagination. Now, how would I set it up to print a several rows from the database into one row in a web browser.

For example, I would like to print the first 20 rows in the database in the following format:

DB Row 1 DB Row 2 DB Row 3 DB Row 4
DB Row 5 DB Row 6 DB Row 7 DB Row 8
DB Row 9 DB Row 10 DB Row 11 DB Row 12
DB Row 13 DB Row 14 DB Row 15 DB Row 16
DB Row 17 DB Row 18 DB Row 19 DB Row 20

And then have the next 20 rows print in the same format on the following page (using the pagination code that was provided to me).

Anything help send me in the right direction would be greatly appreciated. Thanks!

[bradgrafelman]

Again, you should be able to search for terms like "table columns" or something of the sort. Whenever I've given coding examples, I explain that I'm using the modulo or modulus function, so you might have luck searching for either of those keywords as well.

[mpar612]

Alrighty, thanks for all of your help!

I have the results printing the way I want them, but now it is not returning everything in the database. I have 12 rows in the database and it is only returning 11 results. I tried adding some rows of info to the db and removing rows and it always returns one less entry than what is stored in the database. I have been working on this all night and I have to be missing something.

Does anyone have any thoughts? Any help would be appreciated. Thanks!

<?php

$limit  = 20;                
$query_count = "SELECT * FROM lounge_products";     
$result_count = mysql_query($query_count);     
$totalrows = mysql_num_rows($result_count);  

$PHP_SELF = $_SERVER['PHP_SELF'];

if(!isset($_GET['page'])){ // Checks if the $page variable is empty (not set)
    $page = 1;     // If it is empty, we're on page 1
} else{

    $page = $_GET['page'];
}



$limitvalue = $page * $limit - ($limit);  
// Ex: (page2 * 5(items per page) = 10) - 5 = 5 <- data starts at 5

$query  = "SELECT * FROM lounge_products LIMIT $limitvalue, $limit";         
$result = mysql_query($query) or die("Error: " . mysql_error());  

if(mysql_num_rows($result) == 0){
    echo("Nothing to Display!");
}
echo("<table>");

while($row = mysql_fetch_array($result)){

$counter = 0;
$cells_per_row = 4;
while($row=mysql_fetch_array($result))
{
   $counter++;
   if(($counter % $cells_per_row) == 1)  { echo '<tr>'; }
   echo '<td>' . ('<img src="'.$row[image] .'" height="60" width="60">') . '</td>';
   if(($counter %  $cells_per_row) == 0) { echo '</tr>'; }
}
   // just in case we haven't closed the last row
   // this would happen if our result set isn't divisible by $cells_per_row
   if(($counter % $cells_per_row) != 0) { echo '</tr>'; }

} //closing while loop

echo("</table>");

if($page != 1){  
    $pageprev = $page - 1; //decrementing $page-- does not work on all php configurations.


    echo("<a href=\"$PHP_SELF?page=$pageprev\">PREV</a> ");  
}else{
    echo("PREV");
}

$numofpages = $totalrows / $limit;  


#echo "<br>", $totalrows;
   #exit;

for($i = 1; $i <= $numofpages; $i++){
    /* This for loop will add 1 to $i at the end of each pass until $i is greater  
    than $numofpages. */
    if($i == $page){
        echo($i." ");
    }else{
        echo("<a href=\"$PHP_SELF?page=$i\">$i</a> ");
    }
} //code above has been decoded lozza


if(($totalrows % $limit) != 0){
    if($i == $page){
        echo($i." ");
    }else{
        echo("<a href=\"$PHP_SELF?page=$i\">$i</a> ");
    }
}

if(($totalrows - ($limit * $page)) > 0){
    $pagenext = $page + 1; //incrementin $page like $page++ does not work in all enviroments

    echo("<a href=\"$PHP_SELF?page=$pagenext\">NEXT</a>");  
}else{
    echo("NEXT");  
}

mysql_free_result($result); 

										?>

[mpar612]

More specifically, the first row returned from the database is omitted.

Thanks!

[bradgrafelman]

That's because you have two while() loops...

    while($row = mysql_fetch_array($result)){ 

$counter = 0; 
$cells_per_row = 4; 
while($row=mysql_fetch_array($result)) 
{ 

[mpar612]

That was it. Thank you very much!