The LIKE operator doesn't match NULL column values. You'd probably
be better off not putting "col_name LIKE '%'" in your query at all
when no specific value for col_name is to be searched for. Your
query will run more quickly, too, because there will be fewer
conditions in the WHERE clause.
At 2:34 AM +0000 2000-01-06, hypnos wrote:
>I've run into a problem here, but I'm sure I'm
>overlooking something rather similar.
>
>First off, my table declaration:
>
>mysql> DESC headers;
>+-------------+-----------------------+------+-----+---------+-------
>---------+
>| Field | Type | Null | Key | Default | Extra
>|
>+-------------+-----------------------+------+-----+---------+-------
>---------+
>| msg_id | mediumint(8) unsigned | | PRI | 0 |
>auto_increment |
>| msg_from | char(255) | | MUL | |
>|
>| msg_to | char(255) | | MUL | |
>|
>| msg_cc | char(255) | YES | | NULL |
>|
>| msg_subject | char(255) | | MUL | |
>|
>+-------------+-----------------------+------+-----+---------+-------
>---------+
>5 rows in set (0.01 sec)
>
>The problem is the "msg_cc" column, defined as NULL.
>
>This database archives e-mail messages, and everything
>is taken care of automatically. Mail comes in thru
>sendmail, a copy is made, a cronjob puts it into a
>temporary database, and my custom scripts pull out
>the headers I want (see above) and the message body.
>The headers go in one table (the one above) and
>the message bodies go into another table.
>
>My problem has to do, I believe, with trying to
>use a wildcard on the msg_cc column in a SELECT
>query. I have PHP scripts to search this database.
>Every field is searchable, and it is possible to
>have search criteria for only one field or for all
>of them, so for any column that doesn't have search
>criteria associated with it is searched with "%".
>
>Okay, here's an example query created from PHP to
>search only on the msg_to field:
>
>mysql> SELECT p1.msg_id,p1.msg_from,p1.msg_to,
> -> p1.msg_cc,p1.msg_subject
> -> FROM headers AS p1, messages AS p2
> -> WHERE p1.msg_id LIKE '%' AND
> -> p1.msg_from LIKE '%' AND
> -> p1.msg_to LIKE '%eroticaism%' AND
> -> p1.msg_cc LIKE '%' AND
> -> p1.msg_subject LIKE '%' AND
> -> p2.msg_body LIKE '%' AND
> -> p1.msg_id = p2.msg_id
> -> ORDER BY p1.msg_id;
>Empty set (4.00 sec)
>
>However, if I remove the "p1.msg_cc LIKE '%' AND"
>part from the query, I get 9 rows returned. I'll
>just paste the first to save space:
>
>+--------+-------------------------------------+--------+-------------+
>| msg_id | msg_to | msg_cc | msg_subject |
>+--------+-------------------------------------+--------+-------------+
>| 2124 | | NULL | ... |
>+--------+-------------------------------------+--------+-------------+
>
>(I removed the msg_from field above and deleted the
>name/email address to protect the innocent :-)
>
>Anyways, all the fields returned have NULL in the
>msg_cc field.
>
>I want to be able to search the msg_cc field also,
>using the "%" wildcard if no specific search
>criteria are specified. I know this problem has
>to do with the column being defined as NULL, but it
>isn't reasonable to change it to NOT NULL at this
>point.
>
>As I said, I'm sure I'm overlooking something simple
>here, regarding using wildcard fields on NULL columns.
>I could change my query used in the PHP script, but
>sometimes msg_cc has a value, sometimes it's NULL.
>
>Any suggestions/pointers/etc appreciated.
>
>Thanks
>
>------------------------------------------------------
>hypnos <mailto:hypnos@stripped>
>
>
>
>--
>---------------------------------------------------------------------
>Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
>posting. To request this thread, e-mail mysql-thread23235@stripped
>
>To unsubscribe, send a message to:
> <mysql-unsubscribe-paul=snake.net@stripped>
--
Paul DuBois, paul@stripped
