[RESOLVED] Comparing an Array against a DB

nawrik

Hi all,

all help is greatly appreciated - my head is spinning on this one!

Here's the situation...

I'm trying to create a photo 'tag' script - think keywords for photos.

What I've achieved so far:

The user after submitting their 'tags' - punctuation gets stripped and replaced with commas - and all white space is removed:

these,are,tags

I have then use

$tag = explode(',', $tags);

to create the array $tag - giving each word it's own usage eg:

$tag[0]

The next stage is to compare the values in this array to the existing "tag_name" column in my database:

if the word doesn't exist, store it and give it a unique id - if it already exists - don't do anything with it.

Eventually I will have a list of tag_names in a db. Every photo (in a separate table) will then have a 'tags' column - containing either the tag_id or tag_name (I haven't decided yet).

The reason I have got a separate 'tag' table is for ease of future 'search by tag' facilities.

Here is my code so far - please rip to bits and help compare the array $tag[] with the column tag_name...

....unless of course some one knows a slicker way to achieve my goal!

if (isset($_POST['submit']))
	{
	$tags = htmlentities($_POST['tags']); // Grab tags created by user
	$separators = array(".", ",", ";", ":", "-", "_", "|");   //Create an array wit likely punctuation between tags  

  	$tags = str_replace($separators, ',', $tags); // Replace any punctuation with a comma   

  	$tags = str_replace(' ', '', $tags); // Remove any remaining white space

$tag = explode(',', $tags);  //  explode $tags, remove the commas and place in Array $tag

//You can now echo each tag individually
//echo $tag[0];
//echo $tag[1];
//echo $tag[2];

$tagname['tag_name'] = $value;

foreach ($tag as $tag=>$value)
{
$query = mysql_query("SELECT * FROM tags WHERE tag_name = '$value'");
}
if(mysql_num_rows($query) == 0)
{


//loop through the array
$number = count($tag);
for ($t=0; $t<$number; $t++)
{
	$separatetag = $tag[$t];

	if ($tag[$t] <> '')
	{
	mysql_query("INSERT INTO tags (tag_id, tag_name) VALUES ('', '$separatetag')");
	}
}



}


}



?>

A mess I know - all help appreciated!!

Weedpacket

Your foreach line has the potential to break something: $tag and $tag is being used to mean two different things at the same time.

foreach($tag as $key=>$value)
{
    $tag[$key] = "'".mysql_real_escape_string($value)."'";
}
$query = "Select tag_name from tags where tag_name in (".join(',', $tags).")";

After running that query and collecting the returned tag_names into their own array $existing_tags, array_diff($tags, $existing_tags) will give you a list of all the new tags that need to be inserted.

nawrik

Weedpacket,

thank you for your reply - however it seems to have confused me even more.

Even after a sleep, it hasn't clicked.

I've started fresh and here is where I am:

if (isset($_POST['submit']))
{
	$newtags = htmlentities($_POST['tags']); // Grab tags created by user
	$separators = array(".", ",", ";", ":", "-", "_", "|");   //Create an array wit likely punctuation between tags  

  	$newtags = str_replace($separators, ',', $newtags); // Replace any punctuation with a comma   

  	$newtags = str_replace(' ', '', $newtags); // Remove any remaining white space

$newtagarray = explode(',', $newtags);  //  explode $tags, remove the commas and place in Array $tag

//You can now echo each tag individually
//echo $tag[0];
// or see what tha array contains...
print_r ($newtagarray);
}

As you can see, so far I've created a NUMERICAL array with the $_POST 'tags'.

It looks like this:

Array ( [0] => new [1] => key [2] => words )

Now I've gone onto creating an array from the column I want to compare the new 'tags' to:

$gettags = mysql_query("SELECT tag_name FROM tags");
while ($currenttags = mysql_fetch_array($gettags, MYSQL_ASSOC))
{
print_r ($currenttags);
}

This output:

Array ( [tag_name] => andy ) 
Array ( [tag_name] => silhouette ) 
Array ( [tag_name] => barrel ) 
Array ( [tag_name] => aerial ) 
Array ( [tag_name] => surf ) 
Array ( [tag_name] => wave )

This seems to have made SEPARATE arrays - not each word in ONE array (andy/silhouette/barrel...).

I'm thinking this is because it's within the while statement?

But I don't know how to get the 'print_r' outside of this???

So, one step at a time - first problem seems to proucing the correct array from the database.

I think the next problem will be the MYSQL_NUM and MYSQL_ASSOC differences between each array....

Weedpacket

Like I said, collect the returned tag names into their own array:

$existing_tags = array();
while ($currenttags = mysql_fetch_array($gettags, MYSQL_ASSOC))
{
    $existing_tags[] = $currenttags['tag_name'];
}
print_r($existing_tags);

nawrik

Fantastic WeedPacket - thank you for your patience.

I am now having another problem - I don't understand why...any ideas:

This is what i'm using to get the current database of tags. For testing i've print_r the array:

$gettags = mysql_query("SELECT tag_name FROM tags");
$currenttags = mysql_fetch_row($gettags);

$existing_tags = array();
while ($currenttags = mysql_fetch_array($gettags, MYSQL_ASSOC))
{
    $existing_tags[] = $currenttags['tag_name'];
}
echo "<pre>";
print_r($existing_tags); 
echo "</pre>";

The $existing_tags array outputs:

Array
(
    [0] => aerial
    [1] => wave
)

This is all good except the word "aerial" is actually on row 1 - there is the word "barrel" on line '0'.

Changing various words - it seems for some reason ROW 0 in the DB get's missed off everytime the array is called.

This causes the problem when updating with new tags - because is the user inputs a word that exists in ROW 0 - but according to the array doesn't exist - we will get a duplicate entry in the DB.

I can work around this by simply putting a random word in 0 - letting it fill the gap - however this doesn't strike me as correct.

EDIT -
I'm pretty sure my table is correct - this may help though:

tag_id - INT(6) - auto_increment - PRIMARY.
tag_name VARCHAR(20)

Any ideas?

Thank you again!

nawrik

Sorted.

It seems the:

mysql_fetch_row

was messing with the query - needlessly.

Thanks all.