Welcome to WebmasterWorld Guest from 3.80.32.33

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

MySQL / PHP LIKE Command

     
2:30 pm on Feb 8, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:June 17, 2002
posts:1187
votes: 6


Like is useful for finding records that are 'like' something e.g. find last_name contains rizzo, rizo, etc.

SELECT last_name from DATA where last_name like '%riz%';

Thats fine. But how do I find records where the last_name is contained in a string?

This is the opposite kind of searching. e.g. I have a field called CODE and this field contains a letter from 'A' to 'Z'. I have a PHP variable which contains the letters 'CGRY'.

So how do I select records where the field (CODE) is contained in the string $srch?

$srch = 'CGRY';
$result = mysql_query("SELECT CODE from DATA where ".$srch." LIKE %CODE%");

That's not going to work because it doesn't like LIKE %CODE%

Any ideas? I was thinking of using IN("A", "B"..."Z") but that seems inefficient.

4:01 pm on Feb 8, 2003 (gmt 0)

Administrator

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 24, 2001
posts:15756
votes: 0


IN is the best to use I would think.
5:35 pm on Feb 8, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:June 17, 2002
posts:1187
votes: 6


Yeh, thats what I was thinkning,but it seems too inefficient to me. Especially with long lists.

OK, lets turn this problem around a bit. It is quite possible that I'm not using the best method for extracting data. Maybe my conversion from PHP/dBASE to PHP/MySQL is not sound.

I have a form with check boxes in which users can use to include or exclude searches.

_ apples _ bananas _ oranges _ melons _ strawberries

check boxes called "apples", "bananas" etc. if checked, then the variable is set.

The database contains a field with a FRUIT_CODE for each fruit: Apples = A, Bananas = B, oranges = C, melons = D, strawberries = E.

Why do I do that? To keep the size of the database down, thus speed as fast as possible :-)

So with the flat file dBASE I'd use the following code:

First I build a search string to match against the FRUIT_CODE. This is so that users could choose to match none, any or all fruit combinations:

--------
$fruit_srch = '';
if ( isset($apples) ) {
$fruit_srch = $fruit_srch . 'A';
}
if ( isset($bananas) ) {
$fruit_srch = $fruit_srch . 'B';
}
if ( isset($oranges) ) {
$fruit_srch = $fruit_srch . 'C';
}
if ( isset($melons) ) {
$fruit_srch = $fruit_srch . 'D';
}
if ( isset($strawberries) ) {
$fruit_srch = $fruit_srch . 'E';
}
---------

So if the user wishes to include apples, oranges and strawberries in the search then the fruit_srch = 'ACE'

The database is then scanned to look for records in which the FRUIT_CODE is contained in the $fruit_srch string:

$rec = dbase_get_record($fid, $i); ## This is Foxpro / dBASE stuff

if(preg_match("/$rec[10]/", "$fruit_code") {
# we got a matching record
}

Note that $rec[10] is the FRUIT_CODE field in the database. Thus we get a match if that FRUIT_CODE record is contained in the $fruit_code string.

Now this is what I can't seem to do in MySQL and I'll be really dissapointed with it if it can't be done! I've looked at the MATCH() AGAINST() stuff but that's not suitable as it needs full text search indexes or something, and doesnt work if there are more than 30% matching records.

The IN function could work but as I say that seems inefficient. The whole point of me converting from dBASE to MySQL is so that I can write smaller, faster code, and the datbase searches will be faster. I know I got a steep learning curve here but I just can't see the benefit of MySQL at this stage. To me, its not suitable for what I want to do and its not the all singing all dancing thing its supposed to be.

5:49 pm on Feb 8, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Oct 10, 2001
posts:731
votes: 0



$srch = 'CGRY';
$result = mysql_query("SELECT CODE from DATA where ".$srch." LIKE %CODE%");

I presume having the field name "CODE" and the LIKE variable "CODE" is simply from the example. Also not wrapping the LIKE variable (%CODE%) in quotes or double quotes is a local oversight, right?)

When you say "it doesn't like LIKE %CODE%", what kind of error do you get?

Would adding a search string i.e.:


$srch = 'CGRY';
$subsrch = 'A';
$result = mysql_query("SELECT CODE from DATA where ".$srch." LIKE '%".$subsrch."%'");

Whould the above work? (It works for me...)

5:59 pm on Feb 8, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:July 22, 2002
posts:1782
votes: 0


Imagine you have three guys whose preferences for certain fruits should be stored in a table like this:


+-------+-----------+
¦ name. + fruitcode ¦
+-------+-----------+
¦ aaron ¦ ........1 ¦ // likes APPLEs
¦ jesse ¦ ........3 ¦ // likes APPLEs + PEARs
¦ jamie ¦ ........2 ¦ // likes PEARs
+-------+-----------+

The field definition for fruitcode would look like this:


fruitcode int unsigned not null default 0,

Define some fruity constants.


define('APPLE', 1<<0);
define('PEARS', 1<<1);
...
define('nth-FRUIT', 1<<n);

To search for records with APPLES and PEARS OR the fruits and then in your SQL query test against the fruit field by ANDing.


$fruits = APPLE ¦ PEARS;
$sql = "SELECT FROM table WHERE fruitcode & $fruits";

You might want to have a look at the ENUM [mysql.com] and SET [mysql.com] column types.

Andreas

6:24 pm on Feb 8, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:June 17, 2002
posts:1187
votes: 6


>>When you say "it doesn't like LIKE %CODE%", what kind of error do you get? >>

in the mysql prompt the standard "You have an error in your SQL syntax..."

>>I presume having the field name "CODE" and the LIKE variable "CODE" is simply from the example. Also not wrapping the LIKE variable (%CODE%) in quotes or double quotes is a local oversight, right?)>>

Ok, I was expecting the answer to be just a question of correct syntax, i.e. putting %CODE% in quotes, brackets, curly brackets, whatever. But I guess its just not going to be possible.

The SET thing looks like it could do it. But again, I think that is going to be more of an overhead on the database speed than what I'm using at the moment.

Of course, the simplest answer is this:

$fruit_srch_A = $fruit_srch_B = $fruit_srch_C = $fruit_srch_D = $fruit_srch_E = '';

if ( isset($apples) ) {
$fruit_srch_A = 'A';
}
if ( isset($bananas) ) {
$fruit_srch_B = 'B';
}
if ( isset($oranges) ) {
$fruit_srch_C = 'C';
}
if ( isset($melons) ) {
$fruit_srch_D = 'D';
}
if ( isset($strawberries) ) {
$fruit_srch_E = 'E';
}

$result = mysql_query("SELECT CODE from DATA where
CODE = $fruit_srch_A OR
CODE = $fruit_srch_B OR
CODE = $fruit_srch_C OR
CODE = $fruit_srch_D OR
CODE = $fruit_srch_E);

That works, but it seems more inefficient than what I have now!

The current PHP & dBASE code I have is 1800 lines long. I was hoping that MySQL would have super-whizzo commands which would reduce that drastically.

From what I've seen of MySQL (ok to befair, all flavours of SQL), it is only useful for giant telephone directory search databases :-)

-------
All this talk about fruits is driving me banana's! If you want to see the kind of forms I'm working with follow the link in my profile.

Now tell me how MySQL is going to speed up that!

[edited by: Frank_Rizzo at 6:28 pm (utc) on Feb. 8, 2003]

6:26 pm on Feb 8, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:July 22, 2002
posts:1782
votes: 0



WHERE fruitcode & $fruits

is fairly efficient.

7:54 pm on Feb 8, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:June 17, 2002
posts:1187
votes: 6


Ok, this is about the best I can get this:

$fruit_srch = '';
if ( isset($apples) ) {
$fruit_srch .= "'A',";
}
if ( isset($bananas) ) {
$fruit_srch .= "'B',";
}
if ( isset($oranges) ) {
$fruit_srch .= "'C',";
}
if ( isset($melons) ) {
$fruit_srch .= "'D',";
}
if ( isset($strawberries) ) {
$fruit_srch .= "'E',";
}
$fruit_srch = #some substring func here to drop off the last , from $fruit_srch

$result = mysql_query("SELECT CODE from DATA where FRUIT_CODE IN (" . $fruit_srch . ")");

That saves on a whole shedful of OR statements.

7:55 pm on Feb 8, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Oct 15, 2002
posts:710
votes: 0


I'd use the OR with enum fields. But another idea which might work ok if you're willing to make some minor changes:

Instead of the field containing ACE or ABD, etc use a 2-digit number where:

apples = 1
bananas = 2
oranges = 4
melons = 8
strawberries = 16

So somebody who prefers melons+bananas gets a value of 10, strawberries+apples+oranges gets a value of 21, oranges+melons=12, etc. (Each possible combination generates a unique number.)

$int_code = '0';
if ( isset($apples) ) {
$int_code += 1
}
if ( isset($bananas) ) {
$int_code += 2
}
if ( isset($oranges) ) {
$int_code += 4
}
..etc

SELECT * FROM db_table WHERE db_field='$int_code';

8:11 pm on Feb 8, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:July 22, 2002
posts:1782
votes: 0


That´s the bit bashing approach described in my earlier post jamesa.
However, your version where you test for equality will only return exact matches. If there is a record that has its apples, peaches, and grapes bit set and the user selects apples and peaches then your version would not match the record while the bitwise & approach will match the record.

The if statement is not necessary. You can just do a APPLES ¦ PEACHES ¦ GRAPES to get the right number. If you craft your form with that in mind then all you need to do is loop over the array of option buttons and add the returned values. Search for bit bashing in this forum to get an example of how to do it.

Andreas

10:43 pm on Feb 8, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Oct 15, 2002
posts:710
votes: 0


>> will only return exact matches

Of course (slaps head).

>> That´s the bit bashing approach described in my earlier post

Ahhh. I didn't understand what you were doing there with "<<" until now when I looked at the 'Bitwise Operators' section of the PHP manual (and your other posts). Very cool... thanks, andreasfriedrich. That was a real eye opener for me :)

Ok now I can see that here...

$fruits = APPLE ¦ PEARS; 

... that the value of $fruits would be '3' in this example. (Btw "¦" is a pipe, which on my browser looks similar to an "f" character.)

But how does this line...

$sql = "SELECT FROM table WHERE fruitcode & $fruits";

... match a record that has the bit set for APPLE+PEARS+GRAPES? Maybe I don't get how 'fruitcode & $fruits' works in the WHERE statement.
11:24 pm on Feb 8, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:July 22, 2002
posts:1782
votes: 0


Here´s a little overview of bitwise operations:

Bitwise OR is done by comparing the numbers at the bit level. If either number 1 or number 2 or both have a certain bit set so will the result.


+-------------+
¦ bitwise OR. ¦
+------+------+
¦ 1011 ¦ 1011 ¦ // decimal 11 / 11
¦ 0011 ¦ 0101 ¦ // decimal 03 / 05
+------+------+
¦ 1011 ¦ 1111 ¦ // decimal 11 / 15
+------+------+

Bitwise AND is done by comparing the numbers at the bit level. If both number 1 and number 2 have a certain bit set so will the result.


+-------------+
¦ bitwise AND ¦
+------+------+
¦ 1011 ¦ 1011 ¦ // decimal 11 / 11
¦ 0011 ¦ 0101 ¦ // decimal 03 / 05
+------+------+
¦ 0011 ¦ 0001 ¦ // decimal 03 / 01
+------+------+

Bitwise XOR (exclusive or) is done by comparing the numbers at the bit level. If either number 1 and number 2 but not both have a certain bit set so will the result.


+-------------+
¦ bitwise XOR ¦
+------+------+
¦ 1011 ¦ 1011 ¦ // decimal 11 / 11
¦ 0011 ¦ 0101 ¦ // decimal 03 / 05
+------+------+
¦ 1000 ¦ 1110 ¦ // decimal 08 / 14
+------+------+

So fruitcode & $fruits will return zero if no bits match or the number where the bits of both fruitcode and $fruits are equal. We only really care about the truth value, i.e. whether the result is > 0. This will be the case when the fruit set in $fruits is a subset of the one in fruitcode. (See & (bitwise AND) [mysql.com] at the [url=http://www.mysql.com/]MySQL [mysql.com][/url] website).

Andreas

1:14 am on Feb 9, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Oct 15, 2002
posts:710
votes: 0


Very, very cool. I was just scratching the surface before, now I get it - you're comparing the numbers at the bit level (I had to read that a few times for it to sink in... and put a pen and paper to it).

So...

define('APPLE', 1<<0); //sets APPLE = 1 (at the bit level = 0001)
define('PEARS', 1<<1); // sets PEARS = 2 (at the bit level = 0010)
define('GRAPES', 1<<2); // sets GRAPES = 4 (at the bit level = 0100)

$fruits = APPLE ¦ PEARS; //sets $fruits to 3 (bit level = 0011)

(¦ is the vertical pipe)

//this does an 'AND' bit-level comparison
$sql = "SELECT FROM table WHERE fruitcode & $fruits";

So if fruitcode is apple+pears+grapes, or 0111, then the bitwise 'AND' comparison between 0011 and 0111 = 0011 which is positive. if fruitcode were 1100 then the 'AND' comparison would result in 0000. If fruitcode were just APPLE, 0001, then the result would be 0001 which is still positive.

This made my day andreas :)

1:28 am on Feb 9, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:July 22, 2002
posts:1782
votes: 0


Yep, that´s it James :).

Now you see why I kept insisting that this method would solve Frank´s problem quite nicely.

Andreas

11:06 am on Feb 9, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:June 17, 2002
posts:1187
votes: 6


Many thanks for your help guys.

Andreas version has given me inspiration for something totally different!

There is a function in Clipper called bittoc() which is used for bit manipulation and stuff. So what I am currently doing offline on win98 I can now do on the server using what Andreas has mentioned.

Back to the fruit matching. Whilst the method does work, it is, don't you think just a teeny bit over complicated :-) I mean, I have to set hundreds of defines(.... etc. So in this case the code is still going to be quite big.

I blame the mySQL team for this! Why should we as programmers have to scratch our heads around this. Why doesn't mySQL have the simple command:

if(preg_match("/$row[10]/", "$fruit_code") {
# we got a matching record
}

i.e. rather than WHERE FRUIT_CODE LIKE "%A%"
why can't there be WHERE "A" LIKE %FRUIT_CODE%

Once, again, thanks for spending time on this guys. Much appreciated.

12:15 pm on Feb 9, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:July 22, 2002
posts:1782
votes: 0


Frank_Rizzo [webmasterworld.com] wrote at 11:06 on Feb. 09, 2003 in message #15 [webmasterworld.com]
I have to set hundreds of defines

This wouldn´t work anyway ;). While MySQL [mysql.com] uses 64 bit integers PHP [php.net] uses 32 bit integers on most platforms. So you can identify only 32 unique values. To use that number more efficiently you could create a number of fruit tables and reserve some bits to identify the fruit table to use.

Clipper

Clipper 3 was the first programming language I learned :)

Andreas

9:42 am on Feb 10, 2003 (gmt 0)

Junior Member

10+ Year Member

joined:Jan 29, 2003
posts:43
votes: 0


I think I got late in replying but MySQL does
support regular expressions. I have been using it
for quite some time and it is quite fast too.

Frank u can try this out and I hope it solves
your problem and will draw you more towards
MySQL.

$fruit_srch = '';
if ( isset($apples) ) {
$fruit_srch = $fruit_srch . 'A';
}
if ( isset($bananas) ) {
$fruit_srch = $fruit_srch . 'B';
}
if ( isset($oranges) ) {
$fruit_srch = $fruit_srch . 'C';
}
if ( isset($melons) ) {
$fruit_srch = $fruit_srch . 'D';
}
if ( isset($strawberries) ) {
$fruit_srch = $fruit_srch . 'E';
}

$result = mysql_query("SELECT CODE from DATA where
CODE REGEXP \"[$fruit_srch]\"");

Please look for REGEXP in mysql documentation to
find more about it.