Help with Displaying the Output from a table in a Database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Kingo
Forum Contributor
Posts: 146
Joined: Thu Jun 03, 2004 9:38 am

Post by Kingo »

I understand this part

Code: Select all

$result = mysql_query("SELECT Name,Phone FROM contact LIMIT $start, $rows_per_page"); 

$rows = mysql_num_rows($result); 
$sort = $_POST['sort'];                            <==== this used to be GET now changed to POST 
$orderby = " order by $sort"; 
$result = $result.$orderby;
Actually I'm trying to implement the following.
1. Display the results from the table( Done)
2. Sort the contents of the table by clicking the header in the table( Done)
3. SOrt the contents of the table by selecting an item from the list ( DOne in a crude way i.e, by using the above one)
4. Put a search box and when the user enters somethingh( eg: A ) and hit the submit button, i should get all the rows that start with A. ( TRYING TO DO---)

Actually i wanted all this functionality in One page. SO am Struggling----------Here is my code

I have the codes for different functions , but donot know how to integrate them.

Code: Select all

<html>
<head>
<title>View the Contents of the Table</title>

</head>

<body bgcolor="#FFFFFF" text="#000000" alink="#FF0000" topmargin="1" marginheight="1">

	<table width="730" align="center" border="0" cellpadding="0" cellspacing="0">
    	<tr><td>
		<font size="2" face="Arial, Helvetica, sans-serif"><strong><br>
		<font size="1">Sort by Selection</font></strong></font>
		</td></tr>
        <tr><td valign="top"><font size="2" face="Arial, Helvetica, sans-serif">&nbsp;</font>
        <form name="frm1" action="POST">
		<select name="select" size="1" OnChange="location.href=this.options[this.selectedIndex].value" style="font-family: Arial, Helvetica, sans-serif; font-size:11px">
        <option selected >---------</option>
        <option value="view1.php?sort=name">Name</option>
        <option value="view1.php?sort=phone">Phone</option>
        <option value="view1.php?sort=email">Email</option>
        </select></form> 
		</td></tr>
        <tr><td>
		<font size="2" face="Arial, Helvetica, sans-serif">&nbsp;<strong>Search</strong></font>
		</td></tr>
        <tr><td> 
        <form name="form1" method="post" action="<?php $_SERVER['PHP_SELF'].?search=name ?>">
        <input type="search" name="textfield"><br><br>
		<input name="Submit" type="button" value="Go">
        <br>
        </form>
		</td></tr>
        <tr><td>
		<font size="2" face="Arial, Helvetica, sans-serif">&nbsp;</font>
		</td></tr>
        <tr><td>
		<font size="2" face="Arial, Helvetica, sans-serif">&nbsp;</font>
		</td></tr>
        </table></td>
    <!-- Paste here -->
		<td valign="top"> 
			
			<?php 
			// Connection to Host and Database
			require_once('Connections/mysql_conn.php'); ?>
			
			<?php 
			
			$rows_per_page = 5; 
			
			$result = mysql_query("SELECT Name FROM contact "); 
			
			$total_records = mysql_num_rows($result); 
			
			$pages = ceil($total_records / $rows_per_page); 
			
			mysql_free_result($result); 
			
			//SEARCH FUNCTION
			 
			if(isset($_POST['search']))
			{ 
			  $search = ($_POST['search']); 
			} 
			
			$sql = "select * from contact where name LIKE '%$search%' "; 
			
			$results = mysql_query($sql) or die ("Can't query because ".mysql_error()); 
			
			if ($results){ 
			  //show the results 
			
			} 
						
			if (!isset($_GET['screen'])) 
			{ 
			$screen = 0; // Everything was being reset to 0 
			} else{ 
			$screen = $_GET['screen']; 
			}
			$start = $screen * $rows_per_page; 
		//Search Function
		
		//End of Search Function
			
			$query = "SELECT Name,Phone,Email FROM contact "; 
			
			if(isset ($_GET['sort'])) 
			{ 
			  $sort = $_GET['sort']; 
			  $query.= " ORDER BY $sort"; 
			} 
			
			$query .= " LIMIT $start, $rows_per_page"; 
			
			$result = mysql_query($query) or die("Query failed: ". mysql_error()); 
			
			$rows = mysql_num_rows($result); 
			
			echo "<table  border="1">";
			echo "<tr><td>";
			
			echo "<font size="2" face="Arial, Helvetica, sans-serif"><a href="".$_SERVER['PHP_SELF']."?sort=name"><b>Name</b></a></font></td>";
			echo "<td><font size="2" face="Arial, Helvetica, sans-serif"><a href="".$_SERVER['PHP_SELF']."?sort=phone"><b>Phone</b></a></font></td>";
			echo "<td><font size="2" face="Arial, Helvetica, sans-serif"><a href="".$_SERVER['PHP_SELF']."?sort=email"><b>Email</b></a></font></td></tr>";
			//$row_num = 0;
			while ($row = mysql_fetch_array($result))
			{ 
			//$color = ($row_num % 2)?"white":"black"; 
			//  $row_num++; 
			//  echo '<tr style="background-color:'.$color.'">'; 
			
			$Name = $row['Name'] ; 
			$Phone = $row['Phone']; 
			$Email = $row['Email']; 
			
			//make a display block to display the results on a html table row at a time 
			echo "<tr><td width="25%"><font size="2" face="Arial, Helvetica, sans-serif">$Name</font></td>"; 
			echo "<td width="25%"><font size="2" face="Arial, Helvetica, sans-serif">$Phone</font></td>" ; 
			echo "<td width="25%"><font size="2" face="Arial, Helvetica, sans-serif">$Email</font></td> </tr>";
			} 
			echo "</font></table>";
			
			echo "<p><hr></p>"; 
			// let's create the dynamic links now 
			if ($screen > 0) { 
			$url = "view1.php?screen=" . ($screen - 1); 
			echo "<a href="$url"><font size="2" face="Arial, Helvetica, sans-serif">Previous</font></a>\n"; 
			} 
			// page numbering links now 
			for ($i = 0; $i < $pages; $i++) { 
			$url = "view1.php?screen=" . $i; 
			echo " | <a href="$url"><font size="2" face="Arial, Helvetica, sans-serif">$i</font></a> |"; 
			} 
			
			if ((($screen+1)*$rows_per_page)< $total_records)
			{ 
			  if ($screen < $pages)
			   { 
				$url = "view1.php?screen=" . ($screen + 1); 
				echo "<a href="$url"><font size="2" face="Arial, Helvetica, sans-serif">Next</font></a>\n"; 
			   } 
			}
			?> 
	  
	</td></tr>
	</table>

</body>
</html>
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

The trick to sorting the searched results is to pass the where clause (or place the searched textbox result) into a hidden field so that it gets passed back to the server AGAIN when the order by functionality is used....At work so, maybe later I can look at this ad help striaghten you out
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

the relevant section of code to integrate search and paging

Code: Select all

//paging
         if (!isset($_GET['screen']))
         {
           $screen = 0; // Everything was being reset to 0
         } else{
           $screen = $_GET['screen'];
         }
         $start = $screen * $rows_per_page;

         $query = "SELECT Name,Phone,Email FROM contact ";
         //SEARCH FUNCTONALITY
         if(isset($_POST['searchfield']))
         {
           //in your form you limit the search to name only based on the action tag
           $search = ($_POST['searchfield']);
           $query .=" WHERE name like '%$search%' ";
         }
         if(isset ($_GET['sort']))
         {
           $sort = $_GET['sort'];
           $query.= " ORDER BY $sort";
         }

         $query .= " LIMIT $start, $rows_per_page";
Kingo
Forum Contributor
Posts: 146
Joined: Thu Jun 03, 2004 9:38 am

STill have a problem

Post by Kingo »

Hello,
I used your code, but still have a problem..When i click the submit button nothingh is happening. I guess these is some thingh wrong in the action filed. I used the following.

Code: Select all

<form name="form1" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
        <input type="text" name="searchfield"><br><br>
		<input name="Submit" type="button" value="Go">
Please help me out. Thanx
Kingo
Forum Contributor
Posts: 146
Joined: Thu Jun 03, 2004 9:38 am

Post by Kingo »

I got it. Here is the corrected code.

Code: Select all

<form name="form1" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
        <input type="search" name="searchfield"><br><br>
		<input type="submit" name="btnsubmit" value="Go">
Thanx very much for all your help. I really really appreciate it. good Day!
Kingo
Forum Contributor
Posts: 146
Joined: Thu Jun 03, 2004 9:38 am

Post by Kingo »

The results that are getting displayed are sorted only on the first page, If I click page 3 or page 4 the results are not sorted.
Please help
Kingo
Forum Contributor
Posts: 146
Joined: Thu Jun 03, 2004 9:38 am

Post by Kingo »

I guess I should pass the sort variable here. But I donn know how to do it.

Code: Select all

if ($screen > 0) { 
         $url = "view1.php?screen=" . ($screen - 1); 
         echo "<a href="$url"><font size="2" face="Arial, Helvetica, sans-serif">Previous</font></a>\n"; 
         } 
         // page numbering links now 
         for ($i = 0; $i < $pages; $i++) { 
         $url = "view1.php?screen=" . $i; 
         echo " | <a href="$url"><font size="2" face="Arial, Helvetica, sans-serif">$i</font></a> |"; 
         } 
          
         if ((($screen+1)*$rows_per_page)< $total_records) 
         { 
           if ($screen < $pages) 
            { 
            $url = "view1.php?screen=" . ($screen + 1); 
            echo "<a href="$url"><font size="2" face="Arial, Helvetica, sans-serif">Next</font></a>\n"; 
            }
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

instead of using GET to decide what to sort by, I would use session. like

Code: Select all

<?php
$_SESSION['sortMe']="name"; //default
if isset($_GET['sort']){
  $_SESSION['sortMe']=$_GET['sort'];
}

query.=" ORDER BY {$_SESSION['sortMe']}";

?>
This will keep sorting by the same thin until it is changed by another Getg
Kingo
Forum Contributor
Posts: 146
Joined: Thu Jun 03, 2004 9:38 am

Post by Kingo »

I used the following

Code: Select all

$_SESSION['sortMe']="name"; //
	
	if(isset ($_GET['sort'])) 
	{ 
	  //$sort = $_GET['sort']; //Line 89
	  $_SESSION['sortMe']=$_GET['sort']; 
	  $query.= " ORDER BY $sort"; 
	 } 
	
	$query .= " LIMIT $start, $rows_per_page"; 
	
	$result = mysql_query($query) or die("Query failed: ". mysql_error());
ANd I got the FOLLOWING ERROR.
Notice: Undefined variable: sort in c:\inetpub\wwwroot\php_remotefiles\view.php on line 89
Query failed: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 5' at line 1
Kingo
Forum Contributor
Posts: 146
Joined: Thu Jun 03, 2004 9:38 am

Post by Kingo »

If I use this

Code: Select all

query.="ORDER BY {$_SESSION['sortMe']}";
I get the Following Error.
Parse error: parse error, unexpected T_CONCAT_EQUAL in c:\inetpub\wwwroot\php_remotefiles\view.php on line 92
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

Code: Select all

<?php
$_SESSION['sortMe']="name"; // 
    
   if(isset ($_GET['sort'])) 
   { 
     //$sort = $_GET['sort']; //Line 89 
     $_SESSION['sortMe']=$_GET['sort']; 
     $query.= " ORDER BY $_SESSION[sort]";//changed this!! 
    } 
    
   $query .= " LIMIT $start, $rows_per_page"; 
    
   $result = mysql_query($query) or die("Query failed: ". mysql_error()); 
?>
try that and dont forget to put session_start() at the beginning of your script
Kingo
Forum Contributor
Posts: 146
Joined: Thu Jun 03, 2004 9:38 am

Post by Kingo »

I doest work. I used the following

Code: Select all

$query = "SELECT Name,Phone,Email FROM contact "; 
	
	session_start();
	
	$_SESSION['sortMe']="name"; //
	
	if(isset ($_GET['sort'])) 
	{ 
	  //$sort = $_GET['sort']; 
	  $_SESSION['sortMe']=$_GET['sort']; 
	  //$query.= " ORDER BY $sort"; 
	  query.="ORDER BY $_SESSION['sort']"; 

	 } 
	
	$query .= " LIMIT $start, $rows_per_page"; 
	
	$result = mysql_query($query) or die("Query failed: ". mysql_error()); 
	
	$row = mysql_num_rows($result);
Got the following error
Parse error: parse error, unexpected T_CONCAT_EQUAL in c:\inetpub\wwwroot\php_remotefiles\view.php on line 92
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

a little looking reveals....

Code: Select all

<?php
   if(isset($_GET['sort'])) //dropped space after isset
   { 
     //$sort = $_GET['sort']; 
     $_SESSION['sortMe']=$_GET['sort']; 
     //$query.= " ORDER BY $sort"; 
     $query.="ORDER BY $_SESSION[sort]"; //added $ and dropped '

    } 
    
?>
you should probably look your code over a bit before posting. Also, its helpful to post the line the error returns - in this case 92. I dont know which line 92 is - i need you to tell me
Kingo
Forum Contributor
Posts: 146
Joined: Thu Jun 03, 2004 9:38 am

Post by Kingo »

Thanx very much. But I still have some problem.
I get the following error
Warning: session_start(): Cannot send session cookie - headers already sent by (output started at c:\inetpub\wwwroot\php_remotefiles\view.php:4) in c:\inetpub\wwwroot\php_remotefiles\view.php on line 85

Warning: session_start(): Cannot send session cache limiter - headers already sent (output started at c:\inetpub\wwwroot\php_remotefiles\view.php:4) in c:\inetpub\wwwroot\php_remotefiles\view.php on line 85

ANd when I click the NAME to get sorted according to Name, I get the Following errors

Warning: session_start(): Cannot send session cookie - headers already sent by (output started at c:\inetpub\wwwroot\php_remotefiles\view.php:4) in c:\inetpub\wwwroot\php_remotefiles\view.php on line 85

Warning: session_start(): Cannot send session cache limiter - headers already sent (output started at c:\inetpub\wwwroot\php_remotefiles\view.php:4) in c:\inetpub\wwwroot\php_remotefiles\view.php on line 85

Notice: Undefined index: sort in c:\inetpub\wwwroot\php_remotefiles\view.php on line 94
Query failed: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 5' at line 1

Code: Select all

$query = "SELECT Name,Phone,Email FROM contact "; 
	
	session_start();//Line 85
	
	$_SESSION['sortMe']="name"; //
	
	if(isset($_GET['sort'])) //dropped space after isset 
   { 
     //$sort = $_GET['sort']; 
     $_SESSION['sortMe']=$_GET['sort']; 
     //$query.= " ORDER BY $sort"; 
     $query.="ORDER BY $_SESSION[sort]"; //added $ and dropped ' Line 94

    }
I tried putting session_start(); at the begining , but still i get the same error.
Please help with this
Kingo
Forum Contributor
Posts: 146
Joined: Thu Jun 03, 2004 9:38 am

Post by Kingo »

When I choose Name to sort accordingly, my url is "http://localhost/php_remotefiles/view.php?sort=name "

When I click the page 2, my url is "http://localhost/php_remotefiles/view.php?screen=1"

I guess some change should be made here.
Post Reply