f



need to do a string replace of "asc" to "desc" or "desc" to "asc" first occurrence only

[PHP]
$orderBy = 's.app_date desc, s.last_name asc, s.first_name asc, s.mi
asc';
   if ($_REQUEST['willDesc']) {
    $ascArray = array('asc' => 'desc', 'desc' => 'asc'); 	// ARRAY OF
ALL ORDERING POSSIBILITIES
    $junk = preg_match('/([\s\t]+)a|[de]sc(,?.*$)/i', $orderBy,
$matchArray);
    $orderBy = substr($orderBy, 0, strpos($orderBy, $matchArray[1])) .
' ' . $ascArray[$matchArray[1]] .
               substr($orderBy, strpos($orderBy, $matchArray[1]) +
strlen($matchArray[1]), strlen($orderBy));
   }
[/PHP]

Basic premise:

I have a SQL "ORDER BY" clause that will be configured like $orderBy 's
value.  However, the very first occurrence of "desc" might instead be
"asc".  If the very first occurrence is "asc", it must become "desc";
likewise, if the very first occurrence is "desc", it must become "asc".

I tried Regular Expressions but the pattern failed every single time to
match and replace, so I gave up and tried a string function/RegExp code
combination, also to no avail.

I am not sure how to make this work so I need help figuring out how to
do this.

Thanx
Phil

0
2/3/2006 10:42:35 PM
comp.lang.php 32646 articles. 0 followers. Post Follow

5 Replies
846 Views

Similar Articles

[PageSpeed] 34

comp.lang.php wrote:
> [PHP]
> $orderBy = 's.app_date desc, s.last_name asc, s.first_name asc, s.mi
> asc';
>    if ($_REQUEST['willDesc']) {
>     $ascArray = array('asc' => 'desc', 'desc' => 'asc'); 	// ARRAY OF
> ALL ORDERING POSSIBILITIES
>     $junk = preg_match('/([\s\t]+)a|[de]sc(,?.*$)/i', $orderBy,
> $matchArray);
>     $orderBy = substr($orderBy, 0, strpos($orderBy, $matchArray[1])) .
> ' ' . $ascArray[$matchArray[1]] .
>                substr($orderBy, strpos($orderBy, $matchArray[1]) +
> strlen($matchArray[1]), strlen($orderBy));
>    }
> [/PHP]
> 
> Basic premise:
> 
> I have a SQL "ORDER BY" clause that will be configured like $orderBy 's
> value.  However, the very first occurrence of "desc" might instead be
> "asc".  If the very first occurrence is "asc", it must become "desc";
> likewise, if the very first occurrence is "desc", it must become "asc".
> 
> I tried Regular Expressions but the pattern failed every single time to
> match and replace, so I gave up and tried a string function/RegExp code
> combination, also to no avail.
> 
> I am not sure how to make this work so I need help figuring out how to
> do this.
> 
> Thanx
> Phil
> 


function change_first_order_flag($orderBy){
	$tmp = explode(
		'__SPLIT__HERE__',
		preg_replace(
			'`(asc|desc)`i',
			'__SPLIT__HERE__$1',
			$orderBy
		)
	);

	if(count($tmp)>1){
		// there are at least 2 elements
		// therefore, it was in there at least once
		if(substr($tmp[1],0,3)=='des'){
			// it was in descending order
			$tmp[1]='as'.substr($tmp[1],3);
		}else{
			// it was in ascending order
			$tmp[1]='des'.substr($tmp[1],3);
		}
	}
	
	return join($tmp);
}


-- 
Justin Koivisto, ZCE - justin@koivi.com
http://koivi.com
0
justin4335 (310)
2/3/2006 11:01:40 PM
Ok using your function:

[PHP]
if (!function_exists('change_first_order_flag')) {
 function change_first_order_flag($orderBy){
  $tmp = explode('__SPLIT__HERE__', preg_replace('`(asc|desc)`i',
'__SPLIT__HERE__$1', $orderBy));
  if (count($tmp) > 1) {
   // there are at least 2 elements
   // therefore, it was in there at least once
   if (substr(strtolower($tmp[1]), 0, 3) == 'des') {  // it was in
descending order
    $tmp[1] = 'as' . substr($tmp[1], 3);
   } else {  // it was in ascending order
    $tmp[1] = 'des' . substr($tmp[1], 3);
   }
  }
  return join($tmp);
 }
}
[/PHP]

Produced the following MySQL query syntax error:

[quote]
Fatal error: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near 'des, upper(s.student_firs' at line 1 using query:
[/quote]

I'll keep looking at it but apparently it chops the ORDER BY clause
incorrectly if the original ORDER BY clause contains ".. asc"

Phil
Justin Koivisto wrote:
> comp.lang.php wrote:
> > [PHP]
> > $orderBy = 's.app_date desc, s.last_name asc, s.first_name asc, s.mi
> > asc';
> >    if ($_REQUEST['willDesc']) {
> >     $ascArray = array('asc' => 'desc', 'desc' => 'asc'); 	// ARRAY OF
> > ALL ORDERING POSSIBILITIES
> >     $junk = preg_match('/([\s\t]+)a|[de]sc(,?.*$)/i', $orderBy,
> > $matchArray);
> >     $orderBy = substr($orderBy, 0, strpos($orderBy, $matchArray[1])) .
> > ' ' . $ascArray[$matchArray[1]] .
> >                substr($orderBy, strpos($orderBy, $matchArray[1]) +
> > strlen($matchArray[1]), strlen($orderBy));
> >    }
> > [/PHP]
> >
> > Basic premise:
> >
> > I have a SQL "ORDER BY" clause that will be configured like $orderBy 's
> > value.  However, the very first occurrence of "desc" might instead be
> > "asc".  If the very first occurrence is "asc", it must become "desc";
> > likewise, if the very first occurrence is "desc", it must become "asc".
> >
> > I tried Regular Expressions but the pattern failed every single time to
> > match and replace, so I gave up and tried a string function/RegExp code
> > combination, also to no avail.
> >
> > I am not sure how to make this work so I need help figuring out how to
> > do this.
> >
> > Thanx
> > Phil
> >
>
>
> function change_first_order_flag($orderBy){
> 	$tmp = explode(
> 		'__SPLIT__HERE__',
> 		preg_replace(
> 			'`(asc|desc)`i',
> 			'__SPLIT__HERE__$1',
> 			$orderBy
> 		)
> 	);
>
> 	if(count($tmp)>1){
> 		// there are at least 2 elements
> 		// therefore, it was in there at least once
> 		if(substr($tmp[1],0,3)=='des'){
> 			// it was in descending order
> 			$tmp[1]='as'.substr($tmp[1],3);
> 		}else{
> 			// it was in ascending order
> 			$tmp[1]='des'.substr($tmp[1],3);
> 		}
> 	}
>
> 	return join($tmp);
> }
> 
> 
> -- 
> Justin Koivisto, ZCE - justin@koivi.com
> http://koivi.com

0
2/4/2006 1:37:58 AM
comp.lang.php wrote:
>
> Ok using your function:
>
> [PHP]
> if (!function_exists('change_first_order_flag')) {
>  function change_first_order_flag($orderBy){
>   $tmp = explode('__SPLIT__HERE__', preg_replace('`(asc|desc)`i',
> '__SPLIT__HERE__$1', $orderBy));
>   if (count($tmp) > 1) {
>    // there are at least 2 elements
>    // therefore, it was in there at least once
>    if (substr(strtolower($tmp[1]), 0, 3) == 'des') {  // it was in
> descending order
>     $tmp[1] = 'as' . substr($tmp[1], 3);
>    } else {  // it was in ascending order
>     $tmp[1] = 'des' . substr($tmp[1], 3);
>    }
>   }
>   return join($tmp);
>  }
> }
>
> [/PHP]
>
> Produced the following MySQL query syntax error:
>
> [quote]
> Fatal error: You have an error in your SQL syntax; check the manual
> that corresponds to your MySQL server version for the right syntax to
> use near 'des, upper(s.student_firs' at line 1 using query:
> [/quote]
>
> I'll keep looking at it but apparently it chops the ORDER BY clause
> incorrectly if the original ORDER BY clause contains ".. asc"
>
> Phil

Change:
$tmp[1] = 'des' . substr($tmp[1], 3);

To:
$tmp[1] = 'des' . substr($tmp[1], 2);

That should do it... I was in a hurry to get out of the office, so i
didn't test it.

0
2/4/2006 7:22:03 AM
Much thanx! That worked!!

Of course, I did a slight variation:

$tmp[1] = 'desc' . substr($tmp[1], 3);

Phil

Justin Koivisto wrote:
> comp.lang.php wrote:
> >
> > Ok using your function:
> >
> > [PHP]
> > if (!function_exists('change_first_order_flag')) {
> >  function change_first_order_flag($orderBy){
> >   $tmp = explode('__SPLIT__HERE__', preg_replace('`(asc|desc)`i',
> > '__SPLIT__HERE__$1', $orderBy));
> >   if (count($tmp) > 1) {
> >    // there are at least 2 elements
> >    // therefore, it was in there at least once
> >    if (substr(strtolower($tmp[1]), 0, 3) == 'des') {  // it was in
> > descending order
> >     $tmp[1] = 'as' . substr($tmp[1], 3);
> >    } else {  // it was in ascending order
> >     $tmp[1] = 'des' . substr($tmp[1], 3);
> >    }
> >   }
> >   return join($tmp);
> >  }
> > }
> >
> > [/PHP]
> >
> > Produced the following MySQL query syntax error:
> >
> > [quote]
> > Fatal error: You have an error in your SQL syntax; check the manual
> > that corresponds to your MySQL server version for the right syntax to
> > use near 'des, upper(s.student_firs' at line 1 using query:
> > [/quote]
> >
> > I'll keep looking at it but apparently it chops the ORDER BY clause
> > incorrectly if the original ORDER BY clause contains ".. asc"
> >
> > Phil
>
> Change:
> $tmp[1] = 'des' . substr($tmp[1], 3);
>
> To:
> $tmp[1] = 'des' . substr($tmp[1], 2);
>
> That should do it... I was in a hurry to get out of the office, so i
> didn't test it.

0
2/5/2006 12:05:57 AM
"comp.lang.php" <phillip.s.powell@gmail.com> wrote in message 
news:1139006555.477380.219680@z14g2000cwz.googlegroups.com...
> [PHP]
> $orderBy = 's.app_date desc, s.last_name asc, s.first_name asc, s.mi
> asc';
>   if ($_REQUEST['willDesc']) {
>    $ascArray = array('asc' => 'desc', 'desc' => 'asc'); // ARRAY OF
> ALL ORDERING POSSIBILITIES
>    $junk = preg_match('/([\s\t]+)a|[de]sc(,?.*$)/i', $orderBy,
> $matchArray);
>    $orderBy = substr($orderBy, 0, strpos($orderBy, $matchArray[1])) .
> ' ' . $ascArray[$matchArray[1]] .
>               substr($orderBy, strpos($orderBy, $matchArray[1]) +
> strlen($matchArray[1]), strlen($orderBy));
>   }
> [/PHP]
>
> Basic premise:
>
> I have a SQL "ORDER BY" clause that will be configured like $orderBy 's
> value.  However, the very first occurrence of "desc" might instead be
> "asc".  If the very first occurrence is "asc", it must become "desc";
> likewise, if the very first occurrence is "desc", it must become "asc".
>
> I tried Regular Expressions but the pattern failed every single time to
> match and replace, so I gave up and tried a string function/RegExp code
> combination, also to no avail.
>
> I am not sure how to make this work so I need help figuring out how to
> do this.
>
> Thanx
> Phil
>

this function should work as long as asc and desc are not in the column 
names, and there is only 1 instance of asc or desc.

<?php
function change_first_order_flag($orderBy){
    $o=$orderBy;
    $start=0;
    do {
         $a=stripos($o, "order by", $start);
        if ($a===false) {} else {
            $a+=strlen("order by ");
            $start=$a;
            $ascpos=stripos($o, "ASC", $a);
            $descpos=stripos($o, "DESC", $a);
            if ($ascpos===false) {
                if ($descpos===false) {
                    //nothing to modify
                } else { //desc found
                    $o=substr($o, 0, $descpos-1) . "ASC" . substr($o, 
$descpos+strlen("DESC"));
                    $start=$a+strlen("ASC");
                }
            } else {
                if ($descpos===false) {
                    $o=substr($o, 0, $ascpos-1) . "DESC" . substr($o, 
$ascpos+strlen("ASC"));
                    $start=$a+strlen("DESC");
                } else {
                    //impossible situation!  both ASC and DESC found!
                    print "error: both ASC and DESC found in ORDER BY 
statement"
                }
            }
            //find another column in the ORDER BY series
            $c=strpos($o, ",", $start);
            if ($c===false) {
                break;
            } else {
                $start=$c+1;
            }
        }
    } while (true);
    return $o;
}
?>






0
jmichae31 (131)
2/17/2006 2:22:10 AM
Reply: