[Bonzo]

I am up dating a page and db. Before I had the month + day in one colum and the year in a nother colum of my database. This made it quite easy to use in sorting by year.

I thought to myself today thats not the " correct " way to do it, I sould be using 0000-00-00.

But I have a small problem - I am populating a dropdown list of years to pick and I cant get it to work now.

Any help would be apreciated.

Anthony

PHP Code:

$query = "SELECT DISTINCT year FROM photos ORDER BY year";

  $result = mysql_query($query)

       or die ("Couldn't execute query."); 

Worked OK with the old db.

PHP Code:

$query = "SELECT DISTINCT date ("Y") FROM photos ORDER BY date ("Y") ";

  $result = mysql_query($query)

       or die ("Couldn't execute query."); 

I thought this would be right but it dose not work. Line 38 is the $query one.
Parse error: parse error in /my_site/choicep.php on line 38

[blaw]

Hello there,

Look closely - you are using double-quotations within double-quotations. You need to escape them if you do not want to close/open the string. In SQL, though, I've read somewhere that you should quote string values with single-quotations (double- would work, fine, I think).

The parse error should go away if you either escape those quotations around Y, but another thing I noticed is that the way you specified the year value is somewhat incorrect. What you wanted is perhaps this:

PHP Code:

$query = "SELECT DATE_FORMAT(date, '%Y') AS 'year' FROM photos ORDER BY year"; 

I assumed that you have a field called "date" that holds date data type (i.e. yyyy-mm-dd). If that's the case, the above should get you what you wanted.

HTH.

BTW, more on date/time functions at MySQL.com: http://www.mysql.com/doc/en/Date_and...functions.html

[Bonzo]

I cant get anything to work; think I will have to try extracting the date when I upload to the database and create another colum.

All that happens now is cant complete query or I get the drop down list but there is no text in it.

I also tried substring(date, 0,4) As year but that had the same effect.

Anthony

[Bonzo]

Thinking to myself just now, that if the dropdown list of the form is changing length as I mess about with the script then it must be working.

The problem is that it is not displaying the data in the list which means a different problem !!!

Anthony

[blaw]

Hi again,

Okay, so the problemn is solved now?

If you're trying to do something new with SQL, it's always good to try out your SQL statement in a "pure" MySQL environment (i.e. command line MySQL client) to see if it's really working. One thing at a time, you know. If the query works, then onto PHP, but echoing every single step to see what you expect is what PHP is handling, and so forth. Debugging takes patience, but it's the necessary devil. Good luck!

[Bonzo]

Hello

It is working in that there is a drop down list generated. I can also see the length of the list change as I try different things. If I select the arrow to expand the list there is no text on the list it is just white and if I run the mouse down the list the different "boxs" change to blue !!!!!

Anthony

[Bonzo]

I am a bit depressed at the moment and am not thinking very well.

Here is the HTML output of the script :

<select name='date'>
<option value=''>
<option value=''>
<option value=''>
<option value=''>
</select>

As you can see the "value" is blank.

This is the current script that output the above.

PHP Code:

$query = "SELECT DISTINCT DATE_FORMAT(date, '%Y') FROM photos ORDER BY date";

  $result = mysql_query($query)

       or die ("Couldn't execute query."); 

Anthony

[Bonzo]

I said I was not very well - my excuse anyway.

Your method worked great blaw the problem was .............

I had not changed "date" to "year" in my form discription !!!!!!

Thanks again Anthony