Jump to content


Photo

Array checking


  • Please log in to reply
21 replies to this topic

#1 dewdo

dewdo
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 18 September 2006 - 08:45 PM

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


#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 18 September 2006 - 09:13 PM

I can't help you with your sql as there are no column names, but this looks an identical problem to this one

http://www.phpfreaks....html#msg436674
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 dewdo

dewdo
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 18 September 2006 - 09:54 PM

i dont see how that link gets me there. thanks for searching...

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 18 September 2006 - 10:15 PM

<?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>';
?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 dewdo

dewdo
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 19 September 2006 - 01:10 PM

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?

#6 dewdo

dewdo
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 19 September 2006 - 04:31 PM

$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.

#7 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 19 September 2006 - 05:00 PM

Don't know. Don't know you data or table structure. How are the 3 tables related?
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#8 dewdo

dewdo
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 19 September 2006 - 05:26 PM

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 ....

#9 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 19 September 2006 - 05:47 PM

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]
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#10 dewdo

dewdo
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 19 September 2006 - 06:20 PM

yeah. thats right.

#11 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 19 September 2006 - 06:28 PM

If you need help with queries, giving that sort of info in the first place helps us to help you. I'll set up some test tables and see what I can come up with.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#12 dewdo

dewdo
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 19 September 2006 - 06:33 PM

thanks for helping. i apologize for not spelling it out more clearly earlier in the process.

#13 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 19 September 2006 - 07:39 PM

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
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

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]
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#14 dewdo

dewdo
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 19 September 2006 - 11:01 PM

looks like it will work. thanks for the help!

#15 dewdo

dewdo
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 25 September 2006 - 07:11 PM

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


#16 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 25 September 2006 - 07:41 PM

Same basic query different table and field names
SELECT t.types, c.cps, IF(m.apsid IS NULL, 'no', 'YES') AS matched
FROM allstypes t INNER JOIN allcps c
LEFT JOIN allcombo m ON t.types = m.types AND c.cps = m.cps
ORDER BY t.types, c.cps

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#17 dewdo

dewdo
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 25 September 2006 - 08:30 PM


Incorrect syntax near the keyword 'IF'.

Line 1: Incorrect syntax near ','.


#18 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 25 September 2006 - 08:45 PM

1 ) are you using MySQL?

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

<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>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#19 dewdo

dewdo
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 25 September 2006 - 08:46 PM

this is MS SQL. will it not work?

#20 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 25 September 2006 - 08:51 PM

Why the **** are you posting in the MySQL forum then?

Try
SELECT t.types, c.cps, CASE WHEN m.apsid IS NULL THEN 'no' ELSE 'YES' END AS matched
FROM allstypes t INNER JOIN allcps c
LEFT JOIN allcombo m ON t.types = m.types AND c.cps = m.cps
ORDER BY t.types, c.cps

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users