current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise
up vote 0 down vote favorite

This is the first time I will be using a MVC and i have read the documentation of code igniter.. but i cant seem to get this query right:

$score = $_POST['time'];
$sql = "SELECT COUNT(score) as c FROM highscores WHERE score < '$score' ORDER BY score ASC LIMIT 10";
$result = mysqli_query($con, $sql);
$count = mysqli_fetch_object($result)->c;
if(!is_null($count) && $count < 10) {
    echo 1;
}

and then my code igniter version:

public function insert_highscore($score) {
    $this->db->select('COUNT(score) as c')->from('highscores')->where('score < ' . $score);
    $query = $this->db->get();
    $count = $query->c;
    if(!is_null($count) && $count < 10) {
        echo 1;
    }
    return $count;
}

and i call that function like so:

public function index()
{
    $this->load->view('header');
    $this->load->model('highscores_model');
    $data = $this->highscores_model->get_highscores();
    $this->highscores_model->insert_highscore("00:00:01.11");
    foreach($data as $highscore){
        echo $highscore['name'];
        echo $highscore['score'];
    }
    $this->load->view('footer');
}

and getting this error

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':00:01.11' at line 3

SELECT COUNT(score) as c FROM (`highscores`) WHERE `score` < 00:00:01.11

Filename: /storage/websites/cambist_app/codeigniter/models/highscores_model.php

Line Number: 15

Any Help Greatly Appreciated

share|improve this question
    
What's not working? Getting undesired result from query or is query/function failing to return anything? – Hart CO Jun 9 '13 at 16:51
    
please see update – Dawid van der Hoven Jun 9 '13 at 16:55
    
hi just change your where method where('score < ' , $score) don't concat your string – umefarooq Jun 9 '13 at 17:02
up vote 1 down vote accepted

in your model method you are just running query but not fetching records just after your db->get() put row() or result method to get records against query row will get only single record result will get object of records as array also in you where i made little modification

public function insert_highscore($score) {
    $this->db->select('COUNT(score) as c')->from('highscores')->where('score < ' , $score);
    $query = $this->db->get()->row();
    $count = $query->c;
    if(!is_null($count) && $count < 10) {
        echo 1;
    }
    return $count;
}

you can use the count_all_results to get all records

 public function insert_highscore($score) {
        $this->db->where('score < ' , $score);
        $count = $this->db->count_all_results('highscores');
        if(!is_null($count) && $count < 10) {
            echo 1;
        }
        return $count;
    }
share|improve this answer
    
Error Number: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':00:01.11' at line 3 SELECT COUNT(*) AS numrows FROM (highscores) WHERE score < 00:00:01.11 Filename: /storage/websites/cambist_app/codeigniter/models/highscores_model.php Line Number: 15 – Dawid van der Hoven Jun 9 '13 at 17:00
    
hi just change your where method where('score < ' , $score) don't concat your string if you score column data type is varchar then you need to use score "00:00:01:11" – umefarooq Jun 9 '13 at 17:03
    
hey, seems to have done something.. thank you.. just have : '$score' in 'where clause' that now – Dawid van der Hoven Jun 9 '13 at 17:09
    
FIXED.. THANK YOU! – Dawid van der Hoven Jun 9 '13 at 17:10
    
great and your welcome – umefarooq Jun 9 '13 at 17:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.