Page 1 of 1

Comparing the results of two queries

Posted: Tue Sep 23, 2003 7:35 pm
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)

Posted: Tue Sep 23, 2003 8:43 pm
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.

Posted: Tue Sep 23, 2003 11:07 pm
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.

Posted: Wed Sep 24, 2003 7:39 pm
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.

Posted: Wed Sep 24, 2003 8:24 pm
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.

Posted: Thu Sep 25, 2003 11:58 am
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

Posted: Thu Sep 25, 2003 1:00 pm
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.

Posted: Thu Sep 25, 2003 2:25 pm
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.