Populate HTML Select Box from MySQL

carterlangley · February 28, 2014

Hi all,

I really need some help here please. I am going to include my code that I have.

Problem is the select box is not loading from the database. The connection is there, that I have established. But the select box is just not populating. I have now been trying for the last 3 days and just cannot get this to work. I really need some help here please! I am sorry if this is a topic that has been thrashed to death, but I am at my wits end and just cannot get this to work no matter how hard I Google!!

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
	"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
	<title>Teaching Int Limited - Home</title>
	<meta name="title" content="Teaching Int Limited">
	<link rel="shortcut icon" href="home/images/favicon.ico">
	<meta http-equiv="Window-target" content="_top">
	<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
	<meta http-equiv="Content-Language" content="en-gb">
	<meta name="MSSmartTagsPreventParsing" content="TRUE"><!--Providing Quality English Teachers to Schools and Organisations-->
	<meta name="description" content="Providing Quality English Teachers to Schools and Organisations">
	<meta name="Abstract" content="Providing Quality English Teachers to Schools and Organisations">
	<meta name="keywords" content="TEFL,english,teacher">
	<meta name="Robots" content="index, follow">
	<meta name="Distribution" content="Global">
	<meta name="Revisit-After" content="30 days">
	<meta name="Rating" content="General">
	<meta name="Reply-to" content="info@teaching-int.com">
	<meta name="Owner" content="Teaching Int Limited">
	<meta name="Author" content="Carter Langley">
	<meta name="copyright" content="Teaching Int Limited - 2013">
	<link href='http://fonts.googleapis.com/css?family=Great+Vibes' rel='stylesheet' type='text/css'>
	<link rel="stylesheet" type="text/css" href="form.css">
	<link rel="stylesheet" type="text/css" href="header.css">
	<link rel="stylesheet" type="text/css" href="site.css">
</head>
<body>
<?php include("header.php"); ?>
<?php

if(isset($_POST['add']))
{
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbbase = 'database';
$conn = mysql_connect($dbhost, $dbuser, $dbpass, $dbbase);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$campus_id = $_POST['campus_id'];
$company_id = $_POST['company_id'];
$campus_name = $_POST['campus_name'];
$address_street = $_POST['address_street'];
$address_city = $_POST['address_city'];
$address_province = $_POST['address_province'];
$address_postalcode = $_POST['address_postalcode'];
$address_country = $_POST['address_country'];
$main_tel = $_POST['main_tel'];
$second_tel = $_POST['second_tel'];
$main_email = $_POST['main_email'];
$second_email = $_POST['second_email'];

$sql = "INSERT INTO `teaching`.`campus` (`campus_id`, `company_id`,'campus_name','address_street','address_city','address_province','address_postalcode','address_country','main_tel','second_tel','main_email','second_email') 
VALUES ('NULL', '$company_id','$campus_name','$address_street','$address_city','$address_province','$address_postalcode','$address_country','$main_tel','$second_tel','$main_email','$second_email')";
$retval = mysql_query( $sql, $conn );

if(! $retval )
{
die('Could not enter data: ' . mysql_error());
}
echo "Entered data successfully. Redirecting in 2 seconds.\n";
mysql_close($conn);
header('Refresh: 2; URL=edit.php');
}
else
{
?>
<form method="post" action="<?php $_PHP_SELF ?>">
<table width="100%" align="left" border="0" cellspacing="1" cellpadding="2">
<tr>
<td><label for="company_id">Company</label></td>
<td>
<select name="company_name">
<option value="">--- Select a Company ---</option>
<?php
$sql_select = "SELECT * FROM `company`";
$retval_selectcompany = mysql_query( $sql_select, $conn );
while($row = mysql_fetch_assoc($retval_selectcompany)) {
echo '<option value='.$row["company_name"].'>'.$row["company_name"].'</option>';
}
?>
</select>
</td>
</tr>
<tr>
<td><label for="campus_name">Campus Name</label></td>
<td><input name="campus_name" type="text" id="campus_name"></td>
</tr>
<tr>
<td><label for="address_street">Street Address</label></td>
<td><input name="address_street" type="text" id="address_street"></td>
</tr>
<tr>
<td><label for="address_city">City</label></td>
<td><input name="address_city" type="text" id="address_city"></td>
</tr>
<tr>
<td><label for="address_province">Province</label></td>
<td><input name="address_province" type="text" id="address_province"></td>
</tr>
<tr>
<td><label for="address_postalcode">Postal Code</label></td>
<td><input name="address_postalcode" type="text" id="address_postalcode"></td>
</tr>
<tr>
<td><label for="address_country">Country</label></td>
<td><input name="address_country" type="text" id="address_country"></td>
</tr>
<tr>
<td><label for="main_tel">Main Tel</label></td>
<td><input name="main_tel" type="text" id="main_tel"></td>
</tr>
<tr>
<td><label for="second_tel">Alt Tel</label></td>
<td><input name="second_tel" type="text" id="second_tel"></td>
</tr>
<tr>
<td><label for="main_email">Main Email</label></td>
<td><input name="main_email" type="text" id="main_email"></td>
</tr>
<tr>
<td><label for="second_email">Second Email</label></td>
<td><input name="second_email" type="text" id="second_email"></td>
</tr>
<tr>
<td></td>
<td>
<input name="add" type="submit" id="add" value="Add Campus">
</td>
</tr>
</table>
</form>
<?php
}
?>
</body>
</html>

Ch0cu3r · February 28, 2014

Your code is only connecting to mysql when your form has been submitted

if(isset($_POST['add']))
{
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbbase = 'database';
$conn = mysql_connect($dbhost, $dbuser, $dbpass, $dbbase);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
...

Move your database connection code outside of the if

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbbase = 'database';
$conn = mysql_connect($dbhost, $dbuser, $dbpass, $dbbase);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}

if(isset($_POST['add']))
{
...

Edited February 28, 2014 by Ch0cu3r

mac_gyver · February 28, 2014

this is apparently part of a class/course. whomever is teaching should have shown some basic troubleshooting tips to get php and your code to help you.

1) you need to set php's error_reporting to E_ALL and display_errors to ON in your php.ini to get php to help you.

2) you need to always check for query errors. you are checking if your connection and the insert query is working, but you are not checking and reporting if the select query is working.

the above two suggestions will have been producing php errors and/or query errors that would alert you to the fact that your database connection isn't present when you try to run the select query and you will also get a mysql error (after you have a connection present for both queries) telling you that you are not selecting a database. the 4th parameter of the msyql_connect() statement is not the database name. you need to use a mysql_select_db() statement to select a database.

you will also be getting a php error due to the $_PHP_SELF. that variable doesn't exist at all and in the older php versions where something like that variable did exist, it would have been $PHP_SELF. you should be using $_SERVER['PHP_SELF'] in your form's action='' attribute or more simply just leave the action='' attribute empty to submit to the same page.

lastly, the mysql_ (no i) functions are depreciated and will be removed in a future version of php. you need to use the mysqli or PDO database functions so that you don't need to rewrite your code when the mysql_ functions get removed from the php language.

Edited February 28, 2014 by mac_gyver

carterlangley · March 1, 2014

Hi mac_gyver

Nope, this is not part of a class/course. This is something I am trying to develop for a business I have just started. Can't afford professional developers at this stage so have to go it alone for now! My poor brain is overheating trying to figure this whole lot out. I come from an oil and gas background doing rope access work so this is something that is totally alien to me.

1) I tried that previously as advised and it did absolutely nothing at all. Got a blank page. But, saying that, I have managed to get the page to display at least. So there is some progress there......

2) Query errors. I did a check on the connection to the database by supplying an incorrect password and I got the necessary error. So the connection is working and correct. I have been able to submit information to the database as well, so no problem with the connection.

The database name is part of the connection statement and not selected seperately. Apparently this is an acceptable manner to connect as well?

I had no idea that the self variable was incorrect. On my other pages that just submit info that variable seems to be working fine?

When it comes to the mysqli functions, I am at a total loss. All I can hope for is that the company starts to make money in the next two years so that I can afford to hire a professional developer to redo everything. Otherwise it is back to LibreOffice Calc which is not the ideal solution at all.

But, you have managed to bring some possibilities to mind that I am going to be checking out. I will revert back to you with the results once I have tried them out and let you know the outcomes.

Thank you so much for your detailed response. It is nice to know in this day and age that there are still people out there who are prepared to actually sit down and take the time to try and help others!

carterlangley · March 1, 2014

Hi mac_gyver

I tried changing the variable to what you suggested. No go.

I moved the connection information to above the if statement. No go.

I tried the error class you gave. No go.

I am getting a blank page now. Nothing happening at all.

Ch0cu3r · March 1, 2014

I am getting a blank page now. Nothing happening at all.

Then do as mac_gver suggested to do first

1) you need to set php's error_reporting to E_ALL and display_errors to ON in your php.ini to get php to help you.

or add the following as the first couple of lines in your code

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
?>

Report the errors here

When it comes to the mysqli functions, I am at a total loss.

There is hardly any difference between the two. The only difference is you need to add i infront of mysql in the function name, and where the connection resource was optional in the mysql_* function it is now required, and is now the first argument to the function that requires it.

The bit you maybe confused by is the OOP aspect. Which is fine just concentrate on the procedural functions. The mysqli documentation always gives code examples in both coding styles.

Also I didn't notice this earlier, (as I though you where using mysqli). This

$conn = mysql_connect($dbhost, $dbuser, $dbpass, $dbbase);

Should actually be

$conn = mysql_connect($dbhost, $dbuser, $dbpass)
mysql_select_db($dbbase);

mysql_connect does not have a forth argument for passing the the database name.

Edited March 1, 2014 by Ch0cu3r

mac_gyver · March 1, 2014

you are able to insert information into your database table because your query specified the database_name.table_name - i.e. `teaching`.`campus`. your select query won't work without either selecting the database in the php code or specifying the database name along with the table name. consistency counts along with brevity/clearness in coding. since most applications will have only one database, you should select the database in your php code and leave it out of the query statements.

your usage of $_PHP_SELF is incorrect. the only reason it works as a form action is because an empty/nonexistent variable will result in an empty action='' attribute. however, when you turn on php's error reporting this will be replaced by a php error message and then won't submit (you will typically get a 404 error because using the php error message as a url to submit the form to doesn't exist.)

after you set php's error_reporting and display_errors as suggested in your php.ini, for the master php.ini, you will need to restart your web server to get the changes to take effect. you should also confirm that the changes were made to the php.ini that php is using by looking at the output from a phpinfo() statement.

this is the line running your select query - $retval_selectcompany = mysql_query( $sql_select, $conn ); it has no error checking, so you don't know if or why it is failing (currently the while(){} loop trying to retrieve the data will throw a php error, if php's error reporting/display errors were on, about the result resource and the loop will be skipped over.) this is your error checking logic after the insert query is executed - if(! $retval ) { die('Could not enter data: ' . mysql_error()); } you need to do something similar after the select query so that any query errors will be detected and reported (you will be getting a 'no database selected' error in the current code.)

the reason those replying to you in this thread know that the 4th parameter in the mysql_connect() function isn't the database (it's a flag that forces a new database connection, in mysqli it is the database name), that you need to select the database in the posted code so that all the queries will work, that there are not that many differences between using the mysql and mysqli functions, and that $_PHP_SELF isn't a php variable (past or current) is because we have studied those specific sections in the php.net documentation as part of our learning and experience and would not have taken the time to write out what your posted code is doing incorrectly if it wouldn't have been of help to you.

Edited March 1, 2014 by mac_gyver

carterlangley · March 1, 2014

I added this to the top of the page

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
?>

Still a blank page. What is php.ini?

So all I have to do is change mysql to mysqli in the code? I tried to make sense of the documentation but I have no idea what they are talking about. This has been a copy and paste exercise for me as I have absolutely no idea what I am doing. What worries me is how on earth do I protect all the private information that is being displayed from snooping eyes? And what is OOP and procedural functions?

How do I restart a webserver? Is Apache a webserver or LAMP or WAMP or something?

Hell, sorry if I am asking stupid questions but I am really battling to get my head around this!

Edited March 1, 2014 by carterlangley

jazzman1 · March 1, 2014

Why did you close the connection and then you want to run a new sql select statement?

echo "Entered data successfully. Redirecting in 2 seconds.\n";
mysql_close($conn);
header('Refresh: 2; URL=edit.php');
}
else
{
?>
<form method="post" action="<?php $_PHP_SELF ?>">
<table width="100%" align="left" border="0" cellspacing="1" cellpadding="2">
<tr>
<td><label for="company_id">Company</label></td>
<td>
<select name="company_name">
<option value="">--- Select a Company ---</option>
<?php
$sql_select = "SELECT * FROM `company`";
$retval_selectcompany = mysql_query( $sql_select, $conn );
while($row = mysql_fetch_assoc($retval_selectcompany)) {
echo '<option value='.$row["company_name"].'>'.$row["company_name"].'</option>';
}
?>

PS: Why don't you read what gurus suggested above to debug mysql queries using a mysq_error function?

$retval_selectcompany = mysql_query( $sql_select, $conn );

// to 

$retval_selectcompany = mysql_query( $sql_select, $conn ) or die(mysql_error());

More, you should echo this php variable to get some result, but it's not the issue here.

<form method="post" action="<?php $_PHP_SELF ?>">

Edited March 1, 2014 by jazzman1

Ch0cu3r · March 1, 2014

How do I restart a webserver? Is Apache a webserver or LAMP or WAMP or something?

yes, Apache is the webserver. Any changes you have made to the servers configuration (either Apache's httpd.conf or PHP's php.ini) the webserver needs to restarted in order for the changes to take affect.

LAMP and WAMP are common acronyms used to describe the software stack a server is using for hosting a website. The first letter represents the operating system/environment, So L is is for Linux, W is for Windows and M is for Mac. The other three letters - AMP - represents Apache, PHP and MySQL.

Edited March 1, 2014 by Ch0cu3r

carterlangley · March 1, 2014

And to everyone who has been helping me with this problem............It is now working!! You cannot believe how relieved and grateful I am for the help guys! Thank you so much.

A decent book on php and mysql and html is definitely on my list of things to buy next!

jazzman1 · March 1, 2014

The other three letters - AMP - represents Apache, PHP and MySQL.

An abbreviation of LAMP also represents (for me) - Linux/Apache/MySQL/PHP/Perl/Python :happy-04:

Sign In

Populate HTML Select Box from MySQL

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information