Solved

Selecting and displaying data from multiple tables with a variable

Posted on 2009-05-13
5
378 Views
Last Modified: 2012-05-06
Could use a bit of help, I'm loosing hair over this one....

I have 3 tables of data, and need to return a result from all 3 tables based on a variable and time limit
for example... I need to list all the entries for a client for the past seven days from all 3 db tables.


$client="testing";
$sql = "
SELECT table7.submission_id, table7.employee, table7.client, table_8.submission_id, table8.employee, table8.client, table_9.submission_id, table9.employee, table9.client
FROM table7, table8, table9
WHERE table7.client, table8.client, table9.client 
	LIKE CONCAT('%', $client ,'%') 
	AND date >= (SELECT max(date) - INTERVAL '7' DAY FROM table7.submission_date, table8.submission_date, table9.submission_date)";
 
$result = @mysql_query($sql,$connection) or die(mysql_error());	
 
if( mysql_num_rows( $result ) == 0 ) {print "<p class='error'>You currently have no reports available to you</p>";die;}
else {while( list( $submission_id, $employee, $client, $submission_date, ) = mysql_fetch_array( $result ) ) {
 
echo "
  	<table class='list_table' width='500' cellpadding='1' border='1' cellspacing='1'><tr style='height: 20px; font-size:10px;'>
            <td width='150'>$client</td>
            <td width='45'>$employee</td>
            <td width='45'>$submission_id</td>
            <td width='45'>$submisson_date</td></tr>";

Open in new window

0
Comment
Question by:piecealava
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 9

Expert Comment

by:wellhole
ID: 24379157
In your sql statement you need to indicate how each of the tables are related, such as

where table7.id = table8.id
and table8.name = table9.name
and table7.client = table8.client
and table7.client = table9.client

Also, for WHERE table7.client, table8.client, table9.client

I don't believe you can use a comma like this....... If you indentify how the tables are related, you only need to use ONE of them   table7.client LIKE CONCAT('%', $client ,'%')

0
 

Author Comment

by:piecealava
ID: 24379539
I've  incorporated your suggestions, but it returns 0 results when leaving out the like clause,  
When adding the like clause back in I get an error -- Unknown column 'testing' in 'where clause'

here is my code now... any ideas???
SELECT table7.submission_id, table7.col_4, table7.col_2, table7.submission_date, table8.submission_id, table8.col_4, table8.col_2, table8.submission_date,table9.submission_id, table9.col_2, table9.col_4, table9.submission_date
FROM table7, table8, table9
WHERE table7.col_4 = table8.col_4 
AND table8.col_4 = table9.col_4
AND table7.submission_id = table8.submission_id
AND table8.submission_id = table9.submission_id
AND table7.col_2 = table8.col_2
AND table8.col_2 = table9.col_2
AND table7.submission_date = table8.submission_date
AND table8.submission_date = table9.submission_date
 
AND table7.col_2 
	LIKE CONCAT('%', $client ,'%') 

Open in new window

0
 
LVL 9

Expert Comment

by:wellhole
ID: 24379601
If you're getting 0 results, then its possible you're not linking the tables correctly. As for the 'testing' error, its probably from $client. Try '$client' instead.
0
 

Author Comment

by:piecealava
ID: 24380464
Thanks for the $client fix, that works, but I still cannot get the needed results from multiple tables

How do I get the same results from table8,and table 9 included in the result?  col_2 is the client in all tables.  The code below returns the resluts I want from 1 table.  what's my next step?

$client="testing";
$sql = "
SELECT DISTINCT submission_id, col_4, col_2, submission_date
FROM table7
WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= submission_date
AND col_2 LIKE ('%' '$client' '%')
	";

Open in new window

0
 
LVL 9

Accepted Solution

by:
wellhole earned 500 total points
ID: 24381028
Any reason you're using distinct? The next step is to add 1 table at a time and join them based on their relation to each other. So, add table8 and link it to table7 and see if you're getting the results you wanted. Then, move onto table9.

Assuming these tables have 1 to 1 relation, then you should always get the same number of results (unless you have conditions for each table). You can use that as your marker.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question