Jump to content

SELECT COUNT (*) Help

phpwolf

By phpwolf
in PHP Coding Help

Recommended Posts

phpwolf Newbie

phpwolf

Posted
Posted

Hello

 

I looked up some other pagination posts here on phpfreaks,

and it might be the

 

$_SESSION['mysqlquery'] = "SELECT COUNT(*) FROM mysqldbtable";

 

statement that is not constructed correctly, but im not sure.

 

Once again, im trying to get the script to only make pagelinks for the rows in the 2nd mysql_query.

 

 

Here is the search and pagination code again:

 

 

<?php

session_start();

$sess = session_id();

var_dump($sess);

 

 

?>

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1 Transitional//EN"

"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<head>

 

 

</head>

 

 

 

<body>

 

 

 

<p>

 

<form method="post" action="http://localhost/search.php" name="searchengine">

 

 

Search:

<input name="search" size="20" maxlength="80">

 

</input>

 

 

 

 

 

<p>

Display Rows On Page:

 

<select name="rowsperpage">

<option value="10" selected="">10</option>

<option value="20">20</option>

<option value="30">30</option>

<option value="40">40</option>

<option value="50">50</option>

<option value="100">100</option>

</select>

 

</p>

 

 

<div id="selectsortorder">

Sort Order:

 

<select name="sortorder">

<option value="ID ASC">A - Z</option>

<option value="ID DESC">Z - A</option>

 

</select>

 

</div>

 

<button  TYPE="submit" NAME="submitform">SEARCH!

 

 

</button>

 

 

 

 

</form>

 

 

 

 

 

<?php

 

 

 

 

$conn = mysql_connect('localhost','root','') or trigger_error("SQL", E_USER_ERROR);

$db = mysql_select_db('mysqldb',$conn) or trigger_error("SQL", E_USER_ERROR);

 

 

 

 

if (isset($_POST['submitform']))  //  If SEARCH! button is pressed, then...

{

 

unset($_SESSION);  //  unset the previous sessionvariables.

$rowsperpage= @$_POST['rowsperpage'];//  load new $rowsperpage only when SEARCH! Button is pressed.

$sortorder = @$_POST['sortorder'];  //  load new $sortorderperpage only when SEARCH! Button is pressed.

 

}

 

 

 

 

 

$_SESSION['rowsperpage'] = $rowsperpage;

$_SESSION['sortorder'] = $sortorder;

 

 

 

 

 

if(!isset($_SESSION['searchvar']))

 

{

 

$searchvar = $_POST['search'];

 

}

 

 

elseif(isset($_SESSION['searchvar']))

 

{

 

$searchvar = $_SESSION['searchvar'];

 

}

 

 

 

 

 

$_SESSION['searchvar'] = $searchvar;

 

 

var_dump($_SESSION['searchvar']);

 

 

 

 

 

 

 

 

 

// find out how many rows are in the table

$_SESSION['mysqlquery'] = "SELECT COUNT(*) FROM mysqldbtable";

$result = mysql_query($_SESSION['mysqlquery'], $conn) or trigger_error("SQL", E_USER_ERROR);

$r = mysql_fetch_row($result);

$numrows = $r[0];

 

// number of rows to show per page 

 

 

// find out total pages

$totalpages = ceil($numrows / $_SESSION['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) * $_SESSION['rowsperpage'];

 

 

$_SESSION['offset'] = $offset;

 

 

 

$_SESSION['mysqlquery'] = "SELECT ID, CLIENTNAMES FROM mysqldbtable WHERE `CLIENTNAMES` REGEXP '$_SESSION[searchvar]' ORDER BY $_SESSION[sortorder] LIMIT $_SESSION[offset], $_SESSION[rowsperpage]";

 

$_SESSION['mysqlquery'];

 

 

 

 

 

 

 

 

 

 

$_SESSION ['result111'] = mysql_query($_SESSION['mysqlquery'], $conn) or trigger_error("SQL", E_USER_ERROR);

 

// while there are rows to be fetched...

while ($list = mysql_fetch_assoc($_SESSION ['result111'])) {

  // echo data

  echo $list['CLIENTNAMES'] . "<br />";

} // end while

 

// range of num links to show

$range = 5;

 

// 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'><<</a> ";

  // get previous page num

  $prevpage = $currentpage - 1;

  // show < link to go back to 1 page

  echo " <a href='{$_SERVER['PHP_SELF']}?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']}?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'>></a> ";

  // echo forward link for lastpage

  echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";

} // end if

 

 

 

 

 

mysql_close();

 

 

?>

 

 

</body>

 

 

 

 

Any help will be greatly appreciated

Thank you

 

Link to comment
https://forums.phpfreaks.com/topic/193333-select-count-help/
Share on other sites
phpwolf Newbie

phpwolf

Posted
  • Author
Posted

Hi nilansanjaya,

 

2nd mysql_query is this one:

 

$_SESSION['mysqlquery'] = "SELECT ID, CLIENTNAMES FROM mysqldbtable WHERE `CLIENTNAMES` REGEXP '$_SESSION[searchvar]' ORDER BY $_SESSION[sortorder] LIMIT $_SESSION[offset], $_SESSION[rowsperpage]";

 

The problem is that the script makes pagelinks for all rows in the databasetable, and not as it should, which is only make links for the results in the $_SESSION['mysqlquery'] above.

Link to comment
https://forums.phpfreaks.com/topic/193333-select-count-help/#findComment-1017978
Share on other sites
phpwolf Newbie

phpwolf

Posted
  • Author
Posted

 

Hey guys here's the code again, color formatted.

 

 

Im not getting anywhere with this script, I've been working on it for a few weeks already, but can't solve it.

 

 

I've changed the line 

$_SESSION['mysqlquery'] = "SELECT COUNT(*) FROM mysqldbtable";

a few times, but this doesn't correct the problem,

 

Is there any other way to make the script paginate correctly???

 

Any help is greatly appreciated

Thank you

 

<?php
session_start();
$sess = session_id();
var_dump($sess);


?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<head>


</head>



<body>



<p>

<form method="post" action="http://localhost/search.php" name="searchengine">


Search:
<input name="search" size="20" maxlength="80">

</input>





<p>
Display Rows On Page:

<select name="rowsperpage">
<option value="10" selected="">10</option>
<option value="20">20</option>
<option value="30">30</option>
<option value="40">40</option>
<option value="50">50</option>
<option value="100">100</option>
</select>

</p>


<div id="selectsortorder">
Sort Order:

<select name="sortorder">
<option value="ID ASC">A - Z</option>
<option value="ID DESC">Z - A</option>

</select>

</div>

<button  TYPE="submit" NAME="submitform">SEARCH!


</button> 




</form>




<?php




$conn = mysql_connect('localhost','root','') or trigger_error("SQL", E_USER_ERROR);
$db = mysql_select_db('mysqldb',$conn) or trigger_error("SQL", E_USER_ERROR);




if (isset($_POST['submitform']))  //  If SEARCH! button is pressed, then...
{

unset($_SESSION);  //  unset the previous sessionvariables.
$rowsperpage= @$_POST['rowsperpage'];//  load new $rowsperpage only when SEARCH! Button is pressed.
$sortorder = @$_POST['sortorder'];  //  load new $sortorderperpage only when SEARCH! Button is pressed.

} 





$_SESSION['rowsperpage'] = $rowsperpage;
$_SESSION['sortorder'] = $sortorder;





if(!isset($_SESSION['searchvar']))

{

$searchvar = $_POST['search'];

}


elseif(isset($_SESSION['searchvar']))

{

$searchvar = $_SESSION['searchvar'];

}





$_SESSION['searchvar'] = $searchvar;


var_dump($_SESSION['searchvar']);









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

// number of rows to show per page   


// find out total pages
$totalpages = ceil($numrows / $_SESSION['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) * $_SESSION['rowsperpage'];


$_SESSION['offset'] = $offset;



$_SESSION['mysqlquery'] = "SELECT ID, CLIENTNAMES FROM mysqldbtable WHERE `CLIENTNAMES` REGEXP '$_SESSION[searchvar]' ORDER BY $_SESSION[sortorder] LIMIT $_SESSION[offset], $_SESSION[rowsperpage]";

$_SESSION['mysqlquery'];










$_SESSION ['result111'] = mysql_query($_SESSION['mysqlquery'], $conn) or trigger_error("SQL", E_USER_ERROR);

// while there are rows to be fetched...
while ($list = mysql_fetch_assoc($_SESSION ['result111'])) {
   // echo data
   echo $list['CLIENTNAMES'] . "<br />";
} // end while

// range of num links to show
$range = 5;

// 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'><<</a> ";
   // get previous page num
   $prevpage = $currentpage - 1;
   // show < link to go back to 1 page
   echo " <a href='{$_SERVER['PHP_SELF']}?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']}?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'>></a> ";
   // echo forward link for lastpage
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";
} // end if





mysql_close();


?>


</body>

Link to comment
https://forums.phpfreaks.com/topic/193333-select-count-help/#findComment-1018029
Share on other sites
phpwolf Newbie

phpwolf

Posted
  • Author
Posted

Solved It!

 

After viewing the MYSQL part of the forum,

i've found this to work:

Change line:

 

$_SESSION['mysqlquery'] = "SELECT COUNT(*) FROM mysqldbtable";

 

to:

 

$_SESSION['mysqlquery'] = "SELECT COUNT ( CLIENTNAMES ) FROM mysqldbtable WHERE `CLIENTNAMES` REGEXP '$_SESSION[searchvar]'";

 

This isn't really ideal coz im trying to implement a multi-field search script and this complicates multi search pagination.

Link to comment
https://forums.phpfreaks.com/topic/193333-select-count-help/#findComment-1018232
Share on other sites

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.