MySQL Forums
Forum List  »  PHP

Dealing with inputs from HTML forms
Posted by: Andy A
Date: December 29, 2012 09:06AM

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).

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?

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" />

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.

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.

Options: ReplyQuote

Written By
Dealing with inputs from HTML forms
December 29, 2012 09:06AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.