searching a database

jf1243

Hi guys! I really appreciate there being people to help newbies such as myself. I have some questions I wanted to ask that I was hoping some of you could answer. Again, I’m completely new to php so please take it easy with me if I do or say something wrong.

So I’m working on a project that is due in less than two weeks. I’m working in conjunction with a big flea market that decided to digitize the greater part of their information. My task was to create a database but the only database knowledge I have is Access. I wanted to impress the boss by trying to learn MySQL and PHP, but its proven to be a nuisance for this old dog. I downloaded WAMP and made a database using PHPmyAdmin that contained the info of all the almost 500 booths (i.e. columns with booth number, booth status (leased, for sale etc), if it had a roof, tables, racks, and a column for other comments I wanted to put in) and exported it into MySQL format. I uploaded the database to the server so that’s taken care of. The biggest problem I’m having is creating the file that will query the database. The boss said he wants it so that people can go online and look for a booth that is for sale, has tables and a roof and have returned to them the booths that meet those criteria. While I’m sure that is a common question on here, here is a twist. Since it’s mainly going to be older (and I mean OLDER) folks using this, I wanted to use a search form that used checkboxes instead of an empty field and having them type what they’re looking for. Is that possible? If it is, I managed to make this (though I don’t know if it is correct or not)

<html>
<head>
<title>Search For A Booth</title>
</head>
<body>
<form method="post" action="<?php echo $PHP_SELF;?>">
</br>
<b><font size=4>
Please check the boxes of the booth features you are looking for:
</br>
</br>
<input type="checkbox" value="Booth Status" name="Booth Status[]">: : For Sale</br>
<input type="checkbox" value="Booth Status" name="Booth Status[]">: : Leased</br>
<input type="checkbox" value="Roof" name="Roof[]">: : Roof</br>
<input type="checkbox" value="Tables" name="Tables[]">: : Tables</br>
<input type="checkbox" value="Racks" name="Racks[]">: : Racks</br>
</br>
What type of ground surface would you prefer? 
</br>
</br>
<input type="checkbox" value="Grass" name="Platform Type[]">: : Grass</br>
<input type="checkbox" value="Dirt" name="Platform Type[]">: : Dirt</br>
<input type="checkbox" value="Wooden" name="Platform Type[]">: : Wooden</br>
<input type="checkbox" value="Gravel" name="Platform Type[]">: : Gravel</br>
<input type="checkbox" value="Paved" name="Platform Type[]">: : Paved</br>
</br>
</b></font>
<input type="submit" value="submit" name="submit"><br />
</form><br />
</body>
</html>

okram

Hi,

so your question is if it's possible to use a searchform with checkboxes? Sure it is.
When you have all your search criteria beforehand available you can even make it a static one.
Though I would refrain from using names for input field with spaces. "Platform Type[]".

PHP will rename it to "Platform_Type[]" anyway. You can walk these arrays in your phpcode with "foreach" and so build your SQL-query.

Greetz.
-okram

jf1243

Thanks for the reply. That sort of took care of one of my questions. I was actually going to post both of my questions together but the post was too long so I decided to cut it into two part but it took a while for the thread to post to I took a nap :o

The other problem I have is, I need to make the file that actually goes through and will query the database. That's where my php inexperience is really kicking me in the butt. I made two files

<?php 
$sql_host = "localhost"; 
$sql_user = "username"; 
$sql_pass = "pass";  

$sql_db = "test"; 
?>

and

<?php 

include 'connection_file.php';


if(empty($sql_host) || empty($sql_user) || empty($sql_pass) || empty($sql_db) || !file_exists('connection_file.php')){ 
echo 'You did not fill in all the required fields in the connection_file.php file or it does not exist in this directory....'; 
exit(); 
} else{ 


mysql_connect($localhost, $username, $pass) or die("Could not connect"); 
mysql_select_db($test) or die(mysql_error()); 


function searchForm(){ 

  $searchwords = (isset($_GET['words']) ? htmlspecialchars(stripslashes($_REQUEST['words'])) : ''); 
  $Booth Number = (($_GET['by'] == 'Booth Number') ? ' selected="selected"' : '' ); 
  $Booth Status = (($_GET['by'] == 'Booth Status') ? ' selected="selected"' : '' ); 
  $Roof = (($_GET['by'] == 'Roof') ? 'selected="selected"' : '' );
  $Tables = (($_GET['by'] == 'Tables') ? ' selected="selected"' : '' ); 
  $Racks = (($_GET['by'] == 'Racks') ? ' selected="selected"' : '' );
  $Platform Type = (($_GET['by'] == 'Platform Type') ? ' selected="selected"' : '' );


  echo '<form action="'.$_SERVER['PHP_SELF'].'" method="GET">'."\n"; 
  echo '<input type="hidden" name="cmd" value="search">'."\n"; 
  echo 'Search For: <input type="text" name="words" value="'.$searchwords.'"> '."\n"; 
  echo 'Search By: '."\n"; 
  echo '<select name="by">'."\n"; 
  echo '<option value="Booth Number" '.$Booth Number.'>Booth Number</option>'."\n"; 
  echo '<option value="Booth Status" '.$Booth Status.'>Booth Status</option>'."\n"; 
  echo '<option value="Roof" '.$Roof.'>Roof</option>'."\n"; 
  echo '<option value="Tables" '.$Tables.'>Tables</option>'."\n";
  echo '<option value="Racks" '.$Racks.'>Racks</option>'."\n";
  echo '<option value="Platform Type" '.$Platform Type.'>Platform Type</option>'."\n";
  echo '<input type="submit" value="Search">'."\n"; 
} 


$cmd = (isset($_GET['cmd']) ? $_GET['cmd'] : ''); 


switch($cmd) 
{ 
  default: //If no search words have been entered.... 
    echo '<h1 align=center>Search Database!</h1>'."\n"; 
    echo '<h2>Our Search Form</h2>'."\n"; 
    echo '<p>Type in your keywords below.</p>'."\n"; 
    searchForm(); 

  break; 


  case "search":  

    echo '<h1 align=center>Search Results</h1>'; 
    searchForm(); 
    echo '<h3>Search Results:</h3><br />'; 

$by = $_GET['by'];  
$searchstring = mysql_escape_string($_GET['words']); 

switch($by){ 
case "Booth Number": 
    $sql = "SELECT `Booth Number`, `Booth Status`, `Roof`, `Tables`, `Racks`, `Platform Type`
           MATCH(`Booth Number`, `Booth Status`, `Roof`, `Tables`, `Racks`, `Platform Type`)  
           AGAINST ('$searchstring' IN BOOLEAN MODE) AS score FROM test  
           WHERE MATCH(`Booth Number`, `Booth Status`, `Roof`, `Tables`, `Racks`, `Platform Type`)  
           AGAINST ('$searchstring' IN BOOLEAN MODE) ORDER BY score BOOTH NUMBER"; 
break; 
case "Booth Status": 
    $sql = "SELECT `Booth Number`, `Booth Status`, `Roof`, `Tables`, `Racks`, `Platform Type`
           MATCH(`Booth Number`, `Booth Status`, `Roof`, `Tables`, `Racks`, `Platform Type`)  
           AGAINST ('$searchstring' IN BOOLEAN MODE) AS score FROM test  
           WHERE MATCH(`Booth Number`, `Booth Status`, `Roof`, `Tables`, `Racks`, `Platform Type`)  
           AGAINST ('$searchstring' IN BOOLEAN MODE) ORDER BY score BOOTH NUMBER";   
break; 
case "Roof": 
    $sql = "SELECT `Booth Number`, `Booth Status`, `Roof`, `Tables`, `Racks`, `Platform Type`
           MATCH(`Booth Number`, `Booth Status`, `Roof`, `Tables`, `Racks`, `Platform Type`)  
           AGAINST ('$searchstring' IN BOOLEAN MODE) AS score FROM test  
           WHERE MATCH(`Booth Number`, `Booth Status`, `Roof`, `Tables`, `Racks`, `Platform Type`)  
           AGAINST ('$searchstring' IN BOOLEAN MODE) ORDER BY score BOOTH NUMBER"; 
break; 
case "Tables": 
    $sql = "SELECT `Booth Number`, `Booth Status`, `Roof`, `Tables`, `Racks`, `Platform Type`
           MATCH(`Booth Number`, `Booth Status`, `Roof`, `Tables`, `Racks`, `Platform Type`)  
           AGAINST ('$searchstring' IN BOOLEAN MODE) AS score FROM test  
           WHERE MATCH(`Booth Number`, `Booth Status`, `Roof`, `Tables`, `Racks`, `Platform Type`)  
           AGAINST ('$searchstring' IN BOOLEAN MODE) ORDER BY score BOOTH NUMBER"; 
break; 
case "Racks": 
    $sql = "SELECT `Booth Number`, `Booth Status`, `Roof`, `Tables`, `Racks`, `Platform Type`
           MATCH(`Booth Number`, `Booth Status`, `Roof`, `Tables`, `Racks`, `Platform Type`)  
           AGAINST ('$searchstring' IN BOOLEAN MODE) AS score FROM test  
           WHERE MATCH(`Booth Number`, `Booth Status`, `Roof`, `Tables`, `Racks`, `Platform Type`)  
           AGAINST ('$searchstring' IN BOOLEAN MODE) ORDER BY score BOOTH NUMBER"; 
break; 
case "Platform Type": 
    $sql = "SELECT `Booth Number`, `Booth Status`, `Roof`, `Tables`, `Racks`, `Platform Type`
           MATCH(`Booth Number`, `Booth Status`, `Roof`, `Tables`, `Racks`, `Platform Type`)  
           AGAINST ('$searchstring' IN BOOLEAN MODE) AS score FROM test  
           WHERE MATCH(`Booth Number`, `Booth Status`, `Roof`, `Tables`, `Racks`, `Platform Type`)  
           AGAINST ('$searchstring' IN BOOLEAN MODE) ORDER BY score BOOTH NUMBER"; 
break; 
} 


$result = mysql_query($sql) or die (mysql_error()); 


switch($by){ 
default: 
searchForm(); 
break; 
case "Booth Number": 
    while($row = mysql_fetch_object($result)){ 
    echo $row->title ."\n<br>"; 
    echo $row->desc ."\n<br>"; 
    echo '<a href="'. $row->link .'">I'll insert link later</a>'."<br>\n"; 
    echo '<hr size="1">'."\n"; 
    } 
break; 
case "Booth Status": 
    while($row = mysql_fetch_object($result)){ 
    echo '<a href="'. $row->link .'">I'll insert link later</a>'."\n<br>"; 
    echo '<hr size="1">'."\n"; 
    } 
break; 
case "Roof": 
    while($row = mysql_fetch_object($result)){ 
    echo $row->desc ."\n<br>"; 
    echo '<a href="'. $row->link .'">I'll insert link later</a>'."\n<br>"; 
    echo '<hr size="1">'."\n"; 
    } 
break; 
} 
case "Tables": 
        while($row = mysql_fetch_object($result)){ 
        echo $row->title ."\n<br>"; 
        echo $row->desc ."\n<br>"; 
        echo '<a href="'. $row->link .'">I'll insert link later</a>'."<br>\n"; 
        echo '<hr size="1">'."\n"; 
        } 
case "Racks": 
        while($row = mysql_fetch_object($result)){ 
        echo $row->title ."\n<br>"; 
        echo $row->desc ."\n<br>"; 
        echo '<a href="'. $row->link .'">I'll insert link later</a>'."<br>\n"; 
        echo '<hr size="1">'."\n"; 
        } 
case "Platform Type": 
        while($row = mysql_fetch_object($result)){ 
        echo $row->title ."\n<br>"; 
        echo $row->desc ."\n<br>"; 
        echo '<a href="'. $row->link .'">I'll insert link later</a>'."<br>\n"; 
        echo '<hr size="1">'."\n"; 
        } 
} 
} 
?>

I found this example online and I tried to change the names to the files I'm using. My database is called "test" and the table within is called "booths" I uploaded the file to the server and tried to test it but I got a

Could not connect

error message. That's whats giving me the biggest headache, getting that search file going. Now that I know that I can use the checkbox search form that I wanted, all that's left is getting the search page going and then getting the whole she-bang-bang working as one. Any suggestions, anybody?

okram

jf1243 wrote:

<?php 
$sql_host = "localhost"; 
?>

and

mysql_connect($localhost, $username, $pass) or die("Could not connect");

At least a little typo here. ;-)

$localhost => $sql_host

-okram

jf1243

Technically wouldn't changing

$localhost => $sql_host

be the same thing? I was putting the server address for localhost in the first file so I would put "web.server.edu" on $sql_host as well as $localhost on the second file. I don't know, I could be wrong, hence the reason I'm asking