Hi Dave, all!
revDAVE wrote: > [[...]] > > I'm hoping to get results based on what criteria I type - but I'm not > getting what I expect. I think it's just getting results where in addition > to getting search criteria I type - ALSO none of the search fields can be > blank (not what I hoped) ... 1) Please double-check your other search fields are really blank in the database (have been set to an empty string). If they have not been set at all, I assume they are NULL, and the predicate "column LIKE '%'" will return an "unknown" truth value for this row, so the SELECT will not return it. Documented here: http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html "If either expr or pat is NULL, the result is NULL." (Yes, the three-valued logic of SQL needs getting used to.) > > [[...]] > > > I made a simple results page, > > ... More code here ... ( DW CS3 ) > > $name_list1 = "-1"; > if (isset($_GET['Name'])) { > $name_list1 = $_GET['Name']; > } > $top_list1 = "-1"; > if (isset($_GET['Topic'])) { > $top_list1 = $_GET['Topic']; > } > $mess_list1 = "-1"; > if (isset($_GET['Message'])) { > $mess_list1 = $_GET['Message']; > } > mysql_select_db($database_test1, $test1); > $query_list1 = sprintf("SELECT * FROM mytable WHERE Name LIKE %s and Message > LIKE %s and Topic LIKE %s ORDER BY mytable.id desc", GetSQLValueString("%" . > $name_list1 . "%", "text"),GetSQLValueString("%" . $mess_list1 . "%", > "text"),GetSQLValueString("%" . $top_list1 . "%", "text")); 2) Are you sure "GetSQLValueString()" will evaluate "$name_list1" to an empty string if the variable was set to -1 (your default value), and it will quote the resulting string? If anything else is returned, you will get the wrong pattern for your LIKE predicate. Get some trace of the command given to the database to check this. 3) Assuming you don't run into the NULL problems listed at 1), and your pattern really gets set to '%%' if no value is passed via GET, IMO this should work - but it is no good code: You give the SQL parser a statement that contains conditions which are completely irrelevant, because they will match all rows: column LIKE '%%' It is better not to generate any condition at all if no restriction is specified on that column. (Yes, I know this gets somewhat complicated when you have to decide whether to generate an "AND" or not.) For your current case of just three columns, additional irrelevant conditions may be tolerable, but in general you should avoid them. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
