CodeIgniter SQL Building

Question

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 = ?";

Is there any link between the admin and staff tables? If there is, you need to add that to your WHERE. If not, you need to either use 2 queries or a UNION. — gen_Eric
– gen_Eric, Commented Nov 16, 2012 at 16:06
There is no link between them. I'll look into the UNION option. — Rich Coy
– Rich Coy, Commented Nov 16, 2012 at 16:07

gen_Eric · Accepted Answer · 2012-11-16 16:09:31Z

1

$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 = ?";

edited Nov 16, 2012 at 16:09

answered Nov 16, 2012 at 15:45

gen_Eric

228k42 gold badges304 silver badges343 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Rich Coy Over a year ago

Thanks. Feel pretty dumb about missing that. I now get a SQL error though that I think relates to the admin_email not being a field in the staff table. Do I need to break this into two queries or is there a better way to form this one query?

gen_Eric Over a year ago

What's the SQL error? You should be able to do it on one query.

gen_Eric Over a year ago

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

Rich Coy Over a year ago

Thanks. I got the join to work. I edited the initial question to the working code. I had to specify what to pull since the two tables have a different number of columns.

viperfx · Accepted Answer · 2012-11-16 15:59:11Z

1

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

Try that. Should fix the SQL error

answered Nov 16, 2012 at 15:59

viperfx

3275 silver badges18 bronze badges

1 Comment

Rich Coy Over a year ago

That fixed the error but now both valid and invalid emails are showing up no match.

Collectives™ on Stack Overflow

CodeIgniter SQL Building

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related