MySQL call from PHP (CodeIgniter) not working right

Question

I'm new to PHP (and CodeIgniter) and I'm having trouble trying to get a simple call of $this->db->query() to work. In my code (see bottom of post) I am looking through the errors and warnings that have been logged in my database table eventLog and comparing each to a list of accepted error messages. If any message isn't found to be on that list, then I want to acknowledge it by setting "ack = 't'". However the code can't successfully run and gives the message:

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

UPDATE eventLog SET ack = 't' WHERE id =

This message seems to indicate that "$row['id']" is not working the way I intended. It should give me the id of the error or warning, but the error suggests that it's giving nothing, or in the wrong format. What am I missing? Or is there a better way to do this that may bypass my problem?

MY CODE:

$acceptedMessages = array("whatever",
                          "whateverelse"
                          );

$sql = "(SELECT id, mesg FROM eventLog WHERE level = 'error') UNION   
          (SELECT id, mesg FROM eventLog WHERE level LIKE 'warn%')" ; 

foreach ($this->db->query($sql) as $row) { 
  foreach ($acceptedMessages as $messagePart) { 

    $pos = strpos($row['mesg'], $messagePart); 
    if ($pos !== false) { 
      continue 2; 
    } 
  } 
  // if we get here the eventLog message didn't match any accepted messages, so
  // acknowledge it
  $idNum = $row['id']; 
  $sql3 = "UPDATE eventLog SET ack = 't' WHERE id = " . $idNum; 
  $res = $this->db->query($sql3); 
  if (!$res) { echo "Failed to acknowledge error/warning with id " . $row['id'] . " in eventLog"; } 
}

Samutz · Accepted Answer · 2014-02-06 04:09:32Z

1

$this->db->query($sql) returns a result object, but not an array, so that foreach shouldn't be working. Try this instead:

$query = $this->db->query($sql);
foreach ($query->result_array() as $row) {

Check out the docs for more options: http://ellislab.com/codeigniter/user-guide/database/results.html

answered Feb 6, 2014 at 4:09

Samutz

2,3004 gold badges24 silver badges29 bronze badges

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

Comments

Sudhir Bastakoti · Accepted Answer · 2014-02-06 03:36:25Z

1

you could write simple query, like, change:

$sql = "(SELECT id, mesg FROM eventLog WHERE level = 'error') UNION   
          (SELECT id, mesg FROM eventLog WHERE level LIKE 'warn%')" ;

to

$sql = "SELECT id, mesg FROM eventLog WHERE (level = 'error' OR level LIKE 'warn%')" ;

AND:

$sql3 = "UPDATE eventLog SET ack = 't' WHERE id = " . $idNum; 
$res = $this->db->query($sql3);

to

$act = 't';
$sql3 = "UPDATE eventLog SET ack = ? WHERE id = ?"; 
$res = $this->db->query($sql3, array($act, $idNum));

or

$act = 't';
$data = array(
    'act' => $act
);

$this->db->where('id', $idNum);
$this->db->update('eventLog', $data);

edited Feb 6, 2014 at 3:36

answered Feb 6, 2014 at 3:24

Sudhir Bastakoti

100k15 gold badges163 silver badges169 bronze badges

2 Comments

Max Over a year ago

Also I am aware of the inefficient parts I've put in the code. The first one you point out is because I've simplified it from how it actually appears in the code, where the UNION is necessary. And the second one I made inefficient just to pull the pieces of my problem apart and see if I could tinker with them to solve it.

Max Over a year ago

Alright I'll try rewriting my code a bit, though I'm hoping someone else will figure out my mistake.

Collectives™ on Stack Overflow

MySQL call from PHP (CodeIgniter) not working right

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related