how to dynamically create a table that displays one row of data at a time?

[chansky]

hi there

I'm trying to create a table that displays only one row of data at a time fetched from MySQL. When a user click the button "next", a new row of data will display on the same table. So in order to do this the next row of data will need to replace the previous row on the same table; that table always displays only one row and the latest row from the database.

Does anyone have any idea to go about it?

[cgraz]

<?

/* you could put these following variables in a seperate file and include them on the page. That's how I normally do it, I only changed it to look like this for the example */

$server = "localhost";
$username = "user";
$password = "pass";
$database = "your_db";
$table = "your_table";



// Connect To MySQL Server
@mysql_connect($server, $username, $password) or die("Couldn't Connect to Database");

// Select Database
@mysql_select_db($database) or die("Couldn't Select Database");

// set number of results to display per page
$pagelimit = "1";

// run query (change yourtable to the name of your table)
   $strSQL = mysql_query("SELECT * FROM $table");

// count number of matches
   $totalrows = mysql_num_rows($strSQL);

// determine how many pages there will be by using ceil() and dividing total rows by pagelimit
   $pagenums = ceil ($totalrows/$pagelimit);

// if no value for page, page = 1
	if ($page==''){
		$page='1';
	}
// create a start value
	$start = ($page-1) * $pagelimit;

// blank matches found
echo "<b>" . $totalrows . " matches found</b><br>\n";

// Showing Results 1 to 1 (or if you're page limit were 5) 1 to 5, etc.
$starting_no = $start + 1;

if ($totalrows - $start < $pagelimit) {
   $end_count = $totalrows;
} elseif ($totalrows - $start >= $pagelimit) {
   $end_count = $start + $pagelimit;
}


echo "Results $starting_no to $end_count shown.<br>\n";

// create dynamic next, previous, and page links

/* lets say you're set to show 5 results per page and your script comes out with 7 results.
this will allow your script to say next2 if you're on the first page and previous5 if you're on the second page. */

if ($totalrows - $end_count > $pagelimit) {
   $var2 = $pagelimit;
} elseif ($totalrows - $end_count <= $pagelimit) {
   $var2 = $totalrows - $end_count;
}

$space = "&nbsp;";

// previous link (make sure to change yourpage.php to the name of your page)
if ($page>1) {
		echo "« <a href='yourpage.php?page=".($page-1)."' class=main>Previous" . $space . $pagelimit . "</a>" . $space . $space . "";
	}

// dynamic page number links (make sure to change yourpage.php to the name of your page)

for ($i=1; $i<=$pagenums; $i++) {
	if ($i!=$page) {
		echo " <a href='yourpage.php?page=$i' class=main>$i</a>";
	}
	else {
		echo " <b class='red'>$i</b>";
	}
}


// next link (make sure to change yourpage.php to the name of your page)

if ($page<$pagenums) {
	echo "" . $space . $space . $space . $space . " <a href='youerpage.php?page=".($page+1)."' class=main>Next " . $var2 . "</a> »";
}

/* output your data wherever you'd like.

BUT

in order for this all to work, before outputting your data, you have to run the query over using MySQL's LIMIT. This will limit how many results are actually displayed on the page. */

   $strSQL = mysql_query("SELECT * FROM $table LIMIT $start,$pagelimit");

// LIMIT 0,10 will start at 0 and display 10 results
// LIMIT 10,5 will start at 10 and display 5 results

/* now you can do whatever you'd like with this query. it will only output one page at a time. change the $pagelimit variable to whatever to output more than 1 result per page. */


?>

This should be pretty self explanatory. Post back if you run into any problems.

Cgraz

[nca]

I only want 10 next and previous page links. This shows a link to every next and previous pages. What would I have to do, to do this?

[marian]

You need to use pagination. You need to set your pagination limit to 10 then use the next/previous links.

Hope this helps!

[nca]

pagination? please explain

[NoFear]

kudos to cgraz
That is some polished looking code. I'm impressed 🙂

Unfortunately, my code NEVER looks that clean (blush)

[cgraz]

NCA, if i understood you correctly you only want a max of 10 pages? That means if the total number of pages is more than ten, your page limit will be calculated automatically to make ten pages? Now this is just off the top of my head, but this should work.

What this code does is it calculates the total number of pages. If that number is less than or equal to ten, it continues, if it's more than ten, then it automatically sets the total page number to 10, and calculates how many results per page it would need to display to achive that.

// Connect To MySQL Server
@mysql_connect($server, $username, $password) or die("Couldn't Connect to Database");

// Select Database
@mysql_select_db($database) or die("Couldn't Select Database");


// set number of results to display per page
$pagelimit = "1";

// run query (change yourtable to the name of your table)
   $strSQL = mysql_query("SELECT * FROM $table");

// count number of matches
   $totalrows = mysql_num_rows($strSQL);

// calculate how many pages this would be
$pagenums = ceil ($totalrows/$pagelimit);

// checks to see how many pages this comes out to
if ($pagenums <= 10) { // came out to 10 pages or less

   // don't do anything

} else { // more than 10 pages. Calculate how many results p/page to display so thta the page number comes out to 10

   $pagenums = "10"; // set your pagenumber to 10
   $pagelimit = $pagenums/$totalrows; // figure out how many results per page this would need to display

}

// if no value for page, page = 1
    if ($page==''){
        $page='1';
    }


// rest of code here

Be sure to let me know how it works

And to NoFear: I spent a really long time tryign to figure this out. Went through all kinds of tutorials and sites. Then, getting it to work the way i want (like having it print Showing Results 1 to 9) and (saying next 4 [calculating how many results were on the next page]) was a nightmare. I played around till I got it, and posted it completely commented so other people could follow.

Glad to know it's been useful for a few people.

Cgraz

[nca]

Hi cgraz, what I meant was having a "google type" of next and previous thing. It would not show a link to every single page of results found, but it would show only 10 next and previous links. Do you see what I am saying?
Thank you very much for your help! 😃

[orionblue]

cgraz, thanks for the awesome script above - either I'm catching on to php or your script was incredibly easy to follow.. Who am I kidding, it's the latter or I wouldn't be asking more questions.

I have WHERE = requirements on the $result, passed from a form on another page and then captured in variables in the display results page - everything looks great in the first result, then on page+1 or -1 I get:

0 matches found
Results 4 to 0 shown.

and no results displayed at all.

The more I mess with it the more I lose the working-ness of the code... Yes, i said working-ness. My head is tired. Do I need to create another variable to capture the $strSQL and if so, where do I drop it? Or maybe I'm off track.

Any suggestions you (or any other kind soul) could make would be very much appreciated.

[darkfunkel]

I am using this code. It works fine untill I start putting a loop in at the end. am I meant to put the whole code in a loop as I always get an error with the query

 

/* output your data wherever you'd like.

BUT

in order for this all to work, before outputting your data, you have to run the query over using MySQL's LIMIT. This will limit how many results are actually displayed on the page. */

print "Query used: ";
print 'mysql_query("SELECT * FROM '.$table.' LIMIT '.$start.','.$pagelimit.'")';
$query = mysql_query('SELECT * FROM '.$table.' LIMIT '.$start.','.$pagelimit);
$result = mysql_query($query); 

while($r=mysql_fetch_array($result)) /*this causes the loop to be ran until the query is empty*/ 
{   

/* This bit sets our data from each row as variables, to make it easier to display */ 
            $topic = $r["title"];
            $news = $r["news"];
            $name = $r["name"];
            $date = $r["date"];
        

PHP shows the error on the line where it begins while. I have not included the whole loop as it is far too big. but I have tried with mulitple different loops from different tables. if you could help I would be grateful

[cgraz]

$query = mysql_query('SELECT * FROM '.$table.' LIMIT '.$start.','.$pagelimit);
$result = mysql_query($query); 

should be

$query = "SELECT * FROM $table LIMIT $start,$pagelimit";
$result = mysql_query($query); 

Cgraz

[darkfunkel]

I have tried both of those the latter is purely to ensure the variables work as I inserted or die mysql_error (but used the right syntax) and it returned the query with the variables untouched

[cgraz]

If you want to error check do this:

$query = "SELECT * FROM $table LIMIT $start,$pagelimit";
print $query . "<br>";
$result = mysql_query($query) or die(mysql_error());

You were running mysql_query() twice on the code you posted.

Cgraz

[habs20]

Great post, I just have two quick question regarding some problems I having with a modification to this.

Instead of using a static page, can you use $PHP_SELF?
I have tried but keep getting parse error.

// previous link (make sure to change yourpage.php to the name of your page)
if ($page>1) {
echo "« <a href='mfm.php?page=".($page-1)."' class=main>Previous" . $space . $pa
gelimit . "</a>" . $space . $space . "";
}

Secon question,

I can not seem to get this to work with SORT. I have additional sort query based on the user clicking on up and down arrors.
I have tried it like this

$query .= "LIMIT $start, $pagelimit ORDER BY $order $way ";

and then like this

$query .= "LIMIT $start, $pagelimit";
$query .= "ORDER BY $order $way ";

Neither way works. Any suggestions will be of great help.

Much thanks in advance.

[cgraz]

Yeah you can use PHP_SELF (I wrote this when I was just learning PHP). It would look something like this

// previous link (make sure to change yourpage.php to the name of your page) 
if ($page>1) { 
echo "« <a href='" . $PHP_SELF . "?page=".($page-1)."' class=main>Previous" . $space . $pa 
gelimit . "</a>" . $space . $space . ""; 
} 

As for the ORDER BY LIMIT problem, make the first query look like this

$strSQL = mysql_query("SELECT * FROM table ORDER BY $order $way");

// and the second one like this:

$strSQL = mysql_query("SELECT * FROM table ORDER BY $order $way LIMIT $start,$pagelimit");

If both your variables have values, then that will work just fine.

Cgraz

[Jungl3M4n]

Originally posted by cgraz
Yeah you can use PHP_SELF (I wrote this when I was just learning PHP). It would look something like this

// previous link (make sure to change yourpage.php to the name of your page) 
if ($page>1) { 
echo "« <a href='" . $PHP_SELF . "?page=".($page-1)."' class=main>Previous" . $space . $pa 
gelimit . "</a>" . $space . $space . ""; 
} 

As for the ORDER BY LIMIT problem, make the first query look like this

$strSQL = mysql_query("SELECT * FROM table ORDER BY $order $way");

// and the second one like this:

$strSQL = mysql_query("SELECT * FROM table ORDER BY $order $way LIMIT $start,$pagelimit");

If both your variables have values, then that will work just fine.

Cgraz [/B]

Cgraz, i'm doing something like this but i need is to show 25
records from the ones in the db, i dont want a page limit, just
show the next and prev links (neither to have a pagination
number prev 2 3 4 5 6 next, just prev | next)

here my code.

$offset=2;
                if (!isset($min)) $min=0;
                if (!isset($max)) $max=$min+$offset;

$result = sql_query("select * from ".$prefix."_lista_de_juegos order by jid DESC limit $min,$offset",$dbi);
if(!$result) die(_ERROR."<br>");
$nrows  = sql_num_rows($result, $dbi);
$x=0;
while (list($jid, $jnombre, $jgenero, $jreview, $jdcreenshot, $jdemo) = sql_fetch_row($result, $dbi)) 
	echo "<center><table width=\"90%\" border=\"0\" cellspacing=\"4\" cellpadding=\"0\" align=\"center\">
<tr>
<td>".$jnombre."</td>
<td>".$jgenero."</td>
<td>".$jreview."</td>
<td>".$jscreenshot."</td>
<td>".$jdemo."</td>
</tr>
</table></center>";

now keep in mind that i'm using phpnuke 6.5 some stuff is from it, i cant get the next and prev buttons to be shown, i setet the offset to 2 because i have not filled the database yet, just using somes a b c d e f as values on the db nut it will contain more than 200 reccords.

any idea?

[cgraz]

If you just want prev | next, then drop the for loop out that creates the 1 2 3 4 5 page numbers.

    for ($i=1; $i<=$pagenums; $i++) {
        if ($i!=$page) {
            echo " <a href='yourpage.php?page=$i' class=main>$i</a>";
        }
        else {
            echo " <b class='red'>$i</b>";
        }
    }

If you want 25 results per page, change the $pagelimit variable to 25. The rest should all be the same.

I'm not sure what you mean by you don't want a page limit. If you want next and previous, then you'll need a pagelimit. The variable just helps you display x amount of results per page.

I also don't see anywhere in your code where you have the next and previous buttons. You also need to make sure you run your select query twice. The first time to do run some basic calculations, the second time using LIMIT to display the right amount of results per page. Re-read the first page w/the code; it's all commented and easy to follow.

Cgraz

[Jungl3M4n]

Originally posted by cgraz
If you just want prev | next, then drop the for loop out that creates the 1 2 3 4 5 page numbers.

    for ($i=1; $i<=$pagenums; $i++) {
        if ($i!=$page) {
            echo " <a href='yourpage.php?page=$i' class=main>$i</a>";
        }
        else {
            echo " <b class='red'>$i</b>";
        }
    }

If you want 25 results per page, change the $pagelimit variable to 25. The rest should all be the same.

I'm not sure what you mean by you don't want a page limit. If you want next and previous, then you'll need a pagelimit. The variable just helps you display x amount of results per page.

I also don't see anywhere in your code where you have the next and previous buttons. You also need to make sure you run your select query twice. The first time to do run some basic calculations, the second time using LIMIT to display the right amount of results per page. Re-read the first page w/the code; it's all commented and easy to follow.

Cgraz [/B]

When i sayd a page limit i meant i dont want a maximum pages to be displayed just want a maximum of results per page.

i will tell you exactly what i wanted to do!

Lets say that I have a list of books for a store, the table will contains id, name, genere, review, picture, details

now, the books will be shown by id desc. so is (order by id desc)

The code i have is this:

$offset=2;
                if (!isset($min)) $min=0;
                if (!isset($max)) $max=$min+$offset;

$result = sql_query("select * from ".$prefix."_lista_de_juegos order by jid DESC limit $min,$offset",$dbi);
if(!$result) die(_ERROR."<br>");
$nrows  = sql_num_rows($result, $dbi);
$x=0;
while (list($jid, $jnombre, $jgenero, $jreview, $jdcreenshot, $jdemo) = sql_fetch_row($result, $dbi)) 
	echo "<center><table width=\"90%\" border=\"0\" cellspacing=\"4\" cellpadding=\"0\" align=\"center\">
<tr>
<td>".$jnombre."</td>
<td>".$jgenero."</td>
<td>".$jreview."</td>
<td>".$jscreenshot."</td>
<td>".$jdemo."</td>
</tr>
</table></center>";
		echo "<center>";

            $prev=$min-$offset;
            if ($prev>=0) {
                    print "<a href=\"dont know\">";
                    print "<img src=\"images/buttons/previous.gif\" border=\"0\" title=\""._PREV." "._RESULTS."\"></a>&nbsp;&nbsp;&nbsp;&nbsp;";
            }
            $next=$min+$offset;
	if ($x>=2) {
                    print "<a href=\"dont know\">";
                    print "<img src=\"images/buttons/next.gif\" border=\"0\" title=\""._NEXT." "._RESULTS."\"></a>";
            }
echo "</center>";

To be totally honest i'm using another code i found on phpnuke, i'm still learning php and i cant get this to work. maybe implementing your code will do the trick, just i dont know how to implement it.

any idea of how?

[cgraz]

I've never used PHP NUKE before, but if you're pulling out of a mysql database, it's virtually the same. It looked as if you were already working with some stuff (the offset, etc), so i just got rid of it and added in the code to display 25 results per page. Make sure to add in your database connection and select the database (mysql_connect() and mysql_select_db())

// set number of results to display per page
$pagelimit = "25";

$strSQL = mysql_query("select * from ".$prefix."_lista_de_juegos") or die("error"); // changed sql_query to mysql_query and dropped the LIMIT

// count number of matches
   $totalrows = mysql_num_rows($strSQL);


// determine how many pages there will be by using ceil() and dividing total rows by pagelimit
   $pagenums = ceil ($totalrows/$pagelimit);

// if no value for page, page = 1
    if ($page==''){
        $page='1';
    }
// create a start value
    $start = ($page-1) * $pagelimit;

// blank matches found
echo "<b>" . $totalrows . " matches found</b><br>\n";

// Showing Results 1 to 25, etc.
$starting_no = $start + 1;

if ($totalrows - $start < $pagelimit) {
   $end_count = $totalrows;
} elseif ($totalrows - $start >= $pagelimit) {
   $end_count = $start + $pagelimit;
}

echo "Results $starting_no to $end_count shown.<br>\n";

if ($totalrows - $end_count > $pagelimit) {
   $var2 = $pagelimit;
} elseif ($totalrows - $end_count <= $pagelimit) {
   $var2 = $totalrows - $end_count;
}

$space = "&nbsp;";

// previous link

if ($page>1) {
        echo "« <a href=" . $PHP_SELF . "?page=".($page-1)."' class=main>Previous" . $space . $pagelimit . "</a>" . $space . $space . "";
    }

// next link (make sure to change yourpage.php to the name of your page)

if ($page<$pagenums) {
    echo "" . $space . $space . $space . $space . " <a href='" . $PHP_SELF . "?page=".($page+1)."' class=main>Next " . $var2 . "</a> »";
   }

$result = mysql_query("select * from ".$prefix."_lista_de_juegos LIMIT $start,$pagelimit") or die("error"); // this needs the limit

// NOW DISPLAY YOUR RESULTS HOWEVER YOU WANT. wILL DISPLAY 25 RESULTS PER PAGE

Now just display the results however you want.

Cgraz

[Jungl3M4n]

Please take a look at this code:

$offset=2;
                if (!isset($min)) $min=0;
                if (!isset($max)) $max=$min+$offset;

$result = sql_query("select * from ".$prefix."_lista_de_juegos order by jid DESC limit $min,$offset",$dbi);
if(!$result) die(_ERROR."<br>");
$nrows  = sql_num_rows($result, $dbi);
$x=0;
while (list($jid, $jnombre, $jgenero, $jreview, $jdcreenshot, $jdemo) = sql_fetch_row($result, $dbi)) 
	echo "<center><table width=\"90%\" border=\"0\" cellspacing=\"4\" cellpadding=\"0\" align=\"center\">\n<tr>\n<td>".$jnombre."</td>\n<td>".$jgenero."</td>\n<td>".$jreview."</td>\n<td>".$jscreenshot."</td>\n<td>".$jdemo."</td>\n</tr>\n</table></center>";
	echo "<center>";

            $prev=$min-$offset;
            if ($prev>=0) {
                    print "<a href=\"DONTKNOW\">";
                    print "<img src=\"images/buttons/previous.gif\" border=\"0\" title=\""._PREV." "._RESULTS."\"></a>&nbsp;&nbsp;&nbsp;&nbsp;";
            }
            $next=$min+$offset;
	if ($x>=2) {
                    print "<a href=\"DONTKNOW\">";
                    print "<img src=\"images/buttons/next.gif\" border=\"0\" title=\""._NEXT." "._RESULTS."\"></a>";
            }
echo "</center>";

Sorry but your code doesnt did the trick.

the code i'm posting show the results as far as now i only have 5 records added to the database i'm testing with a limit of 2 per page so i can get 3 result pages.

i really dont know how nor where to add the code to do the prev/next buttons i'm wondering if you with my code can help me out.

the mysql connection is done in some code that is not shown because is a global thing in phpnuke.

I just want to add the next and previous link, can you take a look at the code and replace what is needed to be replaced ?

Thanks a lot in advance,

Best regards,