Hi There, some odd DB issues..

Hi There, some odd DB issues.. - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Hi There, some odd DB issues.. (/showthread.php?tid=21912)

Pages: 1 2

Hi There, some odd DB issues.. - El Forum - 08-24-2009

[eluser]Noy Gabay[/eluser]
So I've been using CI for some time now, and I have now bumped into this weird problem. This code:

Code:
$data = array( 'Title' => $title, 'Content' => $content );

$where = "ID = $ID";

$sql = $this->db->update_string('sitewide', $data, $where); 

$this->db->query($sql);

Updates the DB's records to 0. All the values I've been trying to update, are updated to this.
I've also tried doing this, having the same result:

Code:
$data = array('Title' => $title, 'Content' => $content);

$this->db->where('ID', $ID);

$this->db->update('sitewide', $data, "ID = $ID");

The next odd thing is that the function $this->db->last_query() prints out a correct update string, which updates correctly as SQL command directly to mysql.

I'm not even sure of how to debug it, not to mention solving it.

Any kind of help will be very appreciated..

Hi There, some odd DB issues.. - El Forum - 08-24-2009

[eluser]verynewtothis[/eluser]
On your second approach, do either:

Code:
$this->db->update('sitewide', $data, "ID = $ID"); 

//Even better if you do it like this:

$this->db->update('sitewide', $data, array('ID' => $ID));

OR this:

Code:
$this->db->where('ID', $ID);

$this->db->update('sitewide', $data);

See if this helps..

Hi There, some odd DB issues.. - El Forum - 08-24-2009

[eluser]Noy Gabay[/eluser]
Thank you very much for your response.
Unfortunately, this did not do the trick.

My code looks like this now:

Code:
$data = array( 'Title' => $title, 'Content' => $content );

$this->db->update('sitewide', $data, array('ID' => $ID));

And the output from $this->db->last_query() is this (which works great in phpMyAdmin, by the way):

Code:
UPDATE `sitewide` SET `Title` = 'Title Title', `Content` = 'Content Content' WHERE `ID` = 1

The specific record's data after update, though, looks like this:
ID = 1
Title = 0
Content = 0

Some weird stuff.. I'm puzzled. :roll:

Hi There, some odd DB issues.. - El Forum - 08-25-2009

[eluser]Noy Gabay[/eluser]
Help? anybody?
~drowning in frustration~

Some conclusions:
The problematic function is $this->db->query, since even when I try to update using regular SQL syntax it still destroys all the values and sets it as 0. So, even when I only do this:

Code:
$this->db->query("UPDATE `sitewide` SET `Title` = 'Title Title', `Content` = 'Content Content' WHERE `ID` = 1 ");

I get the DB values as 0.

I've ran through the config files like a zillion times, didn't find anything interesting there..
Moving on to check the query function..

again, any help would be greatly appreciated..

Hi There, some odd DB issues.. - El Forum - 08-25-2009

[eluser]davidbehler[/eluser]

Code:
$this->db->set('Title', $title);

$this->db->set('Content', $content);

$this->db->where('ID', $ID);

$this->db->update('sitewide');

Have fun

Hi There, some odd DB issues.. - El Forum - 08-25-2009

[eluser]Noy Gabay[/eluser]
still not doin' the trick..

So, I went all the way to the database/drivers/mysql/mysql_driver, and looked at this (line #137):

Code:
function _execute($sql)

    {

        $sql = $this->_prep_query($sql);

        return @mysql_query($sql, $this->conn_id);

    }

When I echo the $sql, it looks fine. Works well as SQL input in phpMyAdmin.

The weird thing is, that if I take the echoed string (value of $sql) and put it instead of the variable, like this:

Code:
return @mysql_query("UPDATE `sitewide` SET `Title` = 'Title Title', `Content` = 'Content Content' WHERE `ID` = 1 ", $this->conn_id);

It updates the DB!

I'm not even sure how to debug such a thing..

Hi There, some odd DB issues.. - El Forum - 08-25-2009

[eluser]verynewtothis[/eluser]
did you check if the $ID variable has the valid ID value in it? try echoing the value of $ID before the query..

Hi There, some odd DB issues.. - El Forum - 08-25-2009

[eluser]Noy Gabay[/eluser]
I did. It's valid, but for some reason still won't update. The whole query is valid, and displays as a perfectly-working sql command using last_query().
More to that, this is not working:

Code:
$this->db->query("UPDATE `table` SET `Title` = '$Title', `Content` = '$Content' WHERE `ID` =$ID LIMIT 1 ;");

But this IS working:

Code:
$this->db->query("UPDATE `table` SET `Title` = 'Title', `Content` = 'Content' WHERE `ID` =1 LIMIT 1 ;");

How come..?

P.S. -- The variables have a valid value.

Hi There, some odd DB issues.. - El Forum - 08-25-2009

[eluser]verynewtothis[/eluser]
Just some observations.. these might not be directly linked to your problem but something that i noticed

1) Use of single quotes around variable names..
2) using LIMIT in an update query.. if your ID is unique, you don't need that..

anyways... I am completely stumped by the way...
why don't you try this:

Code:
$sql = "UPDATE `table` SET `Title` = ?, `Content` = ? WHERE `ID` =?";

$this->db->query($sql , array($title , $content, $ID));

Hi There, some odd DB issues.. - El Forum - 08-25-2009

[eluser]Noy Gabay[/eluser]
OK.. Still investigating --
if I init the variable using

Code:
$this->input->post('Field_Name', TRUE)

it does not update the date (sets everything to 0), but if I init it manually, it does (using the same method I have shown before).

$this->input->post('Field_Name', TRUE) echoes a correct string, but it still won't update. Ideas?

$this->db->last_query() prints this:

Code:
UPDATE `table` SET `Title` = 'Title', `Content` = 'Content' WHERE `ID` ='1'