LinuxQuestions.org
LinuxAnswers - the LQ Linux tutorial section.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices



Reply
 
Search this Thread
Old 12-23-2012, 08:31 PM   #1
watice
LQ Newbie
 
Registered: Dec 2012
Posts: 2

Rep: Reputation: Disabled
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.
 
Old 12-23-2012, 08:49 PM   #2
Habitual
Senior Member
 
Registered: Jan 2011
Distribution: Undecided
Posts: 3,620
Blog Entries: 1

Rep: Reputation: Disabled
n/m.

Spock out.
 
Old 12-24-2012, 12:42 AM   #3
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Rep: Reputation: 282Reputation: 282Reputation: 282
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.
 
Old 12-24-2012, 11:52 AM   #4
watice
LQ Newbie
 
Registered: Dec 2012
Posts: 2

Original Poster
Rep: Reputation: Disabled
Quote:
Originally Posted by Wim Sturkenboom View Post
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 View Post
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 View Post
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 View Post
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!
 
Old 12-24-2012, 01:56 PM   #5
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Rep: Reputation: 282Reputation: 282Reputation: 282
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.

Last edited by Wim Sturkenboom; 12-24-2012 at 01:58 PM.
 
1 members found this post helpful.
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
mysql query two tables please Help Mike1569 Linux - Server 2 07-09-2009 01:31 AM
MySQL - Query and compare 2 tables rookiepaul Programming 4 05-12-2008 10:05 AM
Wierd SQL query selecting from table acording another tables lack of values? matthewhardwick Programming 3 07-30-2007 06:51 AM
bash - comparing a variable to several values davee Programming 3 05-05-2003 08:26 AM
SQL query, comparing tables ngomong Programming 3 07-07-2002 08:44 PM


All times are GMT -5. The time now is 01:13 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration