Page 1 of 1

Control Flow Functions - MySql

Posted: Fri Feb 10, 2006 9:56 pm
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

Posted: Fri Feb 10, 2006 10:02 pm
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. :)

Posted: Fri Feb 10, 2006 10:42 pm
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 ) . "'";

Posted: Fri Feb 10, 2006 11:08 pm
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?

Posted: Fri Feb 10, 2006 11:12 pm
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

Posted: Sat Feb 11, 2006 3:34 am
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.

Posted: Sat Feb 11, 2006 9:36 am
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

Posted: Sat Feb 11, 2006 1:11 pm
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???

Posted: Sat Feb 11, 2006 2:28 pm
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

Posted: Sat Feb 11, 2006 2:30 pm
by hawleyjr
Changed the subject to -> Control Flow Functions - MySql