Page 1 of 1

Combine different values

Posted: Wed Jul 29, 2009 11:51 am
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 !

Re: Combine different values

Posted: Wed Jul 29, 2009 1:18 pm
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.

Re: Combine different values

Posted: Wed Jul 29, 2009 1:28 pm
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?

Re: Combine different values

Posted: Wed Jul 29, 2009 2:53 pm
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?

Re: Combine different values

Posted: Wed Jul 29, 2009 2:57 pm
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?

Re: Combine different values

Posted: Wed Jul 29, 2009 4:02 pm
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

Re: Combine different values

Posted: Wed Jul 29, 2009 8:06 pm
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. :?

Re: Combine different values

Posted: Thu Jul 30, 2009 1:50 am
by bobb
I also don't know how to do it...


Can someone help me please? :)