Skip to main content
P
pp140
Participant
September 22, 2010
Question

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

  • September 22, 2010
  • 3 replies
  • 1106 views

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
This topic has been closed for replies.
3 replies
A
Anonymous
September 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);
P
pp140Author
Participant
September 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.
A
Anonymous
September 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.
Lawrence_Cramer
Lawrence_Cramer
Inspiring
September 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
B
bregent
Participating Frequently
September 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.

        
                                                

        

    


            

                                        

            
                        

                                                                                                            
            







        

                    

    
    





    


                                        

                                                        

            
        
    






                                    
Terms & ConditionsAccessibility statement

            
        



        
            
    
        
    
        



        
        
        


        


        
        
        
        
                                                        

                            
    

                    

  

    

      
        
      
    

    

      
        Using the community
      
      
        Terms of use
      
      
        Privacy
      
      
        Cookie preferences
      
      
        Do not sell or share my personal information
      
      
        ad choicesAdChoices