MX Query Builder Forum :: Multiple search in a table

Hello Florin,
maybe thisi is not strictly related to your product...
but I ask a suggestion I can can move to solve this problem.

I have  a table that stores  different  houses in Italy.
This table has many  camps like city, tipology, area, rooms, etc.
I want to make some search into this table.
I have no problems do make a search only for one camp.

But I need and I'm trying to make (unsuccesfully) a search with multiple parameters with the same form.
For example I want to find all the houses located in
Rome (city) ,  
that have 6 rooms (price)
and houses   flat (typology)

When I send all these thrre  parameters (city, price and typologY) is OK but
when I send only one parameter there is problem.
The problem is that  i  cannot  find for example  only  the houses located in Rome 
I  must choose (mandatory) also the rooms and the tipology.

Into the page of the results I have make this Sql recordset

SELECT *

FROM immobili_imm WHERE tipologia_imm = '$_POST[tipologia_imm]'   AND   localita_imm = '$_POST[localita_imm]' AND prezzo_imm =< '$_POST[prezzo_imm]'

Thank you

Rino

View full message

Hello Rino,

You need to edit your Recordset in Advanced View in DW and use sql dynamic parameters into the query:

SELECT *

FROM immobili_imm WHERE param1 AND param2 AND param3

The parameters will be defined as:

param1

Default Value: 1=1

Run-time Value: $_POST[localita_imm]

param2

Default Value: 1=1

Run-time Value: $_POST[tipologia_imm]

param3

Default Value: 1=1

Run-time Value: $_POST[prezzo_imm]

Then, go into the generated code and modify it like:

From:

$param1_Recordset2 = "1=1";
if (isset($search)) {
  $param_Recordset2 = (get_magic_quotes_gpc()) ? $search : addslashes($search);
}

To:

$param_Recordset1 = "1=1";
  if (isset($_POST[localita_imm])) {
  $param1_Recordset1 = "localita_imm = " . (get_magic_quotes_gpc()) ? $_POST[localita_imm] : addslashes($_POST[localita_imm]);}
}

for each of

Hello Ionut,
thanks for your precious support,
I try to solve in all the weekend but still I'm not able to solve this problem.
So I'm asking you another little support.

I make a recordset in Advanced View in DW:

SELECT *
FROM immobili_imm WHERE param1 AND param2 AND param3

I have definited these parameters as

param1

Default Value: 1=1

Run-time Value: $_POST[localita_imm]

param2

Default Value: 1=1

Run-time Value: $_POST[tipologia_imm]

param3

Default Value: 1=1Run-time Value: $_POST[prezzo_imm]

The generated code in DW is (is different from yours  that say to me)

$param1__Recordset1 = '1=1';
if (isset($_POST[localita_imm])) {
  $param1__Recordset1 = $_POST[localita_imm];
}

I modified it with

$param1_Recordset1 = "1=1";
  if (isset($_POST[localita_imm])) {
  $param1_

What is the error that is given?

Hello Ionut,
this is the 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 'AND AND LIMIT 18446744073709551615' at line 1

I'm using PHP ADODB

If it can help
I attach the files and the URL

Thanks.
Rino

If you are using PHP_ADODB server model (powered by PHAkt), you don't need to modify the generated code in order to add the get_magic_quotes_gpc() condition.

The code should just be:

$param1__Recordset1 = '1=1';
if (isset($_POST[localita_imm])) {
  $param1__Recordset1 = "localita_imm = " . $_POST[localita_imm];
}

If this does not help, please also attach here the database .sql script and I will do further testing.

Ionut

Hello Ionut
I have make another page with another recorset
SELECT *
FROM immobili_imm
WHERE param1 AND param2 AND param
ande definited
param1
param2
param3

as before.

Then I modified the code generated by DW
with  the last you send me:
$param1__Recordset1 = '1=1';
if (isset($_POST[localita_imm])) {
  $param1__Recordset1 = "localita_imm = " . $_POST[localita_imm];
}

At the end I have this code:
but I get always error (different)

===============================================
// begin Recordset
$maxRows_Recordset1 = 10;
$pageNum_Recordset1 = 0;
if (isset($_GET['pageNum_Recordset1'])) {
  $pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;

$param1__Recordset1 = '1=1';
if (isset($_POST[localita_imm])) {
  $param1__Recordset1 = "localita_imm = " . $_POST[localita_imm];
}

$param2__Recordset1 = '1=1';
if (isset($_POST[tipologia_imm])) {
  $param2__Recordset1 = "tipologia_imm = " . $_POST[tipologia_imm];
}

$param3__Recordset1 = '1=1';
if (isset($_POST[prezzo_imm])) {
  $param3__Recordset1 = "prezzo_imm = " . $_POST[prezzo_imm];
}

$query_Recordset1 = sprintf("SELECT * FROM immobili_imm WHERE %s AND %s AND %s", $param1__Recordset1,$param2__Recordset1,$param3__Recordset1);
$Recordset1 = $R1->SelectLimit($query_Recordset1, $maxRows_Recordset1, $startRow_Recordset1) or die($R1->ErrorMsg());
if (isset($_GET['totalRows_Recordset1'])) {
  $totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
} else {
  $all_Recordset1 = $R1->SelectLimit($query_Recordset1) or die($R1->ErrorMsg());
  $totalRows_Recordset1 = $all_Recordset1->RecordCount();
}
$totalPages_Recordset1 = (int)(($totalRows_Recordset1-1)/$maxRows_Recordset1);
// end Recordset

===============================================================
I send you the .sql

Thank you

I feel a  little in debt towards  you and InterAkt...

There were some mistakes into the code, like for example $_POST[tipologia_imm] should be $_POST['tipologia_imm'] and the parameter code should be rmodified as:

$param1_Recordset1 = "1=1";
  if (isset($_POST['localita_imm'])) {
  $param1_Recordset1 = "localita_imm = '" . $_POST['localita_imm']."'";

I am attaching the modified page back to you.

Ionut

Hello Ionut,
please can you tell me How to take the file?

I have done the new recordset, but still there is problem.
I have done a recorset

SELECT *
FROM immobili_imm
WHERE param1 AND param2 AND param3

And I have definited these three parameters:
param1
1=1
$_POST['localita_imm']

param2
1=1
$_POST['tipologia_imm']

param3
1=1
$_POST['prezzo_imm']

Then I change the code from

$param1__Recordset1 = '1=1';
if (isset($_POST['localita_imm'])) {
  $param1__Recordset1 = $_POST['localita_imm'];

to:

$param1_Recordset1 = "1=1";
  if (isset($_POST['localita_imm'])) {
  $param1_Recordset1 = "localita_imm = '" . $_POST['localita_imm']."'";

For all the three parameters

Now If a want to make a dinamic table for the results
the code returns as it was before:

I chande again (with the dynamic table)

and upload the page to the server.

The page don't give any results.

Rino

The code for the parameters should use !empty() instead of isset():

$param1_Recordset1 = "1=1";
  if (!empty($_POST['localita_imm'])) {
  $param1_Recordset1 = "localita_imm = '" . $_POST['localita_imm']."'";

Because the Recordset code is manually modified customized into the page, it is no longer recognized by DW, so you cannot apply Dynamic Table using this Recordset. So the best practice would be create a simple Recordset, apply Dynamic Table and then customise the Recordset code.

Anyway, as you already applied Dyn. Table in your page, you can just do the !empty() modification into teh code for all 3 query parameters.

I am also attaching you the corrected page back.

Ionut