Query Question???

Anon

Hello Everyone,
I am trying to write a query that will bring up specific records based on the unit. I have created a drop down on my form which has the units listed and once selected I would like to see only the records that are assigned to that unit.

Here is the form:
<?
include("config.inc");

$db_name = "$db";

$table_name = "units";

$connection = @mysql_connect("$server", "$user", "$password") or
die("Couldn't connect.");

$db = @mysql_select_db($db_name, $connection) or die("Couldn't select
database.");

$sql = "
SELECT id, unit
FROM $table_name
ORDER BY unit
";

$result = @($sql,$connection) or die("Couldn't execute query.");

while ($row = mysql_fetch_array($result)) {

$unit = $row['unit'];

$option_block .= "<option value=\"$unit\">$unit</option>";

}

  <form method="post" action="view_lsvbyunit.php">
  <SELECT NAME="org">
  <option selected>View By Unit</option>
  <? echo "$option_block"; ?>
  </SELECT>
  <input type="submit" name="submit" value="Go">
  </form>

I am not sure how to write the query to retrieve the information. There might be a better way of doing it, but I am on a brain dump right now...

Any help would be gratefully appreciated.

Thanks
:o) Ivan

Anon

in "view_lsvbyunit.php" you could write:

<?
$result = mysql_query ("SELECT unit FROM $table_name WHERE unit = '$org'");
?>

Anon

Ok I still don't get it. Maybe I explain it wrong. This is what I have. Will this work?

Here is my form:

  <form method="post" action="view_singleunit.php">
  <SELECT NAME="org">
  <option selected>View By Unit</option>
  <? echo "$option_block"; ?>
  </SELECT>
  <input type="submit" name="submit" value="Go">
  </form>

Here is my view_singleunit.php:

include("config.inc");

$db_name = "$db";

$table_name = "lsv";

$connection = @mysql_connect("$server", "$user", "$password") or
die("Couldn't connect.");

$db = @mysql_select_db($db_name, $connection) or die("Couldn't select
database.");

$chk_id = "SELECT org from $table_name WHERE org = \"$org\"";

$chk_id_res = @($chk_id,$connection) or die("Couldn't execute query.");

$chk_id_num = mysql_num_rows($chk_id_res);

if ($chk_id_num == "0") {

header("location: $indexurl/index.htm");
exit;

} else {

$sql = "
SELECT *
from $table_name
where $table_name.org = \"$org\"
";

$result = @($sql,$connection) or die("Couldn't execute query.");

while ($row = mysql_fetch_array($result)) {

$reg = $row['reg'];
$sn = $row['sn'];
$make = $row['make'];
$model = $row['model'];
$type = $row['type'];
$adate = $row['adate'];
$offsym = $row['offsym'];
$cc = $row['cc'];
$vco_n = $row['vco_n'];
$vco_e = $row['vco_e'];
$vcnco_n = $row['vcnco_n'];
$vcnco_e = $row['vcnco_e'];
$org = $row['org'];
$remarks = $row['remarks'];

}
}

$the_lsv = "

<tr>

<td bgcolor=#800000><font size=1 face=arial color=#ffffff>Registration:</font></td>

<td bgcolor=#800000><font size=1 face=arial color=#ffffff>Serial Number:</font></td>

<td bgcolor=#800000><font size=1 face=arial color=#ffffff>Organization:</font></td>

<td bgcolor=#800000><font size=1 face=arial color=#ffffff>Office Stmbol:</font></td>

<td bgcolor=#800000><font size=1 face=arial color=#ffffff>Phone:</font></td>

<td bgcolor=#800000><font size=1 face=arial color=#ffffff>Maint:</font></td>

<td bgcolor=#800000><font size=1 face=arial color=#ffffff>Mileage:</font></td>

<td bgcolor=#800000><font size=1 face=arial color=#ffffff>Waiver:</font></td>

</tr>

while ($row = mysql_fetch_array($result)) {

$vid = $row['vid'];

$reg = $row['reg'];

$sn = $row['sn'];

$org = $row['org'];

$offsym = $row['offsym'];

$vco_n = $row['vco_n'];

$vco_e = $row['vco_e'];

$the_lsv .= "<tr>

<td bgcolor=#ECDC94><font size=1 face=arial>$offsym</font></td>

<td bgcolor=#ECDC94><font size=1 face=arial><center><img src=\"images/arrow.gif\" border=\"0\"></center></font></td>
";
}

$the_lsv .= "</tr></table>";

Anon

Ok I still don't get it. Maybe I explain it wrong. This is what I have. Will this work?

Here is my form:

Here is my view_singleunit.php:

include("config.inc");

$db_name = "$db";

$table_name = "lsv";

$connection = @mysql_connect("$server", "$user", "$password") or
die("Couldn't connect.");

$db = @mysql_select_db($db_name, $connection) or die("Couldn't select
database.");

$chk_id = "SELECT org from $table_name WHERE org = \"$org\"";

$chk_id_res = @($chk_id,$connection) or die("Couldn't execute query.");

$chk_id_num = mysql_num_rows($chk_id_res);

if ($chk_id_num == "0") {

header("location: $indexurl/index.htm");
exit;

} else {

$sql = "
SELECT *
from $table_name
where $table_name.org = \"$org\"
";

$result = @($sql,$connection) or die("Couldn't execute query.");

while ($row = mysql_fetch_array($result)) {

}
}

$the_lsv = "

<tr>

<td bgcolor=#800000>Registration:</td>

<td bgcolor=#800000>Serial Number:</td>

<td bgcolor=#800000>Organization:</td>

<td bgcolor=#800000>Office Stmbol:</td>

<td bgcolor=#800000>Phone:</td>

<td bgcolor=#800000>Maint:</td>

<td bgcolor=#800000>Mileage:</td>

<td bgcolor=#800000>Waiver:</td>

</tr>

while ($row = mysql_fetch_array($result)) {

$vid = $row['vid'];

$reg = $row['reg'];

$sn = $row['sn'];

$org = $row['org'];

$offsym = $row['offsym'];

$vco_n = $row['vco_n'];

$vco_e = $row['vco_e'];

$the_lsv .= "<tr>

<td bgcolor=#ECDC94>$offsym</td>

<td bgcolor=#ECDC94><center><img src=\"images/arrow.gif\" border=\"0\"></center></td>
";
}

$the_lsv .= "</tr></table>";

Thanks for any help.
:o) Ivan

Anon

you could do away with all those:
$reg = $row['reg'];
lines by just using one line:
extract ($row);

Anon

Still no work... I think it might have somrthing to do with the $sql statement??? All help is welcomed...

  <form method="post" action="view_singleunit.php">
  <SELECT NAME="org">
  <option selected>View By Unit</option>
  <? echo "$option_block"; ?>
  </SELECT>
  <input type="submit" name="submit" value="Go">
  </form>

include("config.inc");

$db_name = "$db";

$table_name = "lsv";

$connection = @mysql_connect("$server", "$user", "$password") or
die("Couldn't connect.");

$db = @mysql_select_db($db_name, $connection) or die("Couldn't select
database.");

$chk_id = "SELECT org from $table_name WHERE org = \"$org\"";

$chk_id_res = @($chk_id,$connection) or die("Couldn't execute query.");

$chk_id_num = mysql_num_rows($chk_id_res);

if ($chk_id_num == "0") {

header("location: $indexurl/index.htm");
exit;

} else {

$sql = "
SELECT *
from $table_name
where org = \"$org\"
";

$result = @($sql,$connection) or die("Couldn't execute query.");

while ($row = mysql_fetch_array($result)) {

extract ($row);

}
}

Thanks
:o) Ivan