show first in list only

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

Post Reply
inosent1
Forum Commoner
Posts: 97
Joined: Wed Jan 28, 2009 12:18 pm

show first in list only

Post by inosent1 »

i have a table that records assignments. each assignment is related to a particular case. each case can have 20-50 ppl working on it at one time. i want the user to see only the list of cases they need to work on, but not a long list of all the specific assignments related to the case.

for example

user A has three tasks assigned for three cases.

case task user_id
  • case task user_id
    smith task_a 1
    smith task_b 1
    smith task_c 1
    jones task_a 1
    jones task_b 1
    jones task_c 1
    henry task_a 1
    henry task_b 1
    henry task_c 1
what i want to do is show a list of records from this table by using a query that says at first "show me all the tasks in this table for this user that meets these parameters" and then limits the display to, "but show only the first row for each case"

the output then will look like this:
  • case
    smith
    jones
    henry
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: show first in list only

Post by requinix »

What you've posted is rather ambiguous, but it sounds like you just need a query.

Code: Select all

SELECT `case` FROM `table` WHERE `task` = whatever AND `user_id` = whatever
inosent1
Forum Commoner
Posts: 97
Joined: Wed Jan 28, 2009 12:18 pm

Re: show first in list only

Post by inosent1 »

thanks for the reply.


this works, but pulls every record from the db where the user id identifies what files to pull, and in this case where the completion date is blank:

Code: Select all

<?
include'dbinfo.inc.php';

$bln=""

$query="SELECT * FROM assignments WHERE uid ='$uid' AND dt_complete ='$bln' ORDER BY id DESC";

$result=mysql_query ($query);
$num=mysql_num_rows ($result);
mysql_close();
?>

<table valign=top border=1 width=100% align="center" class="sortable" id="sample">
<thead><tr> 
<th class='tableh2'>Last</th>
<th class='tableh2'>First</th>
<th class='tableh2'>Property</th>
<th class='tableh2'>Assigned On</th>
<th class='tableh2'>Assigned By</th>
<th class='tableh2'>Task</th>


</tr>
</thead>
<?php
$i=0;
while ($i < $num) {
$id=mysql_result($result,$i,"id");
$file_id=mysql_result($result,$i,"file_id");
$dt_assign=mysql_result($result,$i,"dt_assign");
$assigned_by=mysql_result($result,$i,"assigned_by");
$b_fname=mysql_result($result,$i,"b_fname");
$b_lname=mysql_result($result,$i,"b_lname");
$task=mysql_result($result,$i,"task");
$subj_prop_street=mysql_result($result,$i,"subj_prop_street");
$subj_prop_city=mysql_result($result,$i,"subj_prop_city");
$subj_prop_state=mysql_result($result,$i,"subj_prop_state");
$subj_prop_zip=mysql_result($result,$i,"subj_prop_zip");

?>
<tr>

<td  width="15%" valign=top><span class="style2"><?php echo "$b_lname"; ?>
<td  width="15%" valign=top><span class="style2"><?php echo "$b_fname"; ?>
<td  width="15%" valign=top><span class="style2"> <?php echo "$subj_prop_street $subj_prop_city $subj_prop_state $subj_prop_zip"; ?>
<td  width="15%" valign=top><span class="style2"> <?php echo "$dt_assign"; ?>
<td  width="15%" valign=top><span class="style2"> <?php echo "$assigned_by"; ?>
<td  width="15%" valign=top><span class="style2"><?php echo "$task"; ?>
<td width="4%" valign=top><span class="style2">


</tr>
<?php
++$i;
} 
echo "</table>";


?>
however i dont want the list to show all the tasks, i take care of that on a separate page. for this page all i want is for the first record of each applicable group to show, and that is determined by the file_id. the file_id corresponds to each unique case.

when i use this code:

Code: Select all

<?
include'dbinfo.inc.php';

$bln=""

$query="SELECT file_id FROM assignments WHERE uid ='$uid' AND dt_complete ='$bln' ORDER BY id DESC";

$result=mysql_query ($query);
$num=mysql_num_rows ($result);
mysql_close();
?>

<table valign=top border=1 width=100% align="center" class="sortable" id="sample">
<thead><tr> 
<th class='tableh2'>Last</th>
<th class='tableh2'>First</th>
<th class='tableh2'>Property</th>
<th class='tableh2'>Assigned On</th>
<th class='tableh2'>Assigned By</th>
<th class='tableh2'>Task</th>


</tr>
</thead>
<?php
$i=0;
while ($i < $num) {
$id=mysql_result($result,$i,"id");
$file_id=mysql_result($result,$i,"file_id");
$dt_assign=mysql_result($result,$i,"dt_assign");
$assigned_by=mysql_result($result,$i,"assigned_by");
$b_fname=mysql_result($result,$i,"b_fname");
$b_lname=mysql_result($result,$i,"b_lname");
$task=mysql_result($result,$i,"task");
$subj_prop_street=mysql_result($result,$i,"subj_prop_street");
$subj_prop_city=mysql_result($result,$i,"subj_prop_city");
$subj_prop_state=mysql_result($result,$i,"subj_prop_state");
$subj_prop_zip=mysql_result($result,$i,"subj_prop_zip");

?>
<tr>

<td  width="15%" valign=top><span class="style2"><?php echo "$b_lname"; ?>
<td  width="15%" valign=top><span class="style2"><?php echo "$b_fname"; ?>
<td  width="15%" valign=top><span class="style2"> <?php echo "$subj_prop_street $subj_prop_city $subj_prop_state $subj_prop_zip"; ?>
<td  width="15%" valign=top><span class="style2"> <?php echo "$dt_assign"; ?>
<td  width="15%" valign=top><span class="style2"> <?php echo "$assigned_by"; ?>
<td  width="15%" valign=top><span class="style2"><?php echo "$task"; ?>
<td width="4%" valign=top><span class="style2">


</tr>
<?php
++$i;
} 
echo "</table>";


?>

the result i get is this

Code: Select all


Warning: mysql_result() [function.mysql-result]: id not found in MySQL result index 9 in /home/content/20/x/pipe.php on line 902

Warning: mysql_result() [function.mysql-result]: dt_assign not found in MySQL result index 9 in /home/content/20/x/pipe.php on line 906

Warning: mysql_result() [function.mysql-result]: assigned_by not found in MySQL result index 9 in /home/content/20/x/pipe.php on line 907

Warning: mysql_result() [function.mysql-result]: b_fname not found in MySQL result index 9 in /home/content/20/x/pipe.php on line 908

Warning: mysql_result() [function.mysql-result]: b_lname not found in MySQL result index 9 in /home/content/20/x/pipe.php on line 909

Warning: mysql_result() [function.mysql-result]: task not found in MySQL result index 9 in /home/content/20/x/pipe.php on line 910

Warning: mysql_result() [function.mysql-result]: subj_prop_street not found in MySQL result index 9 in /home/content/20/x/pipe.php on line 911

Warning: mysql_result() [function.mysql-result]: subj_prop_city not found in MySQL result index 9 in /home/content/20/x/pipe.php on line 912

Warning: mysql_result() [function.mysql-result]: subj_prop_state not found in MySQL result index 9 in /home/content/20/x/pipe.php on line 913

Warning: mysql_result() [function.mysql-result]: subj_prop_zip not found in MySQL result index 9 in /home/content/20/x/pipe.php on line 914


it says 'not found' when i use SELECT file_id

but if i use SELECT *

if finds every applicable record.

but i am interested in displaying only the first row of each group.

eg

excerpt of the raw table data
file_id task b_fname uid dt_complete
1 do_this1 jack 2
1 do_this2 jack 2
1 do_this3 jack 4
2 do_this1 dave 5
2 do_this2 dave 2
2 do_this3 dave 2
3 do_this1 mary 6
3 do_this2 mary 2
3 do_this3 mary 2


the 'dt_complete' in this case is blank

if i want to pull tasks for the user with the 'uid' 2, using the code above that works, what i will see is this:

file_id task b_fname uid dt_complete
1 do_this1 jack 2
1 do_this2 jack 2
2 do_this2 dave 2
2 do_this3 dave 2
3 do_this2 mary 2
3 do_this3 mary 2

but i dont want to, on this page, show all the individual tasks

i want the user to see that for a specific case they still have tasks outstanding and then on another screen to see what they are.

i was hoping to use the same table to accomplish this query, but so far i have not been able to figure out how to do it.

the result i am looking for is this:

1 do_this1 jack 2
2 do_this2 dave 2
3 do_this2 mary 2


and in terms of display, simply

b_fname
jack
dave
mary


thanks again for the input!
Post Reply