Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Filter recordset with an array

Posted on 2009-02-23
6
Medium Priority
?
265 Views
Last Modified: 2012-05-06
Hi,

I appreciate you taking the time to read my question.

I have 3 tables in my database.

==========================
employees
==========================

id      name            jobid         deptid      hired
1      john                  283            172            01/01/2004
2      sue                  283            87            09/04/2001
3      mary            129            31            11/17/2003
4      adam            52            172            03/23/1998
5      lisa                  52            87            12/05/2007

==========================
job
==========================
52            job1
129            job2
283            job3


==========================
department
==========================
31            dept1
87            dept2
172            dept3

So I started out with creating a recordset:

rsEmp = SELECT * FROM employees

Then created 2 arrays from the Job & Department tables

arrJob      = (52,129,283)
arrDept      = (31,87,172)

What I'm trying to do is create a report page using the one recordet (rs) and filtering the results based off the values from the 2 arrays. I'm trying to do this by creating 2 tables in my page, both wrapped in a loop. Here's the desired output:

==========================
Output 1 - filter by Job
==========================

Job 1
adam      03/23/1998
lisa            12/05/2007

Job 2
mary      11/17/2003

Job 3
john            01/01/2004
sue            09/04/2001

==========================
Output 2 - filter by Dept
==========================

Dept 1
mary      11/17/2003

Dept 2
sue            09/04/2001
lisa            12/05/2007

Dept 3
john            01/01/2004
adam      03/23/1998

Your help is greatly appreciated.


0
Comment
Question by:kobeballa
  • 3
  • 3
6 Comments
 
LVL 39

Accepted Solution

by:
Roger Baklund earned 2000 total points
ID: 23717271
It is easier to do it with SQL, using joins:

SELECT job.*,id,name,hired
FROM employees,job
WHERE job.jobid = employees.jobid
ORDER BY job.jobid

SELECT department.*,id,name,hired
FROM employees,department
WHERE department.deptid = employees.deptid
ORDER BY department.deptid

To do it with PHP and the arrays, you must loop over the arrays, and have an nested inner loop over the resultset. Because you must loop the resultset multiple times, you must use mysql_data_seek() to reset it between each loop:
$rs = mysql_query('SELECT * FROM employees');
echo '<table>';
foreach($arrJob as $job) {
  $job_rs=mysql_query('select jobname from job where jobid='.$job) or die(mysql_error());
  echo '<tr><th colspan="2">'.mysql_result($job_rs,0).'</td></tr>';
  mysql_data_seek($rs,0);
  while($row = mysql_fetch_assoc($rs)) {
    if($row['jobid'] == $job)
      echo '<tr><td>'.$row['name'].'</td><td>'.$row['hired'].'</td></tr>';
  }
  echo '<tr><td colspan="2">&nbsp;</td></tr>';  # blank line
}
foreach($arrDept as $dept) {
  $dept_rs=mysql_query('select deptname from department where deptid='.$dept) or die(mysql_error());
  echo '<tr><th colspan="2">'.mysql_result($dept_rs,0).'</td></tr>';
  mysql_data_seek($rs,0);
  while($row = mysql_fetch_assoc($rs)) {
    if($row['deptid'] == $dept)
      echo '<tr><td>'.$row['name'].'</td><td>'.$row['hired'].'</td></tr>';
  }
  echo '<tr><td colspan="2">&nbsp;</td></tr>';  # blank line
}
echo '</table>';

Open in new window

0
 

Author Comment

by:kobeballa
ID: 23718372
Hi CXR,

Thank you very much. Re; your comment with it's "easier to do it w/ SQL", wouldn't I still need to do the same filtering method to get the output as I desired? The JOIN QUERY will output

==========================
query
==========================
52      job1      adam
52      job1      lisa
129      job2      mary
283      job3      john
283      job3      use

Don't I still need the same filtering method to generate "echo '<tr><th colspan="2">'.mysql_result($job_rs,0).'</td></tr>';"
0
 

Author Comment

by:kobeballa
ID: 23718659
Thank you so much. Can you help with another question:
http://www.experts-exchange.com/index.jsp?qid=24171038
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 39

Expert Comment

by:Roger Baklund
ID: 23719968
>> Don't I still need the same filtering method to generate "echo '<tr><th colspan="2">'.mysql_result($job_rs,0).'</td></tr>';"

No. With the join you let the database server do the work. It will do two of the things you now are doing in the PHP code: fetch the job names and sort/group the rows. The PHP code would be much simpler:
$rs = mysql_query(
  "SELECT job.*,id,name,hired
   FROM employees,job
   WHERE job.jobid = employees.jobid
   ORDER BY job.jobid");
echo '<table>';
$jobname = '';
while($row = mysql_fetch_assoc($rs)) {
    if($row['jobname'] != $jobname)
      echo '<tr><th colspan="2">'.$row['jobname'].'</td></tr>';
    echo '<tr><td>'.$row['name'].'</td><td>'.$row['hired'].'</td></tr>';
  }
echo '</table>';

Open in new window

0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 23720100
Sorry, there was a bug in that code. This should be correct:
$rs = mysql_query(
  "SELECT job.*,id,name,hired
   FROM employees,job
   WHERE job.jobid = employees.jobid
   ORDER BY job.jobid");
echo '<table>';
$jobname = '';
while($row = mysql_fetch_assoc($rs)) {
    if($row['jobname'] != $jobname) {
      if($jobname)
        echo '<tr><td colspan="2"> </td></tr>';  # blank line
      echo '<tr><th colspan="2">'.$row['jobname'].'</td></tr>';
      $jobname = $row['jobname'];
    }
    echo '<tr><td>'.$row['name'].'</td><td>'.$row['hired'].'</td></tr>';
  }
echo '</table>';

Open in new window

0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question