Output a repeated value only once in a PHP drop-down list

Fabrizzio PHP · August 28, 2009

Hi,

My database MAP has the following tables and values:

id university post_code borough

1 UCL W2 Westminster

2 Camden U. N1 Merrylbourne

3 Cambridge W2 Westminster

4 Oxford Se Aldahyde

etc.

I've got a PHP form with a drop down list which displays a borough list and when selecting a borough (ie. Westminster) it should display all the available universities (ie. UCL and Cambridge) in the borough. My problem is that my dropdown list shows all the boroughs from my table, so if I've got 4 universities in a same borough, that borough value will repeat 4 times. On the other hand, once the values are not repeated in the drop down list, when selected, it should post all of the universitites within that borough.

Please keep in mind that I'm just learning PHP beginner, so I would really appreciate any help possible.

My form.php has the following:

<?php
// Connect database
mysql_connect("localhost","root","xxxxx");
mysql_select_db("map"); 

// If submitted, check the value of "select". If its not blank value, get the value and put it into $select.
if(isset($select)&& $select!=""){
$select=$_GET['select'];
}
?>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
</head>
            
<body>
<form id="form1" name="form1" method="post" action="result.php?select=<?php echo urlencode($select['id']); ?>"> 
Search by borough :
<select name="select">
<option value="">--- Select ---</option>
<?php
// Get records from database (table "subjects").
$list=mysql_query("SELECT * FROM subjects ORDER BY id ASC");

// Show records by while loop.
while($row_list=mysql_fetch_assoc($list)){
?>
<?php
$select = $_GET['select'];?>
<option value="<?php echo $row_list['borough']; ?>" <?php if($row_list['id'] == $select){ echo "selected"; } ?>><?php echo $row_list['borough']; ?></option>
<?php
// End while loop.
}
?>
</select> 
<input type="submit" name="Submit" value="select" />
</form>

<?php
mysql_close();
?>
</p>
</body>
</html>

My result.php is:

<?php
// Connect database
mysql_connect("localhost","root","xxx");
mysql_select_db("map"); 
?>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
</head>
<body>

    <?php
    $select = $_POST['select'];
    $query= mysql_query("SELECT * FROM subjects WHERE borough = '$select' ");
    $row_list=mysql_fetch_assoc($query);
    ?>
    
    The borough is: <?php echo $_POST["select"]; ?><br />
    The Universities in this borough are: <?php echo $row_list['university']; ?><br />
    

    
    
</body>
</html>

Thanks

Mark Baker · August 28, 2009

You should probably refactor your database to have a separate table listing the boroughs

Fabrizzio PHP · August 28, 2009

Hi Mark,

The basic idea is that the user should be able to search by boroughs, by university, by post_code or by courses.

So if you search by boroughs it should display all the universities within the selected borough, and if you search by university it should display all the courses within the university, etc.

I've currently nested to tables: one called "subjects" which contains: id, university, post_code, borough and content, and a second one called: "pages" which contains subj_id, id, course_name, description.

If I take your suggestion, that would mean I would need to create at least 4 tables: one for university, one for borough, one for post_code, and one for subjects, and the same data would be repeated 4 times.

Mark Baker · August 28, 2009

You want 4 tables:

Borough (borough_id, borough_name)

Subject (subject_id, subject_title)

University (university_id, university_name, post_code, borough_id)

University_subjects (university_id, subject_id)

And this structure does not duplicate data. The process of identifying a structure like this is known as normalisation (normalization if you use the american spelling), and is a standard technique for database design

Mark Baker · August 28, 2009

Data should look something like this:

Borough

    borough_id  borough_name
    1           Westminster
    2           Marylebone
    3           Aldahyde

University

    university_id  university_name  post_code  borough_id
    1              UCL              W2         1
    2              Camden U.        N1         2
    3              Cambridge        W2         1
    4              Oxford           Se         3

Subject

    subject_id  subject_title
    1           Greek
    2           History
    3           Economics
    4           Computer Science
    5           Law

University_subjects

    university_id  subject_id
    1              2
    1              5
    2              3
    2              4
    3              2
    3              3
    3              4
    3              5
    4              1
    4              2
    4              5

DavidAM · August 28, 2009

I have to agree with Mark, you should normalize the database, especially if it will contain lots of data. Performance will (should) be better and updates simpler.

That being said, to get the results you want with what you have try:

//** Instead of SELECT * FROM subjects ORDER BY id ASC
$list=mysql_query("SELECT DISTINCT borough FROM subjects ORDER BY borough ASC");

Of course you can't order by id now since you are not returning it and there would be multiple id's for a single borough, but I would think you want it ordered by borough for this list anyway.

If you want to be really fancy, you could do something like this:

SELECT borough, count(*) AS ucount FROM subjects GROUP BY borough

Then you could show the number of universities in your drop down i.e. Westminster (2). Of course, you would have to strip off the count part in result.php. Note: you don't need an ORDER BY in this example because the server sorts it for the GROUP BY (might not need it for the first example either).

Fabrizzio PHP · August 28, 2009

Thanks Mark and David,

I will have a look at both options!

Fabrizzio PHP · August 28, 2009

Hi David,

I tried your option

$list=mysql_query("SELECT DISTINCT borough FROM subjects ORDER BY borough ASC");

and works quite well! boroughs are displayed only ones.

Still, in my result.php,

<?php
$select = $_POST['select'];

$query= mysql_query("SELECT * FROM subjects WHERE borough = '$select' ");

$row_list=mysql_fetch_assoc($query);

?>

The borough is: <?php echo $_POST["select"]; ?> 

The the Institutes are: <?php echo $row_list['menu_name']; ?>

I still only get one result for universities, although it should display all the universities within the borough.

ReKoNiZe · August 28, 2009

Do this:

echo "The borough is $select<br />";
echo "The the Institutes are:<br /><ul>";
while($row_list=mysql_fetch_assoc($query))
echo "<li>$row_list['menu_name']</li>";
echo "</ul><br />";

Excuse the cheap formatting, was just an example, you have to loop through all of your array and list each one.

Fabrizzio PHP · August 28, 2009

Hi ReKoNIZe,

Thanks for the tip! I'm using your code but I'm getting the following error message:

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in C:\wamp\www\map\myform.php on line 23

This is what I've got:

<?php
echo "The borough is $select ";

echo "The the Institutes are: <ul>";

while($row_list=mysql_fetch_assoc($query)){

echo "<li>$row_list['menu_name']</li>";

echo "</ul> ";}

?>

Fabrizzio PHP · August 28, 2009

Hi ReKonize,

I've fixed the code:

<?php
echo "The borough is $select ";

echo "The the Institutes are: <ul>";

while($row_list=mysql_fetch_assoc($query)){

echo '<li>'.$row_list['menu_name'].'</li>';

}

echo '</ul>';

echo ' ';

?>

I'm actually getting all of the values except for one (the value which belongs to the select)

ReKoNiZe · August 28, 2009

Try using $_POST['select'] then.

Sign In

Output a repeated value only once in a PHP drop-down list

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information