PHP INSERT INTO coding for MySQL

[eraseyourhead]

I am having trouble getting a simple form to submit data to a database.  I have followed an example in a PHP/MySQL book (Welling and Thomson) and created a simple form to update a DVD collection.  Right now I just have a form started and am just trying to get it to INSERT records into my database.  It is making a connection to the database, but it returns my Error stating that the record could not be added.  While all of my code is very basic,I am just trying to get an understanding as to how it is working... I have looked in MySQL through command prompt and the database exists, but records are not being added.  I can add records to the table through CMD prompt.  I will post my code for the database and my two php files for inserting records.

 

Database:

 

create database movie_info;

 

use movie_info;

 

create table movies

                (movieid int unsigned not null auto_increment primary key,

                title char(50) not null,

                movieyear char(4) not null,

                genre char(25) not null,

                subgenre char(25),

                director char(30),

                actor1 char(30),

                actor2 char(30),

                actor3 char(30),

                discs char(2),

                season char(2),

                comments char(200)

                );

 

 

 

Form:

 

 

 

function input_form(){

?>

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

  <table bgcolor="#cccccc">

   <tr>

     <td colspan="2">Enter a new DVD:</td>

   <tr>

     <td>Title:</td>

     <td><input type="text" name="title"/></td></tr>

   <tr>

     <td>Year:</td>

     <td><input type="text" name="year"/></td></tr>

   <tr>

   <tr>

     <td>Genre:</td>

     <td><input type="text" name="genre"/></td></tr>

   <tr>

   <tr>

     <td>Sub-Genre:</td>

     <td><input type="text" name="subgenre"/></td></tr>

   <tr>

   <tr>

     <td>Director:</td>

     <td><input type="text" name="director"/></td></tr>

   <tr>

   <tr>

     <td>Actor:</td>

     <td><input type="text" name="actor1"/></td></tr>

   <tr>

   <tr>

     <td>Actor:</td>

     <td><input type="text" name="actor2"/></td></tr>

   <tr>

   <tr>

     <td>Actor:</td>

     <td><input type="text" name="actor3"/></td></tr>

   <tr>

   <tr>

     <td>Number of discs:</td>

     <td><input type="text" name="discs"/></td></tr>

   <tr>

   <tr>

     <td>Season:</td>

     <td><input type="text" name="season"/></td></tr>

   <tr>

   <tr>

     <td>Comments:</td>

     <td><input type="text" name="comments"/></td></tr>

   <tr>

  

     <td colspan="2" align="center">

     <input type="submit" value="Submit"/></td></tr>

   <tr>

 </table></form>

 <?php

 }

 

 

 

and the INSERT code:

 

 

 

<?php

                require_once('movie_functions.php');

                //require_once('db_functions.php');

               

                do_html_header('Moviebase');

               

                @$title = $_POST['title'];

                @$year = $_POST['year'];

                @$genre = $_POST['genre'];

                @$subgenre = $_POST['subgenre'];

                @$director = $_POST['director'];

                @$actor1 = $_POST['actor1'];

                @$actor2 = $_POST['actor2'];

                @$actor3 = $_POST['actor3'];

                @$discs = $_POST['discs'];

                @$season = $_POST['season'];

                @$comments = $_POST['comments'];

 

 if (!$title || !$year || !$genre) {

                echo "You have not entered all of the required details. <br />"

                                 ."Please go back and try again.<br /><br />"

                                 ."<a href='movies.php'>Go Back</a>";

    exit;

                }             

 

                @$db = new mysqli('localhost', 'root', '********', 'movie_info');

   

                if (mysqli_connect_errno()) {

                                echo "Error: Could not connect to database.  Please try again later.";

                                exit;

                                }

               

                $query = "INSERT INTO movies VALUES (NULL, '".$title."', '".$year."', '".$genre."', '".$subgenre."',

                                                '".$director."', '".$actor1."', '".$actor2."', '".$actor3."',

                                                '".$discs."', '".$season."', '".$comments."')";

                                               

                $result = $db->query($query);

               

                if ($result)

                                {

                                echo $db->affected_rows." has been inserted into the database.";

                                //input_form();

                                }

                                else

                                {

                                echo "An error has occurred. The item was not added.";

                               

                //for testing

                echo "<br />Result: ".$result;

                                echo "<br />".$title;

                                echo "<br />".$year;

                                echo "<br />".$genre;

                                echo "<br />".$subgenre;

                                echo "<br />".$director;

                                echo "<br />".$actor1;

                                echo "<br />".$actor2;

                                echo "<br />".$actor3;

                                echo "<br />".$discs;

                                echo "<br />".$season;

                                echo "<br />".$comments;

                                //input_form();

                                }

               

                $db->close();

               

                footer();

               

                ?>

 

This all will return all variable values (except $result), so it seems like $result is empty.

 

Any help in understanding this would be greatly appreciated, Thanks!

[AbraCadaver]

Instead of:

echo "An error has occurred. The item was not added.";

Do:

echo $db->error;

[taquitosensei]

With the way you have your query if the number of columns is different than the number of columns you're trying to insert it will fail. I usually specify my column names.

insert into table(column1,column2,column3) values('value1','value2','value3')

I am having trouble getting a simple form to submit data to a database.  I have followed an example in a PHP/MySQL book (Welling and Thomson) and created a simple form to update a DVD collection.  Right now I just have a form started and am just trying to get it to INSERT records into my database.  It is making a connection to the database, but it returns my Error stating that the record could not be added.  While all of my code is very basic,I am just trying to get an understanding as to how it is working... I have looked in MySQL through command prompt and the database exists, but records are not being added.  I can add records to the table through CMD prompt.  I will post my code for the database and my two php files for inserting records.

 

Database:

 

create database movie_info;

 

use movie_info;

 

create table movies

                (movieid int unsigned not null auto_increment primary key,

                title char(50) not null,

                movieyear char(4) not null,

                genre char(25) not null,

                subgenre char(25),

                director char(30),

                actor1 char(30),

                actor2 char(30),

                actor3 char(30),

                discs char(2),

                season char(2),

                comments char(200)

                );

 

 

 

Form:

 

 

 

function input_form(){

?>

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

  <table bgcolor="#cccccc">

   <tr>

     <td colspan="2">Enter a new DVD:</td>

   <tr>

     <td>Title:</td>

     <td><input type="text" name="title"/></td></tr>

   <tr>

     <td>Year:</td>

     <td><input type="text" name="year"/></td></tr>

   <tr>

   <tr>

     <td>Genre:</td>

     <td><input type="text" name="genre"/></td></tr>

   <tr>

   <tr>

     <td>Sub-Genre:</td>

     <td><input type="text" name="subgenre"/></td></tr>

   <tr>

   <tr>

     <td>Director:</td>

     <td><input type="text" name="director"/></td></tr>

   <tr>

   <tr>

     <td>Actor:</td>

     <td><input type="text" name="actor1"/></td></tr>

   <tr>

   <tr>

     <td>Actor:</td>

     <td><input type="text" name="actor2"/></td></tr>

   <tr>

   <tr>

     <td>Actor:</td>

     <td><input type="text" name="actor3"/></td></tr>

   <tr>

   <tr>

     <td>Number of discs:</td>

     <td><input type="text" name="discs"/></td></tr>

   <tr>

   <tr>

     <td>Season:</td>

     <td><input type="text" name="season"/></td></tr>

   <tr>

   <tr>

     <td>Comments:</td>

     <td><input type="text" name="comments"/></td></tr>

   <tr>

  

     <td colspan="2" align="center">

     <input type="submit" value="Submit"/></td></tr>

   <tr>

 </table></form>

 <?php

 }

 

 

 

and the INSERT code:

 

 

 

<?php

                require_once('movie_functions.php');

                //require_once('db_functions.php');

               

                do_html_header('Moviebase');

               

                @$title = $_POST['title'];

                @$year = $_POST['year'];

                @$genre = $_POST['genre'];

                @$subgenre = $_POST['subgenre'];

                @$director = $_POST['director'];

                @$actor1 = $_POST['actor1'];

                @$actor2 = $_POST['actor2'];

                @$actor3 = $_POST['actor3'];

                @$discs = $_POST['discs'];

                @$season = $_POST['season'];

                @$comments = $_POST['comments'];

 

 if (!$title || !$year || !$genre) {

                echo "You have not entered all of the required details. <br />"

                                 ."Please go back and try again.<br /><br />"

                                 ."<a href='movies.php'>Go Back</a>";

    exit;

                }             

 

                @$db = new mysqli('localhost', 'root', '********', 'movie_info');

   

                if (mysqli_connect_errno()) {

                                echo "Error: Could not connect to database.  Please try again later.";

                                exit;

                                }

               

                $query = "INSERT INTO movies VALUES (NULL, '".$title."', '".$year."', '".$genre."', '".$subgenre."',

                                                '".$director."', '".$actor1."', '".$actor2."', '".$actor3."',

                                                '".$discs."', '".$season."', '".$comments."')";

                                               

                $result = $db->query($query);

               

                if ($result)

                                {

                                echo $db->affected_rows." has been inserted into the database.";

                                //input_form();

                                }

                                else

                                {

                                echo "An error has occurred. The item was not added.";

                               

                //for testing

                echo "<br />Result: ".$result;

                                echo "<br />".$title;

                                echo "<br />".$year;

                                echo "<br />".$genre;

                                echo "<br />".$subgenre;

                                echo "<br />".$director;

                                echo "<br />".$actor1;

                                echo "<br />".$actor2;

                                echo "<br />".$actor3;

                                echo "<br />".$discs;

                                echo "<br />".$season;

                                echo "<br />".$comments;

                                //input_form();

                                }

               

                $db->close();

               

                footer();

               

                ?>

 

This all will return all variable values (except $result), so it seems like $result is empty.

 

Any help in understanding this would be greatly appreciated, Thanks!

[eraseyourhead]

Thanks for the help. It works now... It ended up being the length of the 'year' field... I am an idiot.  I was using 'test 2' in the form... the $db->error helped, thanks again!