[SOLVED] Combine two lists into mysql

[alconebay]

I have a table in mysql of about 650 dog breeds, it's a complete list of all dog breeds. I have another list (text file) of just AKC recognized breeds (about 150 breeds, all of which are also on the complete list in the table). I want to mark as AKC all the breeds in the table on the complete list that are also on the list AKC breed list. I added a AKC field to the table with a default value of "NO".

 

I can format the AKC list easily using dreamweaver find/replace. I have all the breeds listed with a "!" at the beginning of the name and a "@" at the end) Now, can I write a php query that will read my AKC list and match the breed names up and update the AKC field to "YES" if it finds it on my AKC list?

[phpretard]

Can you post your code.

[alconebay]

I don't have any code. Just a list of AKC dogs in notepad and a table in mysql of all dog breeds. When I say I can easily format the AKC list I mean I can tell dreamweaver the find the "!" marks and replace the withm a quotation mark or whatever. Maybe I could format the list so that the breeds are listed for ab array or something?

[alconebay]

Would something like this work?

 

"breeds" is the name of my table with all the dog breeds in it and "breed" is the name of the field that has all the breed names.

 

$query="UPDATE breeds SET AKC='YES' WHERE breed='(My list of AKC breeds)'";

 

Only how would I format the list so that the query could read them? Enclosed in apostrophe's and separated by a comma?

[chigley]

<?php

$lines = file("akc.txt");

foreach($lines as $line) {
$line = trim($line);

if(substr($line, 0, 1) == "!") {
	$breed = substr($line, 1, (strlen($line) - 2));
	$query = mysql_query("UPDATE table SET AKC = 'YES' WHERE breed = '{$breed}'") or die(mysql_error());
}

}

?>

 

^ Try that

[alconebay]

I get this:

 

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table SET AKC = '1' WHERE breed = 'Affenpinsche'' at line 1"

 

My akc.txt looks like this:

 

!Affenpinscher

!Afghan Hound...

 

php (btw, I changed "YES" and "NO to "1" and "0"):

 

<?php

include ("database/connect.php");

$lines = file("akc.txt");

 

foreach($lines as $line) {

$line = trim($line);

 

if(substr($line, 0, 1) == "!") {

$breed = substr($line, 1, (strlen($line) - 2));

$query = mysql_query("UPDATE table SET AKC = '1' WHERE breed = '{$breed}'") or die(mysql_error());

}

 

}

 

?>

 

 

[alconebay]

Opps, I changed "table" to my table name and it ran. But, I checked the AKC breeds and the AKC field still says no ("0") for all of them, they didn't get updated.

[alconebay]

OK, here is whats happening, the last letter in the breed name is getting cut off. When it reads "!Affenpinscher" in the text file it tries to update "Affenpinsche". I added a dog breed "Affenpinsche" to the table and it got updated as AKC. ???

[alconebay]

Ahh, I changed "(strlen($line) - 2)" to "(strlen($line) - 1)" and it worked. Sorry chigley, I had told you I had a "@" after all my breeds so you were making sure it didnt copy over. Then I took the @ sign off. Anyway, thanks chigley, the script works great!

 

 

<?php

include ("database/connect.php");

$lines = file("akc.txt");

 

foreach($lines as $line) {

$line = trim($line);

 

if(substr($line, 0, 1) == "!") {

$breed = substr($line, 1, (strlen($line) - 1));

$query = mysql_query("UPDATE breeds SET AKC = '1' WHERE breed = '{$breed}'") or die(mysql_error());

}

 

}

 

?>