By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,991 Members | 1,737 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,991 IT Pros & Developers. It's quick & easy.

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

P: n/a
[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

Feb 3 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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 - ju****@koivi.com
http://koivi.com
Feb 3 '06 #2

P: n/a
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:

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:
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 - ju****@koivi.com
http://koivi.com


Feb 4 '06 #3

P: n/a
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:

> 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


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.

Feb 4 '06 #4

P: n/a
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:

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


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.


Feb 5 '06 #5

P: n/a

"comp.lang.php" <ph**************@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.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;
}
?>


Feb 17 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.