PHP - Searching Content From A Database

i'm working on a project that stores records of architecture and architects.

What i have to do is create a way for users to search the records in a simple way.

The user can search by Building name, Architect, Location, Date it was built, Building type and architecture style.

Currently i can only search properly by building name, date, type and style. If i add architect to the search query the records screw up when i try to search for the other fields individually..so for example if i add the architect to the query and try to only search by building name the results get screwed up and it displays more results that don't have nothing in comon rather than just display one result which would be the building name

Like i said this only happens if i add the "architect" to the query


Since the architect, location, date, type and style are stored in different tables i search for them first and get the id. Once i've grabbed the ID for those fields i do the actual search to get the buildings that match those records by ID

In the actual query i use the OR statement to search all 5 fields regardless if the user left them empty. So if th user only searches for a building name it the query will basically search for the buildingID OR the rest of the fields as well.

I hope someone here can help me figure out why when i add the architect field to the query the results get screwed up.

I have the code displayed below:

<?php

if(isset($_POST['search'])){

$building  = mysql_real_escape_string($_POST['building']);

$architect = stringForUrl($_POST['architect']);

$date = mysql_real_escape_string($_POST['date']);

$style = mysql_real_escape_string($_POST['style']);

$type = mysql_real_escape_string($_POST['type']);

$find_building = mysql_query("SELECT * FROM points WHERE name LIKE '$building'") or die(mysql_error());

$tmp_building = mysql_fetch_assoc($find_building);

$buildingID = $tmp_building['id'];

$find_architect = mysql_query("SELECT * FROM architects WHERE full_name LIKE '$architect'") or die(mysql_error());

$tmp_architect = mysql_fetch_assoc($find_architect);

$architectID = $tmp_architect['id'];

$find_style = mysql_query("SELECT * FROM styles WHERE name LIKE '$style'") or die(mysql_error());

$tmp_style = mysql_fetch_assoc($find_style);

$styleID = $tmp_style['id'];

$find_type = mysql_query("SELECT * FROM types WHERE name LIKE '$type'") or die(mysql_error());

$tmp_type = mysql_fetch_assoc($find_type);

$typeID = $tmp_type['id'];

}

?>
<?php

if(isset($_POST['search'])){

$session_arch_id = $_SESSION['architect_id'];
//query that does the actual search 
$get_search_points = mysql_query("SELECT * FROM points WHERE id='$buildingID' OR style_id='$styleID' OR type_id='$typeID'  OR architect_id='$architectID' OR date='$date' ORDER BY name") or die(mysql_error());

$num_search_points = mysql_num_rows($get_search_points);

print($get_search_points);

?>
                            <h3>Architecure on the map</h3>
                            <span id="num_buildings">Showing <?php echo $num_search_points; ?> buildings</span>
                            <table width="605" cellspacing="0" cellpadding="5" border="0" id="wam_results-tbl">
                            

<tr class="search_headings">
                                

<td width="156">Building Name</td>
                                    <td width="114">Architect</td>
                                    <td width="45">Date</td>
                                    <td width="144">Building Type</td>
                                    <td width="96">Style</td>
                                </tr>
                                <?php

while($points = mysql_fetch_assoc($get_search_points)){

$point_type_id = $points['type_id'];

$point_style_id = $points['style_id'];

$point_architect_id = $points['architect_id'];

$point_firm_id = $points['firm_id'];

?>
                                <tr>
                                

<td><?php echo $points['name']; ?></td>
                                    <td>
                                    <?php

if(!empty($point_architect_id)){

$get_architect = mysql_query("SELECT * FROM architects WHERE id='$point_architect_id'") or die(mysql_error());

$architect = mysql_fetch_assoc($get_architect);

echo ''.$architect['first_name'].' '.$architect['last_name'].'';

}elseif(empty($point_architect_id)){

$get_firm = mysql_query("SELECT * FROM firms WHERE id='$point_firm_id'") or die(mysql_error());

$firm = mysql_fetch_assoc($get_firm);

echo $firm['name'];

}

?>
                                    </td>
                                    <td><?php echo $points['date']; ?></td>
                                    <td>
                                    <?php

$get_types = mysql_query("SELECT * FROM types WHERE id='$point_type_id'") or die(mysql_error());

$type = mysql_fetch_assoc($get_types);

echo $type['name'];

?>
                                    </td>
                                    <td>
                                    <?php

$get_styles = mysql_query("SELECT * FROM styles WHERE id='$point_style_id'") or die(mysql_error());

$style = mysql_fetch_assoc($get_styles);

echo $style['name'];

?>
                                    </td>
                                </tr>
                                <?php

}

?>
                            </table>
                            <?php

}

?>

Hi guys,

I want a search to be done.

 That is when the user type the relevant customer id and clicks on search button the details of the customer should appear in a form. Therefore i tried the following code but it generates an empty form.

All the customer details were stored in the database under customer table.
customer(customer_id, full_name, name_with_initials, address, contact_number, gender)

Can anyone show me where i went wrong?



<?php
$connect=mysql_connect("localhost","root","");
mysql_select_db("bank",$connect) or die ("could not select database");
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<style type="text/css">
<!--
#apDiv4 {
position:absolute;
width:776px;
height:598px;
z-index:3;
left: 206px;
top: 300px;
}
#form1 {
font-size: 18px;
font-weight: bold;
}
body,td,th {
font-size: 18px;
}
-->
</style>
</head>

<body>


   
<div id="apDiv4">
  <form action="" method="post" name="form1" id="form1">
    <fieldset>
      <legend class="cap"> Customer Details</legend>
      <table width="85%" height="350" border="0" align="center" cellpadding="5" cellspacing="0">
      <?php
           if(isset($_POST['customer_id'])){ 
        $customer_id=$_POST['customer_id']; 
       
           $query = "select * from customer where customer_id=" .$customer_id;
   $result = mysql_query($query) or die(mysql_error());
   while ($row=mysql_fetch_array($result)){
   
   }
  ?>
      
        <tr>
          <td>&nbsp;</td>
          <td class="title02">&nbsp;</td>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
        </tr>
        <tr height="30">
          <td width="2%" height="35">&nbsp;</td>
          <td width="46%" class="title02" align="left">National ID</td>
          <td width="50%" class="attribute1" align="left"><input type="text" name="nic" size="30"   class="attribute1" value="<?php echo $row['nic'];?>"></td>
          <td width="2%">&nbsp;</td>
        </tr>
        <tr height="30">
        <td height="33">&nbsp;</td>
        <td width="46%" class="title02" align="left">Full Name</td>
        <td width="50%" class="attribute1" align="left"><input type="text" name="full_name" size="30"   class="attribute1" value="<?php echo $row['full_name'];?>"></td>
          <td width="2%">&nbsp;</td>
        </tr>
        <tr height="30">
          <td height="34">&nbsp;</td>
          <td class="title02" align="left">Name With Initials</td>
          <td width="50%" class="attribute1" align="left"><input type="text" name="name_with_initials" size="30"   class="attribute1" value="<?php echo $row['name_with_initials'];?>"></td>
        </tr>
        <tr height="30">
          <td width="2%">&nbsp;</td>
          <td width="46%" class="title02" align="left">Address</td>
          <td width="50%" class="attribute1" align="left"><label>
            <textarea name="address" id="textarea" cols="45" rows="5"><?php echo $row['address']; ?></textarea>
          </label></td>
          <td width="2%">&nbsp;</td>
        </tr>
        <tr height="30">
          <td width="2%">&nbsp;</td>
          <td width="46%" class="title02" align="left">Contact Number</td>
          <td width="50%" class="attribute1" align="left"><input type="text" name="contact_number" size="30" class="attribute1" value="<?php echo $row['contact_number']?>"></td>
          <td width="2%">&nbsp;</td>
        </tr>
        <tr height="30">
          <td width="2%">&nbsp;</td>
          <td width="46%" class="title02" align="left">Sex</td>
          <td width="50%" class="attribute1" align="left"><p>
   <select name="gender" id="jumpMenu" >
          <option selected="selected"><?php echo $row['gender']; ?></option>
            
            <option>Male</option>
            <option>Female</option>
          </select>          
          <td width="50%" class="attribute1" align="left">&nbsp;</td>
            
            <br />
          </p></td>
          <td width="2%">&nbsp;</td>
        </tr>
        
      </table>
      <p align="center">&nbsp;</p>
      <p align="center">
        <label>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </label>
        <label>
          <input name="button" type="submit" id="button"  value="Approve Account"  />
          &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </label>
        <label> <a href="accsup_help.php">
          <input name="button" type="submit"  id="button"   value="Help"  />
        </a></label>
        </td>
      </p>
    </fieldset>
    <td width="5%">&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td align="center">&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td><font color="red" size="1" ></font></td>
      <td>&nbsp;</td>
    </tr>
    <?php
   }
   
   ?>
      
    </table>
  </form>
</div>
<img src="../images/mahapitiya 1.jpg" width="1024" height="139" />
</body>
</html>



Thanks,

Hey guys, im new around here, and i have just recently started teaching myself PHP, by looks of this site i seem very experienced but im eager to learn

I seem to get an error with this code. The point of this code is to look into my database and pull out all entries with the first name _____ (whatever the user inputs)

Error: Warning: mysql_fetch_array() expects parameter 1 to be resource, null given in C:\xampp\htdocs\searchans.php on line 12

Code: Filename:search.php
Code: [Select]
<html>
<form action='searchans.php' method='POST'>
First Name: <input type='text' name='firstname'><br>
<input type='submit' value='Search'>
</form>


</html>Code: Filename:searchans.php
Code: [Select]
<?php

$firstname = $_POST['firstname'];

$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

 
 
 $query = mysql_query("SELECT * FROM Persons WHERE FirstName='$firstname'");
 
 while($row = mysql_fetch_array($result))
  {
  echo $row['FirstName'] . " " . $row['LastName'] . " " . $row['Age'];
  echo "<br />";
  }
 
 
?>

Hello,

I have this code running and I keep getting
"Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /home/.../search.php on line 10

Any help would be appreciated

   <table><tr><td>ID</td><td>SCHOOL NAME</td><td>TEACHER NAME</td></tr>
<?php 
  if(isset($_POST['submit'])){ 
  if(isset($_GET['go'])){ 
  if(preg_match("/^[  a-zA-Z]+/", $_POST['name'])){ 
  $name=$_POST['name']; 
  $db=mysql_connect  ("", "", "") or die ('I cannot connect to the database  because: ' . mysql_error()); 
  $mydb=mysql_select_db(""); 
  $sql="SELECT  ID, school_name, teacher_name FROM Project_Registration WHERE school_name LIKE '%" . $name .  "%'"; 
  $result=mysql_query($sql); 
  while($row = mysql_fetch_array($result, MYSQL_ASSOC)){ 
      echo 
         "<tr><td> {$row['ID']}                           </td>" .
         "<td>     {$row['school_name']}             </td>" .
         "<td>     {$row['teacher_name']}    </td></tr>";}}}}
?> 
    </table>

I think I've coded myself into a corner.  I have numerous items entered with measurement extremes - highest and lowest. Now I want somebody to be able to search for an item that could match a specific length.

So I could have several items that are entered into a database like this:

Code: [Select]
item_id  |   length  |   high_low_param
-----------+-----------+----------------------
1234      |    12 cm  |  high
-----------+-----------+---------------------
1234      |    6 cm    | low


If somebody searches for an item that is 8 cm long, I want the results to be item 1234 and any other items that span that range. I'm not sure how to do this? array_fill ? I don't know. I don't want to slow down the database too much, and I don't want to have to put an entry for every single length each item could be.

Any suggestions?