[PHP] Grab name from mysql and but display in date order with next and prev

Cools · May 4, 2011

wondering if any one could help..

wondering of the any's......

i want to pull a student info from mysql DB, searching for Studen by name but output ordered by date.

The code is below... if there's a simple way of doing it.. im all up for it.. it's a OLD bit of code i found and it works so im happy.. it's not for public use, just for in house for staff.

-------

this line

$result = mysql_query("SELECT * FROM $tablen ORDER BY date DESC LIMIT $start, $limit");// Normal query

if i add

WHERE users_name='$users_name'

$result = mysql_query("SELECT * FROM $tablen WHERE users_name='$users_name' ORDER BY date DESC LIMIT $start, $limit");// Normal query

i can search for name and it displays in date order for the next and prev does not work.

Thanks.





Add a commnet.









include("conf.inc.php"); // Includes the db and form info.
$tablen = $con;//Sets the table name
$users_name=$_POST['users_name'];
//Pagination
$limit = 10;// Set the limit of results per page
$page = $_GET['page'];// Sets the page, the default is ?page=
$totalrows = mysql_num_rows(mysql_query("SELECT id FROM $tablen"));// Counts the total rows
if(empty($page))// If the page is empty
{
$page = '1';// Page is 1
};
$start = ($page-1)*$limit;
$start = round($start,0);// Sets the start
$result = mysql_query("SELECT * FROM $tablen ORDER BY date DESC LIMIT $start, $limit");// Normal query
while ($r = mysql_fetch_array($result))
{
echo nl2br("      </pre><table align='\"center\"' width='\"50%\"' border='\"1\"' cellpadding='\"1\"' cellspacing='\"0\"'> 
       
         ".$r["users_name"]." - ".$r["lesson"]."  

".$r["comment"]."
             
             ".$r["date"]."    By  ".$r["stuser"]."  

   ");
           
echo (" Delete    |    Edit  ");

echo "
 

           
       
     </table><br><br>";// Echoes the content<br>};<br>$totalusers = mysql_num_rows(mysql_query("SELECT id FROM $tablen"));<br>// echo "<br>Total posts: $totalusers<br>";// Echoes the total users<br>$totalpages = $totalrows / $limit;// Get the total pages<br>$totalpages = ceil($totalpages);// Rounds the total pages to the highest possible numer<br>if($page == 1)// If the page is 1<br>{<br>$actualpage = '1';// Sets the actual page<br>}<br>else// Else<br>{<br>$actualpage = "$page";// The page is the one it's specified in ?page=<br>}<br>if($page < $totalpages)// If the actual page is smaller than the totalpages<br>{<br>$nv = $page+1;// Sets the next variable<br>$pv = $page-1;// Sets the preview variable<br>$nextpage = "<a href="?page=%24nv" rel="">></a>";// Sets the next page<br>$prevpage = "<a href="?page=%24pv" rel=""><</a>";// Sets the prev page<br>$firstpage = "<a href="%5C%22?page=1%5C%22" rel="">«</a>";// Sets the first page<br>$finalpage = "<a href="%5C%22?page=%24totalpages%5C%22" rel="">»</a>";// Sets the final page<br>}<br>if($page == '1')<br>{<br>$nv = $page+1;<br>$nextpage = "<a href="?page=%24nv" rel="">></a>";<br>$prevpage = "<";<br>$firstpage = "«";<br>$finalpage = "<a href="%5C%22?page=%24totalpages%5C%22" rel="">»</a>";<br>}elseif($page == $totalpages){<br>$pv = $page-1;<br>$nextpage = ">";<br>$prevpage = "<a href="?page=%24pv" rel=""><</a>";<br>$firstpage = "<a href="%5C%22?page=1%5C%22" rel="">«</a>";<br>$finalpage = "»";<br>}<br>if($totalpages == '1' || $totalpages == '0'){<br>	$nextpage = ">";<br>	$prevpage = "<";<br>	$firstpage = "«";<br>	$finalpage = "»";<br>}<br>echo "<p align='\"center\"' class='\"post-footer' align-center> $firstpage $prevpage Actual Page: $actualpage $nextpage $finalpage
Pages: $totalpages </p> ";<br><br>?><br><br><br><br><b

pcstru · May 4, 2011

The easiest way to achieve what you want is (I think) to determine if you have been passed $users_name and if you have, retrieve "select users_name from order by date" (no where clause) then loop through the results to find the name you are after at which point you hack/set $page to a value appropriate and then leave everything else to operate as normal. It's a bit of a hack and not very efficient but then a routine that does a full scan every time just to get the number of rows was never designed to be kind to disc spindles :-)!

CESIL · May 4, 2011

Your code has me confused...you seem to be counting the total of all rows, not just the ones matching the user you are searching for, is this not what is breaking the "Next/Prev" code?

Can you give details of the data you are searching and the layout of the results you want to see?

Cools · May 4, 2011

Your code has me confused...you seem to be counting the total of all rows, not just the ones matching the user you are searching for, is this not what is breaking the "Next/Prev" code?

Can you give details of the data you are searching and the layout of the results you want to see?

it's for SEN..

A comment is post in to the MySQL db with Values of : ID, Student_name , Staff_name, Date, Lesson and Comment.

so I want to get the same back out but in date order when I search for a student.

Other staff members need see the comments as there are added.. at the other end of the school saving on paper work....

CESIL · May 4, 2011

I think then that the problem is caused by counting all the rows in the table rather than the number that match the user name.

You have two variables (totalrows and totalusers) that use the same SQL query and therefore have the same value.

I think if you change this query to count IDs for rows that have the required user name in them you will get the next/prev to work...

Really I would need a sample data file to be able to help much more...

glennda · May 4, 2011

I think what you need is to do your mysql query then use while and mysql fetch array something like while($query = mysql_fetch_array($result))

this will then echo what you want for each

e.g if you create a table with an php echo $query['id'] and $query['comment'] in each column - it should then make a row for each entry that is returned.

Toby

Cools · May 4, 2011

I think what you need is to do your mysql query then use while and mysql fetch array something like while($query = mysql_fetch_array($result))

this will then echo what you want for each

e.g if you create a table with an php echo $query['id'] and $query['comment'] in each column - it should then make a row for each entry that is returned.

Toby

i dont get it..

does this part of the code not do that

$result = mysql_query("SELECT * FROM $tablen ORDER BY date DESC LIMIT $start, $limit");// Normal query 
while ($r = mysql_fetch_array($result)) 
{ 
   echo nl2br("      </pre><table align='\"center\"' width='\"50%\"' border='\"1\"' cellpadding='\"1\"' cellspacing='\"0\"'>  
        
         ".$r["users_name"]." - ".$r["lesson"]."  

".$r["comment"]." 
              
             ".$r["date"]."    By  ".$r["stuser"]."  

   "); 
               
echo (" Delete    |    Edit  "); 

echo " 
 

            
        
     </table><br><br>";// Echoes the content <br>}

Cools · May 4, 2011

I think then that the problem is caused by counting all the rows in the table rather than the number that match the user name.

You have two variables (totalrows and totalusers) that use the same SQL query and therefore have the same value.

I think if you change this query to count IDs for rows that have the required user name in them you will get the next/prev to work...

Really I would need a sample data file to be able to help much more...

I love you Bud...

lol

Got it working...

i did not need...

$totalusers = mysql_num_rows(mysql_query("SELECT id FROM $tablen"));[php]

but i need...
[php]$totalrows = mysql_num_rows(mysql_query("SELECT id FROM $tablen"));// Counts the total rows [php]

but changed to.... 
[php]$totalrows = mysql_num_rows(mysql_query("SELECT users_name FROM $tablen"));// Counts the total rows [php]


and now it works... 


new code.. 

[php]

include("conf.inc.php"); // Includes the db and form info.
$tablen = $con;//Sets the table name
$users_name=$_POST['users_name'];
//Pagination
$limit = 10;// Set the limit of results per page
$page = $_GET['page'];// Sets the page, the default is ?page=
$totalrows = mysql_num_rows(mysql_query("SELECT users_name FROM $tablen"));// Counts the total rows
if(empty($page))// If the page is empty
{
$page = '1';// Page is 1
};
$start = ($page-1)*$limit;
$start = round($start,0);// Sets the start
$result = mysql_query("SELECT * FROM $tablen ORDER BY date DESC LIMIT $start, $limit");// Normal query
while ($r = mysql_fetch_array($result))
{
echo nl2br("      </pre><table align='\"center\"' width='\"80%\"' bgcolor='\"#d8EEF4\"' border='\"0\"' cellpadding='\"1\"' cellspacing='\"0\"'> 
       
         ".$r["users_name"]." - ".$r["lesson"]."  


	   ".$r["comment"]."
             
             ".$r["date"]."    By  ".$r["stuser"]."  

   ");
           
echo (" Delete    |    Edit  ");

echo "
 

           
       
     </table><br>";// Echoes the content<br>};<br>//$totalusers = mysql_num_rows(mysql_query("SELECT id FROM $tablen"));<br>// echo "<br>Total posts: $totalusers<br>";// Echoes the total users<br>$totalpages = $totalrows / $limit;// Get the total pages<br>$totalpages = ceil($totalpages);// Rounds the total pages to the highest possible numer<br>if($page == 1)// If the page is 1<br>{<br>$actualpage = '1';// Sets the actual page<br>}<br>else// Else<br>{<br>$actualpage = "$page";// The page is the one it's specified in ?page=<br>}<br>if($page < $totalpages)// If the actual page is smaller than the totalpages<br>{<br>$nv = $page+1;// Sets the next variable<br>$pv = $page-1;// Sets the preview variable<br>$nextpage = "<a href="?page=%24nv" rel="">></a>";// Sets the next page<br>$prevpage = "<a href="?page=%24pv" rel=""><</a>";// Sets the prev page<br>$firstpage = "<a href="%5C%22?page=1%5C%22" rel="">«</a>";// Sets the first page<br>$finalpage = "<a href="%5C%22?page=%24totalpages%5C%22" rel="">»</a>";// Sets the final page<br>}<br>if($page == '1')<br>{<br>$nv = $page+1;<br>$nextpage = "<a href="?page=%24nv" rel="">></a>";<br>$prevpage = "<";<br>$firstpage = "«";<br>$finalpage = "<a href="%5C%22?page=%24totalpages%5C%22" rel="">»</a>";<br>}elseif($page == $totalpages){<br>$pv = $page-1;<br>$nextpage = ">";<br>$prevpage = "<a href="?page=%24pv" rel=""><</a>";<br>$firstpage = "<a href="%5C%22?page=1%5C%22" rel="">«</a>";<br>$finalpage = "»";<br>}<br>if($totalpages == '1' || $totalpages == '0'){<br>	$nextpage = ">";<br>	$prevpage = "<";<br>	$firstpage = "«";<br>	$finalpage = "»";<br>}<br>echo "<p align='\"center\"' class='\"post-footer' align-center> $firstpage $prevpage Actual Page: $actualpage $nextpage $finalpage
Pages: $totalpages </p> ";<br><br>?><b

glennda · May 4, 2011

i dont get it..

does this part of the code not do that

$result = mysql_query("SELECT * FROM $tablen ORDER BY date DESC LIMIT $start, $limit");// Normal query 
while ($r = mysql_fetch_array($result)) 
{ 
   echo nl2br("      </pre><table align='\"center\"' width='\"50%\"' border='\"1\"' cellpadding='\"1\"' cellspacing='\"0\"'>  
        
         ".$r["users_name"]." - ".$r["lesson"]."  

".$r["comment"]." 
              
             ".$r["date"]."    By  ".$r["stuser"]."  

   "); 
               
echo (" Delete    |    Edit  "); 

echo " 
 

            
        
     </table><br><br>";// Echoes the content <br>}

I'll go hide! i missed your while statement my bad!

Cools · May 4, 2011

Dont worry i spoke to soon.. lol now i have added more test students.

it's still not working right.. lol.. but at least next/prev page is working

if you want to have a look.. goto

http://server.ilimits.co.uk/epms/sen

i have zip code to have a look at.

http://server.ilimits.co.uk/epms/sen/sen.zip

PS. i working from tget2.php to get in fro from DB...

Cools · May 5, 2011

Well i re-did the page.. found some code.. so not all my work.

from :: How to use PHP & MySQL Search Rows Data and Paging/Pagination | ShotDev.Com

so here it is of any one needs it

if you need the autocomplete.js msg me.. or see zip above.

 

session_start();
ob_start();
require_once "conf.inc.php";

?>








   
   

<br />
.tableth {<br />
	font-family: Tahoma, Geneva, sans-serif;<br />
}<br />


</pre><form name="frmSearch" method="get" action="<?=%24_SERVER%5B'SCRIPT_NAME'%5D;?>">
 
   
     Students Name
     ">
     
   
 
</form><br><br>if($_GET["txtKeyword"] != "")<br>{<br>$objConnect = mysql_connect("localhost","root","C0nfus3d!") or die(mysql_error());<br>$objDB = mysql_select_db("sen");<br>// Search By Name or Email<br>$strSQL = "SELECT * FROM comments WHERE (users_name LIKE '%".$_GET["txtKeyword"]."%')";<br>$objQuery = mysql_query($strSQL) or die ("Error Query [".$strSQL."]");<br>$Num_Rows = mysql_num_rows($objQuery);<br><br><br>$Per_Page = 5;   // Per Page<br><br>$Page = $_GET["Page"];<br>if(!$_GET["Page"])<br>{<br>	$Page=1;<br>}<br><br>$Prev_Page = $Page-1;<br>$Next_Page = $Page+1;<br><br>$Page_Start = (($Per_Page*$Page)-$Per_Page);<br>if($Num_Rows<=$Per_Page)<br>{<br>	$Num_Pages =1;<br>}<br>else if(($Num_Rows % $Per_Page)==0)<br>{<br>	$Num_Pages =($Num_Rows/$Per_Page) ;<br>}<br>else<br>{<br>	$Num_Pages =($Num_Rows/$Per_Page)+1;<br>	$Num_Pages = (int)$Num_Pages;<br>}<br><br><br>$strSQL .=" order  by date DESC LIMIT $Page_Start , $Per_Page";<br>$objQuery  = mysql_query($strSQL);<br><br>?><br><table bgcolor="#0080FF" cellpadding="0" cellspacing="1" width="80%" border="0" align="center">
  
	Student Name 
	Date
	Comments
	Lesson 
	Staff Memeber
  

while($objResult = mysql_fetch_array($objQuery))
{
?>
  
	=$objResult["users_name"][/url]?>
	=$objResult["date"][/url]?>
	
	=$objResult["lesson"][/url]?>
	=$objResult["stuser"][/url]?>
  

}
?>
</table><br><br><br><br>   <p align="center">Total = $Num_Rows;?> Record : =$Num_Pages;?> Page :

if($Prev_Page)
{
	echo " << Back ";
}

for($i=1; $i<=$Num_Pages; $i++){
	if($i != $Page)
	{
		echo "[ $i ]";
	}
	else
	{
		echo " $i ";
	}
}
if($Page!=$Num_Pages)
{
	echo " Next>> ";
}

mysql_close($objConnect);

}	
?>
</p><br><script type="text/javascript"><br />
			// <![CDATA[<br />
			$(document).ready(function(){	<br />
			$("#txtKeyword").autocomplete("ac/get_list.php", {<br />
		width: 260,<br />
		matchContains: true,<br />
		//mustMatch: true,<br />
		//minChars: 0,<br />
		//multiple: true,<br />
		//highlight: false,<br />
		//multipleSeparator: ",",<br />
		selectFirst: false<br />
	});<br />
			});	<br />
			// ]]><br />
		</script><br><br><br><b

CESIL · May 6, 2011

Glad you got this sorted...I started to work through it but the code you linked to didn't seem to match up to what you said you were doing...

There seems to be an awful lot of code and scripts to do such a simple task...

Still it does seem to be the case that an infinite number of coders will produce an infinite number of different ways to solve a problem...and all of them will usually believe that their method is the best

Cools · May 6, 2011

i know what you mean.. lol..

powdarrmonkey · May 6, 2011

 
$strSQL = "SELECT * FROM comments WHERE (users_name LIKE '%".$_GET["txtKeyword"]."%')";
$objQuery = mysql_query($strSQL) or die ("Error Query [".$strSQL."]");

Warning, SQL injection attack and cross-site scripting attack in one go!

Ahh, found the problem...

[PHP] Grab name from mysql and but display in date order with next and prev

Recommended Posts

Cools

pcstru

CESIL

Cools

CESIL

glennda

Cools

Cools

glennda

Cools

Cools

CESIL

Cools

powdarrmonkey

Create an account or sign in to comment

Create an account

Sign in

Recent Threads

Sage 200 budget holders - test something for me?

Bromcom Support

My Fluke LinkRunner has a fault...

Home

Forums

What's New