yzerman Posted January 31, 2007 Share Posted January 31, 2007 I have 2 tables, each with one field in a mysql database. I need to compare the data in these 2 tables, and print the results of the query out.The second part is not the problem for me. The first part (getting the actual query to work) is the problem.Here are the table setups:[table][tr][td][center]Table 1 (Field) - varchar(40) - unique[/center][/td][td][center]Table 2 (Field) varchar (8 ) - unique[/center][/td][/tr][tr][td][center]12345[/center][/td][td][center]12345[/center][/td][/tr][/table]Now what I want to do with this data, is when a record for table 1, matches the data in table 2, I want it to send that data to another table called matches, and echo that data. I have tried using joins, and I have tried using array_intersect($table1, $table2) and each of these ways results in a null result, however I know that there is at least 1 match.Unfortunatly, my website is down ATM, so pasting a copy of my code isn't going to happen until it gets back up, but any help would be appreciated. Quote Link to comment Share on other sites More sharing options...
Hypnos Posted January 31, 2007 Share Posted January 31, 2007 This is best done with a JOIN in your SQL. Trying to do it in arrays will just add to the headache. SQL database servers are designed just for this sort of thing.I can guess that you would want something like this:SELECT field.table1, unique.table1, field.table2 AS field2FROM table1LEFT JOIN table2ON unique.table1 = unique.table2But I'm not an expert at this stuff, like some of the people in the SQL forums.If you want to learn more about SQL, I highly suggest going here:http://www.sqlzoo.net/It's an amazing world once you get past the "SELECT * FROM table" stuff. Quote Link to comment Share on other sites More sharing options...
yzerman Posted January 31, 2007 Author Share Posted January 31, 2007 [quote author=yzerman link=topic=124822.msg517733#msg517733 date=1170218638]I have tried using joins, and I have tried using array_intersect($table1, $table2) and each of these ways results in a null result, however I know that there is at least 1 match.[/quote]I think you missed the important part, and that was what I had already tried was using joins in an SQL query, and those failed. Quote Link to comment Share on other sites More sharing options...
Hypnos Posted January 31, 2007 Share Posted January 31, 2007 Comparing data on 2 tables is exactly what JOINs are for. If it "didn't work", your syntax was wrong.If you have a table like this:1 orange2 pearAnd another like this:1 apple2 cherryAnd you want:1 orange apple2 pear cherryThen you want a JOIN. It will "work". Even if the ids only match up sometimes (That's why you have "LEFT JOIN", "RIGHT JOIN", etc).If you had a NULL result, you obviously didn't do it right. Or is your coding just inherently 100% perfect? Quote Link to comment Share on other sites More sharing options...
yzerman Posted January 31, 2007 Author Share Posted January 31, 2007 but thats not what I wantIf I have a table like this1 apple2 orange3 watermelonand another like this1 cherry2 pear3 blueberry4 watermelonAnd I want:1 watermelonas the result because it will be the only row that matches, not 1 apple cherry2 orange pearand yes, my coding is 100% perfect (explaining my account here ;) :P ) Quote Link to comment Share on other sites More sharing options...
Hypnos Posted January 31, 2007 Share Posted January 31, 2007 Ignore my dislexia on my previous SQL.SELECT table1.field, table1.unique, table2.unique AS unique2FROM table1INNER JOIN table2ON table1.field = table2.fieldShould output:3 4 watermelon Quote Link to comment Share on other sites More sharing options...
yzerman Posted February 1, 2007 Author Share Posted February 1, 2007 Appreciate the help Hypnos, I will let you know if that works. Quote Link to comment Share on other sites More sharing options...
yzerman Posted February 1, 2007 Author Share Posted February 1, 2007 Ok, I figured out what the problem was. I did not have the tables indexed. The query that finally worked, after I set the tables indexes was:SELECT `table1`.`field`FROM table1, table2WHERE (`table2`.`field` = `table1`.`field`) Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.