problems with searching my sql database

[svgmx5]

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
							}
						?>

 

[ansharma]

You should use full text searching for your table

and use MATCH('col1','col2') AGAINST('keyword') query

 

more explaination

http://www.petefreitag.com/item/477.cfm