How to execute this query? 1

[ERuiz]

I am trying to ececute this query on a php file but just can't get it to work. I'm a newbie, so please don't laugh at my attempt! hahahahaha

What I need is to get the total amount of fields from a table named JOS_THEMES, where VARIABLE = PROFILE_DOMICILE and VALUE = KMIA and VARIABLE = PROFILE_DIVISION and VALUE = APR

Here is my attempt at this:

	$query="SELECT COUNT( ID_MEMBER ) AS count_users"
	. "\n FROM smf_themes"
	. "\n WHERE variable = 'profile_domicile'"
	. "\n AND value = 'KMIA'"
	. "\n AND variable = 'profile_division'"
	. "\n AND value = 'APR'"
	;

Obviously, it's returning the count as "0", because I know that syntax is incorrect. Can anyone help me please?

 

[LazyJones]

Get rid of "\n"'s and try again...

 

[ERuiz]

Ok, tried that and still returns "0". Here is a small sample of table along with some data, so you can perhaps check it out and see how it would be best to accomplish what I am trying to do.

CREATE TABLE `smf_themes` (
  `ID_MEMBER` mediumint(8) NOT NULL default '0',
  `ID_THEME` tinyint(4) unsigned NOT NULL default '1',
  `variable` tinytext NOT NULL,
  `value` text NOT NULL,
  PRIMARY KEY  (`ID_THEME`,`ID_MEMBER`,`variable`(30)),
  KEY `ID_MEMBER` (`ID_MEMBER`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- 
-- Dumping data for table `smf_themes`
-- 


INSERT INTO `smf_themes` VALUES (1, 1, 'profile_biography', '');
INSERT INTO `smf_themes` VALUES (1, 1, 'profile_interests', 'God, aviation, computers and baseball.');
INSERT INTO `smf_themes` VALUES (1, 1, 'profile_occupation', 'Computer Technician');
INSERT INTO `smf_themes` VALUES (1, 1, 'profile_vatsim_pid', '810004');
INSERT INTO `smf_themes` VALUES (1, 1, 'profile_rank', 'Senior Captain');
INSERT INTO `smf_themes` VALUES (1, 1, 'profile_current_status', 'Active');
INSERT INTO `smf_themes` VALUES (1, 1, 'profile_domicile', 'KMIA');
INSERT INTO `smf_themes` VALUES (1, 1, 'profile_division', 'APR');
INSERT INTO `smf_themes` VALUES (1, 1, 'profile_previous_hours', '0');
INSERT INTO `smf_themes` VALUES (1, 1, 'profile_current_hours', '359.5');
INSERT INTO `smf_themes` VALUES (1, 1, 'profile_flight_1', 'AP6532 TJSJ-KMIA');
INSERT INTO `smf_themes` VALUES (1, 1, 'profile_flight_2', 'AP6533 KMIA-TJSJ');
INSERT INTO `smf_themes` VALUES (1, 1, 'profile_flight_3', 'AP6532 TJSJ-KMIA');
INSERT INTO `smf_themes` VALUES (1, 1, 'profile_flight_4', 'AP6533 KMIA-TJSJ');
INSERT INTO `smf_themes` VALUES (1, 1, 'profile_flight_5', 'AP6532 TJSJ-KMIA');
INSERT INTO `smf_themes` VALUES (1, 1, 'profile_flight_6', 'AP6533 KMIA-TJSJ');
INSERT INTO `smf_themes` VALUES (1, 1, 'profile_flight_7', 'AP6532 TJSJ-KMIA');
INSERT INTO `smf_themes` VALUES (1, 1, 'profile_flight_8', 'AP6533 KMIA-TJSJ');
INSERT INTO `smf_themes` VALUES (1, 1, 'profile_flight_9', 'AP6532 TJSJ-KMIA');
INSERT INTO `smf_themes` VALUES (1, 1, 'profile_flight_10', 'AP6533 KMIA-TJSJ');
INSERT INTO `smf_themes` VALUES (3, 1, 'profile_biography', '');

And here is the query I am using:

	$query="SELECT COUNT( ID_MEMBER ) AS count_users FROM smf_themes WHERE variable = 'profile_domicile' AND variable = 'profile_division' AND value = 'KMIA' AND value = 'APR' ";
	$database->setQuery($query);
	echo "Pilots:</td><td width=\"120\"><b>" .$database->loadResult() . "</b></td>\n";

The final result should be "1

 

[LazyJones]

variable = 'profile_domicile' AND variable = 'profile_division'

There's your problem. 'variable' can't be 'profile_domicile' and 'profile_division' at the same time, same goes with 'value'

use OR

 

[LazyJones]

Well, sorry, that won't get the result you are after...
You should follow at least the basic rules of database normalization. You should have separate table for variable where the values are stored with the memberID.

 

[KarveR]

mysql> SELECT COUNT( ID_MEMBER ) AS count_users FROM smf_themes WHERE variable IN ('profile_domicile', 'profile_division') AND value IN ('KMIA','APR'); +-------------+
| count_users |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)


the final result is 2 if you use this query, becuase 2 rows match. In your query nothing would ever match.

You need to be more or less specific about what you need.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[KarveR]

oh this should also be in the mysql forum ->

http://tek-tips.com/threadminder.cfm?pid=436

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[ERuiz]

Ok, that query is not giving the result I am looking for.
The problem is the way the database fields are arranged, it makes it extremely difficult to perform these queries. Intead of recored being arranges by rows, they are arranaged by columns... it's as if you take a regular database and rotate it 90 degrees. Now you have what should be all your field headers, arranged into 1 column and what should be all your row values, arranged into 1 column also.

 

[DRJ478]

I read through the initial post and cannot make much sense out of what you want the query to do.
Let's see if I understand what you want:
You want the number of users that have profile_domicile = KMIA and profile_division = APR ?

What version of MySQL do you use? That might make a big difference. Let us know.

 

[ERuiz]

Hi DRJ478,

That is exactly what I desperately need.

Here is the info you requested:

PHP 4.4.1
MySQL 4.1.13

thanks for your help, my friend. I'm on my Treo 650, so I will be monitoring this on the road! Gotta love these cel phones! hahahah

 

[DRJ478]

Ok, here's a solution.
Sometimes folks overlook that you can join tables, which also means you can join a table to itself. Since both records have the same ID_MEMBER we can then go ahead and state that we need the combination of the first key=value pair in the first record and the second condition in the joined record.
Here's the SQL:

SELECT count(a.id_member)  FROM `smf_themes` AS a LEFT JOIN smf_themes AS b  ON a.id_member = b.id_member WHERE a.variable='profile_domicile' AND a.value='KMIA' AND b.variable='profile_division' AND b.value='APR'

 

[LazyJones]

Desing in that table is straight from hell...

May you should alter your query like this

SELECT DISTINCT ID_MEMBER AS id

and then count the returned id's in your code

 

[ERuiz]

ok, I will try it out in a couple of hours when I get home. Thanks!

 

[KarveR]

cant see the point of counting something you know is going to be 1, surely better grouping is easier than joining tables, but without knowing exactly what results are expected it gets a little tricky.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[ERuiz]

karver, what I meant by saying that the result would be 1, I was referring to the piece of code I posted here. I didn't want to post here all 700+ rows that this table has.

 

[KarveR]

granted, but grouping by id would give the required 1 which would be appropriate for the result, what I meant was could you give an example of a query were the result would be more than 1? this way it gives a cleaerer definition of how you want to achieve results (oh dear I'm starting to sound like a consultant :-()..

What I posted earlier is accurate, but counting the result gives 2, rather than 1 as only 1 id has both of those criteria, as grouping by id would only return 1 as you requested.


______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[ERuiz]

DRJ478, your query WORKED LIKE A CHARM!!! WOW! I thought it would never be accomplished! I must say that this forum is simply INCREDIBLE! I have had a couple of problems resolved here where they were not been able to anywhere else. Amazing. Thanks a million to you and EVERYONE who chipped in, you guys are great!

 

[ERuiz]

Ok, I have yet another question! Using the query supplied by DRJ478:

SELECT count(a.id_member)  FROM `smf_themes` AS a LEFT JOIN smf_themes AS b  ON a.id_member = b.id_member WHERE a.variable='profile_domicile' AND a.value='KMIA' AND b.variable='profile_division' AND b.value='APR'

How would I edit this query to include another parameter? For this new query, I need to have included:

variable = 'profile_rank'
value = 'Trainee'

 

[ERuiz]

To make it a bit clearer, now I need the query to give me the total amount of users that have

profile_domicile = KMIA
profile_division = APR
profile_rank = Trainee

Hope this is a bit clearer.

 

[DRJ478]

You need to add another JOIN of the table to itself and specfy the condition in the where clause.
That's it.