Welcome, Guest: Register On Nairaland / LOGIN! / Trending / Recent / New
Stats: 3,151,642 members, 7,813,155 topics. Date: Tuesday, 30 April 2024 at 07:53 AM

Help Needed With Mysql/php Date Query - Programming - Nairaland

Nairaland Forum / Science/Technology / Programming / Help Needed With Mysql/php Date Query (2545 Views)

Generating Crystal Report With MYSQL Database For A VB.NET Application / Genrating Crystal Report With Mysql Database In A Vb.net Application / How To Connet C++ Mfc Application With Mysql (2) (3) (4)

(1) (Reply) (Go Down)

Help Needed With Mysql/php Date Query by g99ma(f): 7:37pm On Sep 12, 2007
Hello,

I have been battling to get this query right for almost 2 days but it seems this battle is one I will not win.

I basically want to display a weekly report for a selected time range. So let's say for instance the selected date range is Sept 12, 2007 to Oct 16, 2007. Based on php date manipulation I was able to get the weeks of year that falls within this date range and they are as follows:

week 37 (Sept 9 - Sept 15)
week 38 (Sept 16 - Sept 22)
week 39 (Sept 23 - Sept 29)
week 40 (Sept 30 - Oct 06)
week 41 (Oct 07 - Oct 13)
week 42 (Oct 14 - Oct 20)

Now in my database table let's say there is an entry (Number of books rented e.g) with start date of Sept 13,2007 and end date of Oct 13, 2007. What I am trying to do is run a select query to pick this entry where the week range falls within the start and end dates in the database. So basically I should have the same value returned for week 37 through 41 and 42 should return null.

Thanks for your anticipated help. Any alternative approach is highly welcome.
Re: Help Needed With Mysql/php Date Query by Fikzy(f): 8:00pm On Sep 12, 2007
can you be more explicit with the fields definitions in your database? i.e, are the date fields defied as mysql database Date Datatype?

post some of your codes too.
Re: Help Needed With Mysql/php Date Query by g99ma(f): 9:00pm On Sep 12, 2007
@Fikzy

Thanks for post. Will let you have my code in a bit , need to put some more comments so you'll understand what I am trying to do.
Re: Help Needed With Mysql/php Date Query by g99ma(f): 9:28pm On Sep 12, 2007
@Fikzy

Find below my php code , sorry that it's a bit long. Also find attached a sample picture of how the report looks like. From the picture you can see that ADN for BG start and end date is Sept 13, 2007 to Oct 13 2007, thus week 42 (Oct 14 - Oct 20) should ideally be 0 (more explanation in the code below). Hope you can help me out.
Thanks
<?php
/**
* Graph
*
* This page display tool forecast report for clients
*
* @package TTS
* @author Ashley Akinpelu
*/
session_start();
header("Cache-control: private"wink;
if (!isset($_SESSION['initiated']))
{
session_regenerate_id();
$_SESSION['initiated']=true;
}
/** mysql functions */
require_once 'mysqlfunctions.php';
//getting database link
$dblink=dbconnect();
/** Site wide functions */
include "functions.php";
/** include standard header file and defines common <head> properties */
$heading="Tool Tracking System | Reports | All Tools";
$topic="Report - All Tools (Client Listing)";
include "header.php";
$admin=$_SESSION['Admin'];
$name=$_SESSION['Name'];
/** File that checks for legitimate login */
include "security.php";
$table="forecast";
$begin=$_SESSION['ccst'];//begin date
$by=substr($begin, 0, 4);//begin year
$bm=substr($begin, 5, 2);//begin month
$bd=substr($begin, 8, 2);//begin day
$end=$_SESSION['ccfh'];//end date
$lbegin=$_SESSION['cclst'];//label begin date
$lend=$_SESSION['cclfh'];//lable end date
$beginw=$_GET['bwk'];//begin week
$endw=$_GET['ewk'];//end week
//writing select query to get row count for date range
$ctsql="SELECT count(*) as ToT FROM $table WHERE start>='$begin' AND end<='$end'";
//executing the select query
$ctres=mysql_query($ctsql,$dblink);
$ctrow=mysql_fetch_array($ctres);
//if row count is zero, no data
if ($ctrow[ToT]==0)
{
echo "<script>alert('Null data was returned from the database for the date range selected. Please select a new date range.')</script>";
echo "<script>window.document.location='report.php'</script>";
}
else
{
$wks=array();
$wkval=array();
//put the week numbers for the date range in an array
for ($week=$beginw; $week<=$endw; $week++)
{
$wks[]=($week);
}
$cwks=count($wks);
if($cwks>10)
{
echo "<script>alert('Please select a new date range that do not span over more than 2 months.')</script>";
echo "<script>window.document.location='report.php'</script>";
}
else
{
for ($i=0; $i<$cwks; $i++)
{
$x=6;
//Timestamp of begin and end dates
$timestamp=mktime(1,1,1,$bm,$bd,$by);
//retrieve week for the begin and end dates
$beginDay=date("w",$timestamp);
//get first day of the begin weeks
$timestampbegin[$i]=$timestamp-(60*60*24)*$beginDay;
$timestampbeginSat[$i]=$timestampbegin[$i]+($x*24 * 60 * 60);
if ($i>0 and $i<$cwks)
{
$y=($i*$x)+$i;
$timestampbegin[$i]=$timestampbegin[$i]+($y*24 * 60 * 60);
$timestampbeginSat[$i]=$timestampbegin[$i]+($x*24 * 60 * 60);
}
$bw[$i]=date("Y-m-d",$timestampbegin[$i]);
$ew[$i]=date("Y-m-d",$timestampbeginSat[$i]);
}
//writing select query to get client that have records for the selected date range
$query="SELECT client_name, count(comp_name) as NumTtl FROM $table WHERE start>='$begin' AND end<='$end'
GROUP BY client_name ORDER BY client_name";
//executing the select query
$result=mysql_query($query,$dblink);
$topic1="$name";
?>
<link href="css/tts.css" rel="stylesheet" type="text/css">
<script type="text/javascript" language="javascript" src="javascript/js.js"></script>
</head>
<body>
<div id="container">
<div id="wcome">
<?php
echo $topic1;
?>
</div>
<div id="header">
<?php
echo $topic;
?>
</div>
<div id="date">
<?php
echo date("F j, Y"wink;
?>
</div>
<div id="center">
<form action="<?php echo $_server['php_self'];?>" method="post" name="aform">
<div class="article">
<?php
echo "<u><b>Date Range:</b> $lbegin - $lend</u><br><br>";
while($row=mysql_fetch_array($result))
{
$clnt=$row[0];
$ent=$row[1];
if ($ent==1)
{
$entf=$ent.' entry found:';
}
if ($ent>1)
{
$entf=$ent.' entries found:';
}
echo "<div class=\"entryH\">$clnt</div>";
echo "<div class=\"entry\">$entf</div>";
//for each cleint name returned get some data from the database
$sql="SELECT comp_name, tool_size, hole_size, func, start, end FROM $table WHERE client_name='$clnt' AND start>='$begin'
AND end<='$end'";
$number=0;
//executing the select query
$res=mysql_query($sql, $dblink) or die("query failed : " . mysql_error());
echo "<table class=\"tab\">
<tr class=\"colHead\">
<td>Name</td>
<td>Tool Size</td>
<td>Hole Size</td>
<td>Avail</td>";
//create td for the numbers of weeks in the date range
for ($i=0; $i<$cwks; $i++)
{
echo "<td>Week $wks[$i]</td>";
}
echo "</tr>";
while($nrow=mysql_fetch_array($res))
{
$compn=$nrow[0];
$tsize=$nrow[1];
$hsize=$nrow[2];
$func=$nrow[3];
$st=$nrow[4];
$ed=$nrow[5];
$number++;
$status=(1 & $number) ? 'Odd' : 'Even';
for ($i=0; $i<$cwks; $i++)
{
/* this is the problem query below*/
//query to get the number of tools that falls within the date range for each comp_name of each client. if a week falls outside the selected time range it should return null
$wkquery="SELECT number from $table WHERE (('$st'>='$bw[$i]' OR '$st'<='$ew[$i]') AND ('$ed'>='$bw[$i]'
OR '$ed'<='$ew[$i]')) AND comp_name='$compn' AND tool_size='$tsize' AND hole_size='$hsize' AND func='$func'
AND start='$st' AND end= '$ed'";
$wkres=mysql_query($wkquery, $dblink) or die("query failed : " . mysql_error());
$wkrow=mysql_fetch_array($wkres);
if($wkrow[0]==null)
{
$numb[$i]=0;
}
else
{
$numb[$i]=$wkrow[0];
$diff=$func-$numb[$i];
if($diff<0 and $numb[$i]>0)
{
$bg="#FF0000";
}
}
}
if ($status=='Odd')
{
echo "<tr class=\"odd\">";
}
else
{
echo "<tr class=\"even\">";
}
echo " <td >$compn</td>
<td >$tsize</td>
<td >$hsize</td>
<td>$func</td>";
for ($i=0; $i<$cwks; $i++)
{
if($numb[$i]==0)
{
echo "<td>$numb[$i]</td>";
}
else
{
echo "<td bgcolor=\"$bg\">$numb[$i]</td>";
}
}
echo "</tr>";
}
echo "</table>";
}
echo "</table>";
}
}
?>
</div>
</form>
</div>
<div id="menu">
<?php
if($admin=="Y"wink
{
menuA();
}
else if($admin=="N"wink
{
menuO();
}
?>
</div>
<div id="footer">
Designed by <b>Ashley Akinpelu</b>.<br>Email:&nbsp;<a href="mailto:g99ma@mun.ca" class="footer">g99ma@mun.ca</a>
</div>
</div>
</body>
</html>

Re: Help Needed With Mysql/php Date Query by webmaster3: 11:40am On Sep 13, 2007
If i understand your post you are trying to select a query based on date range all you have to do is to enter this query format;
SELECT bk FROM BOOKS WHERE date BETWEEN '28-01-2007' AND '28-01-2008';
thats all you need.
Re: Help Needed With Mysql/php Date Query by xanadu: 3:20pm On Sep 13, 2007
Hi, @g99ma.
You did say you would look at alternatives. I'll present you with one way I think perhaps you can achieve the results you want - in a rather simple way. if it suits your purpose you will need to merge it into your main code. Check that it works first, though.

This method assumes a few things:
1. A table of weeks mapping to their various start dates and end dates. E.g. Create a table called weeks, with the following fields: start, end, week_num, where week_num is the week number. An example of 2 such rows is attached below. This means you will need create about 52 rows for the weeks in the year (shouldn't take time - you already pasted 6 rows above) - just a one-off.
I assume you have a way to accept inputs for start date and end date, for your query. So let's say you collect the values like this:

$startdate=$_POST['startdate'];
and
$enddate=$_POST['startdate'];

So at the point where you are doing the check for each week, say the week is in a variable called $weeknum, I would first get an array of the week and its range:
$getDate=mysql_query("select * from weeks where week_num='$weeknum'"wink or die(mysql_error());

Then for that week, the query below should do what we want:
while ($row=mysql_fetch_array($getDate)) {

if ($startdate<$row['end'] AND $enddate>=$row['end']) { echo "Do stuff or say Within range"; } else { echo "Do stuff or say Out of range"; }

}

The above should do one thing - it should return a specific message - in this case 'within range' - if the selected date ranges fall within the week specified. Of course you change the echoed result to what you want.

The downside of this approach is that every year, you will need update the year part of the entry in the weeks table.
I tested the above with the six weeks you put above, and it works. if you have any queries, please let me know.

Re: Help Needed With Mysql/php Date Query by g99ma(f): 6:08pm On Sep 13, 2007
@xanadu
Thanks so much for your help , though I didn't do exactly what you suggested (creating a new table for weeks in the database, etc). Your if statement was exactly what I needed with a lil tweaking.

All,
Thanks for trying to help out as well , twas much appreciated.
Re: Help Needed With Mysql/php Date Query by xanadu: 12:18am On Sep 14, 2007
You're welcome, @g99ma. That's what this forum is all about. I'm glad I was able to assist.

(1) (Reply)

Using 2-dimensional Array In C++ / Are You Ready To Make More Money While You Work Online?..... BITCOIN / Are You New To Programming? Want To Learn But Do Not Know How? Come In!

(Go Up)

Sections: politics (1) business autos (1) jobs (1) career education (1) romance computers phones travel sports fashion health
religion celebs tv-movies music-radio literature webmasters programming techmarket

Links: (1) (2) (3) (4) (5) (6) (7) (8) (9) (10)

Nairaland - Copyright © 2005 - 2024 Oluwaseun Osewa. All rights reserved. See How To Advertise. 43
Disclaimer: Every Nairaland member is solely responsible for anything that he/she posts or uploads on Nairaland.