check record against array

[mrsocks]

hi,

i have a table of products (about 4500). the title field has the brand and the title of the product. I want to step through the titles and check against an array of brand names. If there is a match remove the brand from the title and then update the title without the brand in it.

currently what i have only does the first record. if i use a limit, only the first of the limit works, and if i try to se a count and step throughit that way, it only does one also.

here is what i have:

$brand_array = array();

#####################
$sqlB = "SELECT name FROM brands ORDER by 'name' ASC";
$resultB = mysql_query($sql😎;
while ($rowB = mysql_fetch_array($result😎)
{
$name = $rowB["name"];
$brand_array[] = $name;
}

#####################
$sql = "SELECT id,title FROM products2 ORDER by id ASC";
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result))
{
$id = $row["id"];
$title = $row["title"];

##############
while ($rowA = each($brand_array))
	{
	$a_string = $rowA["value"];
	$s_check = strpos($title,$a_string);

//this is just to visually see it working \/
echo $title;
echo "<br>\n";
echo $a_string;
echo "<br>\n";
//this is just to visually see it working /\
if ($s_check === 0)
{
$tmp_str = str_replace($a_string,"",$title);
$titleD = trim($tmp_str);
echo $titleD;

		$sqlD = "update products2 SET title = '$titleD' where id = '$id'";
		$sql_result_brandD = mysql_query($sqlD) or die ("count update title!");
		}

	}

##############		

}

##################
echo "done";

thanks.

[stolzyboy]

have you checked out [man]in_array[/man]

[mrsocks]

i am not sure what you are suggesting using that function.

the titles have a brand name in them already. i am using a function to check the title and see if a brand name string matches a part of the title, if it does, it replaces the brand portion of the title with a space and then trims that space out.

i would think using the in_array function would be more for checking a list against another var (if the vars are seperate, i cannot do this beacuse some brands have a space in them and there for cannot even use an explode function to seperate them) - is this what you were suggesting?

[stolzyboy]

kinda, didn't fully understand your post at first...

[m_tt]

Can you give an example of the before and after output you are looking for?

You could probably do this with a simple SQL query.. no script required

[mrsocks]

current product setup
id | title
1 | Suzuki Katana
2 | Honda 900RR
3 | Yamaha YZ650

an array of brands is built from another table; brands
brands_array("Honda","Suzuki","Yamaha")

i want to take each row and check the title against the brand array and if there isa matching string (brnad name) in the title, remove it and save the title to the same spot - without the brand.

desired result:
id | title
1 | Katana
2 | 900RR
3 | YZ650

thanks for the interest.

[m_tt]

Not sure if this works since I can't see your tables but you could mess around with it a little bit : )

<?php
//	Setup brands array
	$brands = array();

//	Query
$getBrands = mysql_query("SELECT `name` FROM brands ORDER BY name ASC")or die(mysql_error());
	//	Check if query is empty
	if (mysql_num_rows($getBrands) > 0)	{
		//	Loop query results
		while(list($brand_Name) = mysql_fetch_row($getBrands))	{
			//	Insert results into the array
			$brands[] = $brand_Name;
		}
	} else {
		//	Query is empty / No Results
	}

//	Now cycle products
	$getTitles = mysql_query("SELECT `id`,`title` FROM products2 ORDER BY id ASC")or die(mysql_error());
		//	Check if query is empty
		if (mysql_num_rows($getTitles) > 0)	{
			//	Loop query results
			while(list($id,$title) = mysql_fetch_row($getTitles))	{
				//	Cycle through brand results array created above ^^
				for ($i = 0; $i < count($brands); $i++)	{
					//	See if the title contains the brand
					if (preg_match("/" . $brands[$i] . "/",$title))	{
						//	Replace brand + space ("Toyota ") with 'nothing'
						$new_Title = str_replace($brands[$i] . " ","",$title);
					}
				}

			//	Insert new data
			mysql_query("UPDATE products2 SET title='" . $new_Title . "' WHERE id='" . $id . "'")or die(mysql_error());
		}
	} else {
		//	No results
	}
?>

[m_tt]

Actually, the update should be in the preg_match if statement..

<?php
					if (preg_match("/" . $brands[$i] . "/",$title))	{
						//	Replace brand + space ("Toyota ") with 'nothing'
						$new_Title = str_replace($brands[$i] . " ","",$title);

					//	Insert new data
					mysql_query("UPDATE products2 SET title='" . $new_Title . "' WHERE id='" . $id . "'")or die(mysql_error());
				}
?>

Try this if you want to test without changing the database..

<?php
//	Now cycle products
	$getTitles = mysql_query("SELECT `id`,`title` FROM products2 ORDER BY id ASC")or die(mysql_error());
		//	Check if query is empty
		if (mysql_num_rows($getTitles) > 0)	{
			//	Loop query results
			while(list($id,$title) = mysql_fetch_row($getTitles))	{
				//	Cycle through brand results array created above ^^
				for ($i = 0; $i < count($brands); $i++)	{
					//	See if the title contains the brand
					if (preg_match("/" . $brands[$i] . "/",$title))	{
						//	Replace brand + space ("Toyota ") with 'nothing'
						$new_Title = str_replace($brands[$i] . " ","",$title);
						//	Insert new data
						//  mysql_query("UPDATE products2 SET title='" . $new_Title . "' WHERE id='" . $id . "'")or die(mysql_error());
					} else {
						$new_Title = "Null";
					}
				}

			echo "Old title: " . $title . " --> New Title: " . $new_Title . "<BR>";
		}
	} else {
		//	No results
	}

?>

PS.. this code is all untested.. I may have a bug or two : P

[majiclab]

I believe the problem is that you are not reset()ing your brands array for each item, so when the second item comes around, the internal pointer for your brands is at the end and has not brands to go through!

From m@tt's code:

//    Now cycle products 
    $getTitles = mysql_query("SELECT `id`,`title` FROM products2 ORDER BY id ASC")or die(mysql_error()); 
        //    Check if query is empty 
        if (mysql_num_rows($getTitles) > 0)    { 
            //    Loop query results 
            while(list($id,$title) = mysql_fetch_row($getTitles))    { 
                //    Cycle through brand results array created above ^^ 
                foreach ($brands as $brand) { 
                    //    See if the title contains the brand 
                    if (preg_match("/" . $brand . "/",$title))    { 
                        //    Replace brand + space ("Toyota ") with 'nothing' 
                        $new_Title = str_replace($brand . " ","",$title); 
                        //    Insert new data 
                        //  mysql_query("UPDATE products2 SET title='" . $new_Title . "' WHERE id='" . $id . "'")or die(mysql_error()); 
                        break; // go on to the next product
                    } else { 
                        $new_Title = "Null"; 
                    } 
                } 

            echo "Old title: " . $title . " --> New Title: " . $new_Title . "<BR>"; 
        } 
    } else { 
        //    No results 
    } 

I changed a couple lines in there. I'm a big fan of the foreach() structure, as you don't have to ever worry about reset()ing or anything, it just goes through each element for you.

As well, I added a break; in there, because currently, it is going through all the brands, even after it has found one. Once you have found a brand, you should just skip the rest and go onto the next product item.

Hope that helps,

-Percy

[m_tt]

Good stuff.. I never really use foreach besides when I have to serialize an array : )

[mrsocks]

THANK YOU! THANK YOU!

all i had to do was add a reset() as suggested. my code worked perfectly, but that was the only issue.

i cannot believe things like that.

thank you again!