Looping through 2 MySQL tables and update one table

[terrid]

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

 

[terrid]

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?

 

[kickstart]

Hi

 

Think I would extract the description, explode it to an array then add % signs and use a LIKE.

 

Something like this:-

 

<?php

$UnwantedWords = array('the','to','a');

$sql1 = "SELECT * FROM records_table";

$rs1 = mysql_query($sql1) or die(mysql_error()." $sql1");

while ($row = mysql_fetch_array($rs1))
{
$AllWords = explode(' ',$row['description']);
$InterestingWords = array_diff($AllWords, $UnwantedWords);
if (count($InterestingWords) > 0)
{
	echo '<br />'.$row['title'].'<br />';
	$sql2 = "SELECT * FROM images_table WHERE title LIKE '%".implode("%' OR title LIKE '%",$InterestingWords)."%'";
	$rs2 = mysql_query($sql2) or die(mysql_error()." $sql2");

	while ($row2 = mysql_fetch_array($rs2))
	{
		print_r($row2);
	}
}
}

?>

 

This is just printing them out for you, but it will be easy for you to change it update the records table. You current table setup only seems to cope with a single image id for each record, and this seems a bit restrictive.

 

This also gives you an array of words that you don't care about (saves searching for common words like "and").

 

All the best

 

Keith

[terrid]

Hi

 

Thanks for that, works perfectly.

 

Could you please provide me with the UPDATE part of the script?

 

Thank you

[kickstart]

Hi

 

Doing an update instead of listing out the matches

 

<?php

$UnwantedWords = array('the','to','a');

$sql1 = "SELECT * FROM records_table";

$rs1 = mysql_query($sql1) or die(mysql_error()." $sql1");

while ($row = mysql_fetch_array($rs1))
{
$AllWords = explode(' ',$row['description']);
$InterestingWords = array_diff($AllWords, $UnwantedWords);
if (count($InterestingWords) > 0)
{
	$sql2 = "SELECT * FROM images_table WHERE title LIKE '%".implode("%' OR title LIKE '%",$InterestingWords)."%'";
	$rs2 = mysql_query($sql2) or die(mysql_error()." $sql2");

	if ($row2 = mysql_fetch_array($rs2))
	{
		$sql3 = "UPDATE records_table SET images_id = ".$row2['id']." WHERE id = ".$row['id']." ";
		$sql3 = mysql_query($sql3) or die(mysql_error()." $sql3");
	}
}
}

?>

 

This will just put in the id of the first image found.

 

All the best

 

Keith

[terrid]

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.

[kickstart]

Hi

 

The reason that is happening is because when "On sale in tesco" is split up one of the words searched for is "on", and "%on%" matches on Amazon.

 

You need to put on in the list of excluded words but that list is case sensitive. If you need it case insensitive then probably easiest to put entries in it in lower case and convert all the values in the array $AllWords to lower case.

 

All the best

 

Keith