Jump to content

Trying to retrieve info from ms access database by using drop down menu(s)

sandipjoshi

By sandipjoshi
in HTML Help

 Share

Recommended Posts

sandipjoshi Newbie

sandipjoshi

Posted
Posted

Hi All,

 

wonder if there is anyone out there that can help me. I am an absolute beginner with PHP and am stuck.

 

I have an MS access database, currently with only one table in it and a few records. I have set this up for test purposes only. I am trying to build a front end whereby the user can select an option by using an HTML drop down list and when they click on the 'GO' button, it posts the info and retrieves only those records from the database with that particular parameter.

 

I currently have an index.html file that sets out the frames for the main page shown here below:

 

<html>
<head>
<title>Outstanding Work for Europe</title>
</head>
<FRAMESET rows="21%,*">
<FRAME src="test.html" scrolling="no" noresize="noresize" frameborder="0">
<FRAME src="blank.html" name="resultspage">
</FRAMESET>
<body>
</body>
</html>

 

Test.html is the page in the smaller of the two frames and the idea here is to have x drop down menus and a submit/go button to retrieve the data which should then be displayed in the lower of the two frames. The code I have for this is:

 

<html>
<head>
<title>Outstanding Work for Europe</title>
</head>
<body>
<h1><center><U>Outstanding work for Europe</u></center></h1>
<br>
<br>
<h3>Please select an option:<h3>
<form method = "post" action = "test.php" target="resultspage">

<select name = country>
<option value = "all">All</option>
<option value = "france">France</option>
<option value = "spain">Spain</option>
</select>


<input type = "submit" value = "GO">
</form>
</body>
</html>

 

Now comes the bit where I am stuck. I need the results page to work as follows. When 'All' is selected all database records are selected, when 'Spain' is selected all database records with the country Spain are selected and put on screen.

 

Currently my test.php script looks like the following:

 

<html>
<head>
</head>
<body>
<?php
$conn=odbc_connect('bugzilla','','');
if (!$conn)
  {exit("Connection Failed: " . $conn);}

$sql="SELECT * FROM Bugs where Product = 'PIE/IMS Europe'";
$rs=odbc_exec($conn,$sql);
if (!$rs)
  {exit("Error in SQL");}

echo "<select><option value ='All' selected='selected'>All</option>";
echo "<option value ='France'>France</option>";
echo "<option value ='Spain'>Spain</option>";

echo "<option value ='P3'>P3</option>";
echo "<option value ='P4'>P4</option>";
echo "<option value ='P5'>P5</option></select>";
echo "<input type='submit' value='GO'>";

echo "<hr>";

echo "<table border=1 align=center><tr>";
echo "<th>ID</th>";
echo "<th>Severity</th>";
echo "<th>Priority</th>";
echo "<th>Accepted</th>";
echo "<th>Assignee</th>";
echo "<th>Status</th>";
echo "<th>Resolution</th>";
echo "<th>Summary</th></tr>";
while (odbc_fetch_row($rs))
{
  $id=odbc_result($rs,"ID");
  $severity=odbc_result($rs,"Severity");
  $priority=odbc_result($rs,"Priority");
  $accepted=odbc_result($rs,"Accepted");
  $assignee=odbc_result($rs,"Assignee");
  $status=odbc_result($rs,"Status");
  $resolution=odbc_result($rs,"Resolution");
  $summary=odbc_result($rs,"Summary");
  echo "<tr><td><a href='http://bugtracker.cpwplc.net/show_bug.cgi?id=$id' target='_blank'>$id</a></td>";
  echo "<td>$severity</td>";
  echo "<td>$priority</td>";
  echo "<td>$accepted</td>";
  echo "<td><a href='mailto:$assignee?subject=Bugzilla $id'>$assignee</a></td>";
  echo "<td>$status</td>";
  echo "<td>$resolution</td>";
  echo "<td>$summary</td></tr>";
}
odbc_close($conn);
echo "</table>";
?>
</body>
</html>

 

At this stage however it just retrieves all database records as I do not know how to get it to just retrieve what has been selected from the test.html page.

 

Please help :-)

Link to comment
Share on other sites
sandipjoshi Newbie

sandipjoshi

Posted
  • Author
Posted

I think I have just figured it out. I think the code is probably quite messy and long and it could probably be fine tuned some what (any help here would be appreciated) but this is what I currently have:

 

<html>
<head>
</head>
<body>
<?php
$conn=odbc_connect('bugzilla','','');
if (!$conn)
  {exit("Connection Failed: " . $conn);}

if ($_POST['country'] == 'All')
{
	$sql="SELECT * FROM Bugs where Product = 'PIE/IMS Europe'";
}
elseif ($_POST['country'] == 'France')
{
	$sql="SELECT * FROM Bugs where Product = 'PIE/IMS Europe' and Country = 'France'";
}
elseif ($_POST['country'] == 'Spain')
{
	$sql="SELECT * FROM Bugs where Product = 'PIE/IMS Europe' and Country = 'Spain'";
}
$rs=odbc_exec($conn,$sql);
if (!$rs)
  {exit("Error in SQL");}


echo "<table border=1 align=center><tr>";
echo "<th>ID</th>";
echo "<th>Severity</th>";
echo "<th>Priority</th>";
echo "<th>Accepted</th>";
echo "<th>Assignee</th>";
echo "<th>Status</th>";
echo "<th>Resolution</th>";
echo "<th>Summary</th></tr>";
while (odbc_fetch_row($rs))
{
  $id=odbc_result($rs,"ID");
  $severity=odbc_result($rs,"Severity");
  $priority=odbc_result($rs,"Priority");
  $accepted=odbc_result($rs,"Accepted");
  $assignee=odbc_result($rs,"Assignee");
  $status=odbc_result($rs,"Status");
  $resolution=odbc_result($rs,"Resolution");
  $summary=odbc_result($rs,"Summary");
  echo "<tr><td><a href='http://bugtracker.cpwplc.net/show_bug.cgi?id=$id' target='_blank'>$id</a></td>";
  echo "<td>$severity</td>";
  echo "<td>$priority</td>";
  echo "<td>$accepted</td>";
  echo "<td><a href='mailto:$assignee?subject=Bugzilla $id'>$assignee</a></td>";
  echo "<td>$status</td>";
  echo "<td>$resolution</td>";
  echo "<td>$summary</td></tr>";
}
odbc_close($conn);
echo "</table>";
?>
</body>
</html>

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.