asked on

Creating a dynamic drop down combo list and displaying only those records

Hi guys hope you can help.

=======
GOAL/s:
=======
Create a dynamic drop down list box which
---------------------------------------------
a) Gets populated from a mysql table field (name_are from table areas_are), and
b) Has a top entry (first in the list) value which, when selected, selects all records.

================
FILE/s Im using:
================
combi.php
connectioninfo.php (just connection information through to a mysql db)

==================
MYSQL Tables Used:
==================
areas_are
scripts_are

=============
Table Fields:
=============

1) table 'areas_are' (2 fields)
id_are
name_are

2) table 'scripts_scr' (4 fields)
id_scr
name_scr
fk_id_are_scr (foreign key references id_are from table areas_are)
code_scr

=====================================
What combi.php looks like currently:
=====================================

----------------------------------------------------- start of combi.php

Insert a new area: _______________ GO (button)

Display all records: ALL (button)

----------------------------------------------------- end of combi.php

==================================
The way Id like combi.php to look:
==================================

----------------------------------------------------- start of combi.php

Insert a new area: _______________ GO (button) ----> a insert statement runs

Display all records: ALL (button)
drop box populated with mysql table field called 'name_are"
||
Display these records: <Dynamic Drop Down box> GO (button) ---> the join query is executed to return what selection the user made in the drop box.

(When the user clicks on 'GO', the following rows are returned on the php page)

name_are (mysql field) name_scr(mysql field) code_scr (mysql field)
row1 row1                        row1
row2 row2
row3
etc
etc

The query to return the above is a join between the 2 tables, which is the following code:

------------------------------------------------- The join query
select name_are, name_scr, code_scr
from areas_are as a
left join scripts_scr as s
on
a.id_are = s.fk_id_are_scr

----------------------------------------------------- end of combi.php

===========
IN SUMMARY:
===========
# Drop box populated with field name name_are from table area_are
# User selection in drop box, and clicking on go, results in a join query run to return rows as selected by user selection in dropbox.
# Results are displayed on the page in 3 areas (one for each field)
# Ideally, the dropbox, would have a first top entry to display all areas, but I dont know how you would do this.

__________________
Display ALL Areas
area1
area2 GO
area3
etc
----------------------------

===============
MY CODE SO FAR:
===============
The following is my code so far.
I cannot get the combo box code working, nor display a combo box on the page at all, so there is something wrong with the combo box code.
Any help on all of this would be of great appreciation to myself.
Thank you everyone.

========================================================================== combi.php

<html>
<form action="combi.php" method="POST">
Insert a new area: <input type="text" name="newarea">
<input type="submit" name="submit" value="Go">
</form>

<form action="combi.php" method="POST">
Display all records: <input type="submit" name="displayall" value="All">
</form>

</html>

<?php

      include("connectioninfo.php");

if($_POST['displayall']) {
      echo "User clicked on the All button <br />";
      $sQuery = "SELECT name_are FROM areas_are";
      $refResult = mysql_query($sQuery);
            while($aRow = mysql_fetch_array($refResult, MYSQL_ASSOC))
            {
            echo "Name: $aRow[name_are]<br />";
            }
      }
elseif ($_POST['submit']) {
echo "User clicked on the Go button <br />";
      if(!empty($_POST[newarea])) {
            $sql="INSERT INTO areas_are (name_are) VALUES ('$_POST[newarea]')";
            mysql_query($sql) or die(mysql_error());
            echo "1 record added";
            print "<br>";
      } else {
      print "Please submit a value. A blank value is not allowed \n";
      }
}

// Combo box code:
// ----------------
function comboBox()
{
// Create a variable for the combo box and call it areacombo
$combo = "<select name=\"areacombo\">\n";

// Create the sql
$sqlcombo = "select * from areas_are order by name_are asc";

// Execute the sql
$querycombo = mysql_query($sqlcombo) or die ("MySQL query failed: " . mysql_error());

while ($resultcombo = mysql_fetch_array($querycombo, MYSQL_ASSOC))
      {
      $combo .= "\t<option value=\"" . $resultcombo[name_are] . "\">" . "\n";
      $combo .= "</select>\n";
      return $combo;
      }
}

comboBox();

?>

======================================================================= End of file.

Vel Eous

$combo .= "\t<option value=\"" . $resultcombo[name_are] . "\">" . "\n";

^^ You have failed to close the option.

Simon336697

ASKER

Hi Tchuki1
Sorry Im not following you.
The code you have written is the code I have.

Arikael

But like Tchuki mentioned you forgot to close your options:

you have <option value="yourvalue">

But correct would be
<option value="yourvalue">yourtext</option>

and the </select> belongs after the while.

Arikael

Make sure that your return is after the while-loop

Otherwise your combobox consists only of one (incomplete) option

while ($resultcombo = mysql_fetch_array($querycombo, MYSQL_ASSOC))
      {
            $combo .= "\t<option value=\"" . $resultcombo["yourvalue"] . "\">" . $resultcombo["yourtext"]."</option>\n";
      
      }
}
 
$combo .= "</select>\n";
      return $combo;

Open in new window

Simon336697

ASKER

Arikael!

Cant thank you enough and all you guys.

Arikael.
Im still not getting a combo box produced on the page however.

Here is the code Im using together with your code above.

Im trying to call the function to display it but nothing is showing.

======================================================================
<html>
<form action="combi.php" method="POST">
Insert a new area: <input type="text" name="newarea">
<input type="submit" name="submit" value="Go">
</form>

<form action="combi.php" method="POST">
Display all records: <input type="submit" name="displayall" value="All">
</form>

</html>

<?php

      include("connectioninfo.php");

if($_POST['displayall']) {
      echo "User clicked on the All button <br />";
      $sQuery = "SELECT name_are FROM areas_are";
      $refResult = mysql_query($sQuery);
            while($aRow = mysql_fetch_array($refResult, MYSQL_ASSOC))
            {
            echo "Name: $aRow[name_are]<br />";
            }
      }
elseif ($_POST['submit']) {
echo "User clicked on the Go button <br />";
      if(!empty($_POST[newarea])) {
            $sql="INSERT INTO areas_are (name_are) VALUES ('$_POST[newarea]')";
            mysql_query($sql) or die(mysql_error());
            echo "1 record added";
            print "<br>";
      } else {
      print "Please submit a value. A blank value is not allowed \n";
      }
}

// Combo box code:
// ----------------
function comboBox()
{
// Create a variable for the combo box and call it areacombo
$combo = "<select name=\"areacombo\">\n";
// Create the sql
$sqlcombo = "select * from areas_are order by name_are asc";

// Execute the sql
$querycombo = mysql_query($sqlcombo) or die ("MySQL query failed: " . mysql_error());

while ($resultcombo = mysql_fetch_array($querycombo, MYSQL_ASSOC))
{
$combo .= "\t<option value=\"" . $resultcombo["yourvalue"] . "\">" . $resultcombo["yourtext"]."</option>\n";

}
}

$combo .= "</select>\n";
return $combo;

comboBox();

?>
=================================================================

Arikael

sorry,

You have to replace "yourvalue" with id_are and yourtext with "name_are"
Or with whatever you want to display in the combobox.
Note that PHP/Javascript will work with the value of your combobox, so when you have

<option value="123">Area 51</option> selected

after you posted your form
$_POST["areacombo"] will hold "123" and not "Area 51" because "Area 51" is just a textual representation of "123" (kinda)

Arikael

oh, another reason why you can't see anything is that you place your ouput after </html>
You should place all our stuff within <body>
Same for your <head>

Look here for a simple example: http://www.htmlcodetutorial.com/document/_BODY.html

ASKER CERTIFIED SOLUTION

Vel Eous

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Simon336697

ASKER

Hi Tchuki1!

Im really slow on this question (im really sorry).

Tchuki1, I am only using one php file with html in it. The following is all one file....

---------------------------------------------------------------------- combi.php
<html>
<form action="combi.php" method="POST">
Insert a new area: <input type="text" name="newarea">
<input type="submit" name="submit" value="Go">
</form>

<form action="combi.php" method="POST">
Display all records: <input type="submit" name="displayall" value="All">
</form>

</html>

<?php

      include("connectioninfo.php");

if($_POST['displayall']) {
      echo "User clicked on the All button <br />";
      $sQuery = "SELECT name_are FROM areas_are";
      $refResult = mysql_query($sQuery);
            while($aRow = mysql_fetch_array($refResult, MYSQL_ASSOC))
            {
            echo "Name: $aRow[name_are]<br />";
            }
      }
elseif ($_POST['submit']) {
echo "User clicked on the Go button <br />";
      if(!empty($_POST[newarea])) {
            $sql="INSERT INTO areas_are (name_are) VALUES ('$_POST[newarea]')";
            mysql_query($sql) or die(mysql_error());
            echo "1 record added";
            print "<br>";
      } else {
      print "Please submit a value. A blank value is not allowed \n";
      }
}

// Create a variable for the combo box and call it areacombo
$combo = "<select name='areacombo'>";

// Create the sql
$sqlcombo = "select * from areas_are order by name_are asc";

// Execute the sql
$querycombo = mysql_query($sqlcombo) or die ("MySQL query failed: " . mysql_error());

while ( $resultcombo = mysql_fetch_array ( $querycombo ) ) {
echo "<option value={$resultcombo['name_are']}>{$resultcombo['name_are']}</option>\n";
}

echo "</select>";

?>
-------------------------------------------------------------

The above gives me all records from the table field name_area but not in a combo box, just as follows....

Insert a new area: _____________ Go

Display all records All

1 1234 13aw 2 34 4 4567 566 a Alter Statement anl aqgfd Authentication Backing up MySQL Databases Creating Databases Creating Tables Delete Statements Describe Statements Drop Statements Drop Statments ew333 etc etc

The above lines are the row entries for field name_are, but not displayed in a combo box.

SOLUTION

Arikael

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Simon336697

ASKER

Arikael,
Tchuki1

Firstly, to both of you........ I cannot thank you enough for the absolute kindness, expertise, and patience with me on this.
Secondly, I wish I could thank both of you in person........ I am so tired but cannot sleep, and wanted to persevere with this regardless because of the time and effort you both have put into this.

YOU GUYS ARE TRULY SENSATIONAL AND GURUS.

THANK YOU SO MUCH.

The combo box now works.

---------------------------------------------------------------------- Here is the final successful code you guys did (combi.php)

<html>
<form action="combi.php" method="POST">
Insert a new area: <input type="text" name="newarea">
<input type="submit" name="submit" value="Go">
</form>

<form action="combi.php" method="POST">
Display all records: <input type="submit" name="displayall" value="All">
</form>

</html>

<?php

      include("connectioninfo.php");

if($_POST['displayall']) {
      echo "User clicked on the All button <br />";
      $sQuery = "SELECT name_are FROM areas_are";
      $refResult = mysql_query($sQuery);
            while($aRow = mysql_fetch_array($refResult, MYSQL_ASSOC))
            {
            echo "Name: $aRow[name_are]<br />";
            }
      }
elseif ($_POST['submit']) {
echo "User clicked on the Go button <br />";
      if(!empty($_POST[newarea])) {
            $sql="INSERT INTO areas_are (name_are) VALUES ('$_POST[newarea]')";
            mysql_query($sql) or die(mysql_error());
            echo "1 record added";
            print "<br>";
      } else {
      print "Please submit a value. A blank value is not allowed \n";
      }
}

// Create a variable for the combo box and call it areacombo
echo "<select name='areacombo'>";

// Create the sql
$sqlcombo = "select * from areas_are order by name_are asc";

// Execute the sql
$querycombo = mysql_query($sqlcombo) or die ("MySQL query failed: " . mysql_error());

while ( $resultcombo = mysql_fetch_array ( $querycombo ) ) {
// echo "<option value={$resultcombo['name_are']}>{$resultcombo['name_are']}</option>\n";
      echo "<option value = {$resultcombo['name_are']}>{$resultcombo['name_are']}</option>\n";
}

echo "</select>";

?>

--------------------------------------------------------------------------------------------------

Guys I will post another question on this just with a tweak that id like to try.

If its okay with both of you, id like to split points 50/50.

Thank you kind geniuses.

I have a long way to go.

Vel Eous

Thanks for the question and the points. Glad this part of your issue is resolved. :)

Simon336697

ASKER

Thanks so much kind Tchuki! :>)