How to show all records by default on search result page?

Report · Sep 22, 2010

Hi

I am trying to make a search page that would execute the search in the database based on one or more field constraints.

(Using MySQL,PHP)

I have 2 columns in the database "vm_ip" (primary key) <IP address>, "Operating_System" <Any, Windows, Solaris, AIX>

need to search vm_ip based on other two fields.

PROBLEM: Need to show all the record when I select "Any" in the Operating_System drop down menu.


<p>Operating System: 
    <select name="os_select" id="os_select">
    <?php 
$os_count=1;
foreach($os_type as $value) //(os_type is array with possible values of OS)
{
echo "<option value=".$os_count.">".$value."</option>";
    $os_count++;
}
 ?>
      <option value=" " selected="selected">Any</option>
    </select>
  </p>

this code POSts NULL value to the search page.

Below code is of recordset on search page

$varOS_virtual = "Operating_System"; // recordset variable set to same as column name (default value) if (isset($_POST['os_select'])) //this should not be true

{ $varOS_virtual = $_POST['os_select']; }

mysql_select_db($database_xyz_db, $xyz_db); $query_virtual = sprintf("SELECT table.VM_IP FROM table

WHERE table.Operating_System=%s", GetSQLValueString($varOS_virtual, "int"));

$virtual = mysql_query($query_virtual, $xyz_db) or die(mysql_error()); $row_virtual = mysql_fetch_assoc($virtual); $totalRows_virtual = mysql_num_rows($virtual);

I expected the 'os_select' field to be null and default value of Operating_System to be "Operating_System" so that the Query shows all records.

But instead the value being passed in the Query is "0". and no records are shown.

What can I do to show all records?

As probably obvious I am new to php/MySQL so all the help is most welcomed .

Thanks

Report · Sep 22, 2010

You need to test the value of the selected field. If the user selects 'Any', then modify the where clause to not include that column. If operating system is the only search criteria (as you have it listed) then just exclude the entire where clause.

Report · Sep 22, 2010

I'm a CF guy so I don't have the exact code, but here's what I do...

In your query set WHERE to "WHERE 1 = 1 so it is always true, then move all your other conditions to "AND" There's a good reason for this...

Now surround each of the AND statements in an IF statement so if the variable exists you include the line in your query, if not yo omit it from the query. This way if no variables exist the query will select ALL records.

Doing this allows you to write extremely flexible queries.

--
Lawrence Cramer - *Adobe Community Professional*
http://www.Cartweaver.com
Shopping Cart for Adobe Dreamweaver
available in PHP, ColdFusion, and ASP

Stay updated - http://blog.cartweaver.com

Report · Sep 22, 2010

$varOS_virtual = "Operating_System"; // recordset variable set to same as column name (default value)
if (isset($_POST['os_select'])) //this should not be true
{
  $varOS_virtual = $_POST['os_select'];
}
mysql_select_db($database_xyz_db, $xyz_db);

if ($varOS_virtual == "") //no filtered criteria
{
$query_virtual = sprintf("SELECT table.VM_IP FROM table");
} else {
$query_virtual = sprintf("SELECT table.VM_IP FROM table 
WHERE table.Operating_System=%s", GetSQLValueString($varOS_virtual, "int"));
}
$virtual = mysql_query($query_virtual, $xyz_db) or die(mysql_error());
$row_virtual = mysql_fetch_assoc($virtual);
$totalRows_virtual = mysql_num_rows($virtual);

Report · Sep 22, 2010

Hi

Thanks for the prompt reply but this does not solve my problem.

First thing I appologise for giving you incorrect info. Actually I have many more constraints on the search apart from OS.

Didn't think I would get single constraint specific ans.

Here is what all i tried and problems I faced:

1. I cannot use seperate queries cause I have around 7-8 other constraints on the search.

2. I cannot play around with the Record set code. For some reason even if I mess with it a little bit Dreamweaver stops recognising the recordset. for eg i tried the below code:

$query_virtual = sprintf("SELECT table.VM_IP FROM table WHERE
table.Operating_System=%s ,($_POST['os_select']=="")? TRUE:GetSQLValueString($varOS_virtual, "int"));

this ran well for the first time, and then the record set was screwed up. kept on asking me to "Discover" the links, which it couldn't do.

3. take a look at this code:

$varOS_virtual = "Operating_System";
if (isset($_POST['os_select'])) {
  $varOS_virtual = $_POST['os_select'];
}
$varState_virtual = "State";               //second constraint
if (isset($_POST['state_select'])) {
  $varState_virtual = $_POST['state_select'];
}
mysql_select_db($database_xyz, $xyz);
$query_virtual = sprintf("SELECT table.VM_IP FROM table AND table.Operating_System=%s AND State=%s",
GetSQLValueString($varOS_virtual, "int"),GetSQLValueString($varState_virtual, "int"));
$virtual = mysql_query($query_virtual, $xyz) or die(mysql_error());
$row_virtual = mysql_fetch_assoc($virtual);
$totalRows_virtual = mysql_num_rows($virtual);

here on passing NULL value for "Any" GetSQLValueString($varOS_virtual, "int") functions returns NULL and though the Default value of

varOS is set to 'Operating_System" query takes NULL value only which when executed shows no records.

My problem majorly revolves around how to put something like "WHERE Operating_System= Operating_System" OR "WHERE Operating_System= TRUE" in the query, when passed through variable they are sent as Strings or NULL.

Thats why WHERE 1=1 also doesnt work, because it has "WHERE Operating_System= NULL" in AND.

Logically when NULL is passed default value of variable should comeinto picture, but tha isnt happening.

Thanks again for answering. hope I was able to explain my problem.

Report · Sep 22, 2010

The example I provided should be enough for you to go off from. If a parmeter is null then run one query else run another one. You can append additional elseif statements if necessary. Use the info you've learned by studying the query to then tailor it to your need.

Sometimes you'll have to play around with the recordset code. It comes w/ development. It doesn't matter if your recordsets in DW break i you posess a knowledge of code.

Report · Sep 22, 2010

>I cannot play around with the Record set code.

>For some reason even if I mess with it a little bit

>Dreamweaver stops recognising the recordset.

Correct. If you alter DW generated code then DW will no longer be able to maintain it. At this point you are creating custom code so you will need to maintain the code manually.

Report · Sep 22, 2010

Thanks a lot for the help. I didn't know that I can work with broken Recordsets too. Though it's a lot less easy now that Dreamweaver wont provide cooked code , I think the new found flexibility will solve my problems.

There is another thing though. even with broken recordset some times Dreamweaver modifies my custom code (I think that happens when I click on discover) is there any way of stopping DW from messing with my code except for not clicking on Discover??

Thanks.