Php – Display data from database into comboboxes

MySQLPHPweb

I have a website where I am trying to display data from the database stored in mysql into comboboxes. Basically it is a location database so I want to display an area and then based on the selection I want the next combobox to show the shops in that location.
I am using php for this. I'm not too familiar with php. I've found loads of tutorials and examples but still I don't seem to be tweeking the code properly to get it to work. Also I'm not sure if I need a seperate php file with connection details or if I can put it all in the one file. I can't even populate one combobox at the minute so I am really trying to go back to basics.

Anyway this is the code so far (obviously I have html tags etc):

<form name="form1" method="POST" action="db.php"> 
<p>  
<select name="users" onchange="showUser(this.value)">
<option selected="selected">Select county</option> 
<p><input type="submit" name="Search" value="Search"> 
</form>

Then I have a db.php file where it stores my database details and sql query

<?php
resource mysql_connect ( [string server [, string username [, string password [, bool     new_link [, int client_flags]]]]])

$host = "localhost"; 
$user = ""; 
$pass = "";

bool mysql_select_db ( string database_name [, resource link_identifier])

$dbname = "databseLocations"; 
mysql_select_db($dbname);

$sql = "select * from locations where county=" . $_POST['county'] . " and name='" .     $_POST['county'] . "'"; 
echo $sql;

resource mysql_query ( string query [, resource link_identifier])

$query = mysql_query($sql);

while ($row = mysql_fetch_array($query)) { 
echo "<p>",$row['id'],": ",$row['county']; 
}
?>

I need this data to be displayed on a map too based on selection but I can worry about that later.

Thanks for any help

Best Answer

Assuming following query :

$sql = "select * from locations where county=" . $_POST['county'] . " and name='" .     $_POST['county'] . "'"; 
$query = mysql_query($sql);

echo '<select name="country" size="1">'
while ($row = mysql_fetch_array($query)) { 
echo '<option value="' . $row['id'] . '">' . $row['county'] . '</option>';
}
echo '</select>';

Note : mysql_* are deprecated.

Related Solutions

Mysql – concatenate multiple MySQL rows into one field

You can use GROUP_CONCAT:

SELECT person_id,
   GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Ludwig stated in his comment, you can add the DISTINCT operator to avoid duplicates:

SELECT person_id,
   GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Jan stated in their comment, you can also sort the values before imploding it using ORDER BY:

SELECT person_id, 
       GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Dag stated in his comment, there is a 1024 byte limit on the result. To solve this, run this query before your query:

SET group_concat_max_len = 2048;

Of course, you can change 2048 according to your needs. To calculate and assign the value:

SET group_concat_max_len = CAST(
                     (SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
                           FROM peoples_hobbies
                           GROUP BY person_id) AS UNSIGNED);

Mysql – Which MySQL data type to use for storing boolean values

For MySQL 5.0.3 and higher, you can use BIT. The manual says:

As of MySQL 5.0.3, the BIT data type is used to store bit-field values. A type of BIT(M) enables storage of M-bit values. M can range from 1 to 64.

Otherwise, according to the MySQL manual you can use BOOL or BOOLEAN, which are at the moment aliases of tinyint(1):

Bool, Boolean: These types are synonyms for TINYINT(1). A value of zero is considered false. Non-zero values are considered true.

MySQL also states that:

We intend to implement full boolean type handling, in accordance with standard SQL, in a future MySQL release.

References: http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html

Best Answer

Related Solutions

Mysql – concatenate multiple MySQL rows into one field

Mysql – Which MySQL data type to use for storing boolean values

Related Topic