Selecting Rows based on date

Smurlos

Hi there

I'm basically doing my own intranet site with all of my bank accounts on so I can keep track of my finances easier.

I have a table in my database called "transactions" with the following fields.
transactionid (The Row ID)
accountid (the Bank Bccount Row ID)
amount (transaction amount)
desc (transaction description)
catid (transaction type)
date (date: YYYYMMDD)

So each transaction has the date in YYYYMMDD format at the moment i.e. 20090412

When I click on an account i.e. "Savings Account #1" it shows me a list of transactions for the current month only (I've created that as a default filter).

What I now want is say a drop down menu OR a list of:

All the months that have transactions previous to the current month.
For example, I have manually input transactions back to April 2009.

So I'd expect the drop down menu / list to show April 2009, May 2009 & June 2009 but not March 2009.

Would anyone be able to help me with a simple bit of scripting on this?
I was wondering if there was a way to search WITHIN a database field?
For example if I had "account.php?accountid=1&filter=200904" would there be a way to search within the date field in my table so that only transactions where the date started with "200904" whether it were "20090412" or "20090420" were displayed?

Many thanks in advance and hope I've not confused you all!
Carl

Smurlos

I've figured out how to do the only showing transactions from a certain date range, however it's probably not very economical...


$SelectTransactions = mysql_query("SELECT * FROM transactions WHERE accountid='$accountid' ORDER BY processed,date");
$NumTransactions = mysql_num_rows($SelectTransactions);

if ($NumTransactions == "0") {
echo("No transactions");
}
else {

echo("<table width='650' cellpadding='3' cellspacing='0'>
<tr>
<td style='border-bottom: 1px solid #000000;' width='25'><b>T ID</b></td>
<td style='border-bottom: 1px solid #000000;' width='175'><b>Transaction Description</b></td>
<td style='border-bottom: 1px solid #000000;' width='75'><b>Date</b></td>
<td style='border-bottom: 1px solid #000000;' width='200'><b>Category</b></td>
<td style='border-bottom: 1px solid #000000;' width='125'><b>Transaction Amount</b></td>
<td style='border-bottom: 1px solid #000000;' width='50'><b>Balance</b></td>
</tr>");

while ($Transaction = mysql_fetch_array($SelectTransactions)) {

if ($Transaction[processed] == "0") {
$bgcolor="#C0ECFA";
}
else {
$bgcolor="#FFFFFF";
}

	$Tdate=$Transaction[date];
	$TransactionCat=$Transaction[categoryid];

// If no specific date range
if (!$range) {

if ((get_date($Transaction[date],"monthyear") == get_date($Today_FullDate_Num,"monthyear"))) {

echo("<tr>
<td style='border-bottom: 1px solid #4BC8F1;' bgcolor='$bgcolor' width='25'><b>$Transaction[transactionid]</b></td>
<td style='border-bottom: 1px solid #4BC8F1;' bgcolor='$bgcolor' width='175'>$Transaction[description]</td>
<td style='border-bottom: 1px solid #4BC8F1;' bgcolor='$bgcolor' width='75'>".formatdate($Tdate)."</td>
<td style='border-bottom: 1px solid #4BC8F1;' bgcolor='$bgcolor' width='200'>".getcategory($TransactionCat)."</td>
<td style='border-bottom: 1px solid #4BC8F1;' bgcolor='$bgcolor' width='125'>
<a href='../adminpanel/finance/edit_transaction.php?tid=$Transaction[transactionid]'>
<b>£".sprintf("%.2f",$Transaction[amount])."</b></a></td>
<td style='border-bottom: 1px solid #4BC8F1;' bgcolor='$bgcolor' width='50'>-</td>
</tr>");

}
else {
echo("");
}

}
else {

if ((get_date($Transaction[date],"monthyear") == get_date($range,"monthyear"))) {

echo("<tr>
<td style='border-bottom: 1px solid #4BC8F1;' bgcolor='$bgcolor' width='25'><b>$Transaction[transactionid]</b></td>
<td style='border-bottom: 1px solid #4BC8F1;' bgcolor='$bgcolor' width='175'>$Transaction[description]</td>
<td style='border-bottom: 1px solid #4BC8F1;' bgcolor='$bgcolor' width='75'>".formatdate($Tdate)."</td>
<td style='border-bottom: 1px solid #4BC8F1;' bgcolor='$bgcolor' width='200'>".getcategory($TransactionCat)."</td>
<td style='border-bottom: 1px solid #4BC8F1;' bgcolor='$bgcolor' width='125'>
<a href='../adminpanel/finance/edit_transaction.php?tid=$Transaction[transactionid]'>
<b>£".sprintf("%.2f",$Transaction[amount])."</b></a></td>
<td style='border-bottom: 1px solid #4BC8F1;' bgcolor='$bgcolor' width='50'>-</td>
</tr>");

}

else {
echo("");
}

}
}
}

So if you went to account.php?accountid=1&range=200906 it would display all transactions from June 2006. But it's selecting all records from the database and using PHP to tailor which records are printed, rather than limiting the results in the database query.

What I need now is to use PHP & my database to list months for which there are transactions. So if my table had the following rows (have limited columns to those important):

transaction id accountid date amount proccessed
1 1 20090410 25.56 1
2 1 20090510 25.56 1
3 1 20090605 51.92 1

Based on those records, I'd like it to say:

April 2009
May 2009
June 2009

Each would link to account.php?accountid=1&range=200906 for june 2009 for example.

Does anyone know how I could do this?
Thanks

Smurlos

Sorted it :-)

// SELECT DATES
$Select_Dates = mysql_query("SELECT DISTINCT LEFT(date,6) AS date FROM transactions WHERE accountid='$accountid' ORDER BY date ASC");
$numdates = mysql_num_rows($Select_Dates);

if ($numdates == "0") {
echo("<a href='account.php?accountid=$accountid&range=".get_date($Today_FullDate_Num,"monthyear")."'>".formatdate($Today_FullDate_Num,"month")."".get_date($Today_FullDate_Num,"year")."</a>");
}
else {

	while ($Months = mysql_fetch_array($Select_Dates)) {

	$Mdate = $Months[date];

	echo("<a href='account.php?accountid=$accountid&range=".get_date($Mdate,"monthyear")."'>".formatdate($Mdate,"month")." ".get_date($Mdate,"year")."</a>  |   ");

	}
}