Database used to populate selection list

Question

i am trying to create a form and in that form have a selection list in which the options are automatically populated with data from a database (namely customer's last names), after which when the last name is chosen from the list and the submit button is hit the "customer ID" that is related to that last name in the database will be submitted to another PHP file (task8.php) to be sent through further queries. I hope i have explained that all in an understandable manner. I have had a go at some code but i am really unsure on how to do this or if what i have written is on the right path.

Here is what i have written so far:

<body>

<?php
$conn = mysql_connect("localhost", "twa312", "dam6av9a");
mysql_select_db("warehouse312", $conn)
or die ('Database not found ' . mysql_error() );

$sql = "select customerID, lastName from customer";
$rs = mysql_query($sql, $conn)
or die ('Problem with query' . mysql_error());

$options= '<option value="0">Choose</option>';

while ($row=mysql_fetch_array($rs)) {
$id=$row["customerID"];
$name=$row["lastName"];
$options="<OPTION VALUE='" . $id . "'>" . $name ."</option>";
}

?>

<form method="GET" action="task8.php" id="custinfo" >

Choose name:<select name="lname" id="lname"><?php echo $options; ?>  
</select>

<p><input type="submit" name="submit" value="Save Data"/>&nbsp;<input type="reset" value="Clear Form" />

</form>

What i am trying to do with the code is access the table "customer" and fields "customerID" and "lastName". Using the customer's last name as the option and the customer's ID as the options value in the selection list. Currently the code displays only a single name as an option in the selection list when it should display all the names in the database. Any help on this would be really great as i am fairly unsure.

print your options in your while loop. 1. form, select, etc. before the php code. Fetch-while loop - print options. Close select, form — Royal Bg, May 05 '13 at 13:54
Sorry, i am getting a general "server error HTTP error 500 (Internal Server Error)" — user2273149, May 05 '13 at 13:56
You might want to check your Apache error log, that may hint at the problem — Aydin Hassan, May 05 '13 at 13:58
Thank you, i have fixed those and am able to see my form now, just the matter of displaying the options in the selection list now. — user2273149, May 05 '13 at 14:26
What is the output of select customerID, lastName from customer ? — bestprogrammerintheworld, May 05 '13 at 14:27
The output? They are both the names of columns in a table i am trying to access. — user2273149, May 05 '13 at 14:32
The output/result if you execute the statement in something like phpMyAdmin... — bestprogrammerintheworld, May 05 '13 at 14:33
The selection list is displayed but only a single option is shown in the list, it should show all of the names listed in the database. — user2273149, May 05 '13 at 14:40

score 1 · Accepted Answer · answered May 05 '13 at 14:14

1

There is an error in the code that I can see would cause PHP to generate notice error.

In the while loop you're using .= on the $options variable that isn't yet defined so PHP will barf on that.

Aside from that, it doesn't make sense to me that you're waiting for $_GET['submit'] to be set before iterating over the result set from mysql. As far as I can tell, the first time you'd hit this page there would be a single option in the select ("Choose"), and since the form submits to a different page I don't think you'd ever see a list of customer last names.

Finally, it's not really recommended to name your submit buttons 'submit', since when the page is parsed by the browser all the form elements of a specific form are created as attributes of that form, JS form objects have a 'submit' method so when you name an input 'submit' you clobber that value in the form object which makes it really hard to submit that form with JS.

answered May 05 '13 at 14:14

Colin

852
7
6

Actually, now that I really look at the code you've got a couple parse errors in the `while` loop. The lines that assign `$id` and `$name` need to be terminated by a semicolon. – Colin May 05 '13 at 14:17
Thank you! Now at least i am able to see my form, the issue now is that no options are being displayed from the database. – user2273149 May 05 '13 at 14:22
And i have also gotten rid of the $_GET['submit'] after realising that you're right and it does make no sense. – user2273149 May 05 '13 at 14:27
As I said in my answer, that's probably because you're waiting for `$_GET['submit']` to be set before you iterate over the result, or because `$options` isn't initialized. Before entering the loop initialize `$options` to your "choose" option. `$options = '';` – Colin May 05 '13 at 14:29
I have changed my code and managed to get a value from the database but it only shows a single name in the selection list instead of all of them, i have made changes to my above code if you are able to help me with a solution. – user2273149 May 05 '13 at 14:37
Now, you're only going to get the last record of the result in the `$options` varibale. Do this: $options = '; while ($row=mysql_fetch_array($rs)) { $id= $row["customerID"]; $name= $row["lastName"]; $options .= '"; } ?>
Choose name:
– Colin May 05 '13 at 14:38
Ah that worked perfectly thanks so much! The last thing i am stuck on is when the form submits it sends the options value (which is the customer ID) to be processed through another PHP (task8.php) and the variable name in that PHP is "custID" – user2273149 May 05 '13 at 14:50
currently it is not sending anything to task8.php by the looks of it. – user2273149 May 05 '13 at 14:51
if i added – user2273149 May 05 '13 at 14:55
The customer id from the form will be in `$_GET['lname']`, so `$custID = $_GET['lname'];` should work. Good luck moving forward. – Colin May 05 '13 at 14:56
I had already previously set up the variable in task8.php as "custID" so i just had to change id in the form from "lname" to "custID" and that worked just fine, thanks so much for all the help! – user2273149 May 05 '13 at 15:01

Lawrence Cherone · Answer 2 · 2013-05-05T14:23:35.483

First off move away from the mysql_functions.

Secondly create a model with all querys related to your customers that will handle fetching/puttin/updating the data related to your customer db.

<?php 
Class CustomerModel{
    private $db;

    function __construct($host,$dbname,$user,$pass){
        $this->dbhost = $host;
        $this->dbname = $dbname;
        $this->dbuser = $user;
        $this->dbpass = $pass;
    }

    private function connect(){
        if (!$this->db instanceof PDO){
            $this->db = new PDO('mysql:dbname='.$this->dbname.';host='.$this->dbhost, $this->dbuser, $this->dbpass);
            $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
            $this->db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC);
        }
    }

    public function select_all_customer($cols="*"){
        $this->connect();
        $sql = "SELECT $cols FROM customer";
        $statement = $this->db->prepare($sql);
        $statement->execute();
        return $statement->fetchAll(PDO::FETCH_ASSOC);
    }

    public function select_customer($cols="*",$where=null, $id=null){
        $this->connect();
        $sql = "SELECT $cols FROM customer WHERE $where = :id";
        $statement = $this->db->prepare($sql);
        $statement->bindParam(':id', $id, PDO::PARAM_STR);
        $statement->execute();
        return $statement->fetchAll(PDO::FETCH_ASSOC);
    }

}
?>

Now you can access the model like:

<form method="POST" action="task8.php" id="custinfo" >
Choose name:
<select name="customerID" id="customerID">
    <option value="0">Choose</option>
    <?php foreach($customer->select_all_customer("customerID, lastName") as $row): ?>
      <option value="<?php echo $row['customerID']?>"><?php echo $row['lastName']?></option>
    <?php endforeach; ?>
</select>
<p><input type="submit" name="submit" value="Save Data"/>&nbsp;<input type="reset" value="Clear Form" />
</form>


<?php 
//Get customer from form values
if($_SERVER['REQUEST_METHOD'] == "POST" && isset($_POST['customerID'])){
    $result = $customer->select_customer("*", "customerID", $_POST['customerID']);
    //Do something with result
    echo '<pre>'.print_r($result, true).'</pre>';

}
?>

Hope it helps

Database used to populate selection list

2 Answers2