Filter between dates with AJAX, PHP and MySQL

Question

I have a problem when I try to implement a date filter:

My database is something like this:

start_date  |  end_date
2000        |  2005

My PHP select looks like this:

if (empty($processo) && empty($ano)){
                        $pegaSonda = $pdo->prepare("SELECT * FROM sonda WHERE `modelo` = ?");
                        $pegaSonda->execute();
                    } else if(!empty($processo) && empty($ano)) {
                        $pegaSonda = $pdo->prepare("SELECT * FROM sonda WHERE n_processo = '$processo'");
                        $pegaSonda->execute();
                    } else if(!empty($processo) && !empty($ano)){
                        //AQUI EU POSSO COLOCAR O FILTRO POR ANO
                        $pegaSonda = $pdo->prepare("SELECT * FROM sonda WHERE n_processo = '$processo'");
                        $pegaSonda->execute();
                    }

The variable $ano (this is the year that the user will write in a input text and the ajax will send to PHP as Post request).

How can I use the SELECT when the user types the year?

The code I've tried looks like this:

"SELECT * FROM sonda WHERE n_processo = '$processo' AND start_date >= '$ano' OR end_date <= '$ano'"

Any help please? Thanks!

Please edit to add a specific problem statement — "it doesn't work" can be assumed, but how does it not work? What error message or incorrect behavior is characteristic? — Nathan Tuggy
– Nathan Tuggy, Commented Jul 6, 2015 at 1:13
@NathanTuggy hi mate, i just edited it, to be honest i'm learning this, so i don't really know if the SELECT with dates is correct or i'm using it wrong. — William Xavier
– William Xavier, Commented Jul 6, 2015 at 1:17

score 1 · Accepted Answer · 2015-07-06 02:29:07Z

1

Answer that we have reached after discussion

'$ano' BETWEEN start_date AND IF(end_date = '', YEAR(CURDATE()), end_date)

edited Jul 6, 2015 at 2:29

answered Jul 6, 2015 at 1:17

user2594803

Sign up to request clarification or add additional context in comments.

8 Comments

William Xavier Over a year ago

Hi, i tried your code, but i'm not sure if it is not working because the amount of conditions in my SELECT: "SELECT * FROM sonda WHERE n_processo = '$processo' AND start_date <= '$ano' AND '$ano' <= end_date"

user2594803 Over a year ago

@WilliamXavier if you need between use BETWEEN operator dev.mysql.com/doc/refman/5.0/en/…

William Xavier Over a year ago

I think i can't use BETWEEN because i have 2 fields to compare right?

user2594803 Over a year ago

@WilliamXavier '$ano' BETWEEN start_date AND end_date

William Xavier Over a year ago

Great, it worked like a charm, it was not working before because sometimes the "end_date" field is empty, is there a way i can avoid this?

|

Collectives™ on Stack Overflow

Filter between dates with AJAX, PHP and MySQL

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related