[RESOLVED] Getting data from multiple tables

MitchEvans

Hi Guys,

I'm sure this is simple (well i hope) but here goes:

I have a table (named responsible) with lots of data in, it has 49 fields each of which can be 0,1 or 2. I have another table (responsible_descriptions) with the description of these 0's,1's and 2's so 2 fields id and description, so 0= fail, 1 = pass and 2 = na.

I am echoing the results like so:

$audit1 = "SELECT * FROM responsible WHERE username LIKE '$username'";
$audit1Results = mysql_query($audit1);
while ($audit1Row = mysql_fetch_array ($audit1Results)){
$ele1 = $audit1Row["1"];
$ele2 = $audit1Row["2"];
$ele3 = $audit1Row["3"];
$ele4 = $audit1Row["4"];
$ele5 = $audit1Row["5"];
$ele6 = $audit1Row["6"];
$ele7 = $audit1Row["7"];
$ele8 = $audit1Row["8"];
$ele9 = $audit1Row["9"];
$ele10 = $audit1Row["10"];
$ele11 = $audit1Row["11"];
$ele12 = $audit1Row["12"];
$ele13 = $audit1Row["13"];
$ele14 = $audit1Row["14"];
$ele15 = $audit1Row["15"];
$ele16 = $audit1Row["16"];
$ele17 = $audit1Row["17"];
$ele18 = $audit1Row["18"];
$ele19 = $audit1Row["19"];
$ele20 = $audit1Row["20"];
$ele21 = $audit1Row["21"];
$ele22 = $audit1Row["22"];
$ele23 = $audit1Row["23"];
$ele24 = $audit1Row["24"];
$ele25 = $audit1Row["25"];
$ele26 = $audit1Row["26"];
$ele27 = $audit1Row["27"];
$ele28 = $audit1Row["28"];
$ele29 = $audit1Row["29"];
$ele30 = $audit1Row["30"];
$ele31 = $audit1Row["31"];
$ele32 = $audit1Row["32"];
$ele33 = $audit1Row["33"];
$ele34 = $audit1Row["34"];
$ele35 = $audit1Row["35"];
$ele36 = $audit1Row["36"];
$ele37 = $audit1Row["37"];
$ele38 = $audit1Row["38"];
$ele39 = $audit1Row["39"];
$ele40 = $audit1Row["40"];
$ele41 = $audit1Row["41"];
$ele42 = $audit1Row["42"];
$ele43 = $audit1Row["43"];
$ele44 = $audit1Row["44"];
$ele45 = $audit1Row["45"];
$ele46 = $audit1Row["46"];
$ele47 = $audit1Row["47"];
$ele48 = $audit1Row["48"];
$ele49 = $audit1Row["49"];

echo "$ele1<br>";
}

This would echo somethimng like this:

1
2

I want it to say:

Fail
Pass
NA
Fail
Fail
Pass

Hope you can help guys

laserlight

I have a table (named responsible) with lots of data in, it has 49 fields each of which can be 0,1 or 2. I have another table (responsible_descriptions) with the description of these 0's,1's and 2's so 2 fields id and description, so 0= fail, 1 = pass and 2 = na.

It sounds like your database schema is not normalised. Perhaps you should have a table with three columns: id, value and description. Then since it looks like these are associated with the users in a way that results in a many to many relationship, you would create another table to associate users and responsibilities. This new table would have columns for the user id and the responsibility id. With this database design, if you want to select the responsibilities for a given user, you would join the three tables together and select the description.

With your current design, it is a little awkward, though it can still be done with a join. Unfortunately, I am not clear as to how these tables are structured and related so I cannot go into more detail.

djjjozsi

is this not enought?

as Silverlight said, you might quess another table structure. your sql query would be soo big if you wanted to join these fields.

<?
$desc[0]="Fail";
$desc[1]="Pass";
$desc[2]="NA";

$audit1 = "SELECT * FROM responsible WHERE username LIKE '$username'";
$audit1Results = mysql_query($audit1);
while ($audit1Row = mysql_fetch_array ($audit1Results)){
{
for($i=1;$i<50;$i++)
${"ele".$i} = $desc[$audit1Row[$i]];
}

echo "$ele1<br>"; 

?>

laserlight

hmm... yes, djjjozsi brings up another point: if those integer values always correspond to the same set of descriptions, then perhaps the responsible_descriptions table is somewhat unnecessary. I am not sure if hard coding the descriptions into the PHP code is the best idea, but then using the descriptions instead of the integer values would be a waste of space.

MitchEvans

Thanks for getting back to me. Here are the table layouts:

table one (reponsible) has 50 fields - id,1,2,3,4,5......49
table two has 3 fields - id,code,desc

How would i join these, the data in fields 1 to 49 in responsible is stored like so 0, 1 OR 2 and table two has data in it such as id=1, code = 1, description = Pass

laserlight

MitchEvans wrote:
How would i join these, the data in fields 1 to 49 in responsible is stored like so 0, 1 OR 2 and table two has data in it such as id=1, code = 1, description = Pass

What are the related columns in the two tables?

Seriously, I suggest that you normalise your database as I have described.

MitchEvans

I will definately be normalising but for today I need to try and join them 1 to 49 is the same as code.

laserlight

hmm... I think a join is going to be very clumsy because of the bad database design. I suggest that you simply run two queries: first, select all the rows from responsible_descriptions and turn them into an array indexed by the code field. Then, select the desired row from the responsible table. Now, getting the corresponding descriptions is a matter of just accessing the array by index. This assumes that the code column is unique.

djjjozsi

you could follow my code suggested before.

laserlight

djjjozsi wrote:
you could follow my code suggested before.

Yes, note that if you use two queries, the first query effectively produces djjjozsi's suggestion. The good part is that your descriptions are stored in the database instead of being hard coded, but the bad part is that you make another query.

djjjozsi

but using numbers as field names is not so lucky, i think.

but using SUB SELECT and set them as alias would make it.

SELECT
1 AS a1 , ( SELECT description FROM responsible_descriptions WHERE id = a1 ) AS d1,
2 AS a2 , ( SELECT description FROM responsible_descriptions WHERE id = a2 ) AS d2,
3 AS a3 , ( SELECT description FROM responsible_descriptions WHERE id = a3 ) AS d3,
4 AS a4 , ( SELECT description FROM responsible_descriptions WHERE id = a4 ) AS d4,
5 AS a5 , ( SELECT description FROM responsible_descriptions WHERE id = a5 ) AS d5,
6 AS a6 , ( SELECT description FROM responsible_descriptions WHERE id = a6 ) AS d6,
7 AS a7 , ( SELECT description FROM responsible_descriptions WHERE id = a7 ) AS d7,
8 AS a8 , ( SELECT description FROM responsible_descriptions WHERE id = a8 ) AS d8,
9 AS a9 , ( SELECT description FROM responsible_descriptions WHERE id = a9 ) AS d9,
10 AS a10 , ( SELECT description FROM responsible_descriptions WHERE id = a10 ) AS d10,
11 AS a11 , ( SELECT description FROM responsible_descriptions WHERE id = a11 ) AS d11,
12 AS a12 , ( SELECT description FROM responsible_descriptions WHERE id = a12 ) AS d12,
13 AS a13 , ( SELECT description FROM responsible_descriptions WHERE id = a13 ) AS d13,
14 AS a14 , ( SELECT description FROM responsible_descriptions WHERE id = a14 ) AS d14,
15 AS a15 , ( SELECT description FROM responsible_descriptions WHERE id = a15 ) AS d15,
16 AS a16 , ( SELECT description FROM responsible_descriptions WHERE id = a16 ) AS d16,
17 AS a17 , ( SELECT description FROM responsible_descriptions WHERE id = a17 ) AS d17,
18 AS a18 , ( SELECT description FROM responsible_descriptions WHERE id = a18 ) AS d18,
19 AS a19 , ( SELECT description FROM responsible_descriptions WHERE id = a19 ) AS d19,
20 AS a20 , ( SELECT description FROM responsible_descriptions WHERE id = a20 ) AS d20,
21 AS a21 , ( SELECT description FROM responsible_descriptions WHERE id = a21 ) AS d21,
22 AS a22 , ( SELECT description FROM responsible_descriptions WHERE id = a22 ) AS d22,
23 AS a23 , ( SELECT description FROM responsible_descriptions WHERE id = a23 ) AS d23,
24 AS a24 , ( SELECT description FROM responsible_descriptions WHERE id = a24 ) AS d24,
25 AS a25 , ( SELECT description FROM responsible_descriptions WHERE id = a25 ) AS d25,
26 AS a26 , ( SELECT description FROM responsible_descriptions WHERE id = a26 ) AS d26,
27 AS a27 , ( SELECT description FROM responsible_descriptions WHERE id = a27 ) AS d27,
28 AS a28 , ( SELECT description FROM responsible_descriptions WHERE id = a28 ) AS d28,
29 AS a29 , ( SELECT description FROM responsible_descriptions WHERE id = a29 ) AS d29,
30 AS a30 , ( SELECT description FROM responsible_descriptions WHERE id = a30 ) AS d30,
31 AS a31 , ( SELECT description FROM responsible_descriptions WHERE id = a31 ) AS d31,
32 AS a32 , ( SELECT description FROM responsible_descriptions WHERE id = a32 ) AS d32,
33 AS a33 , ( SELECT description FROM responsible_descriptions WHERE id = a33 ) AS d33,
34 AS a34 , ( SELECT description FROM responsible_descriptions WHERE id = a34 ) AS d34,
35 AS a35 , ( SELECT description FROM responsible_descriptions WHERE id = a35 ) AS d35,
36 AS a36 , ( SELECT description FROM responsible_descriptions WHERE id = a36 ) AS d36,
37 AS a37 , ( SELECT description FROM responsible_descriptions WHERE id = a37 ) AS d37,
38 AS a38 , ( SELECT description FROM responsible_descriptions WHERE id = a38 ) AS d38,
39 AS a39 , ( SELECT description FROM responsible_descriptions WHERE id = a39 ) AS d39,
40 AS a40 , ( SELECT description FROM responsible_descriptions WHERE id = a40 ) AS d40,
41 AS a41 , ( SELECT description FROM responsible_descriptions WHERE id = a41 ) AS d41,
42 AS a42 , ( SELECT description FROM responsible_descriptions WHERE id = a42 ) AS d42,
43 AS a43 , ( SELECT description FROM responsible_descriptions WHERE id = a43 ) AS d43,
44 AS a44 , ( SELECT description FROM responsible_descriptions WHERE id = a44 ) AS d44,
45 AS a45 , ( SELECT description FROM responsible_descriptions WHERE id = a45 ) AS d45,
46 AS a46 , ( SELECT description FROM responsible_descriptions WHERE id = a46 ) AS d46,
47 AS a47 , ( SELECT description FROM responsible_descriptions WHERE id = a47 ) AS d47,
48 AS a48 , ( SELECT description FROM responsible_descriptions WHERE id = a48 ) AS d48,
49 AS a49 , ( SELECT description FROM responsible_descriptions WHERE id = a49 ) AS d49
FROM responsible

and with d1 .... d49 he get the description value. if the connection between the two table is responsible_descriptions.id and the alies i set.

MitchEvans

Thanks for that djjjozsi, I can't seem to get that working though any ideas why?

I just get a blank screen

laserlight

djjjozsi wrote:
but using numbers as field names is not so lucky, i think.

I agree. Numbered columns is almost always a sign of bad design.

djjjozsi wrote:
but using SUB SELECT and set them as alias would make it.

I considered that, or rather, the equivalent using multiple joins, but as you can see, it is very clumsy, and you have a situation where if the database changes, the code must fundamentally change. Then again, if MitchEvans gets round to normalising the database, it would not matter anyway.

Speaking of normalising the database, I'd say that my initial suggestion is not quite correct since it models a situation where the descriptions are specific to the responsibilities. You may want to keep your current responsible_descriptions table and join that with the join table (i.e., the third table) whenever you want the description. The join table itself would have the "value" (which really is a foreign key to the responsible_descriptions table).

djjjozsi

lets echo these

$audit1Row["a1"]; $audit1Row["d1"];
$audit1Row["a49"]; $audit1Row["d49"];

if its working, the query works.

then use a for to print the results.

MitchEvans

Thanks for all the help guys, your ideas made me come up with this which works great:


$counterel++;

IF (${'el'.$counterel} == '0') {
	${'el'.$counterel} = 'N/A';

}
IF (${'el'.$counterel} == '1') {
	${'el'.$counterel} = 'Pass';

}