problem in fetching from complicated mysql table

I'm using an if clause to fetch the value of my mysql table data because my table schema is not normal.

now for getting these values, I wrote the below code:

 $result = $db->sql_que开发者_如何学JAVAry("SELECT type, var, count from table_counter");
    while ($row =  $db->sql_fetchrow($result)) {
    $type = $row['type'];
    $var =  $row['var'];
    $count = intval($row['count']);
        if(($type == "total") && ($var == "visits")) {
            $totalVisits= $count;
        }elseif(($type == "total") && ($var == "pageviews")) {
            $totalPVisits= $count;
        }
     }

Is there any other way rather than using an if clause?!

SELECT `type`, `var`, SUM(`count`) AS `sum`
FROM table_counter
GROUP BY `type`,`var`;

There's no need for that intval() to convert the count field to integer. It's already defined as an integer in your MySQL table.

What you have here is some kind of EAV data structure and the if is a common way to fetch from it. There are other ways of pivoting them.

What are you aiming to do with the data? Just create the counts?

You could extract the data in more than one query.

Another option is to put the data into an array, then sort it using PHP array sort functions. It could then be split it into two separate arrays, if needed.

You will need to pivot the data. And that will require you to write some code to parse out what you need in php and mysql. This will can get heavy depending on how much data you want and how it's stored and how it needs to be retrieved. Based on your question text it looks you are storing very simple data, but for every type and set of data you are storing you will need to create logic for it.

Soooo, if you know exactly what the variables are going to be you could set up and array of the variable names you want set and loop through them.

$legal_values = array('visits', 'pageviews', etc...);
while ($row = $db->sql_fetchrow($result)) {
    if (in_array($row['var'], $legal_values)) {
        $$row['var'] = $intval($row['count'];
    }
}

This is just a simple example and only works if all the types are count. Obviously if you want to filter it down you will need more logic. There isn't really a catch-all solution for this stuff, so it comes down to writing something that is simple so you understand it and you can easily extend it to handle more data that you might need later.

${$row['type'] . ucfirst($row['var'])} = $row['count'];

That reads total pageviews 107 to $totalPageviews = 107. Depending on where you want to use this, this may be a possibility.