I want to use HTML/PHP so that users can select how to order and filter the data provided to them. However I've been having a few issues with different steps. My data is basically a list of people with dates of birth and dates of death (latter is NULL if living).
1.
I'm having trouble with MySQL recognising a variable from PHP as a date. I have the input from a text field being sent to $date (checked this and the value is being stored here), and I want to use this in the query so that it calculates ages as of $date (though only if $date < date of death).
$date = mysql_real_escape_string($_GET['date']);
I had been using the following to calculate ages from birth-death or birth-now (if living) in the query. This formula works:
DATE_FORMAT(ifnull(date_death,Curdate()), '%Y') - DATE_FORMAT(date_birth, '%Y') - (DATE_FORMAT(ifnull(date_death,Curdate()), '00-%m-%d') < DATE_FORMAT(date_birth, '00-%m-%d')) AS age
Which I changed to the following hoping to calculate ages from birth-$date (ie. current age for people alive on $date) or birth-death (for people already deceased by $date):
DATE_FORMAT(if($date>date_death,date_death,$date), '%Y') - DATE_FORMAT(date_birth, '%Y') - (DATE_FORMAT(if($date>date_death,date_death,$date), '00-%m-%d') < DATE_FORMAT(date_birth, '00-%m-%d')) AS age
However, the age column just ends up completely blank, and I don't understand why. I changed Curdate() in the first formula to $date and it results in blank values. $date is specified before the query, and when echoing $date, it prints the date (which is in the format YYYY-MM-DD (as are date_birth and date_death)). Could it be that MySQL isn't recognising $date as a date even though it's in the correct format?
2.
My idea was that the users would select various radio buttons/dropdowns/checkboxes/text input to provide the information about how the ORDER BY and WHERE parts of the query are specified. I had no problem with the ORDER BY since they may only select one option. However for WHERE, the users may have multiple options selected and I'm not sure how to combine these (especially when dealing with multiple values from checkboxes.
$sort = $_GET['sort'];
if($sort == "dob")
$sorttype = "date_birth";
else if($sort == "dod")
$sorttype = "dodliving";
else if($sort == "age")
$sorttype = "totaldays";
$order = $_GET['order'];
if($order == "a")
$ordertype = "ASC";
else if($order == "d")
$ordertype = "DESC";
$where = "";
$orderby = "ORDER BY $sorttype $ordertype";
$query = "SELECT * FROM everyone $where $orderby ";
And my html form:
<form action="search.php" method="get">
<label>Sort by: </label>
<input type="radio" name="sort" value="dob">dob
<input type="radio" name="sort" value="dod">dod
<input type="radio" name="sort" value="age">age
<label>Order: </label>
<input type="radio" name="order" value="a">ASC
<input type="radio" name="order" value="d">DESC
<label>Filters, Age: </label>
<input type="checkbox" name="age" value="50">50
<input type="checkbox" name="age" value="51">51
<input type="checkbox" name="age" value="52">52
<input type="checkbox" name="age" value="53">53
<input type="checkbox" name="age" value="54">54
<input type="checkbox" name="age" value="55">55
<input name="submit" type="submit" />
</form>
So, for example if someone selected 50, 54 and 55, I'd want to have the output as age=('50' OR '54' OR '55'), but then leave the open possibility for other factors to be added to build the query. eg. another filter might be male/female, in which case I'd want 'WHERE age=('50' OR '54' OR '55') AND sex='F'. None of the groups should have no option selected (though I'd need to add a clause so that if none were selected, it either filters everything out).
The problem with the checkboxes for me is that I have multiple values being input for one thing. The options for ORDER BY were all mutually exclusive so it was no problem. But I don't know how to deal with this for checkboxes. I've looked around and some people suggest using arrays. I did try, but the suggestions I read didn't seem to work for me.
3.
Lastly, a minor problem. I have some columns with a URL and I want to display this as a HTML link in the table that is output. The only problem is that some rows are NULL in this column, so when I decided to put the HTML tags around the column value, it created blank links <a href="">Link</a>. If there's no link in the database for that entry, then I don't want there to be a link at all in the output.
Any help on any of the points are very much appreciated.