April 29, 2012

mysql join query with sum retuns null

Question by dr.linux

If makale_payments table is null the query returns null. How shoul i set the query yo get recors if doesnt exist in SUM(payment_total) like 0.00 ?

SELECT article_name,user_real_name,user_name,SUM(`article_payment`) as holding,paid
FROM makale_articles AS t1
JOIN makale_users ON (t1.article_name=makale_users.user_id) 
JOIN (SELECT user_id,SUM(`payment_total`) as paid FROM makale_payments GROUP BY user_id) AS t2 ON (t1.article_name=t2.user_id)
GROUP BY t2.user_id

Answer by user582118

MySQL does not return aggregate values for rows that return nulls, even if specifically testing for ISNULL. ISNULL is only to check if a column is null, not if the relation returns null. I did however find a (albeit very obscure) way to get around this limitation. The basic premise is to union all two sql statements and use the two unioned query as a subquery to select from.

The unioned queryies would be of the form:

   select column1, column2, SUM(column3) as payment from tablea, tableb where....
   union all
   select column1, column2, 0.00 as payment from tablea --notice no join, and a specified value and alias for aggregate column

With the above query, you will get two rows for every row that the relation is not null, and one row when it is. If you sum that rows and group by, then you should end up with the values you want. So you then create a query that uses the above as the table to select from and sums the aggregate column:

   select column1, column2, SUM(payment) from

   (select column1, column2, SUM(column3) as payment from tablea, tableb where....
   union all
   select column1, column2, 0.00 as payment from tablea) as b
   group by column1, column2

I am probably off on the syntax of the query, sorry, I don’t have MySQL here to test.

Answer by Starx

A simple WHERE wouldn’t have hurt I guess

WHERE SUM(`article_payment`) > 0
April 24, 2012

What's wrong? I am receiving error msg: Invalid arguments passed in eval()

Question by user6919

I write down my php code:

<?php
        // 已有指定 material, 顯示 material 資訊
        if (strlen($m_id) > 0) {
            // 此 material 屬於哪些 mgroup
            $group_info = $mUtil->groupInfo($m_id);
            $group_names = array();
            foreach ($group_info as $mg_id => $row) {
                if (!$row["not_in_group"]) {
                    $group_names[] = $row["mg_name"];
                }
            }
        }
    ?>

  <table width="100%">
    <tr>
      <th colspan="2"><?php echo $m_name; ?></th>
    </tr>
    <tr class="odd">
      <th>Formula</th>
      <td width="80%"><?php echo $formula; ?></td>
    </tr>
    <tr class="odd">
      <th>Alias</th>
      <td><?php echo $alias; ?></td>
    </tr>
    <tr class="odd">
      <th>In groups</th>
      <!-- join() == implode() -->
      <td><?php echo join($group_names, ",&nbsp; "); ?></td>
    </tr>
  </table><br /><br />

but I get these error message:

Notice: Undefined variable: group_names in eval() (line 97 of D:xampphtdocsdrupalmodulesphpphp.module(80) : eval()'d code).
Warning: join() [function.join]: Invalid arguments passed in eval() (line 97 of D:xampphtdocsdrupalmodulesphpphp.module(80) : eval()'d code).
Notice: Undefined variable: group_names in eval() (line 97 of D:xampphtdocsdrupalmodulesphpphp.module(80) : eval()'d code).
Warning: join() [function.join]: Invalid arguments passed in eval() (line 97 of D:xampphtdocsdrupalmodulesphpphp.module(80) : eval()'d code).

Anyone can help me? Thanks a lot….

Answer by Starx

The undefined errors are due to variables like $group_names only being defined once

if (strlen($m_id) > 0) { ... } //condition is true.

Make sure the variable you are using are instantiated before using them.

Using isset($instancename) can be one of the ways to deal with this.

Example:

if(!isset($group_names)) $group_names = array();
// ^ if $group_names is not found then at least initialize it as an empty array
//   so that the rest of the script can go easy

Also, join() needs a glue to join the array, which is not in correct order.

<?php echo join(", ", $group_names); ?>

NOTE: The use of eval() has to be however be discouraged

April 8, 2012

Mysql query and foreach query with double items

Question by ciprian

I am just grabbing user data from a few tables but users have the option of adding more than one skill or exp.

$query_str = "SELECT a.*, b.*, c.*, d.*, e.* FROM edu a
              JOIN exp b ON a.user_id=b.user_id
              JOIN user_profiles c ON a.user_id=c.user_id
              JOIN skills d ON a.user_id=d.user_id
              JOIN comp e ON a.user_id=e.user_id
              WHERE a.user_id = ?";

$query = $this->db->query($query_str, $end_user);
            if($query->num_rows() > 0) {
                    foreach($query->result_array() as $stuff) {
                            $data[] = $stuff;
                    }
                    return $data;
                    } else {
            return false;
                    }

Everything is fine until I try to display the data. If a user has two exp, everything else is showing up twice. I m not sure how to write this. Would it be easier to do separate them? One query for each item?

public function get_education()
    {
           $one_edu = $this->test_model->one_edu($end_user);
            if ($one_edu != false)
            {
                    foreach($one_edu as $edas) {
                            $one_edu_html .='<p>'.$edas['objective'].'</p>';
                    }

                    foreach($one_edu as $exp) {
                            $one_edu_html .= '<p>'.$exp['exp_title'].'</p>';

                    }

                    foreach($one_edu as $educ) {
                            $one_edu_html .= '<p>'.$educ['edu_title'].'</p>';
                    }

                    $result = array('status' => 'ok', 'content' => $one_edu_html);
                    echo json_encode($result);
                    exit();
            }else{
                    $result = array('status' => 'ok', 'content' => '');
                    echo json_encode($result);
                    exit();
            }
    }

Now it s returning something like this:

Objective
Exp title1
Exp title2
Edu title
Edu title <- Extra

Using codeigniter

Answer by Starx

The main reason for this is because you haven’t grouped your rows. Add a grouper, like GROUP BY a.id at the end


Update

The rows are duplicating because they are different, you can group the field on a single rows, using GROUP_CONCAT

SELECT a.user_id, a.education, GROUP_CONCAT(a.edu_title SEPARATOR ",") "Edu_Title", GROUP_CONCAT(b.exp_title SEPARATOR ",") "Experience Title" ,b.experience, c.objective, d.skill, e.comp FROM edu a
JOIN exp b ON a.user_id=b.user_id
JOIN user_profiles c ON a.user_id=c.user_id
JOIN skills d ON a.user_id=d.user_id
JOIN comp e ON a.user_id=e.user_id
WHERE a.user_id = 243;

Demo

...

Please fill the form - I will response as fast as I can!