Form with "Select All" option

[dave1950]

I have a form for a search application that allows a user to select a document type.  There are four document types with values based on the integer value in a mysql db table:

<form  method="get" action="" id="search_criteria"> 
                <select name="doctype">
                    <option value="1">Text</option>
		    <option value="3">Background</option>
		    <option value="2">Reports</option>
 		    <option value="4">Index</option>
	        </select></br><br>	
                <input type="submit" name="submit" value="Submit">
</form>

This works fine but only allows for choosing one document type at a time. 

 

I have added the attribute “multiple” which does make the list entirely visible with the option of choosing more than one doctype, but when I run the application, it only displays one of the doctypes – the last one.  For example, if I choose all four, only results with the Index doctype are displayed.

 

Ideally, I would like for the user to be able to choose “Select All Documents” by default and get search results for all four types.  The form would look like this, but I don’t know what the value for “Select All Documents” should be:

<form  method="get" action="" id="search_criteria"> 
                <select name="doctype">
                    <option value="?">Select All Documents</option>
                    <option value="1">Text</option>
		    <option value="3">Background</option>
		    <option value="2">Reports</option>
 		    <option value="4">Index</option>
	        </select></br><br>	
                <input type="submit" name="submit" value="Submit">
</form>

In the php code, the GET value from the form (doctype) is used in the $doctype variable that is part of the WHERE clause in my sql query.

$doctype = isset($_GET['doctype']) ? $_GET['doctype'] : '';

I have tried entering a value in the form for “Select All Documents” that would fit into the WHERE clause of the sql query, with numerous variations, but no luck.

 

I need some feedback/guidance on how to make this work.  Is there a simple solution that I am overlooking? Or will I have to create some complex IF/ELSE type of code that will work with the sql query?   Any help solving this would be greatly appreciated.

[mac_gyver]

if your goal is to allow a Select All Documents/default choice to exist, you would assign a unique value/keyword to that choice and the server side code would test for that value/keyword and take an appropriate action (i.e. build the query without the $doctype term in the WHERE clause to match all document types.)

 

if your goal is to allow the multiple attribute to work (i.e. the user can select any combination of the choices), you must make the select name attribute an array - name="doctype[]" and process the data on the server as an array of values (using the IN() mysql comparison function in the WHERE clause.)

[dave1950]

Thanks for laying out my options so clearly.  I have assigned a value of "all" in the form for Select All Documents and  am attempting the first option via if/else.  The correct doctype shows up in the url from GET and I am using the same $doctype variable as before:

$doctype = isset($_GET['doctype']) ? $_GET['doctype'] : '';

Then my if/else is this:

if ($doctype = 'all')
{
$dbh = new PDO("mysql:host=$dbhost; dbname=$dbname", $dbuser, $dbpass);
$stmt = $dbh->prepare("SELECT
Full_Documents.FullDocumentID,
Full_Documents.DocType,
CONCAT(Full_Documents.ReadingNo, '-', Full_Documents.SequenceNo) AS full_doc_number,
CONCAT(Readings.Month, '/', Readings.Day, '/', Readings.Year) AS date,
CONCAT(Readings.Year, '-', Readings.Month, '-', Readings.Day) AS DateOrder
FROM Full_Documents, Readings
WHERE Full_Documents.ReadingNo = Readings.ReadingNo AND Full_Documents.SequenceNo = Readings.SequenceNo AND
Full_Documents.SectionText LIKE ?
ORDER BY $order_results
");
$stmt->execute(array("%$search%"));
$num_records = $stmt->rowCount();

if($num_records > 0)
$links = array();{
while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
$links[] = $row;
}
}

else

{
$dbh = new PDO("mysql:host=$dbhost; dbname=$dbname", $dbuser, $dbpass);
$stmt = $dbh->prepare("SELECT
Full_Documents.FullDocumentID,
Full_Documents.DocType,
CONCAT(Full_Documents.ReadingNo, '-', Full_Documents.SequenceNo) AS full_doc_number,
CONCAT(Readings.Month, '/', Readings.Day, '/', Readings.Year) AS date,
CONCAT(Readings.Year, '-', Readings.Month, '-', Readings.Day) AS DateOrder
FROM Full_Documents, Readings
WHERE Full_Documents.ReadingNo = Readings.ReadingNo AND Full_Documents.SequenceNo = Readings.SequenceNo AND
Full_Documents.SectionText LIKE ? AND Full_Documents.SectionTypeID = ?
ORDER BY $order_results
");
$stmt->execute(array("%$search%", $doctype));
$num_records = $stmt->rowCount();

if($num_records > 0)
$links = array();{
while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
$links[] = $row;
}
}

The code runs without error but the search results always reflect the "all" value, even when I have selected a different different doctype in the form and the URL reflects the correct doctype.  Therefore I am assuming that I have a mistake in my if/else, but cannot find anything wrong there.  I am php newbie, so I just want to make sure I am not missing anything obvious.

 

1.  Essentially, is this the approach that you described in your first option?

 

2.  Do you see any problems with the way the if/else is coded?

 

Thanks again for your assistance.

[mac_gyver]

here's another important concept - DRY (Don't Repeat Yourself). you should never repeat code, especially if you want people in help forums to look at your code. when you post a wall of code, it cuts down on the number of people that will even look at it.

 

the parts of your code that are common, should be factored out and not repeated. the only conditional code should be what changes between those two cases. another point of not having a wall of code is it makes it easier to see what the actual code is and what might be wrong with it.

 

the reason for your incorrect result is you have one = in the if ($doctype = 'all'). this assigns the value 'all' to $doctype and then tests if that is true or false. since the string 'all' is a true value, your code always runs that branch of code. two == is an equal comparison.

 

some more tips -

 

1. you should use alias names in your query statements (more DRY. repeating the table name 5-10 times just leads to typo's...)

 

2 you should build your sql query statements in a php variable. this allows you to echo/log it for debugging purposed and separates the query statement from the php code that runs it.

 

3 if $order_results comes from user supplied values, you must validate that what you put into the query only contains expected values to avoid sql injection.

 

4) your Year,  Month, and Day columns should be ONE field of type DATE (YYYY-MM-DD.) you can then use that directly to order by and if you need it in any other format, simply use the mysql DATE_FORMAT() function in your query.

 

5) you apparently have two fields in common between your Full_Documents and Readings tables? that's a bad design. the relationship between related data tables should use one value, the primary id of the main table. based on your column names, do you even have a primary id assigned to the Full_Documents table?

 

you also have some mis-located { } in your if/while logic.

 

equivalent code, without any repetition -

$dbh = new PDO("mysql:host=$dbhost; dbname=$dbname", $dbuser, $dbpass);

$type_term = '';
$parms = array("%$search%");

if ($doctype != 'all')
{
    $type_term = 'AND d.SectionTypeID = ?';
    $parms[] = $doctype;
}

$query = "SELECT
d.FullDocumentID,
d.DocType,
CONCAT(d.ReadingNo, '-', d.SequenceNo) AS full_doc_number,
CONCAT(r.Month, '/', r.Day, '/', r.Year) AS date,
CONCAT(r.Year, '-', r.Month, '-', r.Day) AS DateOrder
FROM Full_Documents d, Readings r
WHERE d.ReadingNo = r.ReadingNo AND d.SequenceNo = r.SequenceNo AND
d.SectionText LIKE ? $type_term
ORDER BY $order_results
";

$stmt = $dbh->prepare($query);
$stmt->execute($parms);
$num_records = $stmt->rowCount();

if($num_records > 0){
    // note: this block of code can be replaced with one fetch_all() statement
    $links = array();
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
        $links[] = $row;
    }
}

also, moving to the php code forum section as this is more php code, rather than just the html of a select menu.

Edited April 19, 2014 by mac_gyver

[dave1950]

Thanks for the abundance of help you have provided.  To my initial question, the answer was simple and direct and fixed the problem.

 

Specifically with regard to points 1 and 2, I will focus on avoiding repetitions in my code and using variables in my queries based on your equivalent code, which is very instructive.

 

3.  Yes the $order_results comes from user supplied values, but it is a forced choice (order by document number or date) in a dropdown menu, so I don't think it presents a risk for sql injection.  The data should be valid with regard to expected values since it works.

 

4 & 5: Yes the database that I inherited is messy and I am working on cleaning up both those issues (and several others). 

 

A lot to learn here.

[mac_gyver]

3.  Yes the $order_results comes from user supplied values, but it is a forced choice (order by document number or date) in a dropdown menu, so I don't think it presents a risk for sql injection.  The data should be valid with regard to expected values since it works.

 

 

anyone can submit anything they want to your web page. your dropdown menu means nothing as far as security is concerned. you MUST enforce security on the server. ALL external data cannot be trusteed.

[dave1950]

Thanks for the explanation and warning.

[mogosselin]

Like @mac_gyver said, anybody can change anything in a web page. Open up the developer tools of your browser (if you're on Windows, press F12, it should work with Chrome, FF and IE). In the panel where you see all of your HTML, try to find your drop down. You can then edit the value or your drop down element, live, and submit the form.

 

Or the longer way: Save any HTML page on your hard drive that has a form on it. Open it in a text editor. Find the <form> element. Ensure that the action attribute has an absolute link as its value (example: <form action="http://www.thesite.com/form">).

 

Change all the values that you want.

 

Open that HTML file in your browser. Submit the form.

That's it, you submitted custom values even if there was "fixed" choices (radio buttons, drop down, etc...).

[dave1950]

Very informative.  As noted, a lot to learn. I really appreciate your additional (and very explicit) input on this important issue.