Show Poll By Date...

[Lou]

I've almost completed my poll item, but I'm struggling putting the finishing touches on it.

 

Basically, I'm storing polls, answers, and votes in MySQL. The polls table has the poll_id, question, and date. The answers table has the answer_id, poll_id, answer, and votes.

 

I have everything automated except when to show a particular poll. During August 2011 I want to show one poll along with it's question and answer choices. During September 2011, another. In the polls table I have a column called date that is type date. It stores dates in the format 2011-08-01 (year-month-day). I thought about making a variable that stores the current date in the same format. However when I put this variable in my query (since the variable will always change based on the current date) I get errors because the query expects the date string to be '2011-08-01', not 2011-08-01, without the quotes.

 

I store every date as Year-Month-FirstDayofMonth... 2011-08-01 for August's poll... 2011-09-01 for September's poll

 

I store the current date (2011-08) for Year-Month plus '01' for the first day of the month like this:

$current_poll = ('Y' . "-" . 'm' . "-" . '01');

 

However when I put $current_poll in the query I always get errors because it expect the string to be surrounded in quotes.

 

Here's my working query with the date hard coded.

 

$select_poll = "SELECT P.poll_id, P.question, A.answer_id, A.answer FROM poll_questions P 
INNER JOIN poll_answers A ON p.poll_id = A.poll_id WHERE P.date = '2011-08-01'";

 

I want the date to be automated so when it's September 1, the poll with the date 2011-09-01 shows.

So I'm stuck... Is there an easier way to do this?

[HartleySan]

I have a feeling that the dates being handled by the database are date objects, whereas the date that your providing is a string object.

 

Actually, I'm really bad at handling dates and have little to no experience with them, but I imagine that PHP has all sorts of conversion functions for this very sort of thing.

 

If you don't resolve it soon, I'll look into it more later, and see if I can find an answer.

[Lou]

I have started to use PDO with the MySQL database for all of my inserts, updates, selects. Would that make a difference? I really need more work on my MySQL/MySQL skills. I had to take a break from this, my head was starting to spin.

[Stuart]

However when I put $current_poll in the query I always get errors because it expect the string to be surrounded in quotes.

 

This is not the problem - is this the actual line you're using?

 

$current_poll = ('Y' . "-" . 'm' . "-" . '01');

 

Because that won't produce a date unless you use the date function like:

 

$current_poll = date('Y-m-01');

 

And then this will work:

 

$select_poll = "SELECT P.poll_id, P.question, A.answer_id, A.answer FROM poll_questions P INNER JOIN poll_answers A ON p.poll_id = A.poll_id WHERE P.date = '$current_poll'";

[Lou]

Hi Stuart.

 

Yes, I used the date() function. I still couldn't get it to work. I'm sure there's a way to do it with that method though, I'll try your suggestion.

 

But, I have gotten it to work by using some SQL functions. This works.

 

$select_poll = "SELECT P.poll_id, P.question, A.answer_id, A.answer FROM poll_questions P 
INNER JOIN poll_answers A ON p.poll_id = A.poll_id 
WHERE date = (SELECT DATE_FORMAT(NOW() ,'%Y-%m-01'))";

 

Each poll question has a date column and I store the first date of the month in it... 2011/08/01, 2011/09/01, 2011/10/01. When the new month starts, the new poll and answers are shown.

[Lou]

Stuart, your way works. I think i either forgot date() or instead of m I used M. So now we have two ways to make it work. Thank you both for your help.