Dreamweaver - Database "default value" question

Author

Database "default value" question - help needed

Steve L

2004-03-30, 8:21 pm

I'm building a PHP/MySQL site. I've got a listing of apartments in my
database, with columns like "type", "rent", "buildingname".

I'm trying to build a search page, with drop down menus so the user can
search for all 1-BR apartments with rent under $1000 in any of my
buildings (for example).

The main thing that's confusing me is the "Any" option in ym menus;
what should that be? And what's the default value in the recordset
dialog in MX2004 supposed to be?

Let's take apartment type. There's a menu in the search form named
"apt" with the labels "Any, Studio, 1BR, 2BR. 3BR" and the values
"any, 0, 1,2,3" respectively. My recordset window has:

SELECT *
FROM apartments
WHERE TYPE = colname
ORDER BY ID ASC

Variable: name= colname, def value=1, run-time value=$_POST['apt']

If I run the search and choose "2BR", it passes the value "2" and
returns my 2BR apartments. But I can't figure out how to modify this
so the user can choose "any" if he/she doesn't care about apartment
size...

Please, anyone help?

Jeffrey D. Cohan

2004-03-30, 8:21 pm

My first impression is that I would probably employ a SWITCH control
function to generate the appropriate mysql select statement. But I
do it by hand, not with DW's recordset functions.

I don't know if that might help.

Jeff C

Steve L

2004-03-30, 8:22 pm

On 2004-03-26 11:09:38 -0500, "Jeffrey D. Cohan" <jeff@nsiteful.com> said:

> My first impression is that I would probably employ a SWITCH control
> function to generate the appropriate mysql select statement. But I
> do it by hand, not with DW's recordset functions.
>
> I don't know if that might help.
>
> Jeff C

Sounds reasonable. But what would be the SQL? And how do you query
the database to essentially return "*" (everything)? This is the
concept that eludes me so far.

Using my example from earlier, I can see how the form passes the value
for number of bedrooms (0,1,2, or 3) but how to pass a value that
essentially makes that form element null in the query; "ignore me,
return all matches for this column".

I'm not above hand-coding this, but it seems so basic it would seem I'm
just missing something conceptual.

Jeffrey D. Cohan

2004-03-30, 8:22 pm

Steve -

The answer may be as simple as this: When the visitor selects "any", your
SQL statement would have no WHERE clase for the 'TYPE' column.

(This is down & dirty; conceptual if not valid code)

SWITCH ($colname) {
case "any":
$sql = "SELECT * FROM apartments ORDER BY ID ASC;";
break;
case "2":
$sql = "SELECT & FROM apartments WHERE TYPE = '2BR' ORDER BY ID ASC";
break;
case "etc":
etc
break;
}

Then do your $result = msql_query($sql); etc

Does that help, at least conceptually?

Jeff C

> Sounds reasonable. But what would be the SQL? And how
> do you query the database to essentially return "*"
> (everything)? This is the concept that eludes me so far.

Steve L

2004-03-30, 8:22 pm

On 2004-03-26 11:44:42 -0500, "Jeffrey D. Cohan" <jeff@nsiteful.com> said:

> Does that help, at least conceptually?

That helps. I wouldn't have though of breaking uo the query like that, thanks.

I guess now I need to brush up on hand-coding the php...

Steve