preg_match() help how to find two words in a string

[CStrauss]

I am just now diving into learning use regular expression and my head is spinning with all the articles I have been reading. I think what is must confusing is all the symbols that are used when creating a regular expression.

So I decided to set someting up to help me play with regular expressions and I have a few questions. But first here is what Im doing to help you help me understand.

I have a database that has a field I entered some keywords. Now Im querying the database to search the keywords for words I passed through a link. Here is the code im using.

	$sql = mysql_query("SELECT * FROM items WHERE itemType={$_GET['itemType']}");
	if(!sql){
		echo "Error Performing MySQL Query: ".mysql_error();
	}else{
		while($row=mysql_fetch_assoc($sql)){
			stripslashes(extract($row));
			echo "<p>$itemType</p>";
			if(preg_match('/.*'.$_GET['item'].'.*/i',$tags)){
				echo "<p>$item_name</p>";
			}
		}
	}

Now this code works fine but im trying to modify it to find the tags that have 2 certian words for example lets say i have some fields that has dog,cat,mouse,bird. And I want to display all the fields that have the words dog and cat in them. In my above code I have it to were i can display one of the two but how can i modify the code above to include both. Or does preg_match only allow to search for one word at a time?

Also i found this code on the net somewhere that sorta explained what i was trying to learn but im not total sure what the symbols mean specificly /.* before and after my variable. I had to concantenate the variable in cause for somereason it gaveme errors if i didnt. I know the i tells it to allow lower and uppercase matches to be vaild. So some can explain what and why the symobls in queston are I might begin to understand a bit better, and if someone can modify what my code should look like if im trying to find two certain words with my statement like cat and dog. I would greatly appreciate it.

Thanks have a good day

[laserlight]

Actually, you do not need to use regular expressions here, as all you want to do is some simple case-insensitive searching of the subject string.

$search_array = array('dog', 'cat');
$sql = mysql_query("SELECT * FROM items WHERE itemType={$_GET['itemType']}");
if (!sql) {
    echo "Error Performing MySQL Query: " . mysql_error();
} else {
    while ($row = mysql_fetch_assoc($sql)) {
        foreach ($row as $field) {
            foreach ($search_array as $search) {
                if (stripos($field, $search) !== false) {
                    // found 'dog', 'cat', or some other search string
                }
            }
        }
    }
}[code=php]
Now, with regex you can write it as:
[code=php]$search_array = array('dog', 'cat');
$search = preg_quote(implode('|', $search_array));
$sql = mysql_query("SELECT * FROM items WHERE itemType={$_GET['itemType']}");
if (!sql) {
    echo "Error Performing MySQL Query: " . mysql_error();
} else {
    while ($row = mysql_fetch_assoc($sql)) {
        foreach ($row as $field) {
            if (preg_match('/(' . $search . ')/i', $field) {
                // found 'dog', 'cat', or some other search string
            }
        }
    }
}

The idea is that /(dog|cat)/i will match 'dog' or 'cat' in a case-insensitive manner.

That said, this is only for testing, right? If you really do have to check columns in a table like that, you have something very wrong with your database schema.

[CStrauss]

well the reason im doing i this way is im acutally setting up a database that has basicly an inventory of items i have stored on my computer and links are generated from categories in my database that send values via the links.

In my database I have all the information needed for an item except some items can be for more then another item in my database so i figured the best way to set it up is have a tags field which is basicly a set of common keywords that realate to several of the diffrent items.

So now i was using the values passed via links as the keywords in the regular expresion one was to get all the items that to keep with my example above was say pets but only wanted items that realated for dogs or cats based on the keywords inserted in the tag fields so thats where i thought regular expression would come in handy to search that tags field for items that dogs and cats in the list.

Get my point? But anyways thats for the examples and I will put them to good use 😃

[CStrauss]

Well my idea sorta worked. In my tags field which is the field that has keywords for example it one record has keywords like this.

Aiko 3,Victoria,Victoria 3,Clothing,Textures.

those are the words im searching for based on what values my link passed.

HERE is the code I ended up getting to work.

	$sql = mysql_query("SELECT * FROM sub_cat_table WHERE itemType=$req ORDER BY sub_catName ");
	if(!$sql){
		echo "Error Performing MySQL Query:".mysql_error();
	}else{
		while($row=mysql_fetch_assoc($sql)){
			stripslashes(extract($row));
			echo '<div style="display:inline;"><a href="inventory.php?req=display&itemType='.$itemType.'&item='.$_GET['item'].'&cat='.$sub_catName.'">'.$sub_catName.'</a>&nbsp;</div>';
		}
	}
		// Display All Items For Selected Item
	$sql = mysql_query("SElECT * FROM poser_items WHERE itemType='$itemType'");
	while($row = mysql_fetch_assoc($sql)){
		stripslashes(extract($row));
		if(eregi("^(".$_GET['item'].").+$",$tags)){
			echo "<p>$item_name</p>";
		}
	}

Now the problem i came across its seems if the value in $_GET['item'] if it has the value of "Victoria" it treats it the same as "Victoria 3" therefor displaying the same data which it shouldnt unless specified in the tags.

So for example in my tags field i have keywords are:
Victoria 3,V3,Clothing,Textures

and the Value of $_GET['item'] is "Victoria" in the code above it shouldnt show the results of the previous tags unless the value was "Victoria 3" or V3.

So now I know my problem is my expression is stoping after the a and not including the 3 therefor it will produce incorrect data. So this goes back to not fully understanding the symbols used in regular expressions yet so what symobl would have it include the white space if one is present and include the next character after that?

The if statement I havent played with in laserlights code, it might work the same for what im doing but im more unless using regular expression to try to learn and get a grasp on using them. Anyways more help is needed please. Thanks

ADDED
I just tried using the if version laserlight gave and it still has the same problem it seems to ignore white spaces causing false results, and easy fix would to change "Victoria 3" to Victoria3 but im trying to find away to not do that. So again a solution to to the if problem would be nice too. Thanks

[laserlight]

Yes, it sounds like you have a database schema problem. Instead of using regex as a hack to solve this, I think a better approach is to redesign the tables and columns in your database.

What is your current database schema?

Have you read the PHPBuilder article on Database Normalization And Design Techniques?

[CStrauss]

hmmm well here is how my database table is set up:

I have a table called poser_items and here is how its set up
item_id medint(25) Null(no) auto_increment primary key
item_name varchar(255) Null(no)
itemType varchar(255) Null(no)
tags varchar(255) Null(no)

table type MYISAM

The important field im having trouble with is the tags field again basicly its just a field of keywords that the query is going to search for matching values sent via links. So in that field i have keywords comma delimeted like this:

Aiko 3,Victoria,Victoria 3,Clothing,Texture

So im not really sure its the database scheme messing up. Im getting results just not correct results for example if its searching the keywords for Victoria 3 it finds that but also includes Victoria which it shouldnt which I thought when it was doing it search, the search stops when it hits the blank space between the a and 3 in Victoria 3 thus making Victoria 3 = to Victoria.

Now after a good nights sleep i figure i try a find and replace to change Victoria 3 to Victoria 3 and see if that helps but not sure. If its not my database set up then I need to find some way to for it to include spaces and special characters in the search so it knows "Victoria" is Diffrent then "Victoria 3".

Hope that clears up a bit im going ot read that article on the link you posted just to see if it us my databse. If you see anything else I can do please let me know

[laserlight]

You could consider changing to use 3 tables, since you have a many-many relationship between items and tags.

Table: poser_items
item_id medint(25) Null(no) auto_increment primary key
item_name varchar(255) Null(no)
itemType varchar(255) Null(no)
tags varchar(255) Null(no)

Table: tags
tag_id medint(25) Null(no) auto_increment primary key
tag_name varchar(255) Null(no)

Table: poser_items_tags
id medint(25) Null(no) auto_increment primary key
item_id medint(25) Null(no)
tag_id medint(25) Null(no)

Now, if you want to select the items with any of the tags "Aiko 3,Victoria,Victoria 3,Clothing,Texture", you might write something like:

SELECT poser_items.* FROM poser_items, tags, poser_items_tags
    WHERE poser_items.item_id=poser_items_tags.item_id
        AND tags.tag_id=poser_items_tags.tag_id
        AND tags.tag_name IN ('Aiko 3','Victoria','Victoria 3','Clothing','Texture')
    GROUP BY poser_items.item_id

I am rather hazy when it comes to a 3-way join, so my SQL statement might be completely wrong, but this is the general idea.