sort/filter displayed data

ttuser4 · Nov 4, 2009

Hi,

I have this existing code:

<div id="body">
<table width="750">
<tr>
<td width="400"><h1>Online Enclosure Quote .beta</h1></td>
<td width="*" align="left"> :: My Quotes Form :: v0.3b | 07/29/09</td>
</tr>
</table>
<?php
include('../global/includes/_db_info.php');

if (isset($_GET['id_row']) && (isset($_GET['action']) && ($_GET['action']=='delete'))) {
mysql_query("UPDATE projects SET Status='C' WHERE ID='$id_row';", $connection);
}

$userid=$_SESSION["uid"];

@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM projects WHERE USERID='$userid' AND STATUS='A' Order by ID Desc";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();
?>
<table border="0" cellspacing="2" cellpadding="2" width="750" id="quotes">
<tr>
<th>Project</th>
<th>Model</th>
<th>Width [ft]</th>
<th>Span [ft]</th>
<th>Total Length [ft]</th>
<th>Date, Time</th>
<th>Total</th>
<th>Currency</th>
<th>Detail</th>
<th>Delete</th>
</tr>

<?php
$i=0;
while ($i < $num) {

$f1=mysql_result($result,$i,"project");
$f2=substr(mysql_result($result,$i,"model"),0,4);
$f3=substr(mysql_result($result,$i,"model"),4,1);
$f4=mysql_result($result,$i,"span");
$f5=mysql_result($result,$i,"totlength");
$f6=mysql_result($result,$i,"timestmp");
$f71=mysql_result($result,$i,"gtotal");
$f72=mysql_result($result,$i,"rate");
$f7=number_format($f71*$f72,2);
$f8=mysql_result($result,$i,"cur");
$f9=mysql_result($result,$i,"id");
?>

<tr>
<td><?php echo $f1; ?></td>
<td><?php echo $f2; ?></td>
<td><?php echo $f3; ?></td>
<td><?php echo $f4; ?></td>
<td><?php echo $f5; ?></td>
<td><?php echo $f6; ?></td>
<td><?php echo $f7; ?></td>
<td><?php echo $f8; ?></td>
<td><a href="details.php?id_row=<?php echo $f9; ?>">Details</a></td>
<td><a href="myquotes.php?id_row=<?php echo $f9; ?>&action=delete" onclick="javascript:return confirm('Are you sure you want to delete this record?')">Delete</a></td>
</tr>

<?php
$i++;
}
?>

</body>

This code works fine, but users are getting too many lines/records displayed on their screens.
I am looking for some way they would be able to:
1) sort data also by 'project', 'total' (default is 'date')
2) filter data so they would see only the last version of each project - something like 'select ... max(version) from projects group by project'

Any help please?

ingresman · Nov 4, 2009

The reason you get too many lines is your loop is controlled by $num which is the number of rows in your result set. so if there are 567 rows in the set your code will display 567 rows which I susoect would be too much.
So you need to paginate the result. There have been a few threads on this forum about doing that and where the query should run and how many times. That will be a matter for yoursefl whether to use a temp table or re-query the db every time.
In any case you will have to decide how many rows to display and use the LIMIT clause in mysql select (look in the manual for full details) which in essence says start at a particular row and only return a certain numner of rows e.g. (and these values are probabbly wrong !)
you limit the display 10 rows then all you need to know is the page number your on (hold it in session) so if page 1 is to be displayed you want to limit 0,10 (note base of 0 not 1), page 2 would be limit 9,10 page 3 19,10 etc ,you get the picture ?, you just need to work out the start row, as I say the mysql manual will give you the format of limit.
Calculating like this lets you display a list of page numbers on the page and you can jump around at random pages.
There will be other ways to do this such as put the result set in a temp table or flat file etc.
To get some pretty collumn sorting have a look at JQuery that allows you to have sort boxes at the top of a list of columns in a table. Not tried it yet but here good things.
Anyhow food for thought!

ttuser4 · Nov 4, 2009

Thanks for your input. Paginating would help to organize records, but sorting/filtering would give users better control what they want to see.
I can write the queries (I guess), but don't know how to provide users criteria selection and refresh page (or show a new one).
For example:
- they would click on heather field "Project" to get records sorted by 'project', allow ascending/descending sort, etc.
- put a button/link they would click to get only the most recent version of each project

ingresman · Nov 4, 2009

Yes. very do-able you need to create the query dynamicly depending on what the user chose.

ttuser4 · Nov 5, 2009

I made some changes to existing code and now selected sort/filter criteria are displayed in a new page:

'myquotes.php' (input):
...

<?php !@include("dealermenu.php"); ?>

<div id="frame">
<div id="body">
<table width="750">
<tr>
<td width="400"><h1>Online Enclosure Quote .beta</h1></td>
<td width="*" align="left"> :: My Quotes Form :: v0.3b | 07/29/09</td>
</tr>
</table>
<table width="750">
<form name="form1" action="myquotesel.php" method="post">
<select size="1" name="sortby">
<option selected value="0">Sort by</option>
<option value="1">Project name A->Z</option>
<option value="2">Project name Z->A</option>
<option value="3">Total amount 0->9</option>
<option value="4">Total amount 9->0</option>
</select>
<input type="checkbox" name="recent" value="yes"> The most recent versions only
<input type="submit" value="Submit" name="B1">
</form>
</table>
<?php
include('../global/includes/_db_info.php');

if (isset($_GET['id_row']) && (isset($_GET['action']) && ($_GET['action']=='delete'))) {
mysql_query("UPDATE projects SET Status='C' WHERE ID='$id_row';", $connection);
}

$userid=$_SESSION["uid"];

@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM projects WHERE USERID='$userid' AND STATUS='A' Order by ID Desc";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();
?>
<table border="0" cellspacing="2" cellpadding="2" width="750" id="quotes">
<tr>
<th>Project</th>
<th>Model</th>
<th>Width [ft]</th>
<th>Span [ft]</th>
<th>Total Length [ft]</th>
<th>Date, Time</th>
<th>Total</th>
<th>Currency</th>
<th>Detail</th>
<th>Delete</th>
</tr>

<?php
$i=0;
while ($i < $num) {

$txservice=mysql_result($result, $i, "txservices");
$siteprep=mysql_result($result,$i, "siteprep");
$sitedelivery=mysql_result($result, $i, "sitedelivery");
$permits=mysql_result($result, $i, "permits");
$supervis=mysql_result($result, $i, "supervis");
$install=mysql_result($result, $i, "install");
$other=mysql_result($result, $i, "other");
$gtotal=mysql_result($result,$i,"gtotal");
$rate=mysql_result($result,$i,"rate");

$subtot_serv=($siteprep+$sitedelivery+$permits+$supervis+$install+$other)*$rate*(1+$txservice/100);
$serv=$txservicep+$subtot_serv;

$goods=$gtotal-$serv;
/*echo "services tax: $txservice ";
echo "services: $serv ";
echo "goods: $goods ";
echo "total: $gtotal ";
echo "rate: $rate "; */
$f1=mysql_result($result,$i,"project");
$f2=substr(mysql_result($result,$i,"model"),0,4);
$f3=substr(mysql_result($result,$i,"model"),4,1);
$f4=mysql_result($result,$i,"span");
$f5=mysql_result($result,$i,"totlength");
$f6=mysql_result($result,$i,"timestmp");
$f7=number_format($goods*$rate+$serv,2);
$f8=mysql_result($result,$i,"cur");
$f9=mysql_result($result,$i,"id");
?>

<tr>
<td><?php echo $f1; ?></td>
<td><?php echo $f2; ?></td>
<td><?php echo $f3; ?></td>
<td><?php echo $f4; ?></td>
<td><?php echo $f5; ?></td>
<td><?php echo $f6; ?></td>
<td><?php echo $f7; ?></td>
<td><?php echo $f8; ?></td>
<td><a href="details.php?id_row=<?php echo $f9; ?>">Details</a></td>
<td><a href="myquotes.php?id_row=<?php echo $f9; ?>&action=delete" onclick="javascript:return confirm('Are you sure you want to delete this record?')">Delete</a></td>
</tr>

<?php
$i++;
}
?>
...

'myquotesel.php' (output):
...
<?php
include('../global/includes/_db_info.php');

/*$recent=$_POST['recent']; */
$sortby=$_POST['sortby'];

if (isset($_GET['id_row']) && (isset($_GET['action']) && ($_GET['action']=='delete'))) {
mysql_query("UPDATE projects SET Status='C' WHERE ID='$id_row';", $connection);
}

$userid=$_SESSION["uid"];

@mysql_select_db($database) or die( "Unable to select database");
/*echo "sort by: $sortby ";
echo "recent: $recent "; */
if (isset($_POST['recent'])) {
switch ($sortby) {
case 0:
$query="SELECT * FROM qrecent WHERE USERID='$userid' AND STATUS='A' ORDER BY ID DESC";
break;
case 1:
$query="SELECT * FROM qrecent WHERE USERID='$userid' AND STATUS='A' ORDER BY project";
break;
case 2:
$query="SELECT * FROM qrecent WHERE USERID='$userid' AND STATUS='A' ORDER BY project DESC";
break;
case 3:
$query="SELECT * FROM qrecent WHERE USERID='$userid' AND STATUS='A' ORDER BY cur, gtotal";
break;
case 4:
$query="SELECT * FROM qrecent WHERE USERID='$userid' AND STATUS='A' ORDER BY cur, gtotal DESC";
break;
}
}
else {
switch ($sortby) {
case 0:
$query="SELECT * FROM projects WHERE USERID='$userid' AND STATUS='A' ORDER BY ID DESC";
break;
case 1:
$query="SELECT * FROM projects WHERE USERID='$userid' AND STATUS='A' ORDER BY project";
break;
case 2:
$query="SELECT * FROM projects WHERE USERID='$userid' AND STATUS='A' ORDER BY project DESC";
break;
case 3:
$query="SELECT * FROM projects WHERE USERID='$userid' AND STATUS='A' ORDER BY cur, gtotal";
break;
case 4:
$query="SELECT * FROM projects WHERE USERID='$userid' AND STATUS='A' ORDER BY cur, gtotal DESC";
break;
}
}

$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();
?>
<table border="0" cellspacing="2" cellpadding="2" width="750" id="quotes">
<tr>
<th>Project</th>
<th>Model</th>
<th>Width [ft]</th>
<th>Span [ft]</th>
<th>Total Length [ft]</th>
<th>Date, Time</th>
<th>Total</th>
<th>Currency</th>
<th>Detail</th>
<th>Delete</th>
</tr>

<?php
$i=0;
while ($i < $num) {

$txservice=mysql_result($result, $i, "txservices");
$siteprep=mysql_result($result,$i, "siteprep");
$sitedelivery=mysql_result($result, $i, "sitedelivery");
$permits=mysql_result($result, $i, "permits");
$supervis=mysql_result($result, $i, "supervis");
$install=mysql_result($result, $i, "install");
$other=mysql_result($result, $i, "other");
$gtotal=mysql_result($result,$i,"gtotal");
$rate=mysql_result($result,$i,"rate");

$subtot_serv=($siteprep+$sitedelivery+$permits+$supervis+$install+$other)*$rate*(1+$txservice/100);
$serv=$txservicep+$subtot_serv;

$goods=$gtotal-$serv;
/*echo "services tax: $txservice ";
echo "services: $serv ";
echo "goods: $goods ";
echo "total: $gtotal ";
echo "rate: $rate "; */
$f1=mysql_result($result,$i,"project");
$f2=substr(mysql_result($result,$i,"model"),0,4);
$f3=substr(mysql_result($result,$i,"model"),4,1);
$f4=mysql_result($result,$i,"span");
$f5=mysql_result($result,$i,"totlength");
$f6=mysql_result($result,$i,"timestmp");
$f7=number_format($goods*$rate+$serv,2);
$f8=mysql_result($result,$i,"cur");
$f9=mysql_result($result,$i,"id");
?>

<tr>
<td><?php echo $f1; ?></td>
<td><?php echo $f2; ?></td>
<td><?php echo $f3; ?></td>
<td><?php echo $f4; ?></td>
<td><?php echo $f5; ?></td>
<td><?php echo $f6; ?></td>
<td><?php echo $f7; ?></td>
<td><?php echo $f8; ?></td>
<td><a href="details.php?id_row=<?php echo $f9; ?>">Details</a></td>
<td><a href="myquotes.php?id_row=<?php echo $f9; ?>&action=delete" onclick="javascript:return confirm('Are you sure you want to delete this record?')">Delete</a></td>
</tr>

<?php
$i++;
}
?>
...

Is there any way to use just one page (refresh after Submit)?

ingresman · Nov 6, 2009

Yes, you can do almost anything on a web page, but I feel you're not a PHP or web based developer.
Try to find a book (wrox are quite good) to understnad how things work.
I enviage your page will have some drop down along the top with a submit below them, click that and it goes back to web server gets the data and formats the page. That's it really al do-able on one page. You can even get PHP to make the drop doews have the same values you supplied when you clicked submit.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

sort/filter displayed data

ttuser4

MIS

ingresman

IS-IT--Management

ttuser4

MIS

ingresman

IS-IT--Management

ttuser4

MIS

ingresman

IS-IT--Management

Similar threads

Part and Inventory Search

Sponsor