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

**Justin Koivisto** · Feb 3 '06, 11:15 PM

Re: need to do a string replace of "asc&qu ot; to "desc&quot ; or "desc&quot ; to "asc" first occurrence only

comp.lang.php wrote:[color=blue]
> [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($matchAr ray[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
>[/color]

function change_first_or der_flag($order By){
$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($t mp[1],3);
}else{
// it was in ascending order
$tmp[1]='des'.substr($ tmp[1],3);
}
}

return join($tmp);
}

--
Justin Koivisto, ZCE - justin@koivi.co m

Temporarily Disabled | Koivi.com

http://koivi.com

**comp.lang.php** · Feb 4 '06, 01:45 AM

Re: need to do a string replace of "asc&qu ot; to "desc&quot ; or "desc&quot ; to "asc&qu ot; first occurrence only

Ok using your function:

[PHP]
if (!function_exis ts('change_firs t_order_flag')) {
function change_first_or der_flag($order By){
$tmp = explode('__SPLI T__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(strtolo wer($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:

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:

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:[color=blue]
> comp.lang.php wrote:[color=green]
> > [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($matchAr ray[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
> >[/color]
>
>
> function change_first_or der_flag($order By){
> $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($t mp[1],3);
> }else{
> // it was in ascending order
> $tmp[1]='des'.substr($ tmp[1],3);
> }
> }
>
> return join($tmp);
> }
>
>
> --
> Justin Koivisto, ZCE - justin@koivi.co m
> http://koivi.com[/color]

**Justin Koivisto** · Feb 4 '06, 07:35 AM

Re: need to do a string replace of "asc&qu ot; to "desc&quot ; or "desc&quot ; to "asc&qu ot; first occurrence only

comp.lang.php wrote:[color=blue]
>
> Ok using your function:
>
> [PHP]
> if (!function_exis ts('change_firs t_order_flag')) {
> function change_first_or der_flag($order By){
> $tmp = explode('__SPLI T__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(strtolo wer($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:
>
>

> 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:
>

>
> I'll keep looking at it but apparently it chops the ORDER BY clause
> incorrectly if the original ORDER BY clause contains ".. asc"
>
> Phil[/color]

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.

**comp.lang.php** · Feb 5 '06, 12:15 AM

Re: need to do a string replace of "asc&qu ot; to "desc&quot ; or "desc&quot ; to "asc&qu ot; first occurrence only

Much thanx! That worked!!

Of course, I did a slight variation:

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

Phil

Justin Koivisto wrote:[color=blue]
> comp.lang.php wrote:[color=green]
> >
> > Ok using your function:
> >
> > [PHP]
> > if (!function_exis ts('change_firs t_order_flag')) {
> > function change_first_or der_flag($order By){
> > $tmp = explode('__SPLI T__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(strtolo wer($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:
> >
> >

> > 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:
> >

> >
> > I'll keep looking at it but apparently it chops the ORDER BY clause
> > incorrectly if the original ORDER BY clause contains ".. asc"
> >
> > Phil[/color]
>
> 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.[/color]

**Jim Michaels** · Feb 17 '06, 02:35 AM

Re: need to do a string replace of "asc&qu ot; to "desc&quot ; or "desc&quot ; to "asc&qu ot; first occurrence only

"comp.lang. php" <phillip.s.powe ll@gmail.com> wrote in message
news:1139006555 .477380.219680@ z14g2000cwz.goo glegroups.com.. .[color=blue]
> [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($matchAr ray[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
>[/color]

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_or der_flag($order By){
$o=$orderBy;
$start=0;
do {
$a=stripos($o, "order by", $start);
if ($a===false) {} else {
$a+=strlen("ord er by ");
$start=$a;
$ascpos=stripos ($o, "ASC", $a);
$descpos=stripo s($o, "DESC", $a);
if ($ascpos===fals e) {
if ($descpos===fal se) {
//nothing to modify
} else { //desc found
$o=substr($o, 0, $descpos-1) . "ASC" . substr($o,
$descpos+strlen ("DESC"));
$start=$a+strle n("ASC");
}
} else {
if ($descpos===fal se) {
$o=substr($o, 0, $ascpos-1) . "DESC" . substr($o,
$ascpos+strlen( "ASC"));
$start=$a+strle n("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;
}
?>

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

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

Comment

Comment

Comment

Comment

Comment