Can’t update database with PHP query
Development and Programming
This question may be related to a resolved thread.
Hi All
As discussed in the thread above, I’ve decided to do something a bit clever with member photos… I want members to be able to choose a photo from a channel as their member photo. But I’m having problems doing something quite basic: updating the relevant table in the database with a new value… This is my first time manipulating the DB directly like this, so I might be missing something obvious…
I set up the code below as a test, and can’t see why it’s not working…
<?php
$image = "testvalue.jpg";
$data = array('photo_filename' => $image);
$sql = $this->EE->db->update_string('exp_members', $data, "member_id = '{member_id}'");
// does something like UPDATE exp_members SET photo_filename = 'someimage.jpg' WHERE member_id = '8'
$this->EE->db->query($sql);
echo $sql;
echo "
".$this->EE->db->affected_rows()." rows were updated";
?>… that returns:
UPDATE exp_members SET photo_filename = ‘testvalue.jpg’ WHERE member_id = ‘4’
0 rows were updated
Any ideas why that’s not updating the relevant row? There definitely is a member with member_id=4 in exp_members, and they do have a photo_filename column there…
Thanks for any help! I’m on EE 2.2.3.
Frank
Moved to Community Help forum by Moderator
Hi, Frank. Did you find out if the update worked by looking at the tables directly?
Hi Sue. Yes - looking in PHPmyAdmin, the database doesn’t seem to have been modified at all…
I’m going to move this to the development and programming section, since troubleshooting this kind of code is beyond the scope of regular support.
Hi Frank,
I’ve only taken a very quick look over your code but perhaps this isn’t working due to the {member_id} you have in your code there?
I’m not sure where you are getting {member_id} from but that isn’t a global variable or anything like that so you might want to use the Session Class to retrieve the Member ID here instead to ensure that is definitely being parsed.
Hope that helps a bit anyway.
Mark
Thanks Mark - that cracked it… This does seem to work:
<?php
$data = array('photo_filename' => $image);
$id = $this->EE->session->userdata('member_id');
$sql = $this->EE->db->update_string('exp_members', $data, "member_id = '" . $id . "'");
$this->EE->db->query($sql);
echo $sql;
echo "
".$this->EE->db->affected_rows()." rows were updated";
?>I thought member_id should work though as it appears in this list of global variables: http://ellislab.com/expressionengine/user-guide/templates/globals/single_variables.html but maybe I’ve got the wrong end of the stick?
Hi Frank,
Sorry about what I said above there. member_id is indeed a Global Variable. Not too sure what I was thinking of there.
The problem here was probably one of parse order though and the best way when doing custom PHP like you are doing here to get at a users ID is to use the session class as that will then have no problems with parsing order.
Glad that got you sorted though.
Mark
No worries! Yes it must be a parse order thing. I thought it would parse before the PHP because I’ve got PHP on output, but it seems to parse later.
Anyway, all working now so I’m happy! Thanks for your help.