Smudly;10964234 wrote:
and there is a row in my table with a field called Super Mario Brothers
A point of semantics: the field is not called Super Mario Brothers, but it has that value. Your meaning is clear in this particular instance, but might be confusing in others.
Smudly;10964234 wrote:
Super Mario Bros
Super Mario Brothers
You could do like this, assuming you want to match each search word against the title
$searchString = 'Super Mario Bros';
$searchArray = explode(' ', $searchString);
$searchString = "(tile like '%" . implode("%' OR title like '%", $searchArray) . "%')";
$query = 'SELECT field1, field2 FROM sheets WHERE ' . $searchString;
The other way to deal with it would be to use MATCH ... AGAINST, but MySQL only has support for full text search with its MyISAM db engine, which may not be acceptable for other reasons: It doesn't enforce referential integrity constraints, and it lacks transaction support which means it's not ACID-compliant (atomicity, consistency, isolation, durability).
Smudly;10964234 wrote:
So another example. I have 5 rows, each with one cell, named as follows:
Here I actually first read this as you were having a (badly designed) table looking like
id somefield otherfield One Two Three Four Five
which is why terminology is important. But the above should be covered in my first code example.
Smudly;10964234 wrote:
The second issue has to do with my sql query. (...) AS WELL as search only those rows that have an active status set to 'yes'
It looks like this:
[code=php]$query = "select * from sheets where active='yes' && artist like \"%$trimmed%\" OR title like \"%$trimmed%\"
First off, don't use double quotes " for quoting character string literals. According to standard SQL, single quotes are used for quoting string literals, while double quotes are used to quote identifiers, i.e.
SELECT "date", "select" FROM "table" WHERE "select"='string literal'
MySQL uses backtick ` to quote identifiers, and allows either single or double quote to quote string literals. Still, I'd rather recommend staying away from identifiers which need to be quoted, and stick to single quoting string literals. This way, your SQL statements will be portable accross different SQL DBMS, or at least have a high chance of being portable.
A second similar point involves the use of && (and) and || (or), since they are not defined in the standard. The boolean operators are: NOT, AND, OR.
Apart from that, I suspect your only problem with the query is that you're not accounting for operator precedence, which is also defined in the standard. Precedence for logical operators are:
1. NOT
2. AND
3. OR
which means
WHERE NOT field1 AND field2 OR field3
-- is equivalent to
WHERE ((NOT field1) AND field2) OR field3
while you seem to want
WHERE field1 AND (field2 OR field3)
I've only used a couple of languages, but all of those have the same operator precedence for logical operators. So does PHP, but in PHP it's also worth noting that there are two and-operators and two or-operators. Each of those sets follow the above, but one of the sets comes before the other. Precedence, top to bottom: !, &&, ||, and, or
When in doubt, consult the documentation, or use parentheses to ensure that you get the right expression.
Smudly;10964234 wrote:
Here is my code below.
<?php
$var = @mysql_safe($_GET['q']) ;
What is mysql_safe()? There allready exists a function called mysql_real_escape_string which really is "mysql-safe" for string literals, no matter how you modify MySQL through configuration file or through run time command and statements. This funciton needs a connection to the database, since it actually checks the settings before escaping strings. Use this function, and no other.
Do not use the error suppression operator @, unless you have one of the _extremely_ few situations where there is no other way to avoid warnings or errors.
# db connection has to be made first. I assume it's called $link
$var = mysql_real_escape_string( (isset($_GET['q']) ? $_GET['q'] : ''), $link);
