Creating dynamic drop down from db without repeat

schmidt82 · June 24, 2011

Hi,

I've spend the last two days now trying to create a drop down list from a database. I have partly succeeded. I've managed to create a drop down with cities from my db, but since the same city appears several times in my db, it also appears more than once in the list. Anyone know how I can avod this?

I have tried with array_unique, but I don't know how to use it on both the 'option value' and the text shown in the list.

I'll try to explain:

In my db I have my table named 'barer'.

From my table I need 'city_id' and 'city_name', but I only want to show them once.

<select name="city">
     <option value="city_id(from db)">city_name(from db)</option>
</select>

I was thinking something like that, but don't know how to get the right values in there.

Any ideas?

Thank you

EdwinPaul · June 24, 2011

Hi,

If you are using MySQL-database, try

SELECT with the DISTINCT option. This will exclude doubles.

Failing_Solutions · June 24, 2011

This should help

<?php 
        $city_query="Select DISTINCT city_id, city_name FROM barer";
$city_results=mysql_query($city_query); 
$options="";
while ($row=mysql_fetch_array($city_results)) {
$theid=$row["city_id"];
        $thename=$row["city_name"];
        $optionsid.="<OPTION VALUE=\"$theid\">".$thename.'</option>';
         
?>

The select

<select name="Select" id="Select" class="select">
      <?php echo $optionsid ?>
    </select>

Failing_Solutions · June 24, 2011

Just noticed an error in the code, change in bold.

<?php

$city_query="Select DISTINCT city_id, city_name FROM barer";

$city_results=mysql_query($city_query);

$optionsid="";

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

$theid=$row["city_id"];

$thename=$row["city_name"];

$optionsid.="<OPTION VALUE=\"$theid\">".$thename.'</option>';

?>

schmidt82 · June 24, 2011

I can't make this work. When I have both the city_id and the city column after DISTINCT I get this error:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in 1.php on line 10

<?php

header('Content-Type: text/html; charset=iso-8859-1');

include 'connect.php';

$city_query = "SELECT DISTINCT city_id, city FROM barer";
$city_result = mysql_query($city_query);
$optionsid = "";
while($row = mysql_fetch_array($city_result))
{
$city_id = $row['city_id'];
$city = $row['city'];
$optionsid.="<option value=\"$city_id\">".$city_id.'</option>';
$optionsname.="<option value=\"$city\">".$city.'</option>';
}
?>

<html>

<select name="city">
<?php echo $optionsid ?>
<?php echo $optionsname ?>
</select>
</html>

silkfire · June 24, 2011

What is the error? Print it with echo mysql_error()

mds1256 · June 24, 2011

This defintley works as i have just tested it.

Make sure your database is all good as well, e.g. make sure you dont have a city name in twice with different 'city_id'

<?php

header('Content-Type: text/html; charset=iso-8859-1');

include 'connect.php';

$city_query = "SELECT DISTINCT city, city_id FROM barer";
$city_result = mysql_query($city_query);
$optionsid = "";
while($row = mysql_fetch_array($city_result))
{
$optionsid.="<option value=\"$row[city_id]\">".$row[city]."</option>";
}
?>

<html>

<select name="city">
<?php echo $optionsid ?>
</select>
</html>

schmidt82 · June 24, 2011

Tried the code below and now I get this error message:

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in 1.php on line 12

<?php

header('Content-Type: text/html; charset=iso-8859-1');

include 'connect.php';

$city_query = "SELECT DISTINCT city_id, city FROM barer";
$city_result = mysql_query($city_query);
$optionsid = "";
while($row = mysql_fetch_array($city_result))
{
$optionsid.="<option value=\"$row['city_id']\">".$row['city']."</option>";
}
?>

<html>

<select name="city">
<?php echo $optionsid ?>
</select>
</html>

silkfire · June 24, 2011

Remove inner quotes from the array element like this: $row[city] (not $row['city']).

EdwinPaul · June 24, 2011

Better would be:

$optionsid.="<option value='".$row['city_id']."'>".$row['city']."</option>";

schmidt82 · June 25, 2011

Tried them both and still get error message:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in 1.php on line 10

<?php

header('Content-Type: text/html; charset=iso-8859-1');

include 'connect.php';

$city_query = "SELECT DISTINCT city_id, city FROM barer";
$city_result = mysql_query($city_query);
$optionsid = "";
while($row = mysql_fetch_array($city_result))
{
$optionsid.="<option value='".$row['city_id']."'>".$row['city']."</option>";
}
?>

<html>

<select name="city">
<?php echo $optionsid ?>
</select>
</html>

Nuv · June 25, 2011

From my table I need 'city_id' and 'city_name', but I only want to show them once.

$city_query = "SELECT DISTINCT city_id, city FROM barer";

Should be

$city_query = "SELECT DISTINCT city_id, city_name FROM barer";

making your complete code to be

<?php

header('Content-Type: text/html; charset=iso-8859-1');

include 'connect.php';

$city_query = "SELECT DISTINCT city_id, city_name FROM barer";
$city_result = mysql_query($city_query);
$optionsid = "";
while($row = mysql_fetch_array($city_result))
{
$optionsid.="<option value='".$row['city_id']."'>".$row['city']."</option>";
}
?>

schmidt82 · June 25, 2011

@Nuv, if you are not even trying to solve the problems in your own code, especially if someone has posted the reason why your code is not working, you are not going to succeed as a programmer.

Thank you for the wise words, but I don't think anyone has told me why it doesn't work. I've used DISTINCT in every way I can possibly think of, and in every way it's been suggested in here, but every time I try to get data from two columns, I get error messages. If I only choose one it works just fine. And if I choose SELECT DISTINCT * I get my dropdown, but it shows the cities more than once. So if anyone can tell me what's wrong I'd be very thankful.

PS. I did find the city_name mistake, but it still didn't work

PFMaBiSmAd · June 25, 2011

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given

^^^ If you search for that error message, you will find that it generally means that your query failed due to an error of some kind (no connection, wrong table/column names, sql syntax error,...) and returned a FALSE value instead of a result resource or that you have some code that is overwriting the result resource or you are using the wrong variable.

Assuming that your query is failing, use mysql_error in some error checking and error reporting logic to get php/mysql to tell you why -

$city_result = mysql_query($city_query) or die("Query failed: $city_query<br />Due to: " . mysql_error());

schmidt82 · June 25, 2011

Thanks for all your help guys. I finally figured it out. There was one thing I hadn't tried yet.

Instead of "SELECT DISTINCT city_id, city_name (or *) FROM barer", I tried "SELECT DISTINCT barer.city_id, barer.city_name FROM barer" and it works perfectly. I don't know why it makes a difference or why I didn't try that before, but after 3-4 days trying to fix it, I finally got it.

Thanks for helping me out

Sign In

Creating dynamic drop down from db without repeat

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

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