Sorting requested data

[Sead1]

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

[Newsgroup_User]

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/

[Sead1]

Hi David,

Thanks so much for your help. I tried your suggestion in several different ways and it is not working for me. I attached the updated code to see if you can spot my mistake! If it is of any help, the error message I receive is regarding line 84, date DESC", GetSQLValueString($colname_rsState, "text"));

Thanks again!

Sead1

[Newsgroup_User]

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/

[Sead1]

Sorry, David,

Below you will find the entire error. Thanks again.

Fatal error: Call to undefined function: getsqlvaluestring() in /data/6/0/52/2/378165/user/384897/htdocs/ecareer/results.php on line 84

[Newsgroup_User · Accepted solution]

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/

[Sead1]

David,

Thank you very much for your time and help on this issue. Your directions fixed my problems and now all of the searches work!

Thanks again and have a great Holiday Season! Enjoy the cyber wine I am sending!

Sead1

[Newsgroup_User]

Sead1 wrote:
> Thank you very much for your time and help on this issue. Your directions
> fixed my problems and now all of the searches work!

Glad to have been of help.

> Thanks again and have a great Holiday Season! Enjoy the cyber wine I am
> sending!

And to you. I think I'll drink some real wine, though. ;-)

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

[jmortimer]

Is the sort not working, or the entire query?

[Sead1]

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

[jmortimer]

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.

[jmortimer]

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.

[Sead1]

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