CODEWITHSUNDEEP
sqlcodeigniter
Rich Coy
Rich Coy

Reputation: 545

CodeIgniter SQL Building

I'm working on a "send my password" type functionality and either my sql is bad or my codeigniter is bad. The form validates just fine and the function in the model gets called but nothing comes back out. I have to check two tables for the submitted email address because we separated user and admin accounts into two tables.

Controller Code:

 public function send() {
    $this->load->library('form_validation');
    $this->form_validation->set_rules('email', 'Email', 'required|valid_email|trim');
    $this->form_validation->set_error_delimiters('<div class="alert alert-error">', '</div><br />');

    if ($this->form_validation->run()) {
        // Check For A Matching Email
        $this->load->model('signin_model');
        if ($this->signin_model('email_verify')) {

            // There Was A Match
            echo "THERE WAS A MATCH";
        } else {

            // No Match
            echo "NO MATCH";
        }

    } else {

        // Form Did Not Validate
        $this->load->view('signin/forgot');
    }
}

Model Code: 

public function email_verify() {
    $sql = "SELECT * FROM admin, staff WHERE staff_email = ? OR admin_email = ?";
    $email = $this->input->post('email');
    $query = $this->db->query($sql, $email, $email);

    if($query->num_rows() == 1) {
        return true;
    } else {
        return false;
    }
}

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 '' at line 1

SELECT * FROM admin, staff WHERE staff_email = '[email protected]' OR admin_email =

UPDATE: The fixed query I'm now using is:

$sql = "SELECT admin_id, first_name FROM admin WHERE admin_email = ? UNION SELECT staff_id, first_name FROM staff WHERE staff_email = ?";

Upvotes: 0

Views: 95

Answers (2)

gen_Eric
gen_Eric

Reputation: 227200

$this->signin_model is not a function. It's an object. To call a model function, you call the function as a property of the object:

$this->signin_model->email_verify()

EDIT: The 2nd parameter to $this->db->query should be an array.

$query = $this->db->query($sql, array($email, $email));

EDIT 2: If there is no link between the 2 tables, then a JOIN will not help you. You can try a UNION instead.

$sql = "SELECT * FROM staff WHERE staff_email = ? ".
    "UNION ALL SELECT * FROM admin WHERE admin_email = ?";

Upvotes: 1

viperfx
viperfx

Reputation: 327

 $query = $this->db->query($sql, array($email, $email));

Try that. Should fix the SQL error

Upvotes: 1

Related Questions