MySQL select--Need Help

I am not very good at mysql.

I have this query working:

SELECT player1 Player, round(sum(masterpoints),2) Total
FROM scores2014
GROUP BY Player
UNION
SELECT player2 Player, round(sum(masterpoints),2) Total
FROM scores2014
GROUP BY Player order by Total DESC

When I attempt to get it working in Codeigniter I use:

    $this->db->select(
"player1 Player, round(sum(masterpoints),2) Total
FROM scores2014
GROUP BY Player
UNION
SELECT player2 Player, round(sum(masterpoints),2) Total
FROM scores2014
GROUP BY Player order by Total DESC");

and get an error message saying the mysql is wrong.

Anyone see the error of may ways?

Thanks,

Bill

Since you're using UNION, you would most likely want to use $this->db->query() instead of $this->db->select().

When using $this->db->select(), you would normally only pass the SELECT portion of your SQL query into the select() method, then you would use other methods (e.g. from(), group_by(), join()) to handle the rest of your SQL. Since CI doesn't include a union() method in the database library, you would either have to rewrite the SQL as a join or pass the whole SQL statement to $this->db->query().

Bonfire

Practical CodeIgniter 3
CodeIgniter Testing Guide

Since you're using UNION, you would most likely want to use $this->db->query() instead of $this->db->select().

Thanks, I rewrote the code to us $this-db-select, but it still does not work.

The code now is:

Code:

$query = $this->db->query(
"SELECT player1 Player, round(sum(masterpoints),2) Total
FROM scores2014
UNION
SELECT player2 Player, round(sum(masterpoints),2) Total
FROM scores2014
GROUP BY Player order by Total DESC");

Any other suggestions to get the mysql working in Codeigniter?

Bill

"It does not work" is not a description of the problem! What is the error message you get?

"It does not work" is not a description of the problem! What is the error message you get?

Thanks, you are right, of course.  I went back to my code and found my problems and solved them.

Sometimes, a kick works.

Bill  

may be you're not switch to developers mode.

"It does not work" is not a description of the problem! What is the error message you get?

Thanks, you are right, of course.  I went back to my code and found my problems and solved them.

Sometimes, a kick works.

Bill  

Cool!