schmidt82 Posted June 24, 2011 Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/240284-creating-dynamic-drop-down-from-db-without-repeat/ Share on other sites More sharing options...
EdwinPaul Posted June 24, 2011 Share Posted June 24, 2011 Hi, If you are using MySQL-database, try SELECT with the DISTINCT option. This will exclude doubles. Quote Link to comment https://forums.phpfreaks.com/topic/240284-creating-dynamic-drop-down-from-db-without-repeat/#findComment-1234184 Share on other sites More sharing options...
Failing_Solutions Posted June 24, 2011 Share Posted 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> Quote Link to comment https://forums.phpfreaks.com/topic/240284-creating-dynamic-drop-down-from-db-without-repeat/#findComment-1234188 Share on other sites More sharing options...
Failing_Solutions Posted June 24, 2011 Share Posted 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>'; ?> Quote Link to comment https://forums.phpfreaks.com/topic/240284-creating-dynamic-drop-down-from-db-without-repeat/#findComment-1234192 Share on other sites More sharing options...
schmidt82 Posted June 24, 2011 Author Share Posted 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> Quote Link to comment https://forums.phpfreaks.com/topic/240284-creating-dynamic-drop-down-from-db-without-repeat/#findComment-1234196 Share on other sites More sharing options...
silkfire Posted June 24, 2011 Share Posted June 24, 2011 What is the error? Print it with echo mysql_error() Quote Link to comment https://forums.phpfreaks.com/topic/240284-creating-dynamic-drop-down-from-db-without-repeat/#findComment-1234212 Share on other sites More sharing options...
mds1256 Posted June 24, 2011 Share Posted 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> Quote Link to comment https://forums.phpfreaks.com/topic/240284-creating-dynamic-drop-down-from-db-without-repeat/#findComment-1234286 Share on other sites More sharing options...
schmidt82 Posted June 24, 2011 Author Share Posted 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> Quote Link to comment https://forums.phpfreaks.com/topic/240284-creating-dynamic-drop-down-from-db-without-repeat/#findComment-1234372 Share on other sites More sharing options...
silkfire Posted June 24, 2011 Share Posted June 24, 2011 Remove inner quotes from the array element like this: $row[city] (not $row['city']). Quote Link to comment https://forums.phpfreaks.com/topic/240284-creating-dynamic-drop-down-from-db-without-repeat/#findComment-1234421 Share on other sites More sharing options...
EdwinPaul Posted June 24, 2011 Share Posted June 24, 2011 Better would be: $optionsid.="<option value='".$row['city_id']."'>".$row['city']."</option>"; Quote Link to comment https://forums.phpfreaks.com/topic/240284-creating-dynamic-drop-down-from-db-without-repeat/#findComment-1234474 Share on other sites More sharing options...
schmidt82 Posted June 25, 2011 Author Share Posted 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> Quote Link to comment https://forums.phpfreaks.com/topic/240284-creating-dynamic-drop-down-from-db-without-repeat/#findComment-1234608 Share on other sites More sharing options...
Nuv Posted June 25, 2011 Share Posted 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>"; } ?> Quote Link to comment https://forums.phpfreaks.com/topic/240284-creating-dynamic-drop-down-from-db-without-repeat/#findComment-1234611 Share on other sites More sharing options...
schmidt82 Posted June 25, 2011 Author Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/240284-creating-dynamic-drop-down-from-db-without-repeat/#findComment-1234748 Share on other sites More sharing options...
PFMaBiSmAd Posted June 25, 2011 Share Posted 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()); Quote Link to comment https://forums.phpfreaks.com/topic/240284-creating-dynamic-drop-down-from-db-without-repeat/#findComment-1234751 Share on other sites More sharing options...
schmidt82 Posted June 25, 2011 Author Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/240284-creating-dynamic-drop-down-from-db-without-repeat/#findComment-1234761 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.