Help: Making of a Guestbook

[CoderJohan]

I'm in bit of a trouble... I have made a Guestbook but i do not wish it to have miles and miles of msg... I want it to create new site if there is more then 30 msg

at the bottom it should say
Page 1,2,3,4,5,6,7,8,9,10

so if i have more then 300 msgs there should be 10 pages

How do I do this the easiest way? i know there is a way to do this with the database

I run the lates PHP version and MYSQL
please help

//Johan

[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 = "30";

// 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's all commented. You should have no problem following it and integrating with your guestbook.

Cgraz

[CoderJohan]

Hello 🙂

I tried the code out but i didn't got it to work... It wrote out the number 1 at the bottom but no Next or Previous... ? i don't know what i did wrong?

Where should the LIMIT code be in this code so it initiate start and so...? i tried it your way but it didn't work

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

right under pagelimit but then it didn't work?
What am i doing wrong?

[cgraz]

Do you have more than 30 resulst?

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

that's where you change how many results per page. The first query just allows you to figure out how many pages you'll ned

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

The second query, the one that actually uses the LIMIT is at the end.

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

The only thing you need to change is $table to your table name, the $pagelimit variable to how many results per page, and the mysql specs. Then, change yourpage.php to whatever your script is called. Aside from that, the code has been tested and used by others with no problem. If you continue having problems, post your code and i'll help you further.

Cgraz

[CoderJohan]

Sugoi!!!!
It works 🙂 Thanks man you have saved my day 🙂
//Johan

[Hoangsta]

i tried your code and it is displaying all the records. i set it to have a limit of 5, but when searching, it display all the records.

my code below from searching2.php

// Database Connection 
include 'db.php';
$table = "BusinessName"; 
if (!$db)
{
echo "Error: Could not connect to database. Please try again later.";
exit;
}
if ($searchterm = $_GET['searchterm']){			
	trim($searchterm);
	$searchterm = addslashes($searchterm);
}
// set number of results to display per page 
$pagelimit = "5"; 

// run query (change yourtable to the name of your table) 
   $strSQL = "SELECT * FROM $table WHERE business_name LIKE '%$searchterm%' 
		                  ORDER BY business_name"; 

$result = mysql_query($strSQL) or die(mysql_error());
// count number of matches 
   $totalrows = mysql_num_rows($result); 

// 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 "Your search for <b>\"" .$searchterm. "\"</b> returns <b>\"".$totalrows."\"</b> matches<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 searching2.php to the name of your page) 
if ($page>1) { 
        echo "« <a href='searching2.php?page=".($page-1)."' class=main>Previous" . $space . $pagelimit . "</a>" . $space . $space . ""; 
    } 

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

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


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

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


for ($i=0; $i <$totalrows; $i++) 
{
$row = mysql_fetch_array($result);
// Build your formatted results here. 
echo "<p><strong>".($i+1).".  ";	
echo "<a href=\"http://www.business.com\">";
echo htmlspecialchars( stripslashes($row["business_name"]));
echo "</a></strong><br> ";
echo "<a href=\"http://www.maporama.com\" target=\"_blank\">Map it!</a>";
echo "</a>";
echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<a href=\"http://{$row['webpagelink']}\" target=\"_blank\">";
echo htmlspecialchars (stripslashes($row["webpagelink"]));
echo "</a><br />";
echo htmlspecialchars (stripslashes($row["business_address"]));
echo ", ";
echo htmlspecialchars (stripslashes($row["bus_city"]));
echo ", ";
echo htmlspecialchars (stripslashes($row["businessacct_state_country"]));
echo " ";
echo htmlspecialchars (stripslashes($row["bus_zip"]));
echo "<br>Phone:<strong>  ";
echo htmlspecialchars (stripslashes($row["business_phone"]));
echo "</strong>&nbsp;&nbsp;Fax:  ";
echo htmlspecialchars (stripslashes($row["business_fax"]));
echo "<p></p></p>";
}
/* 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 = "SELECT * FROM $table LIMIT $start,$pagelimit"; 

   $result = mysql_query($strSQL) or die(mysql_error());

// 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. 

[cgraz]

you need to execute

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

   $result = mysql_query($strSQL) or die(mysql_error()); 

before you start outputting information but after you've made all your calculations. If you use it before your calculations then you'll have to use a different variable name.

And from looking at your query, I'd say you want that above line of code to look more like this:

   $strSQL = "SELECT * FROM $table WHERE business_name LIKE '%$searchterm%' 
                          ORDER BY business_name LIMIT $start,$pagelimit";  

$result = mysql_query($strSQL) or die(mysql_error());

Cgraz

[Hoangsta]

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

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

$strSQL = "SELECT * FROM $table WHERE business_name LIKE '%$searchterm%' 
   ORDER BY business_name 
   LIMIT $start,$pagelimit"; 

   $result = mysql_query($strSQL) or die(mysql_error());
for ($i=0; $i <$totalrows; $i++) 
{
$row = mysql_fetch_array($result);
// Build your formatted results here. 
echo "<p><strong>".($i+1).".  ";	
echo "<a href=\"http://www.yourcompany.com\">";
echo htmlspecialchars( stripslashes($row["business_name"]));
echo "</a></strong><br> ";
echo "<a href=\"http://www.maporama.com\" target=\"_blank\">Map it!</a>";
echo "</a>";
echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<a href=\"http://{$row['webpagelink']}\" target=\"_blank\">";
echo htmlspecialchars (stripslashes($row["webpagelink"]));
echo "</a><br />";
echo htmlspecialchars (stripslashes($row["business_address"]));
echo ", ";
echo htmlspecialchars (stripslashes($row["bus_city"]));
echo ", ";
echo htmlspecialchars (stripslashes($row["businessacct_state_country"]));
echo " ";
echo htmlspecialchars (stripslashes($row["bus_zip"]));
echo "<br>Phone:<strong>  ";
echo htmlspecialchars (stripslashes($row["business_phone"]));
echo "</strong>&nbsp;&nbsp;Fax:  ";
echo htmlspecialchars (stripslashes($row["business_fax"]));
echo "<p></p></p>";
}

is that what you think my code should look like...? because if that is the case, it's showing all of my records, (8), but only 5 records contain information while record 6,7,8 just shows

6.
Map it!

, ,
Phone: Fax:

7.
Map it!

, ,
Phone: Fax:

8.
Map it!

, ,
Phone: Fax: