• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Site Query Error

#1
Hello Everyone i got this error in my site.

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 'WHERE hall_review.status = 0 ORDER BY hall_review.hall_id' at line 1
SELECT hall_review.id,hall.name,hall_review.review,hall_review.review_by,hall_review.rating FROM hall_review JOIN hall ON hall.id = hall_review.hall_id JOIN accounts ON hall.created_by = accounts.accountId AND accounts.accountId = WHERE hall_review.status = 0 ORDER BY hall_review.hall_id
Line Number: 441





Model:
Code:
<?php

class Query extends CI_Model {

    function __construct() {
        parent::__construct();
        $this->load->database();
        $this->load->library('session');
    }

    public function login($Email, $PasswordEncrypted) {
        $this->db->select('*');
        $this->db->from('accounts');
        $this->db->where('email', $Email);
        $this->db->where('password', $PasswordEncrypted);
        $this->db->limit(1);
        return $query = $this->db->get();
    }

    public function delete_image($table, $Id, $KeyID) {
        $this->db->where($KeyID, $Id);
        $query = $this->db->delete($table);
        return $query;
    }

    /* public function insert_car_system($data){

      $out_params = array(
      '@out_insert_id' => NULL
      );
      $query =  $this->db->query("call car_system(".$data['stationId'].','.$data['makeId'].','.$data['modelId'].','.$data['variantId'].")");
      //$this->db->last_query();
      //return $query->result();
      $var['query']       = $query;
      return $var;

      } */

    public function get_model($make) {
        $sql = 'SELECT * FROM model WHERE status= 1 and makeId = "' . $make . '" ;';
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function get_variant($make, $model) {
        $sql = 'SELECT * FROM variant WHERE status= 1 and makeId = "' . $make . '" and modelId = "' . $model . '" ;';
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function query($query) {
        $query = $this->db->query($query);
        return $query->result();
    }

    public function insert_query($table, $data) {
        $query = $this->db->insert($table, $data);
        $var['CreatedID'] = $this->db->insert_id();
        $var['query'] = $query;
        return $var;
    }

    public function update_query($table, $data, $key, $id) {
        $this->db->where($key, $id);
        $query = $this->db->update($table, $data);
        return $query;
    }

    public function status($table, $data, $key, $id, $ac) {
        $this->db->where($key, (int) base64_decode($id));
        $query = $this->db->update($table, $data);
        return $query;
    }

    public function delete($table, $key, $Id) {
        $this->db->where($key, (int) base64_decode($Id));
        $query = $this->db->delete($table);
        return $query;
    }

    public function get_inventory() {
        $sql = 'SELECT count(*) as count FROM car JOIN stock ON stock.carId = car.carId WHERE stock.sold = 0 AND status= 1';
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function get_inventory_req() {
        $sql = 'SELECT count(*) as count FROM car JOIN stock ON stock.carId = car.carId WHERE stock.sold = 0 AND status= 0';
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function get_certified() {
        $sql = "SELECT count(*) as count FROM car JOIN stock ON stock.carId = car.carId WHERE stock.sold = 0 AND status= 1 and car_Type = 'Certified' ";
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function get_noncertified() {
        $sql = "SELECT count(*) as count FROM car JOIN stock ON stock.carId = car.carId WHERE stock.sold = 0 AND status= 1 and car_Type = 'Non-Certified' ";
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function get_wholesale() {
        $sql = "SELECT count(*) as count FROM car JOIN stock ON stock.carId = car.carId WHERE stock.sold = 0 AND status= 1 and car_Type = 'Wholesale' ";
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function get_featured() {
        $sql = "SELECT count(*) as count FROM stock JOIN car ON car.carId = stock.carId WHERE stock.sold = 0 AND status= 1 and featured = 2 ";
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function get_featured_req() {
        $sql = "SELECT count(*) as count FROM stock JOIN car ON car.carId = stock.carId WHERE stock.sold = 0 AND status= 1 and featured = 1 ";
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function get_special() {
        $sql = "SELECT count(*) as count FROM stock JOIN car ON car.carId = stock.carId WHERE stock.sold = 0 AND status= 1 and specialOffer = 2 ";
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function get_special_req() {
        $sql = "SELECT count(*) as count FROM stock JOIN car ON car.carId = stock.carId WHERE stock.sold = 0 AND status= 1 and specialOffer = 1 ";
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function get_inventory_dealer($id) {
        $sql = "SELECT count(*) as count FROM car JOIN stock ON stock.carId = car.carId WHERE stock.sold = 0 AND status= 1 and accountId_created = '$id' ";

        $query = $this->db->query($sql);
        return $query->result();
    }

    public function get_inventory_req_dealer($id) {
        $sql = "SELECT count(*) as count FROM car JOIN stock ON stock.carId = car.carId WHERE stock.sold = 0 AND status= 0 and accountId_created = '$id'";
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function get_certified_dealer($id) {
        $sql = "SELECT count(*) as count FROM car JOIN stock ON stock.carId = car.carId WHERE stock.sold = 0 AND status= 1 and car_Type = 'Certified' and accountId_created = $id ";
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function get_noncertified_dealer($id) {
        $sql = "SELECT count(*) as count FROM car JOIN stock ON stock.carId = car.carId WHERE stock.sold = 0 AND status= 1 and car_Type = 'Non-Certified' and accountId_created = $id ";
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function get_wholesale_dealer($id) {
        $sql = "SELECT count(*) as count FROM car JOIN stock ON stock.carId = car.carId WHERE stock.sold = 0 AND status= 1 and car_Type = 'Wholesale' and accountId_created = $id ";
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function get_featured_dealer($id) {
        $sql = "SELECT count(*) as count FROM stock JOIN car ON car.carId = stock.carId WHERE stock.sold = 0 AND status= 1 and featured = 2 and accountId_created = $id ";
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function get_featured_req_dealer($id) {
        $sql = "SELECT count(*) as count FROM stock JOIN car ON car.carId = stock.carId WHERE stock.sold = 0 AND status= 1 and featured = 1 and accountId_created = $id ";
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function get_special_dealer($id) {
        $sql = "SELECT count(*) as count FROM stock JOIN car ON car.carId = stock.carId WHERE stock.sold = 0 AND status= 1 and specialOffer = 2 and accountId_created = $id ";
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function get_special_req_dealer($id) {
        $sql = "SELECT count(*) as count FROM stock JOIN car ON car.carId = stock.carId WHERE stock.sold = 0 AND status= 1 and specialOffer = 1 and accountId_created = $id ";
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function get_dealers() {
        $sql = "SELECT * FROM accounts WHERE status= 1 and account_type = 4 ";
        $query = $this->db->query($sql);
        return $query->result();
    }

    function get_dealer_station($id) {
        $sql = "SELECT * FROM station WHERE status= 1 and accountId = '$id' ";
        $query = $this->db->query($sql);
        return $query->result();
    }

    function get_count_user() {
        $sql = "SELECT COUNT(*) as exchange FROM inquiries WHERE inquiry_type = 'Sell' AND userID <> 0
            UNION ALL
            SELECT COUNT(*) as sell FROM inquiries WHERE inquiry_type = 'Exchange' AND userID <> 0
            UNION ALL
            SELECT COUNT(*) as purchase FROM inquiries WHERE inquiry_type = 'stock_purchase' AND userID <> 0
            UNION ALL
            SELECT COUNT(*) as stock_exchange FROM inquiries WHERE inquiry_type = 'stock_exchange' AND userID <> 0";

        $query = $this->db->query($sql);
        return $query->result();
    }

    function get_count_guest() {
        $sql = "SELECT COUNT(*) as exchange FROM inquiries WHERE inquiry_type = 'Sell' AND userID = 0
            UNION ALL
            SELECT COUNT(*) as sell FROM inquiries WHERE inquiry_type = 'Exchange' AND userID = 0
            UNION ALL
            SELECT COUNT(*) as purchase FROM inquiries WHERE inquiry_type = 'stock_purchase' AND userID = 0
            UNION ALL
            SELECT COUNT(*) as stock_exchange FROM inquiries WHERE inquiry_type = 'stock_exchange' AND userID = 0";

        $query = $this->db->query($sql);
        return $query->result();
    }

    function get_station_record($id, $start_date, $end_date) {

        $sql = "SELECT COUNT(*) as exchange FROM inquiries WHERE inquiry_type = 'Sell' AND inquiries.stationId = '$id' AND userID <> 0 AND created_date BETWEEN '$start_date' AND '$end_date'
            UNION ALL
            SELECT COUNT(*) as sell FROM inquiries WHERE inquiry_type = 'Exchange' AND inquiries.stationId = '$id' AND userID <> 0 AND created_date BETWEEN '$start_date' AND '$end_date'
            UNION ALL
            SELECT COUNT(*) as purchase FROM inquiries WHERE inquiry_type = 'stock_purchase' AND inquiries.stationId = '$id' AND userID <> 0 AND created_date BETWEEN '$start_date' AND '$end_date'
            UNION ALL
            SELECT COUNT(*) as stock_exchange FROM inquiries WHERE inquiry_type = 'stock_exchange' AND inquiries.stationId = '$id' AND userID <> 0 AND created_date BETWEEN '$start_date' AND '$end_date'";

        $query = $this->db->query($sql);
        return $query->result();
    }

    function get_station_record_guest($id, $start_date, $end_date) {
        $sql = "SELECT COUNT(*) as exchange FROM inquiries WHERE inquiry_type = 'Sell' AND inquiries.stationId = '$id' AND userID = 0 AND created_date BETWEEN '$start_date' AND '$end_date'
            UNION ALL
            SELECT COUNT(*) as sell FROM inquiries WHERE inquiry_type = 'Exchange' AND inquiries.stationId = '$id' AND userID = 0 AND created_date BETWEEN '$start_date' AND '$end_date'
            UNION ALL
            SELECT COUNT(*) as purchase FROM inquiries WHERE inquiry_type = 'stock_purchase' AND inquiries.stationId = '$id' AND userID = 0 AND created_date BETWEEN '$start_date' AND '$end_date'
            UNION ALL
            SELECT COUNT(*) as stock_exchange FROM inquiries WHERE inquiry_type = 'stock_exchange' AND inquiries.stationId = '$id' AND userID = 0 AND created_date BETWEEN '$start_date' AND '$end_date'";

        $query = $this->db->query($sql);
        return $query->result();
    }

    function get_all_inquiries_user($id) {
        $sql = "SELECT inquiries.id,accounts.name as user,account_details.phone,cities.name,accounts.email,inquiries.created_date,"
                . "inquiries.inquiry_type,inquiries.carId,c1.shortCode  from inquiries JOIN accounts ON inquiries.userID = accounts.accountId "
                . "JOIN account_details ON accounts.accountId = account_details.accountId JOIN "
                . "cities ON cities.cityId = account_details.city "
                . "LEFT JOIN station ON station.stationId = '$id'
                        LEFT JOIN cities as c1 ON station.city = c1.cityId"
                . " WHERE inquiries.userID <> 0 AND inquiries.stationId = '$id'";
        $query = $this->db->query($sql);
        return $query->result();
    }

    function get_all_inquiries_guest($id) {
        $sql = "SELECT * from inquiries 
            LEFT JOIN station ON station.stationId = '$id'
            LEFT JOIN cities ON station.city = cities.cityId
            WHERE inquiries.userID = 0 AND inquiries.stationId = '$id'";
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function get_dealers_stations($id) {
        $sql = "SELECT * FROM station WHERE status= 1 and accountId = '$id' ";
        $query = $this->db->query($sql);
        return $query->result();
    }

    

    function check_username($username) {
        $query = $this->db->query("SELECT count(*) as count FROM accounts where username = '" . $username . "'");
        return $query->result();
    }

    function check_station($username) {
        $query = $this->db->query("SELECT count(*) as count FROM station where name = '" . $username . "'");
        return $query->result();
    }

    public function do_edit($table, $data, $email) {
        $this->db->where('email', $email);
        $query = $this->db->update($table, $data);
        return $query;
    }
    
    
    
    
    
    
    
    
    
    //////////////////// salman queries /////////////////////
    
    

    public function get_account_type() {
        $sql = "SELECT * FROM account_type WHERE status = 1";
        $result = $this->db->query($sql);
        return $result->result();
    }

    public function check_email($check) {
        $sql = "SELECT * FROM accounts WHERE email = '$check' and status = 1";
        $result = $this->db->query($sql);
        return $result->result();
    }

    public function get_all_account() {
        $sql = "SELECT accounts.status,accounts.name,accounts.email,accounts.accountId,account_type.name as account_type FROM `accounts` join account_type on account_type.typeId = accounts.account_type order by accounts.accountId DESC";
        $result = $this->db->query($sql);
        return $result->result();
    }

    public function get_user_detail($id) {
        $sql = "SELECT * FROM `accounts` join account_details on account_details.accountId = accounts.accountId WHERE accounts.accountId = $id";
        $result = $this->db->query($sql);
        return $result->result();
    }
    
    public function check_category($check) {
        $sql = "SELECT * FROM category WHERE title = '$check' and status = 1";
        $result = $this->db->query($sql);
        return $result->result();
    }
    
    public function get_all_categories() {
        $sql = "SELECT * FROM `category` order by categoryId DESC";
        $result = $this->db->query($sql);
        return $result->result();
    }
    
    public function get_all_active_categories() {
        $sql = "SELECT * FROM `category` WHERE status = 1 order by categoryId DESC";
        $result = $this->db->query($sql);
        return $result->result();
    }

    public function get_cat_detail($id) {
        $sql = "SELECT * FROM `category` WHERE categoryId = $id";
        $result = $this->db->query($sql);
        return $result->result();
    }
    
public function get_catering_detail($id) {
        $sql = "SELECT * FROM `hall` WHERE id = $id";
        $result = $this->db->query($sql);
        return $result->result();
    }

public function get_catering_detail_images($id) {
        $sql = "SELECT * FROM `hall_pictures` WHERE hall_id = $id";
        $result = $this->db->query($sql);
        return $result->result();
    }

    public function get_pro_detail($id) {
        $sql = "SELECT * FROM `product` WHERE proID = $id";
        $result = $this->db->query($sql);
        return $result->result();
    }
    
    public function get_pro_detail_images($id) {
        $sql = "SELECT * FROM `product_images` WHERE product_id = $id";
        $result = $this->db->query($sql);
        return $result->result();
    }
    
    public function get_all_products() {
       $sql = "SELECT * FROM hall WHERE data_type =  0 ORDER BY id DESC";
       $result = $this->db->query($sql);
        return $result->result();
    }
    
    public function get_all_products_users($id) {
        $sql = "SELECT product.*,category.title as cat_name FROM product JOIN category ON category.categoryId = product.cat_id WHERE product.accountId_created = $id  ORDER BY product.proID DESC";
        $result = $this->db->query($sql);
        return $result->result();
    }

    public function get_all_catering() {
       $sql = "SELECT * FROM hall WHERE data_type =  1 ORDER BY id DESC";
       $result = $this->db->query($sql);
        return $result->result();
    }

public function get_all_deals() {
       $sql = "SELECT * FROM hall WHERE data_type =  2 ORDER BY id DESC";
       $result = $this->db->query($sql);
        return $result->result();
    }

    public function get_deals_detail($id) {
        $sql = "SELECT * FROM `hall` WHERE id = $id";
        $result = $this->db->query($sql);
        return $result->result();
    }
public function get_deals_detail_images($id) {
        $sql = "SELECT * FROM `hall_pictures` WHERE hall_id = $id";
        $result = $this->db->query($sql);
        return $result->result();
    }

    public function get_all_catering_users($id) {
        $sql = "SELECT product.*,category.title as cat_name FROM product JOIN category ON category.categoryId = product.cat_id WHERE product.accountId_created = $id  ORDER BY product.proID DESC";
        $result = $this->db->query($sql);
        return $result->result();
    }
    
    public function get_account_payment_setting($id) {
        $sql = "SELECT * FROM payment_setting WHERE payment_setting.accountId_created = $id ";
        $result = $this->db->query($sql);
        return $result->result();
    }

    
   



    public function get_hall_reviews() {
        
        $id = $this->session->userdata('user_id');
        
        $sql = "SELECT hall_review.id,hall.name,hall_review.review,hall_review.review_by,hall_review.rating FROM hall_review JOIN hall ON hall.id = hall_review.hall_id JOIN accounts ON hall.created_by = accounts.accountId AND accounts.accountId = $id WHERE hall_review.status = 0 ORDER BY hall_review.hall_id";
        $result = $this->db->query($sql);
        return $result->result();
    }




 public function get_user_info($id) {
        $sql = "SELECT accounts.name,account_details.* FROM accounts JOIN account_details ON account_details.accountId = accounts.accountId WHERE accounts.accountId = $id";
        $result = $this->db->query($sql);
        return $result->result();
    }



 public function get_halls() {
        $sql = "SELECT * FROM hall ";
        $result = $this->db->query($sql);
        return $result->result();
    }







}

?>
Reply

#2
Rewrite your query like this:

PHP Code:
$sql "SELECT hall_review.id,hall.name,hall_review.review,hall_review.review_by,hall_review.rating FROM hall_review JOIN hall ON hall.id = hall_review.hall_id JOIN accounts ON hall.created_by = accounts.accountId WHERE accounts.accountId = $id AND hall_review.status = 0 ORDER BY hall_review.hall_id"

Or use CI's query builder class:
PHP Code:
$this->db
->select('hr.id,hr.name,hr.review,hr.review_by,hr.review_rating')
->
from('hall_review hr')
->
join('hall h','h.id = hr.id')
->
join('accounts a','h.created_by = a.accountId')
->
where('a.accountId',$id)
->
where('hr.status',0)
->
order_by('hr.hall_id');
$query $this->db->get();
if (
$query->num_rows() == 0) return FALSE;
return (
$query->result(); 
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.