Multiple MySQL Search wildcard query

Mr_Mako

Hi.

I've been searching most of the night and working with my code to attempt a MySQL search that allows up to 8 possible wildcard queries... with any possible direct queries plus wildcard queries in between.

I've located this thread in my search, which seems to point me in the right direction :

http://www.phpbuilder.com/board/showthread.php?s=&threadid=10265612&highlight=mysql+search+wildcard

However... this deals with a single wildcard query.

I'll include my code below :

<div align=center>
<center>
<form method=POST action=index.php?content=flights&search=1>
<table border=0 cellspacing=0 width=50% cellpadding=0>
  <tr bgcolor=#F1F1F1>
	<td width=50% align=center><font size=2><b>Hub Assignment :</b></font><br>
	$dd_hub_display 
	</td>
	<td width=50% align=center><font size=2><b>Flight Number :</b></font><br><input type=text name=flightnum size=10></td>
  </tr>
  <tr>
	<td width=50% align=center><font size=2><b>Departure ICAO :</b></font><br><input type=text name=depicao size=10></td>
	<td width=50% align=center><font size=2><b>Arrival ICAO :</b></font><br><input type=text name=arricao size=10></td>
  </tr>
  <tr bgcolor=#F1F1F1>
	<td width=50% align=center><font size=2><b>Departure Time (AM/PM) :</b></font><br>
	  <select size=1 name=deptime tury Gothic\">
	  <option selected></option>
	  <option>----------</option>
	  <option value=1:00>1:00</option>
	  <option value=2:00>2:00</option>
	  <option value=3:00>3:00</option>
	  <option value=4:00>4:00</option>
	  <option value=5:00>5:00</option>
	  <option value=6:00>6:00</option>
	  <option value=7:00>7:00</option>
	  <option value=8:00>8:00</option>
	  <option value=9:00>9:00</option>
	  <option value=10:00>10:00</option>
	  <option value=11:00>11:00</option>
	  <option value=12:00>12:00</option>
	  </select>

  <select size=1 name=deptimeampm tury Gothic\">
  <option selected></option>
  <option>----------</option>
  <option value=AM>AM</option>
  <option value=PM>PM</option>
  </select>
</td>
<td width=50% align=center><font size=2><b>Arrival Time (AM/PM) :</b></font><br>
  <select size=1 name=arrtime tury Gothic\">
  <option selected></option>
  <option>----------</option>
  <option value=1:00>1:00</option>
  <option value=2:00>2:00</option>
  <option value=3:00>3:00</option>
  <option value=4:00>4:00</option>
  <option value=5:00>5:00</option>
  <option value=6:00>6:00</option>
  <option value=7:00>7:00</option>
  <option value=8:00>8:00</option>
  <option value=9:00>9:00</option>
  <option value=10:00>10:00</option>
  <option value=11:00>11:00</option>
  <option value=12:00>12:00</option>
  </select>

  <select size=1 name=arrtimeampm tury Gothic\">
  <option selected></option>
  <option>----------</option>
  <option value=AM>AM</option>
  <option value=PM>PM</option>
  </select>
</td>
  </tr>
  <tr>
	<td width=100% align=center colspan=2><font size=2><b>Estimated Time Enroute (ETE) :</b></font><br>
	  <select size=1 name=ete tury Gothic\">
	  <option selected></option>
	  <option>--------------------</option>
	  <option value=1.0>1 Hour</option>
	  <option value=2.0>2 Hours</option>
	  <option value=3.0>3 Hours</option>
	  <option value=4.0>4 Hours</option>
	  <option value=5.0>5 Hours</option>
	  <option value=6.0>6 Hours</option>
	  <option value=7.0>7 Hours</option>
	  <option value=8.0>8 Hours</option>
	  <option value=9.0>9 Hours</option>
	  <option value=10.0>10 Hours</option>
	  <option value=11.0>11 Hours</option>
	  <option value=12.0>12 Hours</option>
	  <option value=13.0>13 Hours</option>
	  <option value=14.0>14 Hours</option>
	  <option value=15.0>15 Hours</option>
	  <option value=16.0>16 Hours</option>
	  </select>
	</td>
  </tr>
  <tr bgcolor=#F1F1F1>
	<td width=100% align=center colspan=2><font size=2><b>Aircraft Model :</b></font><br>
	$ac_menu_display
	</td>
  </tr>
  <tr>
	<td width=100% align=center colspan=2> </td>
  </tr>
  <tr>
	<td width=100% align=center colspan=2><input type=submit value=\"Submit Search\" name=submit class=submit></td>
  </tr>
</table>
</form>
</center>
</div>

... and the PHP code for the query (that I have now) :

	// Create Search Query //
	$search = "SELECT * FROM flight ";
		if($_POST[flight_num] == "") $search .= "flight_num LIKE '%" . $_POST[flight_num] . "%' ";
	$search_result = @mysql_query($search, $connect) or die(mysql_error());

$rt_check = mysql_num_rows($search_result);

if($rt_check == "0") {
	$display_flights = "
	    <tr>
	      <td width=11% align=center colspan=9><font size=3><b>Your Search did not return any results.</b></font></td>
	    </tr>";

} else {
	while ($row = mysql_fetch_array($search_result)) {
		$rnum = $row['flight_num'];
		$rhub = $row['flight_hub'];
		$ricaod = $row['flight_icao_dep'];
		$ricaoa = $row['flight_icao_arr'];
		$rdtime = $row['flight_dep_time'];
		$ratime = $row['flight_arr_time'];
		$rdampm = $row['flight_dep_ampm'];
		$rgdtime = $row['flight_dep_gmt'];
		$rgdampm = $row['flight_dep_gmtap'];
		$raampm = $row['flight_arr_ampm'];
		$rgatime = $row['flight_arr_gmt'];
		$rgaampm = $row['flight_arr_gmtap'];
		$reteh = $row['flight_ete_hrs'];
		$retem = $row['flight_ete_min'];
		$rac = $row['flight_aircraft'];

		$display_flights .= "
			<tr>
			  <td width=11% align=center><font size=1><b>$rnum</b></font></td>
			  <td width=11% align=center><font size=1>$rhub</font></td>
			  <td width=11% align=center><font size=1>$icoad</font></td>
			  <td width=11% align=center><font size=1>$dtime $dampm</font></td>
			  <td width=11% align=center><font size=1>$icaoa</font></td>
			  <td width=11% align=center><font size=1>$atime $aampm</font></td>
			  <td width=11% align=center><font size=1>$reteh H, $retem M</font></td>
			  <td width=11% align=center><font size=1></font></td>
			  <td width=12% align=center></td>
			</tr>
		    <tr>
		      <td width=22% colspan=2>&nbsp;</td>
		      <td width=11% align=right><b><font size=2>GMT :</font></b></td>
		      <td width=11%>$rgdtime $rgdampm</td>
		      <td width=11% align=right><font size=2><b>GMT :</b></font></td>
		      <td width=11%>$rgatime $rgaampm</td>
		      <td width=11% align=right><font size=2><b>Aircraft :</b></font></td>
		      <td width=23% colspan=2>$rac</td>
		    </tr>";
	}
}

$display_flights_table = "
	<div align=center>
	  <center>
		  <table border=0 cellspacing=0 width=75% cellpadding=0>
	    <tr bgcolor=#A9ACE7>
	      <td width=11% align=center><b><font size=2>Flight Number</font></b></td>
	      <td width=11% align=center><b><font size=2>Hub</font></b></td>
	      <td width=11% align=center><b><font size=2>Departure Airport</font></b></td>
	      <td width=11% align=center><b><font size=2>Departure Time (local)</font></b></td>
	      <td width=11% align=center><b><font size=2>Arrival Airport</font></b></td>
	      <td width=11% align=center><b><font size=2>Arrival Time (local)</font></b></td>
	      <td width=11% align=center><b><font size=2>ETE</font></b></td>
	      <td width=11% align=center><b><font size=2>Aircraft</font></b></td>
	      <td width=12% align=center><b><font size=2>Bid on Flight?</font></b></td>
	    </tr>
	    <tr>
	      <td width=11% align=center colspan=9>&nbsp;</td>
	    </tr>
		$display_flights
	  </table>
	  </center>
	</div>";

My PHP code is admittedly not complete for the query due to me attempting to try numerous attempts to get it to work.

I don't exactly know where to go from here... I'd really appreciate some help on how I would proceed, or if my code design is flawed for the type of query I'm attempting to do.

Roger_Ramjet

Don't understand what it is you are trying to do. Neither your code nor your question are clear on that.

One thing I can tell you is that your query will not work with the existing syntax, even if the php works, which I doubt. Looks to me that you are saying if the flight number posted is an empty string then add a LIKE clause to the query and include the empty string in it?? What is that about??

So, forget the code for the moment and state exactly what you are trying to do. Then I am sure you will get some more constructive help.

Mr_Mako

I guess I wasn't too clear on that was I? Sorry about that.

The example above is a search query for users on the site. They will be able to use the included form to do a search for a flight, or a list of flights, based on the search fields (ie, Flight Number, Hub, Aircraft, etc).

If someone uses the form and fills out something in every field, then of course the search query works fine.

However... if a user elects to not fill in a field (such as Aircraft, Departure time, etc), I want that empty field to work as a wildcard.

So, for example, lets say I searched for a flight, but didn't know the flight number. When I searched the database, I only filled out the Flight Hub and Flight Departure ICAO fields. All the other fields I left blank. In this example... every one of the blank fields need to be wildcard variables.

Basically... to explain it another way, any blank field needs to run as a wildcard variable in the query if it is left empty.

I hope this makes sense...

In regards to my PHP query script I know that it won't work as it is now. I've tried many things to get this to work like I need it to and what I had as the $search query before was a standard "SELECT" query with all the fields as variables (ie, "SELECT * FROM table WHERE flight_num = '$rnum' AND flight_hub = '$rhub'... etc)... which of course doesn't work if any field is left blank.

nemonoman

$query="SELECT *
FROM table 
WHERE 1";
if($rnum){
$query.=" AND flight_num=$rnum ";
}
if($rnum){
$query.=" AND flight_hub=$rhub ";
}

etc....

Roger_Ramjet

OK. If nothing is entered by the user then include a wildcard that matches all values in that field?

It is not neccessary to use a wild card to match all entries: just don't include that field in the where clause and all values will automatically be included. Where is an 'opt-out' not an 'opt-in' so there is no need to include a field if all values in that field are valid.

Now if you were using OR instead of AND then yes, a wildcard to match all entries would be correct, except that SELECT * FROM is all you would need to achieve the same effect.