• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

PHP Comparing values in SQL tables


I have two tables (A & B). I want to list on my PHP page all the values from table A and a Yes next to each record if there is a matching record in table B.

Suppose we have the following:
Table A      Support Categories                  
| ID      | Description                      |
| 1      | CV Writing Assistance    |
| 2      | Job Search Assistance   |
| 3      | Interview Skills                |

Table B      
| ClientID  |      SupportID  (references table A  ID)  |      SupportStarted     |      SupportStopped
| C1         |      1                                                       |      01/06/10              |            01/06/10
| C2         |      1                                          |      01/06/10      
| C1         |        2                                          |      05/07/10             |             06/07/10
| C1         |      2                                          |       09/09/10      
| C4         |      1                                          |       19/08/10      

I want to show for a given client whether they have started to receive support and whether they have any opened support areas  (not stopped) in my PHP page.

For example Example.php
---------------------------------------------------------------------------------------------------------
CLIENT ID 1

Description                           |     Support Started       |     Open Support

CV Writing Assistance      |           Yes                            |          No
Job Search Assistance      |           Yes                            |         Yes
Interview Skills      |             No                            |          No

_____________________________________________________________________________

Normally I would do the following.
1.      Run a query which returns all rows from Table A
2.      Use a While() loop to display each row from table A.
3.      Within the While() loop query each record from table B to look for matching rows.
See attached code.
I was wondering if there was a better (more efficient) way of doing this without having to repeat the second query each time a row is loaded from the first query?
Is there any way to compare query results whilst they are in the array?

Example.php
0
EICT
Asked:
EICT
2 Solutions
 
darren-w-Commented:
Might be better doing this in sql

something like

select ClientID, description, 'yes' as Support_started, 'yes' as 'open_support'
from table_b b
left join table_a a
ON b.SupportID = a.ID
where b.ClientID = c1

then just printing this output

there will need to be a little more logic applied to display the support statuses.

D
0
 
Ray PaseurCommented:
There are some things not worth optimizing, and this might be one of them.  If you do not have thousands of rows, and your data base is well-indexed, you might be OK with almost any query structure!
0
 
EICTAuthor Commented:
The query was wrong but the principle is there. Try to do as much in mysql first to you only have to run the query once.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now