Andy
> I am building a user search engine. Now I do not know how to gett
following:
> a mandatory field and a voluntary field
>
> For example I would like to get all female users and those who are from
> canada.
> The results should show all users who are female regardless of nationality
> But it should also show all users from canada.
ok... if the second field is not mandatory... you should not include it in
the where clause... the select will show all the rows regardless the value
in this field...
> How would this look like?
> SELECT *
> FROM table
> WHERE sex=1 AND country='CA'
SELECT *
FROM table
WHERE sex = 1
it will show all the rows...
== relevance ??
ok... if you want to show the Sex= 1 and country='CA' rows first... may be
you can use an UNION, maybe something like this...
SELECT *
FROM table
WHERE sex = 1 and country = 'CA'
UNION
SELECT *
FROM table
WHERE sex = 1 and country <> 'CA'
... or define a calculated field for these "relevance"... and order the
results by this value...
SELECT (sex = 1 AND country = 'CA') AS relevance, sex, country
FROM table
WHERE sex = 1
ORDER BY 1 DESC
... note the relevance includes all the fields... and the where just the
mandatory conditions... the order must be DESC, because the "relevance" is 1
if the two conditions are match... and 0 if not
== is this the problem ??
if not... may be you are intereseted in different JOIN and UNION clauses...
may be a LEFT JOIN or similar
http://www.mysql.com/doc/J/O/JOIN.html
http://www.mysql.com/doc/U/N/UNION.html
8-)
jaime
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
