Control Flow Functions - MySql

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Control Flow Functions - MySql

Post by hawleyjr »

Okay, I should start by saying...I DIDN'T DESIGN THIS... :lol: :lol: :lol: :lol:

I'm working with a table such as:

Code: Select all

ID	|	Name	|	Status   

1		|	Joe		|	5
2		|	Dave	  |	2
3		|	James	 |	1
With that said, each status field has a corresponding PHP var...

Code: Select all

$a_status = array( 1 => 'Active', 2 => 'Suspended', 3 => 'Not Valid' );
Now the easy way would be to create a table of user status' and do a join...I don't have that luxury... :(

When I output the data I do this:

Code: Select all

echo $a_status[$row['status']];
$row['status'] being the recordset...

This is all fine and dandy except I need to sort by status Alpha and not numeric. The real example is much more complex so I can't just do a PHP array sort :(

I can give another example if need be 8O
Last edited by hawleyjr on Sat Feb 11, 2006 2:30 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

what about using the flow control constructs of MySQL?

http://dev.mysql.com/doc/refman/4.1/en/ ... tions.html

I've used IF() in the past to make substitutions based on values of fields. You can order by that too. :)
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

Thanks Feyd...


Wrote a quick function for this:

Note: I haven't tested this yet so it may change :)

Code: Select all

$aOptions = array( 1 => 'Active', 2 => 'Suspended\'s', 3 => 'Not Valid' ); 

echo returnMySQLSelectCase( 'status',$aOptions,'myalias' );

//	CASE status WHEN 1 THEN 'Active' WHEN 2 THEN 'Suspended\'s' WHEN 3 THEN 'Not Valid' END AS myalias 
function returnMySQLSelectCase( $fieldName,$aOptions,$fieldAlias = '' ){

	$fieldAlias = !empty( $fieldAlias )?$fieldAlias:$fieldName;
	
	if( !is_array( $aOptions ) || ( is_array( $aOptions ) && count( $aOptions) < 1 ) )
		return '';
	
	$returnString = ' CASE ' . $fieldName;
	
	foreach( $aOptions as $ak => $tVal ){
                $returnString .= " WHEN '" . addslashes( $ak ) . "' THEN '" . addslashes( $tVal ) . "'";
	}
	
	$returnString .= ' END AS ' . $fieldAlias;
	
	return $returnString;
}
Edit 1: I added a 's just for the fun of it :)
Edit 2: I knew there would be another :)
Changed:
$returnString .= ' WHEN ' . addslashes( $ak ) . ' THEN ' . addslashes( $tVal );
To:
$returnString .= " WHEN '" . addslashes( $ak ) . "' THEN '" . addslashes( $tVal ) . "'";
Last edited by hawleyjr on Fri Feb 10, 2006 11:56 pm, edited 1 time in total.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

This could have also been solved by using array_multisort() or converting to an enum field in mysql, at least I thought there was a way to toggle between getting the string version and the integer version of an enum field in mysql... could be wrong.


j/w - why aren't joins available?
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

I know I can use multisort j/k :)

It's not that I can use a join. The application uses the status array many times throughout the application. I don't want to have a PHP and a mysql version. Basically, if I have to change a label, I don't want to change it in two places :oops:

Edit: Okay, made two changes..no mas cerveza
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

hawlejr wrote: This is all fine and dandy except I need to sort by status Alpha and not numeric. The real example is much more complex so I can't just do a PHP array sort
is the status stored as integer or something else?
what do you actually want to sort??
Is it descending or asceding??

I do not know why I am not able to understand the question...if you are free, then provide an example as you said.
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

Jeesh, I just spent about 20 minutes writing a reply....

So, here is a scaled down version of what I was hoping to say...
raghavan20 wrote:
hawlejr wrote: This is all fine and dandy except I need to sort by status Alpha and not numeric. The real example is much more complex so I can't just do a PHP array sort
is the status stored as integer or something else?
what do you actually want to sort??
Is it descending or asceding??

I do not know why I am not able to understand the question...if you are free, then provide an example as you said.
The field is stored Numeric in the table. However the output is in the array.

The corresponding status array looks something like this:

Code: Select all

$a_status = array(
1='Active',
2='Inactive',
3='Removed',
4='Not Verified',
5='Hibernation');
and my data looks like this

Code: Select all

ID	|	Name	|	Status   

1		|	Joe		|	3
2		|	Dave	  |	5
3		|	James	 |	1
Prior to me taking over this project. This is how they displayed the status

Code: Select all

$qry = "SELECT id,name,status from SOMETABLE ORDER BY status asc";
#get data into and array...
...
..
.
 echo $row['id'] . '-'; 
 echo $row['name'] . '-'; 
 echo $a_status[$row['status']] . '<br />';
If I were to sort by status using the above example I would get:

3 - James - Active
1 - Joe - Removed
2 - Dave - Hibernation


However, you will notice that the output does not appear to be sorted by status asc. This is because status is stored as an integer.

To conclude, using the function above. Here is what I've done...

Code: Select all

$qryString = returnMySQLSelectCase( 'status',$a_status,'status_label' );

$qry = "SELECT id,name,status, $qryString from SOMETABLE ORDER BY statuslabel asc";

/*
SELECT id,name,status, 
CASE status WHEN 1 THEN 'Active' WHEN 2 THEN 'Inactive' 
WHEN 3 THEN 'Removed' WHEN 4 THEN 'Not Valid' WHEN 5 
THEN 'Hibernation' END AS status_label  
from SOMETABLE ORDER BY statuslabel asc
*/
Which would give me my desired output of:

Code: Select all

3 - James - Active
2 - Dave - Hibernation
1 - Joe - Removed
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

thanks for explaining the problem, hawlejr....have you solved the problem??
is this your solution??
$qryString = returnMySQLSelectCase( 'status',$a_status,'status_label' );

$qry = "SELECT id,name,status, $qryString from SOMETABLE ORDER BY statuslabel asc";

/*
SELECT id,name,status,
CASE status WHEN 1 THEN 'Active' WHEN 2 THEN 'Inactive'
WHEN 3 THEN 'Removed' WHEN 4 THEN 'Not Valid' WHEN 5
THEN 'Hibernation' END AS status_label
from SOMETABLE ORDER BY statuslabel asc
*/
can you explain me the code??...thanks
are you using procedures in mysql 5 to solve this???
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

Yes I was able to achieve what I needed to do :)

Um, as for explaining more. The above post really details what i did. For more references check out the link Feyd posted regarding Control Flow Functions

http://dev.mysql.com/doc/refman/4.1/en/ ... tions.html

Or just give it a try.

Here is a more simple example. Say you have a field 1 means yes and 0 means no.

Now you could output this in PHP but why..? :)

Table:

Code: Select all

id   |  yes_or_no
1    |   1
2    |   0
3    |   0
4   |   1
And a query to have 1 print out as Yes and 0 print out as No

SELECT id,CASE yes_or_no WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END FROM MYTABLE ORDER BY yesnolabel asc

This would return:

2 - No
3 - No
1 - Yes
4 - Yes
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

Changed the subject to -> Control Flow Functions - MySql
Post Reply