GROUP BY and DISTINCT Query Problem

[mstern527]

I am trying to write a query that will pull dates from a date column (cl_date) in a table (calendar_League) without giving me the same date more than once. On top of that I am also trying to only return results that are >= todays date.

 

here is what my code looks like...

 

$g_id = $_POST['g_id'];

$c_id = $_POST['c_id'];

$today = date("Y-m-d");

 

"SELECT cl_date FROM calendar_League WHERE g_id='$g_id' AND e_id='$e_id' AND cl_date >= '$today' GROUP BY cl_date"

 

I have also tried this...

 

SELECT DISTINCT cl_date FROM calendar_League WHERE g_id='$g_id' AND e_id='$e_id' AND cl_date >= '$today'

 

Both of those queries gave me the same bad results. If I have multiple unique cl_date results I will only receive one result in my query. If I only have 1 unique cl_date in my result I don't get anything in my select drop down.

 

Now in my testing I have changed the >= to != and I was able to get multiple values that were not the same. (i.e. good results). So there must be something with using DISTINCT or GROUP BY with the greater than statement.

 

The last weird thing I found out was if I run either of the first two queries in phpMyAdmin directly on my DB server I get good results. So there must be something wrong with the statement below.

 

Here is the whole section of code for those queries....

 

$queryDate = mysql_query ("SELECT cl_date FROM calendar_League WHERE g_id='$g_id' AND e_id='$e_id' AND cl_date >= '$today' GROUP BY cl_date");

 

<select name="viewDate">

        <?php 

        if(mysql_num_rows($queryDate)) {

            // we have at least one date, so show all dates as options in select form

                while($row = mysql_fetch_assoc($queryDate))

                { ?>

                <option value="<?php echo $row['cl_date']; ?>" <?php if ($row['cl_date'] == $date) { echo "SELECTED"; } ?>><?php echo date('F jS',strtotime($row['cl_date'])); ?></option>

                <?php }

        }

        ?>

</select>

 

[Wildbug]

Are your id columns numeric?  If so, don't include the quotes around those values in the query string.  You can also use the MySQL CURDATE() function instead of generating a date value, but the results should be equivalent.  The same with using the PHP date function; it'd be easier to use MySQL's DATE_FORMAT, IMO.

 

Don't forget to check for errors on mysql_query().

 

I think this will work:

<?php
$queryDate = mysql_query("SELECT DISTINCT cl_date, cl_date=CURDATE() AS is_today, DATE_FORMAT(cl_date,'%M %D') AS cl_date2 FROM calendar_League WHERE g_id=$g_id AND e_id=$e_id AND cl_date >= CURDATE()");
if (mysql_errno()) die(mysql_error());
?>
<select name="viewDate">
<?php
if ($queryDate && mysql_num_rows($queryDate)) {
	while($row = mysql_fetch_assoc($queryDate)) {
		printf('<option value="%s"%s>%s</option>',
			$row['cl_date'],
			$row['is_today'] == 1 ? ' SELECTED',
			$row['cl_date2']
		);
	}
}
?>
</select>

[mstern527]

Thanks for the tips Wildbug,

 

I am still very new to PHP. I have only been using it for about 4 months now. I never took any formal classes and only know what I have read in books and on forums.

 

Can you clarify something else for me. Regarding the use of quotes in the query string. I have run into instances where my queries don't work unless i use quotes around variables like I did above so I started to single quote all my variables in query strings. I haven't run into any problems since I started doing that. Will adding the quotes hurt anything. I thought it was one of those things where it can't hurt to have them, but if you don't have them in certain cases things won't work.

 

Isn't it also required to have them around variables in all update or insert query strings?

 

Is there a simple rule of thumb that I can go by?

 

Thanks again!

[Wildbug]

Isn't it also required to have them around variables in all update or insert query strings?

 

Is there a simple rule of thumb that I can go by?

 

No, quotes are only required around strings.  This isn't PHP; this is MySQL.  You're using PHP to assemble a command to send to MySQL, but the command needs to be in the format MySQL expects.  Your column values should match your column types.  Now, I think MySQL will convert numeric strings to numbers as best it can ("12the" -> 12), so I'm not sure that is really what caused your query to fail.

 

Incidently, looking at my code, I realize there is an error.  Correction:

$row['is_today'] == 1 ? ' SELECTED', // current line
$row['is_today'] ? ' SELECTED' : '', // replace with this one

 

 

 

(See this link for more info on interpolation of variables in strings in PHP.)