SQL Query Help

[Dynix]

Hi,

I've got a query that works fine, but would like to take it a step further and I've had no luck so far.

The query is:

--------------------------------------------------------
$result = mysql_query("SELECT playerid,race,xp FROM `table` WHERE playerid = 'STEAM_X:X:XXXXXX'");

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
printf("PlayerID: %s || Race: %s || XP: %s<br>", $row[0], $row[1], $row[2]);
}
--------------------------------------------------------

This outputs the following:
PlayerID: STEAM_X:X:XXXXXX || Race: 1 || XP: 5000000
PlayerID: STEAM_X:X:XXXXXX || Race: 2 || XP: 5000000
PlayerID: STEAM_X:X:XXXXXX || Race: 3 || XP: 5000000

Is there anyway to make it so that if the race outputs "1", it's turned into "Hello" for example? So it would look like:

PlayerID: STEAM_X:X:XXXXXX || Race: Hello || XP: 5000000

I've tried meddling with IF statements (if (race == 1) echo('Blah Blah'); if (race == 2) echo('Blah Blah #2'); for example).

Any ideas? Sorry if this is somewhat confusing, hope you can understand it though

Cheers.

 

[Beansprout]

Yup!

SELECT playerid,REPLACE(race,'1','hello') AS race, xp FROM `table` WHERE playerid = 'STEAM_X:X:XXXXXX

But if there's lots of possible values then it's far nicer to do it in PHP I think and store the values in an array in a config file - otherwise when you want to change anything you'll be digging round SQL queries

 

[NightmareXX]

Yea the if statement would work fine

Just remember to put it before the output.

if($row['race'] == 1){
print "hello";
}

 

[Visage]

You need the DECODE function......

 

[Beansprout]

Yea the if statement would work fine

Just remember to put it before the output.

if($row['race'] == 1){
print "hello";
}

$arr = array(
                0 => 'hello',
                1 => 'foo',
                2 => 'bar'
         );

echo $arr[$row['race']];

 

[Dynix]

Cheers guys, got it working

 

[Dj_Jestar]

$arr = array(
                0 => 'hello',
                1 => 'foo',
                2 => 'bar'
         );

echo $arr[$row['race']];

Lazy man's switch()

 

[Goksly]

You need the DECODE function......

In Oracle yes.... im mysql thats to decrypt passwords and stuff iirc. If you wanted to do it in the select statement you would need to use the CASE function:

select CASE job_lvl
WHEN 1 THEN 'level 1
WHEN 2 THEN 'level 2'
ELSE 'Unknown level' end
into job_level from employee where job_id>0 ;

 

[Beansprout]

Lazy man's switch()

I guess it depends how complex it is really as to which method is the best, and how flexible it'll need to be.