Getting annoyed with a sql update script

Here you can find plenty of help with your general coding needs and projects
If it is about a specific program, use the corresponding forum
DO NOT ask basic questions such as "How do I print a variable", use Google for that

Getting annoyed with a sql update script

Postby jaffa » Tue Apr 08, 2008 3:17 am

I'm writing an addon to sync the roster with phpBB. It's nearly done, but one bit is giving me problems. This is how the bit to update a user's avatar looks in the code:
Code: Select all
$query="update {$this->data['config']['forum_prefix']}users u inner join {$roster->db->prefix}members m inner join {$roster->db->prefix}players p 
set u.user_avatar = replace(\"{$this->data['config']['player_avatar']}\", \"%name%\", m.name) 
where ucase(u.name) = ucase(m.name) 
and p.name=m.name 
and user_avatar=\"gallery/blank.gif\" /*only updates people with blank avatars*/
and p.name is not null"
/*only updates people who have uploaded to the roster*/
 


However, this is giving everyone the same avatar. I would think it was the query, but for one thing - When I run the sql that it reportedly used (from the debug output) through phpmyadmin, it works perfectly. I think it way be something to do with the replace, as a similar update works fine without the replace.

Any ideas?

Edit - it's definitely the replace. I ran it just using

Code: Select all
set u.user_avatar = m.name


and it worked fine. Alternative is to update each user individually and do the replace in code rather than SQL but I would prefer to avoid that for speed reasons.
Last edited by jaffa on Tue Apr 08, 2008 3:27 am, edited 2 times in total.
User avatar
jaffa
Roster AddOn Dev
Roster AddOn Dev
 
Posts: 49
Joined: Thu Jan 10, 2008 11:35 pm

Re: Getting annoyed with a sql update script

Postby jaffa » Tue Apr 08, 2008 4:10 am

I still don't know why it doesn't work, but I have removed the replace and am using a concat instead and it seems to work.
User avatar
jaffa
Roster AddOn Dev
Roster AddOn Dev
 
Posts: 49
Joined: Thu Jan 10, 2008 11:35 pm

Getting annoyed with a sql update script

Postby PleegWat » Tue Apr 08, 2008 11:44 am

Does $this->data['config']['player_avatar'] actually contain %name% as a substring (with the %)?

Also, formatting strings like that makes for unreadable code.
I <3 /bin/bash
User avatar
PleegWat
WoWRoster.net Dev Team
WoWRoster.net Dev Team
 
Posts: 1636
Joined: Tue Jul 04, 2006 1:43 pm

Re: Getting annoyed with a sql update script

Postby jaffa » Wed Apr 09, 2008 1:48 am

It did contain that string. What I am doing now is this:

Code: Select all
$ava = explode("%name%",$this->data['config']['player_avatar']);
$query="update {$this->data['config']['forum_prefix']}users u inner join {$roster->db->prefix}members m inner join {$roster->db->prefix}players p
set u.user_avatar = concat('$ava[0]', m.name, '$ava[1]')
where ucase(u.name) = ucase(m.name)
and p.name=m.name
and user_avatar=\"gallery/blank.gif\" /*only updates people with blank avatars*/
and p.name is not null"; /*only updates people who have uploaded to the roster*/



When you say it makes it unreadable, do you mean using %name% for the character name? What do you suggest instead? I could use a path and then get the rest of the filename from siggen I guess. I want to get the whole thing from siggen but haven't worked that out yet.
Last edited by jaffa on Wed Apr 09, 2008 1:49 am, edited 1 time in total.
User avatar
jaffa
Roster AddOn Dev
Roster AddOn Dev
 
Posts: 49
Joined: Thu Jan 10, 2008 11:35 pm


Return to General Code Help

Who is online

Users browsing this forum: Bing [Bot] and 0 guests

cron