Skip to main content
S
Sead1
Known Participant
December 20, 2007
Answered

Sorting requested data

  • December 20, 2007
  • 4 replies
  • 680 views
I want to add a sorting function to my query. I am able to pull all of the data or a selection of the data I have, however when I add in the "ORDER BY date DESC" it does not work for the first of my "else" function. To be more clear:

1. line 5 "SELECT * FROM Listings ORDER BY date DESC, does work
2. line 3 "SELECT * FROM Listings WHERE state = %s ORDER BY date DESC" does NOT work

In line 3 the %s is standing in for what the DW search page sends to the database (that would be a state code, ie. CO, NM, PA).

Do you know how to add in the sort function so that it works?

Thank you very much for your time!

Sead1
This topic has been closed for replies.
Correct answer Newsgroup_User
Sead1 wrote:
> Fatal error: Call to undefined function: getsqlvaluestring() in
> /data/6/0/52/2/378165/user/384897/htdocs/ecareer/results.php on line 84

Move this section of code to the top of your page:

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "",
$theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) :
$theValue;

$theValue = function_exists("mysql_real_escape_string") ?
mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" :
"NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue :
$theNotDefinedValue;
break;
}
return $theValue;
}
}

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

4 replies

J
jmortimer
Participant
December 20, 2007
That is weird.
But what is also weird is that it works without the order by.

Typically when i'ved used the %s you also need the sprintf() function.

So as an example:

sprintf("SELECT * FROM Listings WHERE state LIKE %s ORDER BY date DESC", $_POST['searchfor']);

But it looks like you are just using dreamweaver's wizard. So try this:
Remove this:
if($_POST['searchfor'] != "All") {
$query_R1 = "SELECT * FROM Listings WHERE state = %s ORDER BY date DESC";
} else {
$query_R1 = "SELECT * FROM Listings ORDER BY date DESC";
}

And then in the recordset wizard window:
Filter by state by Form Variable
Selec the "Begins With" option (instead of "=")
and then type in $_POST['searchfor']
Sort by date
Select Descending

Be sure and give your dropdown's ALL selection a value of '%'

That is it.
You'll be good.

Check the code and note the sprintf() function was added for you.
and that the LIKE comparison was used.
S
Sead1Author
Known Participant
December 21, 2007
jmortimer,

Thanks for your time and help in figuring out this issue I was having. I started working with David too and ultimately found the solution--if you are curious, take a look at it.

Have a great Holiday Season, and have a beer on me!

Sead1
J
jmortimer
Participant
December 20, 2007
Sorry, otherwise you don't even need this if/else.

Just do this:
SELECT * FROM Listings WHERE state LIKE %s ORDER BY date DESC.

Give your "ALL" option a value of '%'. This will throw a wild card in for everything, otherwise, you will get an exact match.

Good luck.
J
jmortimer
Participant
December 20, 2007
Is the sort not working, or the entire query?
S
Sead1Author
Known Participant
December 20, 2007
jmortimer,

Thanks for your help.

Everything works fine as it is in line 5 (for the select * ORDER BY/DESC) .

The line 3 query works, but only without the ORDER BY/DESC function; as soon as I add that in, I get an error back, not data.

Sead1
N
Newsgroup_User
Inspiring
December 20, 2007
Sead1 wrote:
> 1. line 5 "SELECT * FROM Listings ORDER BY date DESC, does work
> 2. line 3 "SELECT * FROM Listings WHERE state = %s ORDER BY date
> DESC" does NOT work
>
> In line 3 the %s is standing in for what the DW search page sends to the
> database (that would be a state code, ie. CO, NM, PA).

That's what you want, but it's not how you've written the code. For %s
to represent the value passed through the $_POST array, you need to use
sprintf() in the same way as Dreamweaver does. What's more, your code is
in the wrong order.

The main part of it should look like this:

$colname_rsState = "-1";
if (isset($_POST['searchfor'])) {
$colname_rsState = (get_magic_quotes_gpc()) ? $_POST['searchfor'] :
addslashes($_POST['searchfor']);
}

if($_POST['searchfor'] != "All") {
$query_R1 = sprintf("SELECT * FROM Listings WHERE state = %s ORDER BY
date DESC", GetSQLValueString($colname_rsState, "text"));
} else {
$query_R1 = "SELECT * FROM Listings ORDER BY date DESC";
}
mysql_select_db($database_connCareers, $connCareers);
$query_rsState = "$query_R1";

Note that by adding a conditional clause into the recordset code like
this will prevent Dreamweaver from recognizing the recordset, and the
Bindings panel will no longer show the column names. So you should make
this change only after you have finished laying out your page.

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/
N
Newsgroup_User
Inspiring
December 20, 2007
Sead1 wrote:
> If it is of any help, the error message I receive is
> regarding line 84, date DESC", GetSQLValueString($colname_rsState, "text"));

It would be more helpful to tell me what the error message says.

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/
N
Newsgroup_UserCorrect answer
Inspiring
December 20, 2007
Sead1 wrote:
> Fatal error: Call to undefined function: getsqlvaluestring() in
> /data/6/0/52/2/378165/user/384897/htdocs/ecareer/results.php on line 84

Move this section of code to the top of your page:

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "",
$theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) :
$theValue;

$theValue = function_exists("mysql_real_escape_string") ?
mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" :
"NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue :
$theNotDefinedValue;
break;
}
return $theValue;
}
}

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices