terrid

Could you provide any code? I have in the table an id row for each item that gets inserted into the table from the RSS feed. The RSS feed does not have an ID. What I was checking on is the retailer_message (On sale at Amazon) for example. This is the same in the database as it is in RSS feed, so this was what I was using to check if things were the same, i.e. if the price had changed. What I was thinking was: Count how many rows I have with the rss_url in the db, if it's 5, store in a variable. Parse the XML using the same rss_url, if the count is 5 and matches the above variable, do nothing If it is 6, delete all the rows with the rss_url and then re-insert the parsed rss feed, with the 6 items. This sounds a bit drastic though

Ok I really need help with this. Can anyone give me a starting point, even just psuedo-code? I'm really stuck on this and need some urgent help. Kind regards

$sql = "UPDATE easy_contents SET delivery_cost = '$deliv[0]', price = '$v[0]', total = '$total' WHERE rss_url = '$row[rss_url]' AND title = '$item->title' AND description = '$price_stripped' "; if(!$query = mysql_query($sql)) { echo "Error on line ".__LINE__.". ".mysql_error().".<br />\nQuery: ".$sql; exit; } echo "Query OK. <br />\nUpdated rows: ".mysql_affected_rows().".<br />\nQuery: ".$sql The following seemed to work. How would I do the next part of my script? If the item in the RSS has been removed, then delete the row from the db?

Right after a couple of tweaks I have the code updating rows in my database, the problem is, it seems to update all the rows with the last item in the RSS Feed and not the actual details of each individual item. $connection = mysql_connect("localhost","root","password"); if (!$connection) { die('Could not connect: ' . mysql_error()); } mysql_select_db("at_any_price", $connection); $result = mysql_query("SELECT * from easy_contents"); while($row = mysql_fetch_array($result)) { $articles = array(); $easy_url = $row['rss_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); //strip out all the HTML tags $item->description = str_replace('<table><tr><td width="110">','', $item->description); $item->description = str_replace('</table>','', $item->description); $item->description = str_replace('</td>','', $item->description); $item->description = str_replace('<td>','', $item->description); $item->description = str_replace('<br />','', $item->description); $item->description = str_replace('<b>','', $item->description); $item->description = str_replace('</b>','', $item->description); $item->description = str_replace('</tr>','', $item->description); //find all url encoded £ signs and find the string after //string will be a price preg_match_all('/&#xA3;([0-9.]+)/', $item->description, $results); foreach ($results as $k => $v) { } //find the url encoded £ sign and append the price $all = '&#xA3;'.$v[0]; $price_stripped = str_replace($all, '', $item->description); $desc = preg_match('/&#xA3;([0-9.]+)/', $item->description); //find the discount deleviry cost from the rss using the ~#£NUMBER //this is the discount preg_match_all('/~#£([0-9.]+)/', $item->description, $discount); foreach ($discount as $d => $disc) { str_replace("~#£","", $disc[0]); } //find the remaining £PRICE and this is the delivery cost //this is the delivery_cost preg_match_all('/£([0-9.]+)/', $item->description, $delivery_cost); foreach ($delivery_cost as $del => $deliv) { } //find the | char and find the string after it //this is the retailer_message preg_match_all('/\|(.*?)\./',$item->description,$match); foreach ($match as $rel => $retail) { $retail[0] = str_replace("| ","", $retail[0]); $retail_mess = str_replace(" On","On", $retail[0]); } //echo $retail[0] . $deliv[0] . $row['rss_url'] . "<br />"; $total = $v[0] + $deliv[0] - $disc[0]; mysql_query("UPDATE easy_contents SET delivery_cost = '$deliv[0]', price = '$v[0]', total = '$total' WHERE rss_url = '$row[rss_url]' "); } }

This is what I have at the moment: $connection = mysql_connect("localhost","root","password"); if (!$connection) { die('Could not connect: ' . mysql_error()); } mysql_select_db("at_any_price", $connection); $result = mysql_query("SELECT * from easy_contents"); while($row = mysql_fetch_array($result)) { $articles = array(); $easy_url = $row['rss_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); //strip out all the HTML tags $item->description = str_replace('<table><tr><td width="110">','', $item->description); $item->description = str_replace('</table>','', $item->description); $item->description = str_replace('</td>','', $item->description); $item->description = str_replace('<td>','', $item->description); $item->description = str_replace('<br />','', $item->description); $item->description = str_replace('<b>','', $item->description); $item->description = str_replace('</b>','', $item->description); $item->description = str_replace('</tr>','', $item->description); //find all url encoded £ signs and find the string after //string will be a price preg_match_all('/&#xA3;([0-9.]+)/', $item->description, $results); foreach ($results as $k => $v) { } //find the url encoded £ sign and append the price $all = '&#xA3;'.$v[0]; $price_stripped = str_replace($all, '', $item->description); $desc = preg_match('/&#xA3;([0-9.]+)/', $item->description); //find the discount deleviry cost from the rss using the ~#£NUMBER //this is the discount preg_match_all('/~#£([0-9.]+)/', $item->description, $discount); foreach ($discount as $d => $disc) { str_replace("~#£","", $disc[0]); } //find the remaining £PRICE and this is the delivery cost //this is the delivery_cost preg_match_all('/£([0-9.]+)/', $item->description, $delivery_cost); foreach ($delivery_cost as $del => $deliv) { } //find the | char and find the string after it //this is the retailer_message preg_match_all('/\|(.*?)\./',$item->description,$match); foreach ($match as $rel => $retail) { $retail[0] = str_replace("| ","", $retail[0]); $retail_mess = str_replace(" On","On", $retail[0]); } } } It selects the rows from the db, selects the rss_url and then outputs the data. I then need to update based on the output. All help is welcomed

Hi all I am currently in the process of scoping out a script and I am needing some help. I have a mysql table, that has several rows, each with the following: title | description | price | rss_url | delivery_cost | retailer_message | discount | total --------------------------------------------------------------------------------------------------------------- Halo: Reach (XBOX 360) | Description text | 35.99 | http://www.easycontentunits.com/rss/54626/669/rss2.rss | On sale at Gameplay | 0 | 35.99 ---------------------------------------------------------------------------------------------------------------------------------------------- Halo: Reach | Description text | 36.89 | http://www.easycontentunits.com/rss/54626/669/rss2.rss | On sale at Toys R Us | 0 | 36.89 What I need to do is the following: Select each row and extract the rss_url Parse the rss_url and extract the above details from the rss feed If the details in the rss feed has changed, update the row If the actual rss feed item is no longer in the rss feed, delete it from the table. So If the rss feed changes the first rows details in my db has the price field updated to 40.00, I need to update the row and the price field will change from 35.99 to say 40.00 If the row is no longer in the rss feed, then I need to delete it from the db. Can anyone provide me a small snippet or any advice on how to go about doing this? I can provide the code I'm using to actually insert the rows from the RSS feed if it helps? Thanks

Hi I have several URLs that have a rather long query string at the end, whichIi need to redirect into a more user friendly manor: http://www.example.com/search/?depId=1&typeCatId=1 needs to be redirected to http://www.example.com/clothing/mens http://www.example.com/search/?depId=1&typeCatId=1&typeSubCatId=1 needs to be redirected to http://www.example.com/clothing/polo-shirts But I have no idea how to do this. Could someone provide me with an example for the above so that i can get my site working properly? Thank you

Hi Any ideas why the following is giving me an error: $result = mysql_query("update records_table set logo_id = (select id from logos where name = substring(retailer_message,12))"); Thanks

Hi That works great But the images_id gets set to 6, which is the first image. I need something like the following: id, description, images_id 1, On sale at Amazon 2, On sale at Asda images id, name 1, Amazon 2, Misco 3, Asda 4, Tesco This would then update the records_table and insert the images_id of 1 and 3 respectively.

Hi Thanks for that, works perfectly. Could you please provide me with the UPDATE part of the script? Thank you

Ok, so I found a similar example and I've modified it to match my tables/fields; http://efreedom.com/Question/1-1299352/Find-Likewise-Data-Two-Tables-Mysql UPDATE records_table SET logo_id = (SELECT logos.id FROM logos WHERE logos.name LIKE CONCAT(' % ',records_table.retailer_message,' % ') LIMIT 1) WHERE EXISTS (SELECT records_table.id FROM logos WHERE logos.name LIKE CONCAT('% ',records_table.retailer_message,' %') LIMIT 1) But it doesn't update any rows in the records_table table. Any ideas what I'm doing wrong?

Hi all I have 2 tables: 1. is a tables full of images that have an id, a src and a title (images_table). 2. table is a list of records with an id, a description and an images_id (records_table) What I'm looking to do is: Loop through the records in the records_table and find the description, the description is something like this 'Welcome to Kansas'. Then, I want to loop through the images_table and find the associated image of Kansas, based on a MySQL LIKE statement. This is because the images_table has a title of 'Kansas', for example. Once this has been done, I then need to insert the id of Kansas into the images_id in the records_table a possible MySQL UPDATE. Has anyone got an idea how I could do this? Thanks

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

What happens if you replace: $det[] = $det2; With: $det1[] = $det2;

Take a look at this: http://www.jooria.com/PHP-Pagination-a125.html