Page 1 of 1

performing Search DATE queries (search by EXP)

Posted: Thu Aug 07, 2003 12:35 pm
by kendall
Hello,

i have created a php form that searches a database for individuals either by Name or By a date of birth

I have created the form so as to have the date inputs separte i.e. (year , month , date values)

[quote] question 1 how do I create a query string that allows for a SELECT * FROM table WHERE date LIKE year or month or year month date? [/quote]

[quote] the date coloumn is in a year-month-date format should i split the dates of birth into different columns? thus col1 year, col2 month...? thing is i have a date of birth and a date of death i guess it wont be possible but maybe the design could be different...plase advise[/quote]

Kendall

Posted: Thu Aug 07, 2003 1:46 pm
by xisle
Maybe use seperate columns for year, month, and day for a more basic query.
їcode]WHERE year LIKE '$year' || month LIKE '$month' || day LIKE '$day'ї/code]

I prefer storing timestamps that can be converted on the fly with mysql DATE_FORMAT(); ie.

їcode]SELECT date, raindate, DATE_FORMAT(date,'%a, %b %e, %Y') as show_date, DATE_FORMAT(date,'%j') as sort_date, DATE_FORMAT(raindate,'%a, %b %e, %Y') as show_rain
ї/code]

performing Search DATE queries (search by EXP)

Posted: Thu Aug 07, 2003 1:58 pm
by kendall
xisle,

well i had been able to get a work around that suits me and accomplishes my task i did a

[syntax=php]<?php $query = "SELECT *, DATE_FORMAT(Born, '%d %m %Y') AS theDate FROM quesnel_individuals WHERE MATCH(FName, MName, LName) AGAINST(' ".strip_tags($_POST['Name'])." ') ".$_POST['type']." Born LIKE '%".$date."%' ORDER BY FName, LName, Born ASC"; ?>[/syntax]

my problem i face now is the the $_POST['date'] is removing the zero so an expression [syntax=php]<?php Born LIKE '%-8-%' ?>[/syntax] will result in a NULL result.

I would have done a date() but the thing is the search does not necessary have to have the year month date fields filled....isn't there a way to keep the '08' digit in php?

Kendall
P.S. how come my messages arent [syntax=php]formatted? do you see my messages[/syntax][syntax=php]formatted?[/syntax]

Posted: Thu Aug 07, 2003 2:31 pm
by xisle
sounds like you need to add a zero either before the post or after..

Code: Select all

print"<select name="d">\n";
print"<option value="">day</option>\n";
for ($i=1; $i<32; $i++) &#123;
    if($i<10)&#123;
      $n="0".$i;
    &#125;
    else &#123;
      $n=$i;
    &#125;
	print"<option value="$n">$i</option>\n";
&#125;
print"</select> / \n";
if you are doing that already make sure you are passing a string, not an integer...

Code: Select all

Born LIKE '%$date%'
instead of

Code: Select all

Born LIKE '%".$date."%'
not sure why the \[code\] tag doesn't work, maybe you are using quick reply..

performing Search DATE queries (search by EXP)

Posted: Thu Aug 07, 2003 3:05 pm
by kendall
xsile,

:D believe it or not the error was actually generated bacause of a [syntax=php]<select><option>Select Option</option></select>[/syntax]

i thought my plan would have worked but alas the air has been let out of my tires...I 'don't know whats really going on here but I have a FULL TEXT ON a table (first, middle, last) names i did a match(first, middle, last) against ( a first name ) and it worked fine. then i did it against ( a last name) but i dont get a result when there is suppose to { the last name used is the most common last name in the data} how does the full text thing work in searching columns??

Kendall
P.S. im not using the quick reply

Posted: Thu Aug 07, 2003 3:30 pm
by kendall
ok I found out that beacuse the 'last name' is so common its relavance is causing it to trun up no result is there a way around this?

Kendall

performing Search DATE queries (search by EXP)

Posted: Thu Aug 07, 2003 3:59 pm
by kendall
Seeing the predicament that i am in what other options do i have? I want to searches for individuals using either a name (whether first, middle, last) and/ or a date of birth given that i have a table as follows

table (id, fname, mname, lname, sex, dateofbirth, dateofdeath)

and a <form> <input name= 'name'> <input name='year'> <input name='month'> <input name='date>

and results should be similar, relavance to query

Kendall