PHP, MySQL and two simple forms to search (HELP!)

[epimetheus]

Hi all,

I'm completely new to Apache, PHP and mysql, but I feel like I've made big strides in just my first few days. FWIW, I'm running AMP on my OS X 10.3.4 G5.

Ok, here's the deal -- I created a table in SQL that will track product rebates. Table contains model no, brand, start date, end date, and mail date.

So far, no problems with PHP sucking in the table and displaying the data in nice alternating rows.

Here's the issue --

What I'd like to do is for the end user to be able to search for a model, or select from a dropdown of brands.

So far, I've been able to build a dynamic dropdown to auto-populate the menu (ie, "SELECT DISTINCT brand FROM rebates ORDER by brand").

The problem is that selecting a brand from the dropdown doesn't return rows of model nos = to that specific brand (ie, my dropdown is totally disconnected from returing rows).

Here's my code (minus the usual connection stuff):

--

// this displays everything from the table
$sql = "select * from rebates order by brand";

// check to make sure the query went through okay
if ($get_first_query = mysql_query($sql)) {
$num_items = mysql_num_rows($get_first_query);
?>

<?php

print "<form name=brand method=\"POST\" action=\"rebates.php\">
<SELECT name=\"brand\" size=1>
<OPTION>Select Brand";
$query="SELECT DISTINCT brand FROM rebates ORDER BY brand";
$result = mysql_query($query) or die (mysql_error());
while($row = mysql_fetch_object($result))
{
echo '<option value="' . $row->brand . '">' . $row->brand . '</option>';
}
print "</SELECT>
<input type=\"submit\" name=\"submit\" value=\"Go\">
</form>";
?>

<?
echo "<table width=600 cellspacing=0 cellpadding=5><td width=125>Item No</td><td width=125>Brand</td><td width=125>Start Date</td><td width=125>End Date</td><td width=125>Mail Date</td>";

// if there were any matching results, continue
if ($num_items > 0 ) {
$rowcolor = 0;
while (list($item_no, $brand, $start_date, $stop_date, $mail_date) = mysql_fetch_row($get_first_query)){
$rowcolor++;
$report_class = ($rowcolor % 2) ? '#ececec' : '#ffffcc';
echo "<tr>\n";
echo "<td bgcolor=\"".$report_class."\"><b>$item_no<b></td>\n";
echo "<td bgcolor=\"".$report_class."\">$brand</td>\n";
echo "<td bgcolor=\"".$report_class."\">$start_date</td>\n";
echo "<td bgcolor=\"".$report_class."\">$stop_date</td>\n";
echo "<td bgcolor=\"".$report_class."\">$mail_date</td>\n";
echo "</tr>\n";
} // endwhile
echo "</table>";

}
}
?>

--

Thanks for any help, comments, etc. Also, I have no idea how I would even make a search form to search by model number. How would I plug in a query like 'select item_no from table like %string%'?

Wow, I either need sleep or more caffeine. 🙂

TIA!

[BooRadLey]

hello there, what you could do is display the form, and then if the form is submitted it will display whatever brand is chosen:

<?php 
// connection stuff

// create form
print "<form name=brand method=\"POST\" action=\"rebates.php\"> 
<SELECT name=\"brand\" size=1> 
<OPTION>Select Brand</OPTION>"; 
$query="SELECT DISTINCT brand FROM rebates ORDER BY brand"; 
$result = mysql_query($query) or die (mysql_error()); 
while($row = mysql_fetch_object($result)) 
{ 
  echo '<option value="' . $row->brand . '">' . $row->brand . '</option>'; 
} 
print "</SELECT> 
<input type=\"submit\" name=\"submit\" value=\"Go\"> 
<input type=\"hidden\" name=\"getBrand\" value=\"true\">
</form>";

// if form is submitted display info
if($_POST['getBrand'] == "true") {
  $brand = $_POST['brand'];

  // this displays selected brand from the table 
  $sql = "select * from rebates WHERE brand ='". $brand . "' order by brand"; 

  // check to make sure the query went through okay 
  if ($result = mysql_query($sql)) { 
  $num_items = mysql_num_rows($result);

if($num_items > 0) {
  echo "<table width=600 cellspacing=0 cellpadding=5><td width=125>Item No</td><td width=125>Brand</td><td width=125>Start Date</td><td width=125>End Date</td><td width=125>Mail Date</td></tr>"; 
  while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
     echo "<tr>\n"; 
     echo "<td bgcolor=\"".$report_class."\"><b>".$row['item_no']."<b></td>\n"; 
     echo "<td bgcolor=\"".$report_class."\"><b>".$row['brand']."<b></td>\n"; 
     echo "<td bgcolor=\"".$report_class."\"><b>".$row['start_date']."<b></td>\n"; 
    echo "<td bgcolor=\"".$report_class."\"><b>".$row['stop_date']."<b></td>\n"; 
    echo "<td bgcolor=\"".$report_class."\"><b>".$row['mail_date']."<b></td>\n"; 
    echo "</tr>\n";
  }
  echo "</table>";
}
  }
}
?>

There may be some errors in this code, and for all I know I might have totally messed something up, but try it out and see what happens, I hope I've helped, if not with a working example, a push in the right direction 😃

Brad

[epimetheus]

Originally posted by BooRadLey
There may be some errors in this code, and for all I know I might have totally messed something up, but try it out and see what happens, I hope I've helped, if not with a working example, a push in the right direction 😃

Brad

Thanks for the help, Brad. For some reason in your example it keeps getting a parse error with the line

<OPTION>Select Brand</OPTION>";_

I'll see if i can wrap my head around this tonight and figure it out.

[epimetheus]

Sorry for being a pest, but does anybody else have an idea of what I'm doing wrong?

My PHP book doesn't cover specifically what I'm trying to do, so I'm struggling to cobble this together from an array of code examplse I can find online. Thanks for any help/hints... I might have to start from scratch together with my book and text editor! 😃