Creating a search page that looks into a mysql table

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jedi_TAW
Forum Newbie
Posts: 5
Joined: Sun Oct 09, 2005 11:36 pm

Creating a search page that looks into a mysql table

Post by jedi_TAW »

feyd | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]


Sorry if this has been asked before but I've been at it (coding) for several hours today and have "google search" my problem and can't seem to find an answer. =(

I have a Mysql database with the following tables:

Code: Select all

s_academy (field: academy)
s_level (field: level)
s_topic (field: topic)
d_course (field: id, academy, level, topic, division, title, course_num, summary)
I have a search page found here:
http://wc.four.ws./samplecode/search.php

The drop down boxes are getting their data from the "s_..." tables in the database. What I want to do it have the user select one or all of the drop down boxes, click on the "seach button" and then at the bottom of the page show the results.

I'm lost in how to get the form to give the value to the datbase.

(The entire code for tha page is posted at the bottom of this post.)

Here is what I think is probably the most important sections.

Here I specify the form:

Code: Select all

<form action="" method="get" name="search_engine" id="search_engine">
the form then goes on to put the drop boxes, and the radio buttons.

next the code goes like this:

Code: Select all

<hr>
  <?php 
    $academy_picked = @$_GET['select_academy'];
	$level_picked = @$_GET['select_level'];
	$topic_picked = @$_GET['select_topic'];
	$get_search = "SELECT * FROM d_course WHERE academy = $academy_picked";
    $got_search = mysql_query($get_search);
	while($display_search = mysql_fetch_array($got_search))
	{
	?>
  <table width="100%" border="0">
    <tr>
      <td><?php echo $display_search[academy];?></td>
    </tr>
<?php } ?></table>
This is the part that give me the error? I know the SQL code is not completely "correct" but the issue I am having is how to I get the form to give me the values on the page for my SQL string to then give the to database.

If someone could tell me how that is done the rest I can do.

1) Check to see which fields the user picked
2) Build the SQL statement based on what the user picked
3) Format the search table to show all it's fields
4) If no results display a message as such.

Thanks

***FULL CODE***

Code: Select all

<?php
/////////////////////////////////////////////////////
//              DATABASE CONNECTION                //
/////////////////////////////////////////////////////

// Setting up connection variables to the database //
 include("/var/www/html/access_files/config_wc.php");

// Connecting to the database
mysql_connect($dbhost, $dbuname, $dbpass);
@mysql_select_db("$dbname") or die ("Unable to select database");
?>

<html>
<head>
<title>WC Search Engine</title>
<link rel="stylesheet" type="text/css"
href="http://www.theartofwarfare.net/ftp/wc/template_for_html/taw.css" />
</head>

<body>
<div align="center">
  <p><a name="TOP" id="TOP"></a> <img border="0" src="http://www.theartofwarfare.net/ftp/wc/template_for_html/g_taw.gif" width="683" height="77"></p>
  <table width="100%" border="0">
    <tr>
      <td><div align="center">
          <h1>War College Catalog Search Engine</h1>
        </div></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><form action="" method="get" name="search_engine" id="search_engine">
          <table width="100%" border="0">
            <tr> 
              <td><div align="center"> Academy </div></td>
              <td><div align="center">Level</div></td>
              <td><div align="center">Topic</div></td>
            </tr>
            <tr> 
              <td><div align="center"> 
                  <select name="select_academy" id="select_academy">
                    <option>--- Academy ---</option>
                    <?php
					   $get_academy = "SELECT * FROM s_academy";
					   $got_academy = mysql_query($get_academy);
					   while($display_academy = mysql_fetch_array($got_academy))
					   {
					   echo "<option value=$display_academy[academy]>$display_academy[academy]</option>";
					   }
					   ?>
                  </select>
                </div></td>
              <td><div align="center"> 
                  <select name="select_level" id="select_level">
                    <option>--- Level ---</option>
                    <?php
					   $get_level = "SELECT * FROM s_level";
					   $got_level = mysql_query($get_level);
				 	   while($display_level = mysql_fetch_array($got_level))
					   {
					   echo "<option value=$display_level[level]>$display_level[level]</option>";
					   }
					?>
                  </select>
                </div></td>
              <td><div align="center"> 
                  <select name="select_topic" id="select_topic">
                    <option>--- Topic ---</option>
                    <?php
					   $get_topic = "SELECT * FROM s_topic";
					   $got_topic = mysql_query($get_topic);
					   while($display_topic = mysql_fetch_array($got_topic))
					   {
						echo "<option value=$display_topic[topic]>$display_topic[topic]</option>";
					   }
					?>
                  </select>
                </div></td>
            </tr>
            <tr> 
              <td colspan="3">Optional filters</td>
            </tr>
            <tr> 
              <td colspan="3"><font size="-2">Search <strong>ONLY</strong> within 
                this division: </font> <table width="100%" border="0">
                  <tr> 
                    <td><font size="-2"> 
                      <input type="radio" name="rbtn_aa" value="AA">
                      America's Army</font></td>
                    <td><font size="-2"> 
                      <input type="radio" name="rbtn_bf" value="BF">
                      Battlefield</font></td>
                    <td><font size="-2"> 
                      <input type="radio" name="rbtn_cd" value="CD">
                      Call of Duty</font></td>
                    <td><font size="-2"> 
                      <input type="radio" name="rbtn_df" value="DF">
                      Delta Force</font></td>
                    <td><font size="-2"> 
                      <input type="radio" name="rbtn_fc" value="FC">
                      Fry Cry</font></td>
                    <td><font size="-2"> 
                      <input type="radio" name="rbtn_gr" value="GR">
                      Ghost Recon</font></td>
                  </tr>
                  <tr> 
                    <td><font size="-2"> 
                      <input type="radio" name="rbtn_hl" value="HL">
                      Half Life</font></td>
                    <td><font size="-2"> 
                      <input type="radio" name="rbtn_jo" value="JO">
                      Joint Operations</font></td>
                    <td><font size="-2"> 
                      <input type="radio" name="rbtn_rs" value="RS">
                      Raven Sheild</font></td>
                    <td><font size="-2"> 
                      <input type="radio" name="rbtn_sm" value="SM">
                      Simulators</font></td>
                    <td><font size="-2"> 
                      <input type="radio" name="rbtn_sw" value="SW">
                      Star Wars</font></td>
                    <td><font size="-2"> 
                      <input type="radio" name="rbtn_st" value="ST">
                      Strategy</font></td>
                  </tr>
                </table></td>
            </tr>
            <tr> 
              <td colspan="3"><div align="center"> 
                  <input name="btn_search" type="submit" id="btn_search" value="Search">
                  <input type="reset" name="Reset" value="Reset">
                </div></td>
            </tr>
          </table>
        </form></td>
    </tr>
  </table>
  <p><hr>
  <?php 
    $academy_picked = @$_GET['select_academy'];
	$level_picked = @$_GET['select_level'];
	$topic_picked = @$_GET['select_topic'];
	$get_search = "SELECT * FROM d_course WHERE academy = $academy_picked";
    $got_search = mysql_query($get_search);
	while($display_search = mysql_fetch_array($got_search))
	{
	?>
  <table width="100%" border="0">
    <tr>
      <td><?php echo $display_search[academy];?></td>
    </tr>
<?php } ?></table>

  <p align="center"><font size="1">Last updated on 
    <SCRIPT LANGUAGE="JavaScript">
<!-- Begin
var days = new Array(8);
days[1] = "Sunday";
days[2] = "Monday";
days[3] = "Tuesday";
days[4] = "Wednesday";
days[5] = "Thursday";
days[6] = "Friday";
days[7] = "Saturday";
var months = new Array(13);
months[1] = "January";
months[2] = "February";
months[3] = "March";
months[4] = "April";
months[5] = "May";
months[6] = "June";
months[7] = "July";
months[8] = "August";
months[9] = "September";
months[10] = "October";
months[11] = "November";
months[12] = "December";
var dateObj = new Date(document.lastModified)
var wday = days[dateObj.getDay() + 1]
var lmonth = months[dateObj.getMonth() + 1]
var date = dateObj.getDate()
var fyear = dateObj.getYear()
if (fyear < 2000) 
fyear = fyear + 1900
document.write(wday + ", " + lmonth + " " + date + ", " + fyear)
</SCRIPT>
    </font> <font size="1"><br>
    <br>
    The Art Of Warfare Copyright &copy; 2005</font> </p>
  </div>
</body>
</html>

feyd | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

Image

please use php and code tags when nessicary
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post by n00b Saibot »

hey shiz, is that you? behind bars? 8O :lol:
jedi_TAW
Forum Newbie
Posts: 5
Joined: Sun Oct 09, 2005 11:36 pm

Post by jedi_TAW »

sorry only my 4th post. :oops:
Post Reply