Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- Query Help Request
October 29, 2008, 1:48 am
rate this thread
to patch in
an ability to select a distinct 'name' from the query where the
current 'All' queries all
records for a given period and 'All Unique' would only bring back
so I'd basically end up with:
(probably showing just the 1st record within the table for a given
name). What this
does is allow a quick glance count on how many different people had an
entry for the period.
Currently for example, 'All' gives me all records for those names as
the query input screen might look like this:
<td><select name="name" maxlength="15">
<option value = "All">All</option>
<option value = "All Unique">All Unique</option>
<option value = "Debra">Debra</option>
<option value = "Edward">Edward</option>
<option value = "Richard">Richard</option>
<option value = "Sandra">Sandra</option>
The second page that I'm dealing with currently looks like this:
$query = "SELECT * FROM $table
WHERE 1 = 1 ";
if($month != "All") $query .= "and month = '".
if($year != "All") $query .= "and year = '".$year."'";
if($name != "All") $query .= "and name = '".
$result = mysql_query($query);
$number = mysql_numrows($result);
and I'm hoping to integrate perhaps else/if to include
IF 'All Unique' is selected in the drop down menu,
QUERY DISTINCT NAME ORDER BY NAME, etc...
Can someone help?
Re: Query Help Request
So you want to select distinct names only if the combobox on the form
had a value of 'All Unique' and otherwise select all records that meet
the other criteria?
Don't use "SELECT *". See
for an explanation of why.
FYI, that WHERE clause is unnecessary.
Note: the following assumes that you've already accounted for "magic
quotes". Untested code follows.
$query = "SELECT name FROM $table";
$query_distinct = "SELECT DISTINCT name FROM $table";
$criteria = array();
if ($month != 'All')
$criteria = "month = '" . mysql_real_escape_string($month) .
if ($year != 'All')
$criteria = "year = '" . mysql_real_escape_string($year) . "'";
if ($name == 'All Unique')
$query = $query_distinct;
elseif ($name != 'All')
$criteria = "name = '" . mysql_real_escape_string($name) . "'";
$where_clause = ' WHERE ' . implode(' AND ', $criteria);
$query .= $where_clause . ' ORDER BY name';
Are you checking to make sure that the query succeeded here? E.g.
if (false === $result)
// Handle error
Charles Calvert | Web-site Design/Development
Celtic Wolf, Inc. | Software Design/Development
http://www.celticwolf.com/ | Data Conversion
(703) 580-0210 | Project Management