PHP - Combine Sql Query For Pagination

Hi im having a little trouble with pagination its not letting me use this username verification as well as the forum?
Any help would be appreciated!

<? if(!session_is_registered("username")){
echo "<p>&nbsp;</p>
<form name='login' method='post' action='register.php'>
  <table border='0' width='400' align='center'>
                    <tr>
                        <td width='90'>Username:</td>
                      <td width='300'><input type='text' name='username' /></td>
                    </tr>
                <tr>
                    <td width='90'>Password:</td>
                    <td width='300'><input type='password' name='password' /></td>
                </tr>
                <tr>
                    <td width='90'>Email:</td>
                  <td width='300'><input type='text' name='email' maxlength='100' /></td>
                </tr>
            <tr>
               <td>Gender: </td><td><input name='gender' type='radio' value='male' /> Male
               <input name='gender' type='radio' value='female' /> Female</td></tr>
                <tr>
                    <td width='90'>&nbsp;</td>
                        <td width='300'>
                            <p align='left'><input type='submit' name='submit' value='Submit'></p>                  </td>
                </tr>
  </table>
</form>";

}else{

include 'connect.php';

// find out how many rows are in the table
$sql = "SELECT COUNT(*) FROM newtopic";
$result = mysql_query($sql) or trigger_error("SQL", E_USER_ERROR);
$r = mysql_fetch_row($result);
$numrows = $r[0];

// number of rows to show per page
$rowsperpage = 10;
// find out total pages
$totalpages = ceil($numrows / $rowsperpage);

// get the current page or set a default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
   // cast var as int
   $currentpage = (int) $_GET['currentpage'];
} else {
   // default page num
   $currentpage = 1;
} // end if

// if current page is greater than total pages...
if ($currentpage > $totalpages) {
   // set current page to last page
   $currentpage = $totalpages;
} // end if
// if current page is less than first page...
if ($currentpage < 1) {
   // set current page to first page
   $currentpage = 1;
} // end if

// the offset of the list, based on current page
$offset = ($currentpage - 1) * $rowsperpage;

// get the info from the db
$sql = "SELECT * FROM newtopic LIMIT $offset, $rowsperpage";
$result = mysql_query($sql) or trigger_error("SQL", E_USER_ERROR);

echo "<table width='371' border='0' align='center'>
  <tr>
    <td height='50' colspan='2'><b>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Fight Talk </b><input name='forumtype' type='hidden' value='fighttalk' /></td>
  </tr>
  <tr>
    <td width='160' height='50' align='center' colspan='2'><a href='fighttalknewtopic.php'># New Topic</a></td>
  </tr>
  <tr>
  <td><b>Username:</b></td><td><b>Message:</b></td>
  </tr>";
while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . " <b> " . $row['username'] . "</b><br />" . $row['date'] . " : " . $row['time'] . "</td>";;
  echo "</td>";
  echo "<td>" . $row['title'] . "</td>";
  echo "</tr><tr>"; 
  echo "<td colspan='2'>" . $row['message'] . "</td>";
  echo "</tr>";
  }
  echo "</table>";
 
/******  build the pagination links ******/
// range of num links to show
$range = 3;

// if not on page 1, don't show back links
if ($currentpage > 1) {
   // show << link to go back to page 1
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'>First</a> ";
   // get previous page num
   $prevpage = $currentpage - 1;
   echo " - ";
   // show < link to go back to 1 page
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'>Previous</a> ";
} // end if

// loop to show links to range of pages around current page
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
   // if it's a valid page number...
   if (($x > 0) && ($x <= $totalpages)) {
      // if we're on current page...
      if ($x == $currentpage) {
         // 'highlight' it but don't make a link
         echo " [<b>$x</b>] ";
      // if not current page...
      } else {
         // make it a link
         echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
      } // end else
   } // end if
} // end for
                 
// if not on last page, show forward and last page links       
if ($currentpage != $totalpages) {
   // get next page
   $nextpage = $currentpage + 1;
    // echo forward link for next page
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>Next</a> ";
   echo " - ";
   // echo forward link for lastpage
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>Last</a> ";
} // end if
/****** end build pagination links ******/
?>

Sorry about the long code but this is the part it errors with:

<? if(!session_is_registered("username")){
echo "<p>&nbsp;</p>
<form name='login' method='post' action='register.php'>
  <table border='0' width='400' align='center'>
                    <tr>
                        <td width='90'>Username:</td>
                      <td width='300'><input type='text' name='username' /></td>
                    </tr>
                <tr>
                    <td width='90'>Password:</td>
                    <td width='300'><input type='password' name='password' /></td>
                </tr>
                <tr>
                    <td width='90'>Email:</td>
                  <td width='300'><input type='text' name='email' maxlength='100' /></td>
                </tr>
            <tr>
               <td>Gender: </td><td><input name='gender' type='radio' value='male' /> Male
               <input name='gender' type='radio' value='female' /> Female</td></tr>
                <tr>
                    <td width='90'>&nbsp;</td>
                        <td width='300'>
                            <p align='left'><input type='submit' name='submit' value='Submit'></p>                  </td>
                </tr>
  </table>
</form>";

}else{

Also does anyone know how I can center the the "First - Previous 1-2-3-4 Next - Last" part of the pagination script?
Thanks for the help

Can anyone explain what "of course you'll need to propagate the initial seed in the page requests" means in the following link?

http://stackoverflow.com/questions/4192284/random-values-in-mysql-query-with-pagination

Hi,

I am trying to create a view where the user can sort by any column.

What I have made so far works great, but when it comes to pagination, which I will be trying soon, I foresee a problem .

The thing is, my code gets data out of a database and puts it into a multidimensional array, which is sorted using afew array sorting functions.

It is a query that drives the main loop, and inside that loop are other queries that get various data from other tables, which is where the problem lies when coming to sort with pagination.

What I would like to know is how would I build a myslq query that can accomplish the same thing as my code, but do away with the need for the arrays?

My code is here, sorry about it being so long:


<?PHP



if($_POST['targetid']) {



$box = array() ;



if($_POST['type']) {

$extra = 'AND entity_details.typeRef = ?' ;

$targetType = $_POST['type'] ;

}



$sortMode = $_POST['sortmode'] ;



$targetUser = $_POST['targetid'] ;



include('pdoconnect.php') ;



$result = $dbh->prepare("SELECT entity_details.name, entity_contacts.name AS cName, entity_contacts.id, entity_details.countryRef

 FROM entity_details, entity_contacts

 WHERE entity_contacts.isPrimary = 1

 AND entity_contacts.entityRef = entity_details.id

 $extra

 AND entity_details.ownerRef = ?") ;

if($extra) {

$result->bindParam(1, $targetType, PDO::PARAM_INT) ;

$result->bindParam(2, $targetUser, PDO::PARAM_INT) ;

}

else 

$result->bindParam(1, $targetUser, PDO::PARAM_INT) ;



$result->execute() ;



$count = $result->rowCount() ;



if($count > 0) {

echo "Showing " ;

if($targetType == 2)

echo "prospects!" ;

elseif($targetType == 3)

echo "customers!" ;

elseif($targetType == 4)

echo "leads!" ;

else

echo "everything." ;

echo '<br />' ;

}

if($count > 0) {

$mode = $_GET['sort'] ;

echo "<div class='row'>\n" ;

echo "<div class='cell'>" ;

if($sortMode != 'name')

echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'name');\">Name</a>" ;

else

echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'Rrname');\">Name</a>" ;

echo "</div>" ;

echo "<div class='cell'>" ;

if($sortMode != 'contact')

echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'contact');\">Contact</a>" ;

else

echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'Rrcontact');\">Contact</a>" ;

echo "</div>" ;

echo "<div class='cell'>" ;

if($sortMode != 'email')

echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'email');\">Email</a>" ;

else

echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'Rremail');\">Email</a>" ;

echo "</div>" ;

echo "<div class='cell'>" ;

if($sortMode != 'tel')

echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'tel');\">Tel</a>" ;

else

echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'Rrtel');\">Tel</a>" ;

echo "</div>" ;

echo "<div class='cell'>" ;

if($sortMode != 'payment')

echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'payment');\">Payment Terms</a>" ;

else

echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'Rrpayment');\">Payment Terms</a>" ;

echo "</div>" ;

echo "<div class='cell' style='width:30px;'>" ;

if($sortMode != 'currency')

echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'currency');\">Cur</a>" ;

else

echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'Rrcurrency');\">Cur</a>" ;

echo "</div>" ;

echo "<div class='cell'>" ;

if($sortMode != 'country')

echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'country');\">Country</a>" ;

else

echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'Rrcountry');\">Country</a>" ;

echo "</div>" ;

echo "<div class='cell' style='width:100px;'>" ;

if($sortMode != 'lastc')

echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'lastc');\">Last Contacted</a>" ;

else

echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'Rrlastc');\">Last Contacted</a>" ;

echo "</div>" ;

echo "<div class='cell' style='width:100px;'>" ;

if($sortMode != 'lastm')

echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'lastm');\">Last Marketed</a>" ;

else

echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'Rrlastm');\">Last Marketed</a>" ;

echo "</div>" ;

echo "<div class='cell' style='width:100px;'>" ;

if($sortMode != 'lastd')

echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'lastd');\">Last Deal</a>" ;

else

echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'Rrlastd');\">Last Deal</a>" ;

echo "</div>" ;

echo "<div class='cell'  style='width:50px;'>" ;

if($sortMode != 'cltv')

echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'cltv');\">CLTV</a>" ;

else

echo "<a onclick=\"traderSelectCustomer('$targetUser', '$targetType', 'Rrcltv');\">CLTV</a>" ;

echo "</div>" ;

echo "</div>\n" ;

}

else {

echo "You have no " ;

if($targetType == 2)

echo "prospects!" ;

elseif($targetType == 3)

echo "customers!" ;

elseif($targetType == 4)

echo "leads!" ;

else

echo "nothing!" ;

}





while($row = $result->fetch(PDO::FETCH_ASSOC)) {

$id = $row['id'] ;

$countryid = $row['countryRef'] ;



// Get primary contact data.

$resultName = $dbh->prepare("SELECT entity_contacts_emails.email, entity_contacts_telephones.tel

 FROM entity_contacts_emails, entity_contacts_telephones

 WHERE entity_contacts_emails.contactRef = ?

 AND entity_contacts_telephones.contactRef = ?

 LIMIT 1") ;

$resultName->bindParam(1, $id, PDO::PARAM_INT) ;

$resultName->bindParam(2, $id, PDO::PARAM_INT) ;

$resultName->execute() ;



$rowName = $resultName->fetch(PDO::FETCH_ASSOC) ;

///////////////////////////



// Get country.

$resultCountry = $dbh->prepare("SELECT country

 FROM countries

 WHERE id = ? LIMIT 1") ;

$resultCountry->bindParam(1, $countryid, PDO::PARAM_INT) ;

$resultCountry->execute() ;



$rowCountry = $resultCountry->fetchColumn() ;

///////////////////////////



// Get currencies dealt with.

$resultCurrencies = $dbh->prepare("SELECT currencies.symbol

 FROM entity_currencies_dealt, currencies

 WHERE entity_currencies_dealt.currencyRef = currencies.id

 AND entity_currencies_dealt.entityRef = ?") ;

$resultCurrencies->bindParam(1, $id, PDO::PARAM_INT) ;

$resultCurrencies->execute() ;



while($rowCurrencies = $resultCurrencies->fetch(PDO::FETCH_ASSOC)) {

$currencyString .= $rowCurrencies['symbol'] . " " ;

}

///////////////////////////



// Get payment terms.

$resultTerms = $dbh->prepare("SELECT entity_payment_terms.term

 FROM entity_details, entity_payment_terms

 WHERE entity_details.paymentTermsRef = entity_payment_terms.id

 AND entity_details.id = ?") ;

$resultTerms->bindParam(1, $id, PDO::PARAM_INT) ;

$resultTerms->execute() ;



$resultTerms = $resultTerms->fetchColumn() ;

///////////////////////////

// Get last contacted.

$resultLastCon = $dbh->prepare("SELECT DATE(date)

 FROM contact_method_history

 WHERE id = ?

 ORDER BY date

 LIMIT 1") ;

$resultLastCon->bindParam(1, $id, PDO::PARAM_INT) ;

$resultLastCon->execute() ;



$resultLastCon = $resultLastCon->fetchColumn() ;

///////////////////////////



// Get last enquired.

$resultLastEnq = $dbh->prepare("SELECT DATE(dateCreated)

 FROM entity_enquiries

 WHERE entityRef = ?

 ORDER BY DATE(dateCreated)

 LIMIT 1") ;

$resultLastEnq->bindParam(1, $id, PDO::PARAM_INT) ;

$resultLastEnq->execute() ;



$resultLastEnq = $resultLastEnq->fetchColumn() ;

///////////////////////////





array_push($box, array(rawurldecode($row['name']), rawurldecode($row['cName']), $rowName['email'], $rowName['tel'], $resultTerms, trim($currencyString), $rowCountry, $resultLastCon, $resultLastEnq)) ;



$currencyString = '' ;



}





}



$i = 0 ;



function subval_sort($a, $subkey, $mode) {

foreach($a as $k=>$v) {

$b[$k] = strtolower($v[$subkey]);

}

if(strstr($mode, 'Rr'))

arsort($b);

else

asort($b);

foreach($b as $key=>$val) {

$c[] = $a[$key];

}

return $c;

}



if($sortMode == 'name' || $sortMode == 'Rrname')

$x = 0 ;

if($sortMode == 'contact' || $sortMode == 'Rrcontact')

$x = 1 ;

if($sortMode == 'email' || $sortMode == 'Rremail')

$x = 2 ;

if($sortMode == 'tel' || $sortMode == 'Rrtel')

$x = 3 ;

if($sortMode == 'payment' || $sortMode == 'Rrpayment')

$x = 4 ;

if($sortMode == 'currency' || $sortMode == 'Rrcurrency')

$x = 5 ;

if($sortMode == 'country' || $sortMode == 'Rrcountry')

$x = 6 ;

if($sortMode == 'lastc' || $sortMode == 'Rrlastc')

$x = 7 ;

if($sortMode == 'laste' || $sortMode == 'Rrlaste')

$x = 8 ;

if($count > 0)

$box = subval_sort($box, $x, $sortMode); 



foreach($box as $row) {

echo "<div class='row'>\n" ;

echo "<div class='cell'>" ;

echo $row[0] ;

echo "</div>" ;

echo "<div class='cell'>" ;

echo $row[1] ;

echo "</div>" ;

echo "<div class='cell'>" ;

echo $row[2] ;

echo "</div>" ;

echo "<div class='cell'>" ;

echo $row[3] ;

echo "</div>" ;

echo "<div class='cell'>" ;

echo $row[4] ;

echo "</div>" ;

echo "<div class='cell' style='width:30px;'>" ;

echo $row[5] ;

echo "</div>" ;

echo "<div class='cell'>" ;

echo $row[6] ;

echo "</div>" ;

echo "<div class='cell'>" ;

echo $row[7] ;

echo "</div>" ;

echo "<div class='cell' style='width:100px;'>" ;

echo $row[8] ;

echo "</div>" ;

echo "<div class='cell' style='width:100px;'>" ;

echo $row[9] ;

echo "</div>" ;

echo "<div class='cell' style='width:100px;'>" ;

echo $row[10] ;

echo "</div>" ;

echo "<div class='cell' style='width:50px;'>" ;

echo $row[11] ;

echo "</div>" ;

echo "</div>\n" ;

}



?>


I know maybe it can be done using many subqueries(?) but I am pretty average at MySQL.

Can anyone tell me how?

I want a simple function that converts a sql query into two queries for pagination purposes.

So if the sql query is "SELECT `Name` FROM table1"

I would like to perform:

"SELECT COUNT(*) FROM table 1"

and

"SELECT `Name` FROM table1 LIMIT 10, 20"

(example)

(1) Would this work for most basic queries? (obviously if LIMIT wasn't already used)?

(2) What command would I use to convert "SELECT `Name` FROM table1" to "SELECT COUNT(*) FROM table 1"

I am aware there is more involved than this, but am just interested in the query side of things.

Hi there

I adapted the pagination tutorial from here on PHP Freaks so that it can deal with a db query.

The pagination itself works just fine but I have a small issue, hopefully someone can help me please.

I have set up the query so that it won't allow blank searches. The script works perfectly well without the pagination.

With the pagination, the script now returns a few results as well as the 'you didn't enter a search-term' message - can someone help me tidy it up please? I'm guessing that I've put the search=$search term in too many places in the pagination script.

This is the query

Code: [Select]
if (isset($_GET['search'])) {
$searchTerms = trim($_GET['search']);
$searchTerms = strip_tags($searchTerms); // remove any html/javascript.
   
if (strlen($searchTerms) < 1) {
      $error[] = '<div class="messagebox">You didn\'t specify a search term</div></div>';
      $error[] = '<div class="small"><a href="search2.php"><p>Search Again?</p></a></div>';
   } else {
      $searchTermDB = mysql_real_escape_string($searchTerms); // prevent sql injection.
   }

$sql = "SELECT * FROM pro_words WHERE word LIKE '%{$searchTermDB}%' ORDER BY word LIMIT $offset, $rowsperpage";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$num_rows = mysql_num_rows($result);

//display results

if (mysql_num_rows($result) < 1) {
         $error[] = '<div class="messagebox">Sorry, your search term yielded no results.</div>';
         $error[] = '<div class="small"><a href="search.php"><p>Search Again?</p></a></div>';
      } else {
      echo '<div class="messagebox">Here are your search results.</div>';

while ($list = mysql_fetch_array($result)) {
   // echo data
echo '<div class="small"><a href="word.php?w=' . $list['word'] . '">' . $list['word'] . '</a></div>'; 
  } //end while

  }
}

This is the pagination

Code: [Select]
// range of num links to show
$range = 3;

// if not on page 1, don't show back links
if ($currentpage > 1) {
   // show << link to go back to page 1
   echo " <a href='{$_SERVER['PHP_SELF']}?search=$search&currentpage=1'><<</a> ";
   // get previous page num
   $prevpage = $currentpage - 1;
   // show < link to go back to 1 page
   echo " <a href='{$_SERVER['PHP_SELF']}?search=$search&currentpage=$prevpage'><</a> ";
} // end if

// loop to show links to range of pages around current page
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
   // if it's a valid page number...
   if (($x > 0) && ($x <= $totalpages)) {
      // if we're on current page...
      if ($x == $currentpage) {
         // 'highlight' it but don't make a link
         echo " [<b>$x</b>] ";
      // if not current page...
      } else {
         // make it a link
         echo " <a href='{$_SERVER['PHP_SELF']}?search=$search&currentpage=$x'>$x</a> ";
      } // end else
   } // end if
} // end for
                 
// if not on last page, show forward and last page links       
if ($currentpage != $totalpages) {
   // get next page
   $nextpage = $currentpage + 1;
    // echo forward link for next page
   echo " <a href='{$_SERVER['PHP_SELF']}?search=$search&currentpage=$nextpage'>></a> ";
   // echo forward link for lastpage
   echo " <a href='{$_SERVER['PHP_SELF']}?search=$search&currentpage=$totalpages'>>></a> ";
}

Thanks in advance for any help.