Help with converting SQL results.

[not_sure]

Hi,

 

Using the following logic I am able to connect to a db and return the US State that is associated with the SQL statement.

 

$connect=odbc_connect('123456','aaaaaa','');

$sql = "SQL statement" <--- omitted due to irrelevance

 

$exec=odbc_exec($connect,$sql);

while (odbc_fetch_row($exec))

  {

  $state=odbc_result($exec,"STATE");

  echo $state; 

  }

 

This will correctly grab the state result as expected, however the problem is that all states are coded in the db as numbers. Ex. 1 for Alabama etc.  I didn't design this and it can't be changed... 

 

What I want to accomplish is to display the actual state abbreviation instead in my results - so for 1 I would display AL, etc.

I've tried a few associative arrays/tried to establish a key structure in an array, but I am not having any luck.

 

Any help?

[Barand]

Create a table "state"

 

id  |  Abbrev  |  state

 

1  | AL        |  Alabama

etc

 

and use a JOIN in your query

[Psycho]

@Barand, I'm thinking that table already exists and the OP is not aware of that

. . . I didn't design this . . .

 

$sql = "SQL statement" <--- omitted due to irrelevance

 

@not_sure, why did you omit the one line that may actually help us?

 

Without seeing your DB structure or the query in question it is impossible to say. But, my guess would be that the numerical references are foreign key references to an associative table where the actual State name or abbreviations are stored. If that's the case the query, which you deemed irrelevant, would need to be modified to JOIN onto the other table to get the results you want. I would be curious to knwo the actual name of the field that holds the value you are currently getting.

[mikosiko]

will be interesting to see first the complete definition of the table from where you are selecting the data, or even maybe your DB already has a table to associate the state number with the name, and you just need to modify your select with the proper JOIN; otherwise Barand solution.

 

[Edit:] Psycho beats me.

[not_sure]

Thanks everyone for the quick replies. 

 

I should have specified that I really am just looking for a PHP solution to this. I have read only access to the database and while I didn't design the db, I do have a very high level of knowledge of the table structures. That is why I mention about not designing it - they literally created a state table database that only returns the states by codes rather than the alpha chars. There is no associative table where the actual State name or abbreviations are stored. The only association (and way to know what state it refers to) is on paper.

 

So, just from a PHP perspective, is there a way to convert the results after they are retrieved to the abbreviations? 

 

 

I tried things such as listed here  http://php.net/manual/en/function.key.php  and was able to bring in results like  1AL, but could not actually display just "AL" when the result is 1.

 

I may just have to create a separate db that I have full access to, and retrieve the results from there.

 

 

 

 

[Barand]

Create an array with the code as the keys and the states as the values.