[RESOLVED] Querying and sorting using date

[Law_Ninja]

I found a post that may be able to shed some light.

http://www.phpbuilder.com/board/showthread.php?t=10357487

This is much like what I'm looking, however the criteria is a bit different. The database table contains 3 columns: date, location, and address.

This may get a bit hairy, so please bear with me.

Using PHP, I would like to query the database based on a date, in the format of MM/DD/YYYY, but the date will be one week beforehand (i.e. the date will be 07/22/2008 if the date supplied was 07/29/2008). The database will be searched for dates one week before the date supplied and for all dates thereafter.

The print out I would like would be as follows (in a while loop, I presume):

<input type='radio' id='location' value='07/23/2008, location 1, address 1 /> 07/23/2008: Location 1, Address 1 <br />
<input type='radio' id='location' value='07/23/2008, location 2, address 2 /> 07/23/2008: Location 2, Address 2 <br />
<input type='radio' id='location' value='07/29/2008, location 1, address 1 /> 07/23/2008: Location 1, Address 1<br />
<input type='radio' id='location' value='07/29/2008, location 3, address 3 /> 07/29/2008, Location 3, Address 3

If need any clarification, I'll try to help you. Any and all help would be greatly appreciated.

[ptbsG_Man]

sounds like a simple between statement to me, unless i miss read

SELECT * FROM table_name WHERE date BETWEEN date1 AND date2

more info at
http://www.w3schools.com/sql/sql_between.asp

Hope this helps

[ptbsG_Man]

here you go, should work but I could not test it because i did not want to make a test DB 😉

<?
$date_then = date("m/d/Y", strtotime("-1 week", $_POST['date']));
$date_now = date("m/d/Y");

$sql = "SELECT * FROM MyTable WHERE date BETWEEN ".date_then." AND ".$date_now;

$result = mysql_query($sql);

while($data = mysql_fetch_assoc($result)) {
	echo "<input type='radio' id='location' value='".$data['date'].", ".$data['location_1'].", ".$data['address_1']."' /> ".$data['date'].", ".$data['location_1'].", ".$data['address_1']."<br />";
}

?>

just change the db stuff and vars in the echo statement to reflect your DB.

[Law_Ninja]

This might do the trick. I'll let you know how it works out.

EDIT: Oops, just got your post, ptbsG_Man. I'll try it out.

[Law_Ninja]

<?php

$con = mysql_connect("localhost","username","password");
if(!$con)
	{
		die ('Could not connect to database: ' . mysql_error());
	}

$db_selected = mysql_select_db("tbl_name", $con);

if (!$db_selected)
	{
		die ("Can\'t use db1 : " . mysql_error());
	}


$date_then = date("m/d/Y", strtotime("-1 week", $_POST['date18']));
$date_now = date("m/d/Y");

$sql = "SELECT * FROM MyTable WHERE Warner Robins BETWEEN ".date_then." AND ".$date_now;


$result = mysql_query($sql);

if(!$result)
	{
		echo "FAILED!";
	}
else
{

	while($data = mysql_fetch_assoc($result)) {
		echo "<input type='radio' id='location' value='".$data['date'].", ".$data['location'].", ".$data['address']."' /> ".$data['date'].", ".$data['location'].", ".$data['address']."<br />";
	}

	mysql_close($con);
}
?>

Long story short, it prints "FAILED!" The error:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/trainio8/public_html/test/location/query.php on line 29

Line 29 is the while loop. Essentially, the query failed. The date column in the database houses a date in TEXT format. Do I need to change the column type from TEXT? Also, I found an error in the SQL statement: it needs a $ in front of "date_then", but this doesn't resolve the issue. Just a slight fix.

Further insight would be appreciated.

[ptbsG_Man]

The date should not be a problem because we are creating them with the date function only thing i see wrong is yours shows MyTable still and the column name has a space in it.

Does php give you any sql error that says where in the sql is causing the problem?

[Law_Ninja]

Here's what I fixed (I still get the same error):

<?php

$con = mysql_connect("localhost","username","password");
if(!$con)
	{
		die ('Could not connect to database: ' . mysql_error());
	}

$db_selected = mysql_select_db("trainio8_Georgia", $con);

if (!$db_selected)
	{
		die ("Can\'t use db1 : " . mysql_error());
	}


$date_then = date("m/d/Y", strtotime("-1 week", $_POST['date18']));
$date_now = date("m/d/Y");

$sql = "SELECT * FROM Warner Robins WHERE date BETWEEN ".$date_then." AND ".$date_now;

$result = mysql_query($sql);


while($data = mysql_fetch_assoc($result)) {
			echo "<input type='radio' id='location' value='".$data['date'].", ".$data['location'].", ".$data['address']."' /> ".$data['date'].", ".$data['location'].", ".$data['address']."<br />";
		}

	mysql_close($con);

?>

I fixed the column name and the table name. If the table has a space in it, could that be the problem? How would I fix that? I put a space in the table name for the actual table so that the code would accept a variable that reads the same exact way: with a space, so no guesswork would be involved.

Also, I echoed $date_then: when the input is 08/09/2008, I get 12/24/1969, lol. $date_now is correct, though.

Does php give you any sql error that says where in the sql is causing the problem?

The only error I get is the one I posted above:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/trainio8/public_html/test/location/query.php on line XX

EDIT: I also want to add that I would like $date_now to pretty much be $date_forever. Basically, I would like all dates in the query to be all those that occur after one week in the past. So if the input was 07/23/2008, I would like all queries to include every date in the database after 07/23/2008.

[m_tt]

Yes, the space will cause problems. Also, since you are using m/d/Y format I believe it needs to be quoted as well.

$sql = "SELECT * FROM `Warner Robins` WHERE `date` BETWEEN '" . $date_then . "' AND '" . $date_now . "'";

As for $date_then, what does $_POST['date18'] output?

[m_tt]

Law Ninja;10879589 wrote:

EDIT: I also want to add that I would like $date_now to pretty much be $date_forever. Basically, I would like all dates in the query to be all those that occur after one week in the past. So if the input was 07/23/2008, I would like all queries to include every date in the database after 07/23/2008.

You don't need between for that...

$sql = "SELECT * FROM `Warner Robins` WHERE `date` >= '" . $date_then . "'";

[Law_Ninja]

m@tt;10879591 wrote:

Yes, the space will cause problems. Also, since you are using m/d/Y format I believe it needs to be quoted as well.

$sql = "SELECT * FROM `Warner Robins` WHERE `date` BETWEEN '" . $date_then . "' AND '" . $date_now . "'";

As for $date_then, what does $_POST['date18'] output?

The output is in the form of MM/DD/YYYY.

As for the space problem: 🙁 Guess I'll have to figure something out.

m@tt;10879592 wrote:

You don't need between for that...

$sql = "SELECT * FROM `Warner Robins` WHERE `date` >= '" . $date_then . "'";

Okay, thank you. I will rearrange a few things and try this out again.

[Law_Ninja]

<?php

$con = mysql_connect("localhost","username","password");
if(!$con)
	{
		die ('Could not connect to database: ' . mysql_error());
	}

$db_selected = mysql_select_db("trainio8_Georgia", $con);

if (!$db_selected)
	{
		die ("Can\'t use db1 : " . mysql_error());
	}


$date_then = date("m/d/Y", strtotime("-1 week", $_POST['date18']));
$date_now = date("m/d/Y");

$sql = "SELECT * FROM 'Warner_Robins' WHERE 'date' >= '" . $date_then . "'";


$result = mysql_query($sql);



	while($data = mysql_fetch_assoc($result)) {
		echo "<input type='radio' id='location' value='".$data['date'].", ".$data['location'].", ".$data['address']."' /> ".$data['date'].", ".$data['location'].", ".$data['address']."<br />";
	}

	mysql_close($con);

?>

Same problem:
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/trainio8/public_html/test/location/query.php on line 27

Line 27 = while loop.

[m_tt]

Notice the quotes you use for tables and fields are different than those used for strings.

SELECT * FROM `MyTable` WHERE `MyField` = 'My Value';

[Law_Ninja]

Wow, I've never had a problem with those types of quotes before. Thank you.

That got rid of the error, but now the while loop is not printing anything, which leads me to believe that isn't not finding anything in the database. It won't even echo "Worked".

Any thoughts?

[m_tt]

Try using mysql_num_rows...

if ( mysql_num_rows( $result ) > 0 )
{
	while( $data = mysql_fetch_assoc( $result )) 
	{
		print_r( $data );
	}
}
else
{
	echo "No results.";
}

[Law_Ninja]

<?php

$con = mysql_connect("localhost","username","password");
if(!$con)
	{
		die ('Could not connect to database: ' . mysql_error());
	}

$db_selected = mysql_select_db("trainio8_Georgia", $con);

if (!$db_selected)
	{
		die ("Can\'t use db1 : " . mysql_error());
	}


$future_date = $_REQUEST["date18"];
$zdate = strtotime($future_date);
$xdate = $zdate - (7 * 24 * 60 * 60);


$sql = "SELECT * FROM `Warner_Robins` WHERE `date` >= '" . $xdate . "'";


$result = mysql_query($sql);

	if ( mysql_num_rows( $result ) > 0 )
	{
		while( $data = mysql_fetch_assoc($result))
		{
			print_r( $data );
		}
	}
	else
	{
		echo "No results.";
	}

mysql_close($con);

?>

Prints "No results." 🙁 I don't get it...

And thank you for your help so far. Continued assist is always appreciated.

[m_tt]

Maybe you should try to echo $sql and see what the query looks like.

[Law_Ninja]

Well, I fixed a bug or two, but I still get "No results."

<?php

$con = mysql_connect("localhost","username","password");
if(!$con)
	{
		die ('Could not connect to database: ' . mysql_error());
	}

$db_selected = mysql_select_db("trainio8_Georgia", $con);

if (!$db_selected)
	{
		die ("Can\'t use db1 : " . mysql_error());
	}

$future_date = $_REQUEST["date18"];
$zdate = strtotime($future_date);
$xdate = $zdate - (7 * 24 * 60 * 60);
$ydate = date('m/d/Y', $xdate);

echo $ydate . "<br />";


$sql = "SELECT * FROM `Warner_Robins` WHERE `date` >= '" . $ydate . "'";

echo $sql . "<br />";

$result = mysql_query($sql);

	if ( mysql_num_rows( $result ) > 0 )
	{
		while( $data = mysql_fetch_assoc($result))
		{
			print_r( $data );
		}
	}
	else
	{
		echo "No results.";
	}

mysql_close($con);

?>

This yields the following:

08/02/2008
SELECT * FROM `Warner_Robins` WHERE `date` >= '08/02/2008'
No results.

[m_tt]

What field type are you using for the date column? I'm guessing it's a string, which cannot be compared with those operators ( > < = )

If that is the case, I recommend you convert the field to DATE, which changes the format YYYY-MM-DD and allows less/greater than operators.

If you must have that format, you can convert it after querying or during. See MySQL DATE_FORMAT

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

[Law_Ninja]

Wow, I feel like such an idiot...when I was messing with the field types earlier, I neglected to realize that all my date entries changed to 0000-00-00. :quiet:

On a great note, I'm finally getting results. It prints everything almost as I want it, so I'd like to ask a question, if I may.

The format of the print out (with radio buttons on the left of each entry, respectively):

08/23/2008, Comfort Inn and Suites, 123 Main St.
08/23/2008, Somewhere Else, 123 Someplace Dr.
09/06/2008, Comfort Inn and Suites, 123 Main St.
09/06/2008, Somewhere Else, 123 Someplace Dr.

How would I go about formatting this to make it look like this:

http://www.traininginstructorsinc.com/test/location/preview.html

Here's the PHP code if you need it.

<?php

$con = mysql_connect("localhost","username","password");
if(!$con)
	{
		die ('Could not connect to database: ' . mysql_error());
	}

$db_selected = mysql_select_db("trainio8_Georgia", $con);

if (!$db_selected)
	{
		die ("Can\'t use db1 : " . mysql_error());
	}

$future_date = $_REQUEST["date18"];
$zdate = strtotime($future_date);
$xdate = $zdate - (7 * 24 * 60 * 60);
$ydate = date('Y-m-d', $xdate);


$sql = "SELECT DATE_FORMAT(date, '%m/%d/%Y') AS date,location, address FROM `Warner Robins` WHERE `date` >= '" . $ydate . "' ORDER BY date ASC, location ASC";

$result = mysql_query($sql);

	if ( mysql_num_rows( $result ) > 0 )
	{
		while( $data = mysql_fetch_assoc($result))
		{

			echo "<input type='radio' id='location' value='".$data['date'].",".$data['location'].",".$data['address']."' /> ".$data['date'].", <strong>".$data['location']."</strong>, ".$data['address']."<br />";
		}
	}
	else
	{
		echo "No locations available.";
	}

mysql_close($con);

?>

That's the last thing, I swear. 😃

[m_tt]

This is how I would do it. Group your results in an array, then loop it for output. I did not test this but it should look like what you want.

$results = array( );
if ( mysql_num_rows( $result ) > 0 )
{
	while( $data = mysql_fetch_assoc( $result ) )
	{
		$results[ $data[ 'date' ] ][] = $data; // Build multi-dimensional array
	}
}

if ( $results )
{
	echo '<table border="1">'; // Start outputting the table

foreach( $results as $date => $rows ) // Loop all results
{
	echo '<tr><th colspan="2">' . $date . '</th></tr>'; // Output the group (date) heading

	foreach( $rows as $row ) // Loop all rows for each date
	{
		echo '<tr><td><input type="radio" name="location"></td><td><strong>' . $row['location'] . '</strong><br>' . $row['address'] . '</td></tr>'; 
	}
}

echo '</table>';
}

That's the last thing, I swear.

Now please mark this thread resolved. 🙂