Inserting into MySQL DB from Drop Down List

cdrees

I am trying to pull information from a drop down list (pulling from one MySQL table) and insert that selected item, along with some other information, into another MySQL table.

The drop down list appears, I can select an item and hit submit, and it appears as though it goes through. However, when I look at the DB, all items are inserted into the DB EXCEPT the one I selected from the drop down list.... I'm sure it's something simple, but I'm stumped...

Database setup:

-- Table structure for table `academy_main`

DROP TABLE IF EXISTS academy_main;
CREATE TABLE IF NOT EXISTS academy_main (
id int(11) NOT NULL auto_increment,
course_title varchar(150) collate latin1_general_ci default NULL,
course_desc text collate latin1_general_ci,
location_st varchar(20) collate latin1_general_ci default NULL,
location_co varchar(75) collate latin1_general_ci default NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=14 ;

-- Table structure for table `academy_sched`

DROP TABLE IF EXISTS academy_sched;
CREATE TABLE IF NOT EXISTS academy_sched (
id int(11) NOT NULL auto_increment,
course_title varchar(150) collate latin1_general_ci default NULL,
course_date date default NULL,
location_st varchar(20) collate latin1_general_ci NOT NULL,
location_co varchar(20) collate latin1_general_ci NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=31 ;

Here is my PHP Code for the main page:

<?php

// open the connection
$conn = mysql_connect("localhost", "username", "password");
// pick the database to use
mysql_select_db("test",$conn);
// create the SQL statement
$sql = "SELECT course_title FROM academy_main";
// execute the SQL statement
$result = mysql_query($sql, $conn) or die(mysql_error());
//go through each row in the result set and display data

$options="";

while ($row=mysql_fetch_array($result)) 
{
    $id=$row["id"];
    $title=$row["course_title"];
    $options.="<option VALUE=\"$id\">".$title.'</option>';
}

?>


<body>

<form action="insert2.php" method="post">
<SELECT NAME=id>
<OPTION VALUE=0>Please choose course to schedule
<?=$options?>
</SELECT>
<br />
<br />
Date of Course (YYYY-MM-DD): <br /><input type="text" name="course_date"><br>
<input type="hidden" name="location_st" value="az"><br>
<input type="hidden" name="location_co" value="maricopa"><br>
<input type="Submit">
</form>

</body>

Here is the code for the page that is called from the FORM POST:

<?
$username="username";
$password="password";
$database="test";

$title=$_POST['course_title'];
$date=$_POST['course_date'];
$state=$_POST['location_st'];
$county=$_POST['location_co'];

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query = "INSERT INTO academy_sched VALUES ('','$title','$date','$state','$county')";
mysql_query($query);

mysql_close();

if(!$query){
        echo 'There was a error inserting the data into the database';
} else {
        echo 'Your course has been added';
        echo $options;
} 
?>

Thanks so much!!

rincewind456

As far as I can see you aren't picking up the course id, this is being passed in $POST['id'] not $POST['course_title'].

cdrees

I did look in google... for a couple hours now.. not helping, thanks anyway!

rincewind456

cdrees wrote:
I did look in google... for a couple hours now.. not helping, thanks anyway!

That's my sig and not in anyway aimed at you, my comment was above that and I still think that is your problem

cdrees

Gotcha... sorry about that. I saw your orginal reply, but to be honest... it's still not making sense to me... I am fairly new to using php to connec to to MySQL, and I'm not sure (from my form above) what variable/value I'm supposed to be passing to the insert form.

I've tried a bunch of different ways, and i'm just not understanding what the variable is being captured by the drop down box, and what I'm supposed to be passing to the insert form..

Thanks again!

rincewind456

First thing I notice after taking a deeper look is that your select statement only calls the course_title from the database, yet when you come to make you option select list up (3-4 rows later) you are trying to use the id , you need to amend your option box (see rewritten page below)

I would change line 30

<?=$options?>

<?php print $options?>

You will then on submission of your original form get the following $POST variables (Tip if you are unsure of what variables are being posted create a page

<?php
phpinfo();
?>

call it phpinfo.php and then send your form output to it in other words change you opening form tag to

<form action="phpinfo.php" method="post">

near the end of this long page you will find all the variables that have been initialised).

$_POST["id"]			// course id number
$_POST["course_date"] 	 //Date selected
$_POST["location_st"]	  // hidden input	az
$_POST["location_co"]	// hidden input	maricopa

these are the values you now have to insert to the database, and as you can see there is no course_title just a course id so to over come that here is a copy of the rewritten page

<?php

// open the connection
$conn = mysql_connect("localhost", "user", "password");
// pick the database to use
mysql_select_db("test",$conn);
// create the SQL statement
$sql = "SELECT id, course_title FROM academy_main";
// execute the SQL statement
$result = mysql_query($sql, $conn) or die(mysql_error());
//go through each row in the result set and display data

$options="";

while ($row=mysql_fetch_array($result))
{
    $id=$row["id"];
    $title=$row["course_title"];
    $options.="<option VALUE=\"$title\">".$title.'</option>'."\n";
}

?>


<body>

<form action="insert2.php" method="post">
<SELECT NAME="course_title">
<OPTION VALUE=0>Please choose course to schedule</option>
<?php print $options?>
</SELECT>
<br />
<br />
Date of Course (YYYY-MM-DD): <br /><input type="text" name="course_date"><br>
<input type="hidden" name="location_st" value="az"><br>
<input type="hidden" name="location_co" value="maricopa"><br>
<input type="Submit">
</form>

</body>

This should now produce the variables that you require for inserting to the database.

I would however change your insert page so that some data validation is done, even on the hidden fields, (remember they are not hidden in the source code) to protect against attacks to the database. Rewritten here:

<? 
$username="username"; 
$password="password"; 
$database="test"; 

$title=mysql_real_escape_string($_POST['course_title']); 
$date=mysql_real_escape_string(trim($_POST['course_date'])); 
$state=mysql_real_escape_string($_POST['location_st']); 
$county=mysql_real_escape_string($_POST['location_co']);

mysql_connect(localhost,$username,$password); 
@mysql_select_db($database) or die( "Unable to select database"); 

$query = "INSERT INTO academy_sched VALUES ('','$title','$date','$state','$county')"; 
mysql_query($query); 

mysql_close(); 

if(!$query){ 
        echo 'There was a error inserting the data into the database'; 
} else { 
        echo 'Your course has been added'; 
        echo $options; 
} 
?>

hope this helps. 🙂

cdrees

Thanks so much!!! That did it, although I am getting an error when I try to use the mysql_real_escape_string. When I remove that, it works fine... not sure why it wouldn't be working. I checked the php site, and the context for formatting looks correct..

Either way, thanks again so much for the help.

Here is the Warning/Error I get when I run it with the mysql_real_escape_string

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'ODBC'@'localhost' (using password: NO) in C:\xampp\htdocs\Academy\insert3.php on line 6

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in C:\xampp\htdocs\Academy\insert3.php on line 6

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'ODBC'@'localhost' (using password: NO) in C:\xampp\htdocs\Academy\insert3.php on line 7

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in C:\xampp\htdocs\Academy\insert3.php on line 7

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'ODBC'@'localhost' (using password: NO) in C:\xampp\htdocs\Academy\insert3.php on line 8

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in C:\xampp\htdocs\Academy\insert3.php on line 8

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'ODBC'@'localhost' (using password: NO) in C:\xampp\htdocs\Academy\insert3.php on line 9

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in C:\xampp\htdocs\Academy\insert3.php on line 9

cdrees

Also, just an FYI.... mysql_escape_string worked...

rincewind456

No it's my fauilt I didn't take a great deal of notice of the insert page other than to check that it would work, and as a result it didn't really enter my sluggish brain that your connection call was after the use of mysql_real_escape_string().

For it to work you need to move your connection to the database to the area above the first instance of mysql_real_escape_string().

As I always use an include() at the start of my scripts with the connection parameters this has never happened to me and it didn't occur to me to check. :o

cdrees

right on again... thanks so much! That saved me hours more work...

cdrees

I now understand conceptually how to pull data from one database via a drop down list, and insert into another... very helpful!!!

My next question, is to now do this the right way (recognizing db normalization) and not have repetitive date in my database.

My one table, as I mentioned before, has the following

Table: Academy_main
id
course_title // Title of the course
course_desc // Course Description
location_st // Hidden field - Course location (state)
location_co // Hidden field - Couse location (county)

Table: Academy_sched
id
course_date // date being scheduled
course_title // Being inserted from the academy_main table (pull down list)

I really don't want to keep inserting the actual course_title into the sched database each time. I want to link the two tables to that I only insert the id and date into the sched, and then when I need to show the scheduled courses I pull the course title and course description from the academy_main and the dates from the from the academy_sched database.

What I'm not sure how to do is link the databases within mysql, and/or make the joins with php. From what i've been reading, I'm not even sure if it's possible to create the foreign key links within MySQL (I need to setup an InnoDB to create foreign key links)?

Thanks very much to rincewind456 and everyone for the help!! This is such an awesome community to lend a hand and share their experience!

-Christopher Rees

rincewind456

The first thing you will have to do is take the auto_increment off of the academy_sched table so that when you allocate a date to a course the course_id can be entered into the date table.

You don't need to worry about foreign keys or innodb after you have taken away the unnecessary fields and left yourself with an academy_sched table with just the id and date you can do this query to join them

SELECT * FROM
academy_main m
INNER JOIN academy_sched s 
ON m.id = s.id WHERE m.id =14

The where clause can be anything you want for example "WHERE course_title = 'Learn Something'
Hope this helps.