LinuxQuestions.org - [SOLVED] MySQL: Comparing values in diff tables SQL Query and BASH

- Programming (https://www.linuxquestions.org/questions/programming-9/)

- - MySQL: Comparing values in diff tables SQL Query and BASH (https://www.linuxquestions.org/questions/programming-9/mysql-comparing-values-in-diff-tables-sql-query-and-bash-4175442737/)

MySQL: Comparing values in diff tables SQL Query and BASH

Hi all, I'm trying to compare values in a mysql DB where 1 table has the strings(address) to search for, and 1 table has a complete list of addresses.

Table 1: id, addy1, addy2, addy3
Table 2: zip, status, addy, description, date

So I need to search Table2 for all of the addresses in Table1, and if any of them match the addresses in Table1, then I need the id from Table1 & the full row that matched from Table2. I then need to fire off a php script using the id from Table1 & the status, description, & date from Table2 so I figured the best way to go about this was a bash script on a crontab.

I could really use some help figuring this out as my queries were unsuccessful in accomplishing this, and my searches for answers were equally unfulfilling. Thank you for your time.

Quote:

as my queries were unsuccessful in accomplishing this

Assuming queries as in 'mysql queries', show them so we can see what's wrong. The normal way would be a query with joins; in this case you join on the address fields. Did you try that?

If I understand your table1 correctly, there are three address fields; I also guess that you need to check all three. In this case it might be better to do some normalisation and put the addresses in a separate table (let's call it table1a). Do you have the option to change the tables or is it something fixed?

Can you give some typical data for both tables?

My first thought would be to do the whole exercise in PHP. Is that an option? Where does cron come into the picture? Cron is, to my knowledge, used to schedule repetitive tasks; if that is what you want, cron can schedule any script as far as I know; it does not have to be bash.

Quote:

Originally Posted by Wim Sturkenboom (Post 4855874)

Assuming queries as in 'mysql queries', show them so we can see what's wrong. The normal way would be a query with joins; in this case you join on the address fields. Did you try that?

I did not save the queries I tried & although I suppose I could attempt to recreate them, they seemed fundamentally flawed because I didn't quite understand how to output both the id from the first table, & the entire row from the second table in the same query. I also think that JOINS would be beneficial here, but I'm not quite sure how to put this together.

Quote:

Originally Posted by Wim Sturkenboom (Post 4855874)

If I understand your table1 correctly, there are three address fields; I also guess that you need to check all three. In this case it might be better to do some normalisation and put the addresses in a separate table (let's call it table1a). Do you have the option to change the tables or is it something fixed?

I can indeed change table 1, it is not fixed. When you say put the addresses in a separate table, do you mean exclude the id? Or do you mean putting each address column in a separate table (seems highly inefficient)? As indicated in my OP, all the addresses do need to be checked: (So I need to search Table2 for all of the addresses in Table1)

Quote:

Originally Posted by Wim Sturkenboom (Post 4855874)

Can you give some typical data for both tables?

Table 2

Table 1

Note: ascii_addresses collation is the same as ascii with - meaning UPPERCASE CHAR instead of minus. Feel free to replace if necessary.

Quote:

Originally Posted by Wim Sturkenboom (Post 4855874)

My first thought would be to do the whole exercise in PHP. Is that an option? Where does cron come into the picture? Cron is, to my knowledge, used to schedule repetitive tasks; if that is what you want, cron can schedule any script as far as I know; it does not have to be bash.

PHP is an option, as long as i'm not looping sql queries for each entry. Need to have some sort of efficiency, with mysql calculating & returning the data I need. Also, I had planned to implement DATE comparisons with bash to return entries that aren't older than 4 days old & i'm a bit at a loss as to how to implement all this in php, which is why I preferred bash. But as long as it works, idc what language it's in. I also don't mind writing it myself, not asking for a handout, just the general direction as to how to query the info properly. How would a JOIN query work on this? Happy Holidays, & thanks!

I did set this up already before you posted; based on the info that you provided I created the two tables and put some dummy data in. I did have a quick glance over the posted tables but decided not to use them for the below example.

Code:

mysql> select * from table1;

+------+-----------+----------+---------------+

| id  | addy1    | addy2    | addy3        |

+------+-----------+----------+---------------+

|    1 | 44 galena | NULL    | NULL          |

|    2 | 56 galena | pobox 11 | some more    |

|    3 | NULL      | NULL    | other address |

+------+-----------+----------+---------------+

3 rows in set (0.00 sec)

Code:

mysql> select * from table2;

+------+--------+-----------+-----------------+------------+

| zip  | status | addy      | description    | mydate    |

+------+--------+-----------+-----------------+------------+

| 1724 | ok    | 44 galena | my home        | 2001-08-21 |

| xxxx | new    | pobox 11  | neighboor pobox | 2012-12-24 |

| yyyy | new    | 56 galena | neighboor      | 2012-12-25 |

+------+--------+-----------+-----------------+------------+

3 rows in set (0.00 sec)

The query to get the specified data from both tables

Code:

mysql> select id,status,description,mydate from table1 join table2 on addy1=addy OR addy2=addy OR addy3=addy;

+------+--------+-----------------+------------+

| id  | status | description    | mydate    |

+------+--------+-----------------+------------+

|    1 | ok    | my home        | 2001-08-21 |

|    2 | new    | neighboor pobox | 2012-12-24 |

|    2 | new    | neighboor      | 2012-12-25 |

+------+--------+-----------------+------------+

3 rows in set (0.00 sec)

Note that id 3 from table1 does not occur as there are no matching records in table2; if you still need that, use 'left join' instead of 'join'.

To limit the dates

Code:

mysql> select id,status,description,mydate from table1 join table2 on addy1=addy OR addy2=addy OR addy3=addy where mydate>date_sub(curdate(),interval 4 day);

+------+--------+-----------------+------------+

| id  | status | description    | mydate    |

+------+--------+-----------------+------------+

|    2 | new    | neighboor pobox | 2012-12-24 |

|    2 | new    | neighboor      | 2012-12-25 |

+------+--------+-----------------+------------+

2 rows in set (0.00 sec)

All this can be done in php by connecting to the database and log in, running the query and processing the result. You might want to setup a mysql user that can only run select queries on table1 and table2 because the password will be visible in the script and you don't want the password for a user that can destroy your tables to be visible.

Normalisation is a standard practice; in this case it might be less efficient but if you have 10 times the same address, it's more efficient because the address only needs to be stored once.

What is your part of the php code going to do?

I'm not that familiar with cron; I assume that you want to run your script every e.g. 5 minutes. Somebody else can help with that.

Hope this helps a bit.