?
Solved

PHP: Filter recordset with array/dates

Posted on 2009-02-23
2
Medium Priority
?
487 Views
Last Modified: 2012-06-21
Hi,

I have a table in my database.

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

id     name      date1      date2      date3
1      john      1/1/2010      1/2/2010      1/3/2005
2      sue      1/1/2006      1/2/2010      1/3/2006
3      mary      1/1/2010      1/2/2010      1/3/2010
4      adam      1/1/2008      1/2/2008      1/3/2010
5      lisa      1/1/2010      1/2/2009      1/3/2010

==========================
array
==========================

$expArr = array
(
      "type01"=>array("date1","Floating Date"),
      "type02"=>array("date2","Anniversary"),
      "type03"=>array("date3","Birthday"),
);

==========================
RS
==========================

SELECT * FROM Employee WHERE $expArr[''][0] < CURdate();

I need a loop to filter the database against the values from the 3 array[0] for dates that are older than today

==========================
DESIRED OUTPUT
==========================

*Floating Date*

sue      1/1/2006
adam      1/1/2008


*Anniversary*

adam      1/1/2008
lisa      1/2/2009


*Birthday*

john      1/3/2005
sue      1/3/2006

Your help is greatly appreciated.
0
Comment
Question by:kobeballa
2 Comments
 
LVL 3

Accepted Solution

by:
albrieu earned 2000 total points
ID: 23718714
try this loop code
<?php
$bd_host = "localhost";
$bd_usuario = "root";
$bd_password = "";
$bd_base = "";
 
$con = mysql_connect($bd_host, $bd_usuario, $bd_password) or die(mysql_error());
mysql_select_db($bd_base, $con) or die(mysql_error());
 
foreach($expArr as $exprA)
{
	$sql="SELECT * FROM Employee WHERE ";
	$sql.=$exprA[0] 
	$sql.=" < CURdate();";
	$rs = mysql_query($sql) or die("Error: ".$sql);
 
	echo $exprA[1]."<br><br>";
	
	while($row=mysql_fetch_assoc($rs))
	{
		echo $row['name'].' '.$row[$exprA[0]]."<br>";
	}
	
	echo "<br><br>";
	
}
?>

Open in new window

0
 

Author Comment

by:kobeballa
ID: 23719311
Thank you so much for your help. I will reward you with the points. If it's not too much trouble, I was wondering if you can look at my code and see if you can optimize it.

I changed the desired output to:

=========================================
John

Birthday - 1/3/2005

Sue

Floating Date - 1/1/2006
Birthday - 1/3/2006

Adam

Floating Date - 1/1/2008
Anniversary - 1/2/2008

Lisa

Anniversay - 1/2/2009
=========================================
My Code:
 
$query_rs = "SELECT * FROM viewActivePhysicians";
$rs = mysql_query($query_rs, $pt1) or die(mysql_error());
$row_rs = mysql_fetch_assoc($rs);
 
	do
	{
		echo "<h3>".$row_rs['lastName'].',&nbsp;'.$row_rs['firstName']."</h3>";
		
		foreach($expArr as $exprA)
		{
			$sql="SELECT * FROM viewActivePhysicians WHERE userID = ";
			$sql.=$row_rs['userID']." AND ";
			$sql.="date(".$exprA[0].")";
			$sql.=" < CURdate();";
			$rsDate = mysql_query($sql) or die("Error: ".$sql);
			$rowDateData = mysql_fetch_assoc($rsDate);
			$totalrowDateData = mysql_num_rows($rsDate);			
			
			if ($totalrowDateData > 0) { // Show if recordset not empty 	
				echo $exprA[0]." - ".$rowDateData[$exprA[0]]."<br>";	
			}					 
		}
		
	} while ($row_rs = mysql_fetch_assoc($rs));

Open in new window

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
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…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses
Course of the Month13 days, 12 hours left to enroll

755 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