Comparing the results of two queries

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jmb
Forum Newbie
Posts: 5
Joined: Tue Sep 23, 2003 7:35 pm

Comparing the results of two queries

Post by jmb »

I have two queries. Together they should create 1 list

Code: Select all

<?php
$q = mysql_query("SELECT * FROM C_PDF WHERE c_id = '7' AND a_by = '7'"); 
$r = mysql_fetch_array($q);
//This gives me the following result set
//C_PDF.cd_id = 7
//C_PDF.pdf_id = 123
//C_PDF.a_by = 7
//
//C_PDF.cd_id = 71
//C_PDF.pdf_id = 124
//C_PDF.a_by = 7
//Indicating that there are 2 entries (1 for pdf_id 123 and 1 for pdf_id 124)

$q2 = mysql_query("SELECT * FROM C_PDF WHERE c_id = '71' AND a_by = '7'"); 
$r2 = mysql_fetch_array($q2);
//This gives me the following result set
//C_PDF.cd_id = 71
//C_PDF.pdf_id = 124//also in the first result set
//C_PDF.a_by = 7
//
//C_PDF.cd_id = 7
//C_PDF.pdf_id = 230
//C_PDF.a_by = 7
//Indicating that there are 2 entries (1 for pdf_id 124 and 1 for pdf_id 230)

//I want to display a (CHECKED)checkbox form element 
//beside pdf_id 124 in the second result set because it is also in 
//the first result set, and I only want to display it once.

//I am trying to create a combined list out of the two result sets 
//with (CHECKED) checkboxes by those in both result sets and
//(UNCHECKED) checkboxes by those in the first result set.
//I am having a terrible time with getting pdf_id 124 to display 
//only once. Any help would be greatly appreciated.

?>
(mod_edit: bbcode enabled)
Unipus
Forum Contributor
Posts: 409
Joined: Tue Aug 26, 2003 2:06 pm
Location: Los Angeles, CA

Post by Unipus »

Well, I'm not really clear how your queries work, but if you literally just want to work with those results:

Code: Select all

select distinct pdf_id, a_by, cd_id from C_PDF where (cd_id = 7 OR cd_id = 71) AND a_by = 7;
Hopefully that will steer you somewhere useful.
User avatar
Leviathan
Forum Commoner
Posts: 36
Joined: Tue Sep 23, 2003 7:00 pm
Location: Waterloo, ON (Currently in Vancouver, BC)

Post by Leviathan »

What Unipus gave you was something that will select all the results that are in query 2 as well as query 1. If I understand it right, that's the same as just running query 2, since query 2 is entirely a subset of query 1.

I think we could do this with a single query using a left join on the same table, so the first query gets treated as the table where everything is returned and the second one rerturns all records that match the first table, or null if there's no record in table 2 matching the record in table 1. (Someone please correct me if I've mixed up my left and right joins)

What I mean is this. I'm not certain if it works, or that my syntax is 100% correct, but I think it'll do what you want.

$query = "select * from C_PDF as t1 left join C_PDF as t2 on t1.cd_id = t2.cd_id where t1.cd_id = '7' and t1.a_by = '7' and t2.cd_id = '71' and t2.a_by = '7'"

What this should give you (assuming I did it right) is the two queries you want combined into one. You can loop through the result set, adding an item for each value of t1.cd_id. If t2.cd_id isn't null for a particular row, you can add a checkmark to the checkbox. Give it a try.
jmb
Forum Newbie
Posts: 5
Joined: Tue Sep 23, 2003 7:35 pm

Post by jmb »

Code: Select all

<?php
$query "SELECT * FROM C_PDF AS t1 LEFT JOIN C_PDF AS t2 ON t1.a_by = t2.a_by WHERE t1.cd_id = '7' AND t1.a_by = '7' AND t2.cd_id = '71' AND t2.a_by = '7'"
?>
Here are actual results
t1 results--------------------t2 results
cd_id----pdf_id----a_by----cd_id-----pdf_id----a_by
---7--------11--------7--------71--------83--------7
---7--------11--------7--------71--------160-------7
---7--------82--------7--------71--------83--------7
---7--------82--------7--------71--------160-------7
---7--------83--------7--------71--------83--------7 MATCH
---7--------83--------7--------71--------160-------7
---7--------160-------7--------71--------83--------7
---7--------160-------7--------71--------160-------7 MATCH
---7--------161-------7--------71--------83--------7
---7--------161-------7--------71--------160-------7

Problems:
I am now getting duplicate pdf_id results for cd_id 7 and suspect that if cd_id 71 had another MATCH I would be getting tripple pdf_id's for cd_id 7.

This appears to be close but it is still not quite working out.

Here is what I would like to see
t1 results--------------------t2 results
cd_id----pdf_id----a_by----cd_id-----pdf_id----a_by
---7--------11--------7----------------------------------Unchecked
---7--------82--------7----------------------------------Unchecked
---7--------83--------7--------71--------83--------7---Checked
---7--------160-------7--------71--------160------7---Checked
---7--------161-------7----------------------------------Unchecked

Thanks for taking the time to reply.
User avatar
Leviathan
Forum Commoner
Posts: 36
Joined: Tue Sep 23, 2003 7:00 pm
Location: Waterloo, ON (Currently in Vancouver, BC)

Post by Leviathan »

Ok, I'm waiting for code to compile at work, so I'm going to make a sample table and try it out. Hold on.
jmb
Forum Newbie
Posts: 5
Joined: Tue Sep 23, 2003 7:35 pm

Post by jmb »

Actual contents of the mysql table
cd_id----pdf_id----a_by
---7--------11--------7
---7--------82--------7
---7--------83--------7
---7--------160-------7
---7--------161-------7
---71-------83--------7
---71-------160-------7
---85-------11--------7
---87-------11--------7
---87-------82--------7

Thanks again
User avatar
Leviathan
Forum Commoner
Posts: 36
Joined: Tue Sep 23, 2003 7:00 pm
Location: Waterloo, ON (Currently in Vancouver, BC)

Post by Leviathan »

I couldn't come up with a solution while playing in phpMyAdmin for half an hour last night. Can you explain exactly what your script is trying to accomplish? There may be a better way to do it than trying to write a query like this.
jmb
Forum Newbie
Posts: 5
Joined: Tue Sep 23, 2003 7:35 pm

Post by jmb »

I have a list of contacts (cd_id) who have products they are associated with (pdf_id), and (a_by) lets me know who owns the product.

eg. Mike(cd_id 7) has 5 products(pdf_id 11,82,83,160,161) I know he owns them because (a_by is his cd_id).

Mike(cd_id 7) has given Jim(cd_id 71) access to information for 2 of his products (pdf_id 83,160) I know Mike has allowed Jim access because (a_by is Mike's cd_id which is 7).

I want to give Mike the opportunity to change Jim's list of associated products via a checklist form with current product associations checked and other possible product associations unchecked.

cd_id----pdf_id----a_by
---7--------11--------7
---7--------82--------7
---7--------83--------7
---7--------160-------7
---7--------161-------7
---71-------83--------7
---71-------160-------7
---85-------11--------7
---87-------11--------7
---87-------82--------7

Here is what I would like Mike to see when editing jim's product list
1 list of Mike's products with Jim's current product associations checked.

cd_id----pdf_id----a_by----cd_id-----pdf_id----a_by
---7--------11--------7----------------------------------Unchecked
---7--------82--------7----------------------------------Unchecked
---7--------83--------7--------71--------83--------7---Checked
---7--------160-------7--------71--------160------7---Checked
---7--------161-------7----------------------------------Unchecked

I really appreciate the time you are putting in to this.
Post Reply