Re: SQL question

Addressed to: dfordham@summittech.com php-general@lists.php.net chayes@antenna.nl ** Reply to note from "David Fordham" <dfordham@summittech.com> Fri, 08 Dec 2000 16:23:23 -0700 > > Using other tools it may not be possible and specifically, it is not > possible in MYSQL. You will have to use cursor logic and roll through > a select and drop certain values into variables and do additional > selects off those variables. Basically, you will need to create a > report. I disagree... In Mysql: SELECT Name, Category, SUM( if( Users.UserID = Interest.UserID, 1, 0 )) AS Interested FROM Users, Categories LEFT JOIN Interest USING( UserID )) GROUP BY Name, Category ORDER BY Name, Category This returns: +-------+-----------+------------+ | Name | Category | Interested | +-------+-----------+------------+ | Anna | Bicycles | 0 | | Anna | Trees | 1 | | Anna | Windmills | 0 | | Chris | Bicycles | 1 | | Chris | Trees | 0 | | Chris | Windmills | 1 | | Harry | Bicycles | 0 | | Harry | Trees | 1 | | Harry | Windmills | 1 | +-------+-----------+------------+ 9 rows in set (0.01 sec) In PHP you can then do: echo "<TABLE>\n"; $OldName = ''; while( list( $Name, $Category, $Interested ) = mysql_fetch_row( $Result )) { if( $Name != $OldName ) { echo "<TR><TD colspan=2><BR><BR>&nbsp;-- $Name --</TD></TR>\n"; $OldName = $Name; } $Int = ( $Interested ) ? 'Yes' : 'No'; echo "<TR><TD>$Category</TD><TD>$Int</TD></TR>\n"; } echo( "</TABLE>\n"; which looks pretty much like the requested result to me... -- Anna -- Bicycles No Trees Yes Windmills No -- Chris -- Bicycles Yes Trees No Windmills Yes -- Harry -- Bicycles No Trees Yes Windmills Yes No cursors, no multiple selects, and just a simple pass thru the result to display the data. If you want only one user, add a WHERE clause to select the user. Notes: o The MySQL query is tested, and the resutls are as shown. The PHP code is off the top of my head, and untested. o I changed Categories.Name to Categories.Category to eliminate the need for aliasing the field names. > > > Dear list, > > > > I want to make a query with a column saying yes/no depending on interest of > > a user. > > > > Suppose i have three tables: > > > > USER (userID, name) > > 1 Chris > > 2 Harry > > 3 Anna > > > > CATEGORIES (catID, name) > > 4 bicycles > > 5 windmills > > 20 trees > > > > USER_INTEREST (userID, catID) > > 1 4 > > 1 5 > > > > 2 4 > > 2 20 > > > > 3 20 > > > > > > I would like to get this query result: > > > > CATEGORIE.naam |user_2_is_interested > > --------------------------------- > > bicycles|yes > > windmills|yes > > trees|no > > > > As you see i want every categorie in one row (each once) and the next row > > says whether a user is interested. > > > > So i made this query: > > > > SELECT DISTINCT CATEGORIE.naam naam, > > IF (1=1,"yes","no") user_2_is_interested > > FROM categorie , user_cat > > > > As you see my IF condition is now 1=1. Of course i want to see whether the > > current user ($UID) is interested in this specific categorie. > > But as soon as i try to adapt the WHERE or the IF i get all sorts of doubles. > > Help? > > Chris > > > > > > -------------------------------------------------------------------- > > -- C.Hayes Droevendaal 35 6708 PB Wageningen the Netherlands -- > > -------------------------------------------------------------------- > > > > > > > > -- > > PHP General Mailing List (http://www.php.net/) > > To unsubscribe, e-mail: php-general-unsubscribe@lists.php.net > > For additional commands, e-mail: php-general-help@lists.php.net > > To contact the list administrators, e-mail: php-list-admin@lists.php.net > > > > > > > > > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, > e-mail: php-general-unsubscribe@lists.php.net For additional > commands, e-mail: php-general-help@lists.php.net To contact the list > administrators, e-mail: php-list-admin@lists.php.net > > Rick Widmer Internet Marketing Specialists http://www.developersdesk.com