sort problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
sulen
Forum Commoner
Posts: 79
Joined: Wed Jul 09, 2003 4:55 pm
Location: los angeles
Contact:

sort problem

Post by sulen »

I have a page having a table of 5 columns and the table is sorted based on 2 column headers. The Db query is structured to return 22 results per page therefore the runs into a number of pages based on results returned.

I have got the sort to work but the problem I am facing is that the sort works only for the first page and then when I go to the second page of results I have to click the column header again to sort the contents.

Also when I go to the last page and click the column header it takes me to the first page again and gives me a default sort. I will be grateful if anybody can help me out on this

The code is under

<?php
@ $db = mysql_pconnect("localhost", "root", "dbstuff3r");

if(!$db)
{
echo "error could not connect to the database. please try again later";
exit;
}

mysql_select_db("axis");
$sql = "select * from login where login='$login'";
$result = mysql_query($sql);
$count = mysql_num_rows($result);
if(!$result)
{
echo"<p>Cannot execute query</p>";
}

if($count<1)
{
echo "<p>User needs to enter correct Site Number</p>";
}
else
{
for($i=0; $i<$count; $i++)
{
$row = mysql_fetch_array($result);
$alevel=$row[2];
$region=$row[3];
$branch=$row[4];
$fname=$row[6];
$lname=$row[5];
if ($alevel=='branch')
{
$limit = 22;

$sql3="select * from cmaster where branch='048'";
$result3 = mysql_query($sql3);
$totalrows = mysql_num_rows($result3);

if(empty($page)){
$page = 1;
}
$limitvalue = $page * $limit - ($limit);

if ($_REQUEST["submit"] != "")
{
$OrderVar = $_REQUEST["submit"];
}
else {
$OrderVar = "clname";
}

$sql1 = "select * from cmaster where branch='048' ORDER BY $OrderVar LIMIT $limitvalue,$limit";
$result1 = mysql_query($sql1);
$num_results = mysql_num_rows($result1);
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>AXIS - Altura Customer Contact Information System</TITLE>
<META http-equiv=Content-Type content="text/html; charset=windows-1252">
<LINK REL=StyleSheet HREF='style.css' TYPE='text/css'>
</HEAD>
<BODY>
<?
if($page != 1){
$pageprev = $page;
}
$numofpages = $totalrows / $limit;

for($i = 1; $i <= $numofpages; $i++){
if($i == $page){
echo($i."&nbsp;&nbsp;");
}
else
{
echo("<a class='five' href=\"$PHP_SELF?page=$i&login=$login&pwd=$pwd\">$i</a>&nbsp;&nbsp;");
}
}
if(($totalrows % $limit) != 0){
if($i == $page){
echo($i."&nbsp;&nbsp;");
}
else
{
echo("<a class='five' href=\"$PHP_SELF?page=$i&login=$login&pwd=$pwd\">$i</a>&nbsp;&nbsp;");
}
}

if(($totalrows - ($limit * $page)) > 0){
$pagenext = $page++;
}
?>
<table width='100%'>
<tr>
<td><a class="five" href="contacts.php?submit=clname&page=<?=$pagenext?>&login=<?=$login?>&pwd=<?=$pwd?>">Last Name</a></td>
<td><a class="five" href="contacts.php?submit=cfname&page=<?=$pagenext?>&login=<?=$login?>&pwd=<?=$pwd?>">First Name</a></td>
<td><a class="five" href="contacts.php?submit=cjtitle&page=<?=$pagenext?>&login=<?=$login?>&pwd=<?=$pwd?>">Job Title</a></td>
<td><a class="five" href="contacts.php?submit=cbpno&page=<?=$pagenext?>&login=<?=$login?>&pwd=<?=$pwd?>">Phone Number</a></td>
<td>Email</td>
<td>Job Category</td>
</tr>
<?
for($i=0; $i <$num_results; $i++)
{
$row = mysql_fetch_array($result1);
$siteno=$row[20];
$clname=$row[1];
$cfname=$row[2];
$cjtitle=$row[6];
$cbpno=$row[12];
$cemail=$row[16];
$cjcat=$row[5];
?>
<tr>
<td><a class="five" href="ctdetail.php?login=<?=$login?>&pwd=<?=$pwd?>&branch=<?=$branch?>&region=<?=$region?>&alevel=<?=$alevel?>&lname=<?=$lname?>&fname=<?=$fname?>&clname=<?=$clname?>&cfname=<?=$cfname?>&cbpno=<?=$cbpno?>&siteno=<?=$siteno?>&cname=<?=$cname?>&sstatus=<?=$sstatus?>&scity=<?=$scity?>&cstate=<?=$cstate?>&gcode=<?=$gcode?>"><?=$clname?></a></td>

<td><a class="five" href="ctdetail.php?login=<?=$login?>&pwd=<?=$pwd?>&branch=<?=$branch?>&region=<?=$region?>&alevel=<?=$alevel?>&lname=<?=$lname?>&fname=<?=$fname?>&clname=<?=$clname?>&cfname=<?=$cfname?>&cbpno=<?=$cbpno?>&siteno=<?=$siteno?>&cname=<?=$cname?>&sstatus=<?=$sstatus?>&scity=<?=$scity?>&cstate=<?=$cstate?>&gcode=<?=$gcode?>"><?=$cfname?></a></td>

<td><?=$cjtitle?></td>

<td><?=$cbpno?></td>

<td><?=$cemail?></td>

<td<?=$cjcat?></td>

</tr>
<?
}
?>
</table>
<BR></BODY></HTML>
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

I started reading through your code but I found it very hard to read and gave up.

If code is properly indented it's easier to read. Keeping html out of php scripts also helps as does splitting a script up into smaller, task-specific functions.

One thing I did notice: $OrderVar = $_REQUEST["submit"]

You must not put un-escaped user input into a db query (see mysql_escape_string or addslashes). It's also better to specify $_GET or $_POST array rather than simply use $_REQUEST.

Some security info here (and of course in the manual):

http://www.securereality.com.au/studyinscarlet.txt
http://www.sklar.com/page/article/owasp-top-ten
Post Reply