Combine different values

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
bobb
Forum Newbie
Posts: 18
Joined: Sun Jun 28, 2009 3:41 am

Combine different values

Post by bobb »

Hello guys,

I am having following problem: Right now, i take items out of my database with following query:

Code: Select all

 
<?
$query = "SELECT ".(isset($ssql) ? 'COUNT(value) AS value_count, SUM(count) AS count, value LIKE \'%?%\' AS is_complete, '.$ssql .' AS ' : 'SUM(count) AS count, ')."value, MAX(time) AS time FROM ".$_site['prefix'] . $_table[$_GET['p']]." WHERE ".(isset($subquery[1]) ? $subquery[1] : '1')." GROUP BY ".(isset($ssql) ? $ssql : 'value')." ORDER BY ".$order." ".$by." LIMIT ".(($thispage - 1) * $_config['globallimit']).",".$_config['globallimit'];
 
//...
 
while($row = $_DBC->sql_fetch_assoc($query)){
?>
 
And just as info, for p=boot

Code: Select all

 
<?
if($_GET['p'] == "boot"){
                        
    $ssql = "SUBSTRING_INDEX(value, '?', 1)"; 
                        
}
?>
 
Well, i also use this code for p=browser.
This code gives back items like:

Value Amount
Internet explorer 5.0 6
Internet explorer 6.0 3
Opera 6 2
Safari 2.0 1
Mozilla 5.0 5
Mozilla 6.0 6
...


I actually want to make a global overview, so like:

Internet explorer 9
Opera 6
Safari 1
Mozilla 11


Does anyone know how this is possible? And can I do this already in my query?
Thanks !
User avatar
omniuni
Forum Regular
Posts: 738
Joined: Tue Jul 15, 2008 10:50 pm
Location: Carolina, USA

Re: Combine different values

Post by omniuni »

What you actually need to do is look for the more specific parts of the browser string. Use "gecko" to identify Firefox and Mozilla, use "MSIE" to identify Internet Explorer, Um, KHTML should get Konqueror, and "Webkit" should get Safari.
bobb
Forum Newbie
Posts: 18
Joined: Sun Jun 28, 2009 3:41 am

Re: Combine different values

Post by bobb »

Ow but as you can see that part is already done, I put them in to a certain database system, so people can have detailed and global information.

Global is: Internet explorer, safari, ...
Detailed is: Internet explorer 5.0, Internet explorer 6.0, Safari 2.0,...


They are already in the database in the detailed form as you can see from my query, I just got to build a query that just cuts of the numbers, and makes the sum of all different Internet explorers, the sum of all Safari's, ...

You see?
User avatar
omniuni
Forum Regular
Posts: 738
Joined: Tue Jul 15, 2008 10:50 pm
Location: Carolina, USA

Re: Combine different values

Post by omniuni »

Ah, I see. I was wondering about that.

Why not just query for "WHERE LIKE 'Internet Explorer%' " etc.? Wouldn't that give you the result you're looking for?
bobb
Forum Newbie
Posts: 18
Joined: Sun Jun 28, 2009 3:41 am

Re: Combine different values

Post by bobb »

Mmm, yea, perhaps...

Is there a way to go through an amount of certain keywords?
For example:

Internet Explorer, Safari, Mozilla

And then to type

WHERE LIKE $keyword

only once, but that $keyword is a different browser every time?
bobb
Forum Newbie
Posts: 18
Joined: Sun Jun 28, 2009 3:41 am

Re: Combine different values

Post by bobb »

To make it more clear, I will explain the table structure:

The table 'browser' has following structure (just an example):

[value] [count]
65 2
67 3
68 6
65 1
66 5
68 9
69 3


In another general table 'value' are the meanings of the numbers:

[id] [value]
65 Internet explorer 5.0
66 Internet explorer 6.0
67 Safari 2.0
68 Safari 2.3
69 Mozilla
...


Now it is the intention that I will get the number of how many times Internet explorer is used, not considering the version of it!
So here it is:
Internet explorer: 2+1+5=8
Safari: 3+6+9=18
Mozilla, obiously, 3
User avatar
omniuni
Forum Regular
Posts: 738
Joined: Tue Jul 15, 2008 10:50 pm
Location: Carolina, USA

Re: Combine different values

Post by omniuni »

OH! This is getting into JOIN commands. I FAIL at JOIN's. Someome HELP!!!!

/hyperventilation

Yeah, I'm sorry, I know you need a JOIN, but I'm not sure which one or how to use it. :?
bobb
Forum Newbie
Posts: 18
Joined: Sun Jun 28, 2009 3:41 am

Re: Combine different values

Post by bobb »

I also don't know how to do it...


Can someone help me please? :)
Post Reply