Jump to content

Array checking


dewdo

Recommended Posts

I have a table that has a complete set of labels. I have another table that assigns some labels to some users. I want to create a list that prints the complete set of labels and then says whether it has been assigned to the user or not.

table one:
label 1
label 2
label 3
label 4

table two:
user1 | label 1
user2 | label 2

desired output:
label 1 | user1 | yes
label 2 | user1 | yes
label 3 | user1 | no
label 4 | user1 | no

any ideas?

thanks
Link to comment
Share on other sites

[code]<?php
$username = 'user1';
$sql = "SELECT a.label, b.label
        FROM `table one` a
        LEFT JOIN `table two` b
            ON a.label = b.label AND b.user = '$username'";
$res = mysql_query($sql) or die(mysql_error());
echo '<pre>';
while (list($laba, $labb)=mysql_fetch_row($res)) {
    $assign = $labb ? 'yes' : 'no';
    printf ('%-8s | %-8s | %-4s<br>', $laba, $username, $assign);
}
echo '</pre>';
?>[/code]
Link to comment
Share on other sites

thank you, that was helpful.

If there are three tables [ptypes], [cparties], [eligible]

ptypes have all of label1 and cparties have all of label2, eligible matches some of label1 and label2. I need to show a complete list such that there is all ptypes and all cparties and then a yes/no based on what is in eligible ....

would this be a union or embedded left join of some sort?
Link to comment
Share on other sites

$sql = "SELECT DISTINCT tb1.label, tb2.label2 FROM tb1, tb2, tb3 WHERE '(SELECT tb2.label2 FROM tb2 LEFT JOIN tb3 ON tb2.label2 = tb3.label2)'";

why wont this work?

I am trying to display the entire contents of two tables and then show which of the rows match are recorded together in a third table.
Link to comment
Share on other sites

label2.

I am trying to achieve a list where every type of car is shown (table 1), every possible color is shown (table 2) and then a column is inserted that says whether that car comes in that color (table 3). Except, table 3 just has entries where a car does come in that color. I need the results to yes, if its in table three, or no if its not in table three ....
Link to comment
Share on other sites

So I guess this is what you have?
[pre]
car (T1)              car_color (T3)        color (T2)
---------              --------------        ----------
carID          -----  carID            +--- colorID
car_decription        colorID    ------+    color


1 | BMW 525            1 | R                R | Red
2 | Merc SL500        1 | S                G | Green
3 | Trabant            2 | S                B | Blue
                      3 | G                S | Silver
                      3 | B
[/pre]

And this is what you want?                     
[pre]

          | Red  |  Green | Blue | Silver |
-----------+-------+--------+------+--------+
BMW 525    |  Y    |        |      |  Y    |
Merc SL500 |      |        |      |  Y    |
Trabant    |      |    Y  |  Y  |        |
[/pre]
Link to comment
Share on other sites

OK. This query will do it.

Note that when joining the car and color tables I do not specify a join condition. This combines every value in car table with every value in color table.

I then LEFT JOIN to the car_color table so that if a match is found for the car.color combination then cc.carID has a value, but if no match is found then it contains NULL
[code]
SELECT c.car_desc, co.color, cc.carID
FROM car c INNER JOIN color co
LEFT JOIN car_color cc ON c.carID = cc.carID AND co.colorID = cc.colorID
ORDER BY c.carID, co.colorID
[/code]

gives -->
[pre]
car_desc  |color  | carID
-----------+-------+-------------
BMW 525    |Blue  |
BMW 525    |Green  |
BMW 525    |Red    |  1
BMW 525    |Silver |  1
Merc SL500 |Blue  |
Merc SL500 |Green  |
Merc SL500 |Red    |
Merc SL500 |Silver |  2
Trabant    |Blue  |  3
Trabant    |Green  |  3
Trabant    |Red    |
Trabant    |Silver |
[/pre]
Link to comment
Share on other sites

that query did not end up working ...

i have this structure

I have three tables

AllSTypes                          AllCPS                 
|types|                            |CPS|                 
type 1                              CP 1
type 2                              CP 2
type 3                              CP 3
type 4                              CP 4



AllCombo
|CPS|      |types|      |APSID|
CP 1        type 2        1
CP 2        type 1        2
CP 2        type 2        3
CP 4        type 3        4


I need results that show a full list of Types and CPS and then says whether there is an entry for that combo in AllCombo or not... that part i think we agree on
Link to comment
Share on other sites

1 ) are you using MySQL?

2 ) post the code you are using as I ran the code prior to posting giving

[code]
<html>
<head>
<title>Query</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body>
<table border=1 cellspacing=1 cellpadding=0><tr>
<th>types</th><th>cps</th><th>matched</th></tr>
<tr>
<td>type 1</td><td>CP 1</td><td>no</td></tr>
<tr>
<td>type 1</td><td>CP 2</td><td>YES</td></tr>
<tr>
<td>type 1</td><td>CP 3</td><td>no</td></tr>
<tr>
<td>type 1</td><td>CP 4</td><td>no</td></tr>
<tr>
<td>type 2</td><td>CP 1</td><td>YES</td></tr>
<tr>
<td>type 2</td><td>CP 2</td><td>YES</td></tr>
<tr>
<td>type 2</td><td>CP 3</td><td>no</td></tr>
<tr>
<td>type 2</td><td>CP 4</td><td>no</td></tr>
<tr>
<td>type 3</td><td>CP 1</td><td>no</td></tr>
<tr>
<td>type 3</td><td>CP 2</td><td>no</td></tr>
<tr>
<td>type 3</td><td>CP 3</td><td>no</td></tr>
<tr>
<td>type 3</td><td>CP 4</td><td>YES</td></tr>
<tr>
<td>type 4</td><td>CP 1</td><td>no</td></tr>
<tr>
<td>type 4</td><td>CP 2</td><td>no</td></tr>
<tr>
<td>type 4</td><td>CP 3</td><td>no</td></tr>
<tr>
<td>type 4</td><td>CP 4</td><td>no</td></tr>
</table>
</body></html>[/code]
Link to comment
Share on other sites

My apologies, i just replied to my last message on this thread b/c it was a smilar problem.

I made the modification you suggested by get "Incorrect syntax near the keyword 'ORDER'." I did a search but did not see the answer ... do you have any thoughts?
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.