CODEWITHSUNDEEP
phphtmlmysqlmenudropdown
user5526208
user5526208

Reputation:

Pull and post data from MySQL using dropdown menu

I'm a complete newbie in php & MySQL Basically what I want to do is be able to retrieve data from a table in MySQL database and put it in a drop down menu . After I fill in the other fields I want to data from the drop down menu to be written to another table in the same database .

This is my insert.php file 

        <?php

        #### INSERTS A SINGLE CUSTOMER IN Company-->Customer Database with UTF8 Change for special German Characters  - Cyrilic Doesnt work - Other change then utf8 ???
        #### Getting Data from Index.php 


        $servername = "127.0.0.1";
        $username = "root";
        $password = "";
        $dbname = "company";    

        //making an array with the data recieved, to use as named placeholders for INSERT by PDO.


        #### Getting DAta from Index.php 
        $data = array('CustomerName' => $_POST['CustomerName'] , 'Address1' => $_POST['Address1'], 'Address2' => $_POST['Address2'], 'City' => $_POST['City'], 'PostCode' => $_POST['PostCode'], 'CountryID' => $_POST['CountryID'], 'ContactName' => $_POST['ContactName'], 'ContactEmail' => $_POST['ContactEmail'], 'ContactPhone' => $_POST['ContactPhone'], 'ContactFax' => $_POST['ContactFax'], 'Website' => $_POST['Website']);

        try {
            // preparing database handle $dbh
            $dbh = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password,array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")); ### Database Connect with Special characters Change 
            // set the PDO error mode to exception
            $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            // query with named placeholders to avoid sql injections
            $query = "INSERT INTO customer (CustomerName, Address1, Address2, City, PostCode, CountryID, ContactName, ContactEmail, ContactPhone, ContactFax, Website ) 
            VALUES (:CustomerName, :Address1, :Address2, :City, :PostCode, :CountryID, :ContactName, :ContactEmail, :ContactPhone, :ContactFax, :Website )";
            //statement handle $sth
            $sth = $dbh->prepare($query);
            $sth->execute($data);
            echo "New record created successfully";
            }
        catch(PDOException $e)
            {
            echo $sql . "<br>" . $e->getMessage();
            }
        $dbh = null;
        ?>

And this is the source from the html page 

        <!DOCTYPE html>
        <html lang="en">
        <head>
        <meta charset="UTF-8">
        <title>Add New Product</title>
        </head>
        <body>
        <form action="insert.php" method="post">
            <p>
                <label for="CustomerName">CustomerName:</label>
                <input type="text" name="CustomerName" id="CustomerName">
            </p>
            <p>
                <label for="Address1">Address 1:</label>
                <input type="text" name="Address1" id="Address1">
            </p>
            <p>
                <label for="Address2">Address 2:</label>
                <input type="text" name="Address2" id="Address2">
            </p>

            <p>
                <label for="City">City:</label>
                <input type="text" name="City" id="City">
            </p>

            <p>
                <label for="PostCode">Post Code:</label>
                <input type="text" name="PostCode" id="PostCode">
            </p>

            <p>
                <label for="CountryID">Country ID:</label>
                <input type="text" name="CountryID" id="CountryID">
            </p>

            <p>
                <label for="ContactName">Contact Name:</label>
                <input type="text" name="ContactName" id="ContactName">
            </p>

            <p>
                <label for="ContactEmail">Contact Email:</label>
                <input type="text" name="ContactEmail" id="ContactEmail">
            </p>

            <p>
                <label for="ContactPhone">Contact Phone:</label>
                <input type="text" name="ContactPhone" id="ContactPhone">
            </p>

            <p>
                <label for="ContactFax">Contact Fax:</label>
                <input type="text" name="ContactFax" id="ContactFax">
            </p>

            <p>
                <label for="Website">Website:</label>
                <input type="text" name="Website" id="Website">
            </p>

            <input type="submit" value="Add Records">
        </form>
        </body>
        </html>

So I wanna add this php code that pulls out the country list and prefixes for the phone numbers and then when I hit the submit button the actual output of the drop down menu to we written in the customers table 

                <p>
                    <label for="CountryID">Country:</label>

                        <?php
                        $servername = "localhost";
                        $username = "root";
                        $password ="";
                        $dbname = "company";

                        $con_qnt = mysqli_connect($servername, $username, $password, $dbname);      
                        if(!mysqli_connect("localhost","root",""))
                        {
                            die('oops connection problem ! --> '.mysqli_connect_error());
                        }
                        if(!mysqli_select_db($con_qnt, "company"))
                        {
                            die('oops database selection problem ! --> '.mysqli_connect_error());
                        }


                        $sql = "SELECT * FROM country";
                        $result = mysqli_query($con_qnt, "SELECT * FROM country" );


                        echo "<select name='label'>";
                        while ($row = mysqli_fetch_array($result)) {
                        echo "<option value='" . $row['CountryName' ] . "'>" . $row['CountryName' ] . "     ("  .$row['PhonePrefix' ] . ")"  . "</option>";

                            } 
                        echo "</select>";
                        ?>          
                    <name="CountryID" id="CountryID">
                </p>

I don't even know if this is doable - I searched for long time but couldn't find anything that is kind of what I need . Mostly I found hard coded html dropdown menus . In fact hard coding this will work for the countries but it wont work if I want it to be able to show lets say products in a drop down menu . Thank you all in advance .

Upvotes: 0

Views: 1491

Answers (2)

Alvaro Montoro
Alvaro Montoro

Reputation: 29675

If I got you right, you want to replace this:

<p>
   <label for="CountryID">Country ID:</label>
   <input type="text" name="CountryID" id="CountryID">
</p>

With a dropdown so you can quickly pick the country from the dropdown instead of memorizing the ID for each individual country. And to create the select you are using this code:

<p>
    <label for="CountryID">Country:</label>

    <?php

        // some code here that I removed to avoid noise

        $sql = "SELECT * FROM country";
        $result = mysqli_query($con_qnt, "SELECT * FROM country" );

        echo "<select name='label'>";
        while ($row = mysqli_fetch_array($result)) {
           echo "<option value='" . $row['CountryName' ] . "'>" . $row['CountryName' ] . "     ("  .$row['PhonePrefix' ] . ")"  . "</option>";
        } 
        echo "</select>";
    ?>

    <name="CountryID" id="CountryID">
</p>

That should work fine... after you fix some issues:

  1. If you want to replace the input with the select, the select should have the same name as the input so the value is processed automatically. Right now the select has "label" as the name and there is a strange (and incorrect) name tag. To fix this:

    • Get rid of the unnecessary name tag.

    • Replace the name attribute in the select with value "CountryID":

      echo "<select name='CountryID'>";

  2. The option value should be the country ID so, unless the ID is the country name (not the best choice), you are not using the right value. Change that line too:

    echo "<option value='" . $row['CountryID' ] . "'>" . $row['CountryName' ] . " (" .$row['PhonePrefix' ] . ")" . "</option>";

    (Considering CountryID as the ID, replace it for the right column name).

And I think with those two changes, the rest of the code should work just fine with insert.php that wouldn't require any updates at all. Finally it would look something like this:

<p>
    <label for="CountryID">Country:</label>

    <?php

        // some code here that I removed to avoid noise

        $sql = "SELECT * FROM country";
        $result = mysqli_query($con_qnt, "SELECT * FROM country" );

        echo "<select name='CountryID'>";
        while ($row = mysqli_fetch_array($result)) {
           echo "<option value='" . $row['CountryID' ] . "'>" . $row['CountryName' ] . "     ("  .$row['PhonePrefix' ] . ")"  . "</option>";
        } 
        echo "</select>";
    ?>
</p>

Upvotes: 0

Russel Escalderon Lobrio
Russel Escalderon Lobrio

Reputation: 127

Hi I just made a quick sample to answer your question on how to populate dropdown using data from database. In this example, I used

JQuery's AJAX Function

so first, for the backend, (this is just a quick sample to select data. Do not use this as a pattern for your future codes

<?php
$dbh = new PDO("mysql:host=localhost;dbname=dbhelp", 'root', 'pup');
$stmt = $dbh->prepare("SELECT * from t_country");
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo json_encode($result);
?>

I repeat. This is just for quick reference. Do not use it as a pattern for your code. So using that code, we have now selected data from our table country and usedjson_encode to make it in json format. read more about this here. http://php.net/manual/en/function.json-encode.php.

Next one is the frontend(HTML part)

<body>
     // empty dropdown button to be filled by data from database
     <select id="country">
     </select>
     //include the jquery library

     <script src="//code.jquery.com/jquery-1.11.3.min.js"></script>

     <script type="text/javascript">
     $(document).ready(function () {
          $.ajax ({
                url : 'dropdown_database.php', // the url from which the data will be pulled
                success: function(data) {
                     var country = $.parseJSON(data); // parse the json format data
                     $.each(country, function(i, d) {
                          $('#country').prepend('<option>'+ d.country +'</option>'); // this one adds the <option></option> tag in the dropdown
                     });
                }
          });
     });
     </script>

Upvotes: 0

Related Questions