SELECT statement help

[SweetLou]

I am trying to get all the information about the users from a single table where a certain condition occurs. The table looks like:
[table][tr][td]field_id[/td][td]user_id[/td][td]value[/td][/tr]
[tr][td]field_id[/td][td]user_id[/td][td]value[/td][/tr]
[tr][td]1[/td][td]1[/td][td]Steve[/td][/tr]
[tr][td]2[/td][td]1[/td][td]Blue[/td][/tr]
[tr][td]3[/td][td]2[/td][td]Yes[/td][/tr]
[tr][td]1[/td][td]3[/td][td]Mary[/td][/tr]
[tr][td]2[/td][td]2[/td][td]Red[/td][/tr]
[tr][td]3[/td][td]3[/td][td]No[/td][/tr]
[tr][td]1[/td][td]2[/td][td]Mike[/td][/tr]
[tr][td]2[/td][td]3[/td][td]Red[/td][/tr]
[tr][td]3[/td][td]1[/td][td]Yes[/td][/tr]
[/table]
What I need to do is find all users that field_id #3 is equal to "Yes" then return all information about each user. The final results will be printed into tables like
[table][tr][td]Name[/td][td]Kevin[/td][/tr]
[tr][td]Color [/td][td]Blue[/td][/tr][/table]

[table][tr][td]Name[/td][td]Mike[/td][/tr]
[tr][td]Color [/td][td]Red[/td][/tr][/table]

I first did this:
[code]SELECT user_id FROM table WHERE (field_id = 3 AND value = 'Yes')[/code]
and thought I could somehow get the results to select the data for each user. Then I thought a subquerry would be better:
[code]SELECT field_id, user_id, value FROM table WHERE user_id = (SELECT user_id FROM table WHERE (field_id = 3 AND value = 'Yes'))[/code]
But, I guess this is wrong because I get an error
[quote]mysql_fetch_array(): supplied argument is not a valid MySQL result resource[/quote]
I have never attempted such a query and I am unsure of how to return all the information for each user

[hvle]

try your query like this, the one that has error:
SELECT * FROM table WHERE user_id in (SELECT user_id FROM table WHERE field_id = 3 AND value = 'Yes');

[weknowtheworld]

I think following sql query must fetch the result required :

$sql = "SELECT user_id FROM table WHERE field_id = 3 AND value =\"Yes\"";
$rs = mysql_query($sql);
$row = mysql_fetch_array($rs);
echo $row["user_id"];

[fenway]

Yeah, probably, but where is the colour coming from?

[SweetLou]

Thanks guys. With your help I was able to get the results I wanted. Everything is running well now. Though I'm sure the code I wrote could have been done better.

[mjlogan]

Side Note:

You field 'value' is a reserved word.

[SweetLou]

[quote author=mjlogan link=topic=121748.msg502138#msg502138 date=1168512540]
Side Note:

You field 'value' is a reserved word.
[/quote]Thanks, I knew that. I was just shortening the field names, data to make it easier to read. My actual field is not called "value".

[SweetLou]

Is there anyway to add an "order by" statement to the select statement? I want to order the name. I was thinking maybe something like [code]mysql_query("SELECT field_id, user_id, value FROM xdata_data WHERE user_id in (SELECT user_id FROM xdata_data WHERE (field_id = 20 AND value = 'Yes')ORDER BY (value WHERE (field_id = '1')))");[/code]
But I couldn't find anything about doing an ORDER BY using a field from another column.

[fenway]

I'm not sure what you're trying to order by.

[SweetLou]

In this example, I am trying to order by the name of the user. The user's name is always on a row that starts with the field_id=1. In my real database, it is field_12 = the last name of the user.
I am trying to create an address book from the data in a table, unfortunately, the table is not set up the way I would have done it, where each user is a row. I am able to create each entry, looks something like:

Steve Foley
12 River St.
Tampa, FL 30338

But I can't get them to show alphbetically. I was hoping to get them to sort alphabetically and be able to a LIKE statement so that people can easily find the address of the user.

[fenway]

You may need to wrap the entire thing as a subquery, and then order by the desired field... please post some sample data, though, as INSERT statements, so I can run some tests on my end.

[SweetLou]

Ok, thanks for any help you can give. I was thinking I might have to make a new table and update it regularly from the querry I have already, but if I can sort without doing that, I would be happier.
Here is the dump of my test site:
[code]-- phpMyAdmin SQL Dump
-- version 2.9.1.1-Debian-1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jan 13, 2007 at 11:52 AM
-- Server version: 5.0.30
-- PHP Version: 5.2.0-8
--
-- Database: `forum`
--

-- --------------------------------------------------------

--
-- Table structure for table `phpbb_xdata_data`
--

CREATE TABLE `phpbb_xdata_data` (
  `field_id` smallint(5) unsigned NOT NULL,
  `user_id` mediumint(8) unsigned NOT NULL,
  `xdata_value` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `phpbb_xdata_data`
--

INSERT INTO `phpbb_xdata_data` (`field_id`, `user_id`, `xdata_value`) VALUES
(10, 2, 'Kevin'),
(11, 2, 'McNamee'),
(12, 2, 'Hooker'),
(10, 4, 'Karl'),
(11, 4, 'Semple'),
(12, 4, 'Flanker'),
(10, 5, 'Karl'),
(11, 5, 'Semple'),
(12, 5, 'Prop'),
(13, 2, '216 223-8366'),
(14, 2, 'Sweet Lou'),
(15, 2, '216 345-1413'),
(16, 2, '3632 W. 152nd St. apt 302'),
(17, 2, 'Cleveland'),
(18, 2, 'OH'),
(19, 2, '44111'),
(20, 2, 'Yes'),
(10, 3, 'Kevin'),
(11, 3, 'McNamee'),
(14, 3, 'Sweet Lou'),
(12, 3, 'Hooker'),
(15, 3, '216 366-6213'),
(16, 3, '325663 W. 118th St.'),
(17, 3, 'Cleveland'),
(18, 3, 'OH'),
(19, 3, '44111'),
(20, 3, 'Yes'),
(10, 6, 'George'),
(11, 6, 'Barkley'),
(14, 6, 'The Fish'),
(12, 6, 'Scrumhalf'),
(13, 6, '311 434-2939'),
(16, 6, '132 Lorain Ave.'),
(17, 6, 'Cleveland'),
(18, 6, 'OH'),
(19, 6, '44111'),
(20, 6, 'Yes'),
(14, 4, 'Idiot'),
(13, 4, '382 482-8291'),
(16, 4, '820 Brookpark Ave.'),
(17, 4, 'Clevlenad'),
(18, 4, 'OH'),
(19, 4, '44320'),
(14, 5, 'Happy'),
(15, 5, '492 203-3829'),
(16, 5, '8200 Detroit Ave.'),
(17, 5, 'Lakewood'),
(18, 5, 'OH'),
(19, 5, '44121');
[/code]
I am presently getting the info with:
[code]$sql_list = mysql_query("SELECT field_id, user_id, xdata_value FROM phpbb_xdata_data WHERE user_id in (SELECT user_id FROM phpbb_xdata_data WHERE (field_id = 20 AND xdata_value = 'Yes'))");[/code]
This will be an address book of my rugby team. I am using phpBB2 with a mod that gets the extra information. When field_id = 20 is the row and the xdata_value="yes" then that member is a teammate of mine. I only want the actual team members to show in the address book, not the entire forum's members.

[fenway]

Yeah, that's going to be rather ugly -- you could use a group_concat() but that won't get your very far... and have the table "inverted" as name/value pairs makes it complicated, too.  Why is it stored this way?

[SweetLou]

I didn't make the mod to phpBB, so I have no idea why the author did it this way, like I said, I would have done it another way. I have no idea what you mean by having the table converted to a name/value pairs. Thanks for looking at it. I think my best bet is to get the data with the SELECT statement I have now, then put that info into a new table as each user_id is its own row. I will have to check if the user_id is present and if it is, not to insert a new record and run this script as a crontab once a day or so.
Thanks again, I appreciate you even taking the time to help.

[fenway]

Well, you could run a subquery for each piece to "fake" getting back one record per user, and then the order by would be easy.

[SweetLou]

I was having a bit of trouble, so I rewrote my script. I am now using nested SELECT statements. Though, I am sure this would be big hit on the server. What I did was knowing that each user will have his own user_id, I used the SELECT DISTINCT statement. This gets each user_id. I then have another SELECT that gets the data for each distinct user_id.

As you can see, there is a lot of looping here.
[code]$sql_distinct = mysql_query("SELECT DISTINCT user_id FROM phpbb_xdata_data");
while($dist = mysql_fetch_array( $sql_distinct )) {
$dist2 = $dist['user_id'];
echo $dist2."<br />";
$result0 = "";
$result1 = "";
$result2 = "";
$result3 = "";
$sql_user = mysql_query("SELECT field_id, user_id, xdata_value FROM phpbb_xdata_data WHERE user_id ='" . $dist2 ."'");
while($rover_user = mysql_fetch_array($sql_user)) {
if ($rover_user['field_id'] == 10) {
$result0 = $rover_user['xdata_value'];
}
if ($rover_user['field_id'] == 11) {
$result1 = $rover_user['xdata_value'];
}
if ($rover_user['field_id'] == 12) {
$result2 = $rover_user['xdata_value'];
}
if ($rover_user['field_id'] == 13) {
$result3 = $rover_user['xdata_value'];
}
if ($rover_user['field_id'] == 20) {
$i = "true";
$sql_id = $rover_user['user_id'];
$sql_email = mysql_query("SELECT user_email FROM phpbb_users WHERE (user_id = '" . $sql_id . "')");
while($row2 = mysql_fetch_array($sql_email))
{
    $user_email = $row2['user_email'];
}
}
if ($i == "true") {
echo "<div style=\"border:solid 2px #2f4f4f; margin:0 auto 1em auto; width:400px;\">";
echo "<div style=\"background-color:#526f35; margin:0 0 0.3em 0; font-size:110%;\">".$result0;
if (!empty($result1)){
echo " \"".$result1."\" ";
}
echo $result2."</div>";
echo "<div class=\"smaller\">" . $result3 . "</div>";
if (!empty($user_email)) {
echo "<div class=\"smaller\"><a href=\"mailto:".$user_email."\">" . $user_email . "</a></div>";
}
echo "</div>";
$i = "false";
}
}
}[/code]
This seems to work. It is still in its testing stage, so a bit of clean up is needed, security checks, etc. I'm not very good with SQL, I was wondering if there was a better way to write this. It just seems like a lot of queries.

[fenway]

Not really... even if you used subqueries, the DB would still be doing the same amount of work.