[RESOLVED] Tagging a field based on a lookup

Jon12345

I have 2 tables:

iplistbots - contains a list of ip addresses in a field called ipaddress (which is also the primary key)

logfile - which has several fields but the relevant ones being IPaddress and Bot

In for each ip address in the ipaddress field in the logfile table, I want to do a lookup in the iplistbots table. If a match exists, enter the value Bot in the Bot field of the logfile table. If not, just echo "no match".

Any idea on how to code this?

Thanks,

Jon

rebelo

I think this is what you're looking for, if I understand you correctly. You can actually do this all as a single query rather than a bunch of queries and php loop checks.

UPDATE iplistbots
LEFT JOIN logfile ON (iplistbots.ipaddress=logfile.ipaddress)
SET iplistbots.Bot=logfile.Bot
WHERE logfile.Bot != '';

Jon12345

Well, first of all it is the logfile table that has the Bot field. There is not Bot field in the iplistbots table. Would the code be something like this?

UPDATE logfile
SET logfile.Bot='Yes'
from logfile,iplistbots
WHERE logfile.ipaddress=iplistbots.ipaddress;

That is the idea but not sure about the correct syntax! Nice to know I can do it just as a query though!

bbreslauer

You don't need the FROM part of the query. So it would look like this:

UPDATE logfile
SET logfile.Bot='Yes'
WHERE logfile.ipaddress=iplistbots.ipaddress;

Check out the MySQL documentation at http://dev.mysql.com/doc/mysql/en/ and the UPDATE syntax at http://dev.mysql.com/doc/mysql/en/update.html

rebelo

Jon12345 wrote:
Well, first of all it is the logfile table that has the Bot field. There is not Bot field in the iplistbots table. Would the code be something like this?
UPDATE logfile
SET logfile.Bot='Yes'
from logfile,iplistbots
WHERE logfile.ipaddress=iplistbots.ipaddress;
That is the idea but not sure about the correct syntax! Nice to know I can do it just as a query though!

I think what you're looking for then is something like:


UPDATE logfile
LEFT JOIN iplistbots ON (logfile.ipaddress=iplistbots.ipaddress)
SET logfile.Bot='Yes'

That, I believe, would change the "Bot" field to a value of "Yes" for any record in the logfile table which has a matching partner in the iplistbots table. Is that what you want to do?

Jon12345

Tried that and it updated every record in the logfile table so I don't think it is quite right yet. I put one bogey record in the logile table and it still got updated to a Yes.

Should it be LEFT JOIN logfile instead? i.e. reversed

rebelo

Well, you're updating logfile, so leave it how it is, but to reverse the behavior, use RIGHT JOIN instead of LEFT JOIN. See if that does it?

Jon12345

This worked:

UPDATE kati
RIGHT JOIN iplistbots ON (kati.ipaddress=iplistbots.ipaddress)
SET kati.Bot='Yes'

Thanks for your help.

Jon