Start Free Trial

asked on

Using dreamweaver How do i amalgimate 3 tables of data to be shown on a single php page.

Hello there, Im using Dreamweaver CS4, php and sql.
I want to produce a php page that shows 3 tablesworth of data and I am having some difficulty.

I have created a link on the previous page to produce a jobId= tag in the Url on the page in question. From that URL i can filter the recordset produced for that individual table to show the information stored within that table for the specic jobId requested. But i would like to show related job information stored in related tables on the same page as well, this is so i can update everything a the same time on the same page.

the tables are as follows;

TABLE = Job

jobId
employeeId
loggerEmployeeId
jobStatus
jobActivity
jobDetails

TABLE = Job_Resource

jobId
resourceId

TABLE = Resource

resourceId
resourceName
resourceSeriaNumber
resourceSupplier

I can produce the correct information in dynamic tables for the tables Job and Job_Resource because i can filter the results by the url parameter jobId which is contained in both those tables. BUT i need to show the resource details associated by the resourceId in the Job_Resource table.

Any ideas?

Jason C. Levine

Hi Bucks16,

You will need to know a little bit of SQL query syntax to do this. How are you in that area?

ASKER

I know very very basic, One of my thoughts was the sql query in the recordset path, but wasn't too sure?
i tried the following but was purely guessing;

Select *
From Resource, Job_Resource, Job, Job_Log
Where Resource.resourceId = Job_Resource.resourceId
AND WHERE Job.jobId = colname

Varible Name: Colname
Integer
-1
Run tim value: $_GET['jobId']

i got the varible info from creating a simple recordset first

ASKER

sorry the actual query i used was;

Select *
From Resource, Job_Resource, Job, Job_Log
WHERE Resource.resourceId = Job_Resource.resourceId
AND Job.jobId = colname

ASKER CERTIFIED SOLUTION

Jason C. Levine

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Thanks for your speedy reply on this Jason, im going to go away an pay with this cheers.

ASKER

*Play

ASKER

Hi Jason,

I had forgotten to mention a table, i've had a go at the code but its not right, can you help?

TABLE = Job_Log

jobLogId
jobId
jobLog
employeeId
logDate

SELECT Job.*, Resource.*, Job_Log.*
FROM (Job INNER JOIN Job_Resource ON Job.jobID = Job_Resource.jobId
(INNER JOIN Resource ON Job_Resource.resourceId = Resource.ResourceId))
INNER JOIN Job_Log ON Job.jobId = Job_Log.jobId
WHERE Job_Resource.jobId = colname

Jason C. Levine

Can you perform a SQL dump and load the text file here? It's a heckuva lot easier to do this working with real data...

ASKER

I think this is what your after?
bigredde-hardware.doc

Jason C. Levine

Not quite (I would have to manually re-enter the data). Are you using phpmyadmin?

ASKER

yes

Jason C. Levine

Click the database name in left frame. Then click Export in the right frame.

Select all of the tables you want me to see

Check "SQL" "Structure" "Data" and "Save As File" Then click "Go"

That should make a txt file with what I need to replicate your data.

ASKER

i cant seem to get a text file, i have attched a screen shot, im in the right place, right?
screenshot.jpg

Jason C. Levine

Yes, that's the right screen. Scroll all the way down on the right side to get the "Save As File" option.

ASKER

it saves them as .sql files.

i have attached 2 attempts and renamed them to .txt files to upload.
one is a dump of the whole database "bigredde_hardware" and the other is all the tables selected.
localhost.txt
bigredde-hardware.txt

ASKER

sorry this is the latest ammended one if thats the correct file?
localhost.txt

Jason C. Levine

The first one is perfect. An SQL file is just a plain text file. Okay, let me load this up on my site and play with it for a few minutes.

While I'm doing that, can you rephrase what you want to be able to do with the addition of the 4th table?

ASKER

i would like the page to display all the Job_Log information on the same page as all the other information above, related to the jobId.

so basically all information from all four tables, i imagine i will have to update every table on a seperate page? or can i still use 'update' and 'insert' related to that new recorset?

ASKER

there will normally be multiple jobLogs associated to one jobId

Jason C. Levine

>> there will normally be multiple jobLogs associated to one jobId

What about the other relationships? What will be one-to-many?

>> i imagine i will have to update every table on a seperate page? or can i still use 'update' and 'insert' related to
>> that new recorset?

Most likely separate pages because you will need to pass the Primary Key in order to make the update behavior work. But give me a little time to play.

ASKER

The 'Job' table details will be selected from the jodId= tag in the url along with
'Job_Resource' and 'Job_Log' details

the 'Resource' details will be referenced from the resourcesId field in the Job_Resources table.

There will be 1 set of Job Details related to the jobID
and Multiple sets 'Resource' s and 'Job_Log's shown on the one page

Jason C. Levine

Okay, first things first. The following recordset code does work assuming the query string is ?jid=X

$colname_Recordset1 = "-1";
if (isset($_GET['jid'])) {
  $colname_Recordset1 = (get_magic_quotes_gpc()) ? $_GET['jid'] : addslashes($_GET['jid']);
}
mysql_select_db($database_Jason, $Jason);
$query_Recordset1 = sprintf("SELECT Job.*, Resource.* FROM (Job INNER JOIN Job_Resource ON Job.jobId = Job_Resource.jobId) INNER JOIN Resource ON Job_Resource.resourceId = Resource.resourceId WHERE Job_Resource.jobId=%s", GetSQLValueString($colname_Recordset1, "int"));
$Recordset1 = mysql_query($query_Recordset1, $Jason) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?>

Open in new window

Jason C. Levine

And the will return all jobs and resources, linked.

SELECT Job.*, Resource.* FROM (Job INNER JOIN Job_Resource ON Job.jobId = Job_Resource.jobId) INNER JOIN Resource ON Job_Resource.resourceId = Resource.resourceId

Open in new window

ASKER

what do i do with the first bit of information?

with the last bit of code you gave me, i put it in the recorset query window;

SELECT Job.*, Resource.*
FROM (Job INNER JOIN Job_Resource ON Job.jobID = Job_Resource.jobId) INNER JOIN Resource ON Job_Resource.resourceId = Resource.ResourceId
WHERE Job_Resource.jobId = colname

and appologies in advance if i dont get a reply as in will have to disapear shortly

Jason C. Levine

You also have to define colname using the variable option and set it to $_GET['jid'] or 'id' or whatever you are using.

The idea here is I'm showing you how to use INNER JOIN to relate two tables where you have a linking ID present in both tables. This creates a restrictive link (rows will be only be returned if the link finds matches on both sides of the equation).

The next step is to show you how to use LEFT JOIN to make a less restrictive link, so if one table has rows without a match in the other table, a LEFT JOIN will return all possible data instead of all matching data.

SELECT Job.*, Job_Resource.*
FROM Job LEFT JOIN Job_Resource ON Job.jobId = Job_Resource.jobId

ASKER

so what join is needed because there should be matching links in all tables e.g. jobId and resourceId? which would make me think INNER JOIN?

the statement above doesn't have the Job_Log table included, so i'm not sure if there's any confusion over what the scenario is. so ive tried to clear it up best as possible:

if the sql was to sort by jobID
with a variable of colname = $_GET['jobId'] (defined in the recordset window)

TABLES: Job, Job_Resource, Job_Log, Resource

to show all 'Job' details (one occurence) linked by colname
to show all 'Resource' details (with one or multiple occurences) linked by Job_Resource.resourceId
to show all 'Job_Log' details (with one or multiple occurences) linked by colname or Job.jobId (not which one)?

Jason C. Levine

INNER JOIN only works when there are matching records in both tables. Your sample data only has records for resources up to JobID 3.

>> TABLES: Job, Job_Resource, Job_Log, Resource
>>> to show all 'Job' details (one occurence) linked by colname
>>> to show all 'Resource' details (with one or multiple occurences) linked by Job_Resource.resourceId
>>> to show all 'Job_Log' details (with one or multiple occurences) linked by colname or Job.jobId (not which one)?

Okay, I did sort of misunderstand what your goal was. The above is simpler to see. One further question: how did you want to display all of this data? When you create tables via joins, all of the results come back in rows so you would end up with multiple 'Job' rows for the matching 'Resource' rows:

JobID ResourceID
1 1
1 2
1 3
2 4
2 5
etc.

Is that what you want to see or would you rather see:

JobID 1
Resource 1
Resource 2
Resource 3
JobID 2
Resource 4
Resource 5

ASKER

>>>INNER JOIN only works when there are matching records in both tables. Your sample data only has >>>records for resources up to JobID 3.

Within this page i will make a link to be able to assign resources to this specific job.

>>>>how did you want to display all of this data?

I would like to see the data displayed as the option below;

JobID 1
Resource 1
Resource 2
Resource 3
JobID 2
Resource 4
Resource 5

the same goes for the display of job logs (the data will also be populated by clicking a link on this page)

JobId 1
JobLog 1
JobLog 5
JobLog 3
JobId 2
JobLog 4
JobLog 8
JobLog 12

Jason C. Levine

I have not forgotten you, I'm just super busy with paying work. When I free up a bit, I will show you how to construct the queries and give you a couple of links for creating Nested Repeat Regions so you can display them.

ASKER

No Problem, I appreciate your help.