Getting data from a SQL table into a dropdown box


#1

Basically i would like the drop down to present a list of my suppliers and once a user selects a supplier it adds the phone number and notes of that supplier to the form.

I have basic knowledge of PHP and learning every day ;D
This is my code:

[php]

Supplier Information:

Select Supplier:
" <?php $sql = "SELECT SupplierName From Suppliers order by SupplierName ASC"; $result = mysql_query($sql); while ($row = mysql_fetch_array($result)) { echo ''.$row["Supplierlist"].''; } ?>
Phone Number:
Notes:
[/php]But i only get the suppliers dropdown and thats it with no data populated. I have tried several rewrites but nothing seems to be working.

Thanks James


#2

A quick list of problems -

  1. The php mysql_ extension is obsolete and has been removed from the latest php version. You need to learn and use the php PDO extension, use prepared queries when supplying data to an sql query, and use exceptions to handle database connection/query errors. Doing these things, in addition to allowing your code to work under the latest php version, will actually simplify the php code and the sql query syntax, and make the sql queries secure.

  2. You either don’t have php’s error_reporting set to E_ALL and display_errors set to ON or because you have php ‘business’ logic inside your html document, specifially inside of a tag where it won’t be rendered and displayed by the browser, you are not seeing php errors that would help you identity the cause of the current problem.

Insure that your development system is setup with error_reporting set to E_ALL, display_errors set to ON, and output_buffing set to OFF, in the php.ini that php is using, to get php to help you by reporting and displaying all the errors it detects and it doesn’t hide output due to buffing output and discarding it should you do a header redirect. Stop and start your web server to get any changes to the php.ini to take effect.

The php ‘business’ logic, that is responsible for getting/producing/processing data should be separated from the html document, with any result from this logic stored in appropriately named php variable(s), then just those variable(s) in the html document. Doing this will make writing, testing, and debugging your code easier, since you can examine all the data to see if it is what you expect, before going on to use it in the html document. This will also make changing the database extension easier (see item #1 in this list), since you would only need to change the php business logic, not the code in the html document, and this will also let you switch to use a template engine without changing the php business logic.

  1. You have missing and misused id’s in the html document. The for=’…’ attributes in the <label …> tags need to have a corresponding id=’…’ attribute in the form field. Check that all these exist and match up.

  2. The form field names should match what the data means. If the select/option menu is selecting a supplier, it should be named that. The phone and note fields don’t even have names, and won’t be submitted with the form.

  3. The tags need value=’…’ attributes and the values should be the supplier id. All submitted data must be validated before using it, the supplier id is easier to validate, and any data being stored or retrieved concerning the selected supplier should use the id.