CodeIgniter Forums

Full Version: Problem with query in mysql [Solved]
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

[eluser]gazza7364[/eluser]
Hi

I'm using the following query to count the number of registrations. across 2 tables, users and it_club.

SELECT user.user_id, user.s_name, user.f_name, user.year, it_club.date, it_club.room, it_club.id, COUNT( it_club.user_id )
FROM it_club
INNER JOIN user ON user.user_id = it_club.user_id
WHERE it_club.date
BETWEEN '2012-09-01'
AND '2013-03-07'
GROUP BY it_club.user_id


The query works find if I run it from phpmyadmin, but when using in codigniter I get the following message:-

FUNCTION school.COUNT does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual

school is the name of the database. The code I'm using in codeigniter is as follows:-

Code:
function query_Range2()
{
$start= '2013-03-01'; //$this->input->post('start');
$end = '2013-03-07';  //$this->input->post('end');
$today = date("Y-m-d ");

if ($start > $end){
echo "Check Your Dates";
return False;
}
if ($start == $end){
echo "Dates cannot be the same";
return False;
}
if ($end > $today){

echo "Dates cannot be in the future, please choose todays date or a date in the past";
return False;
}

$sql = "SELECT user.user_id, user.s_name, user.f_name, user.year, it_club.date, it_club.room, it_club.id, COUNT ( it_club.user_id )
       FROM it_club
       INNER JOIN user ON user.user_id = it_club.user_id
       WHERE it_club.date
       BETWEEN '$start'
       AND '$end'
       GROUP BY it_club.user_id
       ORDER BY `user`.`user_id` ASC";  
  $result['data'] = $this->it_model->lunch_id($sql);

  
$this->load->view('table', $result);  
  
  }

I've put some dates in $tart and $end for testing, these are usually sent from jquery from a date picker.

Thanks in advance

El Forum

[eluser]TheFuzzy0ne[/eluser]
That can't work in PHPMyAdmin, because it has a syntax error. You need to remove the space between COUNT and the opening bracket.

El Forum

[eluser]gazza7364[/eluser]
[quote author="TheFuzzy0ne" date="1363698608"]That can't work in PHPMyAdmin, because it has a syntax error. You need to remove the space between COUNT and the opening bracket.[/quote]

Hi

Hi I didn't have a space when I ran it in phpmyadmin, I must of got the space when copying it over. Thanks for pointing it out,