Parse RSS and find images based on join/like clause

[terrid]

Hi

 

I'm not sure if this needs to be in the MySQL forum so bare with me.

 

I have small script which parses an RSS feed and inserts data into a table. This all works fine, but my problem now, is that I have another table, which has logos and when i parse the RSS, I'd like to be able to pass in the logo from the logos table into the RSS parse table.

 

The problem is the RSS feed are products, with different suppliers, so I need to somehow check which manufacturer it is and then insert the image_id from the images table.

 

I have a field in the main table, which has things like 'Available at Amazon' and 'Available at Play.com'

 

What I want is some kind of join/like clause, where I can check, for example the word 'Amazon' in one field and match it to the images table.

 

schema of both tables

rss_table

id, title, message, price, logos_id

1, iPad, Available at Apple, 400.00, BLANK_ID

 

logos

id, name, src

2, Apple, apple-logo.jpg

 

So you can see, any product that gets parsed that has is available at apple, needs to have the logos_id of 2, if that makes sense?

 

Below is a small snippet of my code (it isn't brilliant but works)

 

I just need to then need to find the corresponding image to the manufacturer

<?php

$test = $_POST['post_title'];

$articles = array();
$easy_url = $_POST["url"];

$rawFeed = file_get_contents($easy_url);
$xml = new SimpleXmlElement($rawFeed);

$channel = array();
$channel['title']       = $xml->channel->title;
$channel['link']        = $xml->channel->link;
$channel['description'] = $xml->channel->description;

foreach ($xml->channel->item as $item)
{
        $article = array();
        $article['title'] = $item->title;
        $article['link'] = $item->link;
        $article['description'] = (string) trim($item->description);

preg_match_all('/&#xA3;([0-9.]+)/', $item->description, $results);
foreach ($results as $k => $v) {
}

$all = '&#xA3;'.$v[0];
$price_stripped = str_replace($all, '', $item->description);
$desc = preg_match('/&#xA3;([0-9.]+)/', $item->description);

preg_match_all('/~#£([0-9.]+)/', $item->description, $discount);
foreach ($discount as $d => $disc) {
	str_replace("~#£","", $disc[0]);
}

preg_match_all('/£([0-9.]+)/', $item->description, $delivery_cost);
foreach ($delivery_cost as $del => $deliv) {	
}
   
preg_match_all('/\|(.*?)\./',$item->description,$match);           
        foreach ($match as $rel => $retail) {	
		$retail[0] = str_replace("|","", $retail[0]);	
}   

//$retail_logo = str_replace('On sale at', '', $retail[0]);

$connection = mysql_connect("localhost","root","password");
if (!$connection)
{
  die('Could not connect: ' . mysql_error());
}

$total = $v[0] + $deliv[0] - $disc[0];

mysql_select_db("db_test", $connection);

mysql_query("INSERT INTO rss (title,  price, retailer_message,  logo_id)
VALUES ('$item->title', '$price_stripped', '$retail[0]', '$logo_id')");

mysql_close($connection);

}

?>

 

Any help is appreciated.

 

Thanks