PDO Code Is not inserting data from Form into MySQL Database

Question

I have a form, when I submit it to itself, I get no useful errors except:

PDO::errorInfo(): Array ( [0] => 00000 [1] => [2] => ) Unable to save record. Please try again.

I have been banging away at this for hours. I will be fixing the input validation later. For now, I am just trying to get the data into my database, Please advise.

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

    // include configuration file
    include 'config/core.php';

    // include database connection
    include 'config/database.php';

    // page header
    $page_title="Create a Record";
    include_once "layout_head.php";

    // if the form was submitted
    if($_POST){

        try{

            echo "<p>Location id is : " . $_POST['location_id']."</p>";
            echo "<p>day is: " . $_POST['day']."</p>";
            echo "<p>Time is " . $_POST['time']."</p>";
            echo "<p>Fee is " . $_POST['fee']."</p>";
            echo "<p>Added is " . $_POST['added']."</p>";
            echo "<p>Game is " . $_POST['game']."</p>";
            echo "<p>Race is " . $_POST['race']."</p>";
            echo "<p>BIH is " . $_POST['bih']."</p>";
            echo "<p>Race is " . $_POST['race']."</p>";
            echo "<p>Handicapped is " . $_POST['handicapped']."</p>";
            echo "<p>Table_size is " . $_POST['table_size']."</p>";
            echo "<p>Tables_used " . $_POST['tables_used']."</p>";
            echo "<p>Cueball size is " . $_POST['cueball']."</p>";
            echo "<p>Equipment is " . $_POST['equipment']."</p>";
            echo "<p>Noise level is " . $_POST['noise']."</p>";
            echo "<p>The space is " . $_POST['space']."</p>";
            echo "<p>Pot is " . $_POST['pots']."</p>";
            echo "<p>Description: " . $_POST['description']."</p>";


            // data validation
            if($_POST['location_id']==0){
                echo "<div class='alert alert-danger'>Please select a location.</div>";
            }

            else if($_POST['day']=="empty"){
                echo "<div class='alert alert-danger'>Please select a day.</div>";
            }

            else if(empty($_POST['fee'])){ 
                echo "<div class='alert alert-danger'>Fee cannot be empty.</div>";
            }

            else if($_POST['added']=="empty"){
                echo "<div class='alert alert-danger'>Please select money added.</div>";
            }   

            else if($_POST['game']=="empty"){
                echo "<div class='alert alert-danger'>Please select a game.</div>";
            }   

            else if(empty($_POST['race'])){ 
                echo "<div class='alert alert-danger'>Race cannot be empty.</div>";
            }

            else if($_POST['handicapped']=="empty"){
                echo "<div class='alert alert-danger'>Please select handicapped.</div>";
            }

            else if($_POST['table_size']=="empty"){
                echo "<div class='alert alert-danger'>Please select the tables size.</div>";
            }       

            else if(empty($_POST['tables_used'])){ 
                echo "<div class='alert alert-danger'>Tables used cannot be empty.</div>";
            }       

            else if($_POST['cueball']=="empty"){
                echo "<div class='alert alert-danger'>Please select the cueball size.</div>";
            }       

            else if($_POST['equipment']=="empty"){
                echo "<div class='alert alert-danger'>Please rate equipment.</div>";
            }       

            else if($_POST['noise']=="empty"){
                echo "<div class='alert alert-danger'>Please rate noise.</div>";
            }               

            else if($_POST['space']=="empty"){
                echo "<div class='alert alert-danger'>Please rate space.</div>";
            }       

            else if($_POST['cueball']=="empty"){
                echo "<div class='alert alert-danger'>Please select the cueball size.</div>";
            }   

            else if($_POST['pots']=="empty"){
                echo "<div class='alert alert-danger'>Please select the pots.</div>";
            }           

            else if(empty($_POST['description'])){ 
                echo "<div class='alert alert-danger'>Description cannot be empty.</div>";
            }               
            else{
                // insert query
    /*          $query = "INSERT INTO tournaments SET location_id=:location_id,";
                $query .= "day=:day, time=:time, fee=:fee, added=:added, game=:game, race=:race, ";
                $query .= "bih=:bih, rule=:rule, handicapped=:handicapped, ";
                $query .= "table_size=:table_size, tables_used=:tables_used, cueball=:cueball, ";
                $query .= "equipment=:equipment, noise=:noise, space=:space, pots=:pots, description=:description, created=:created"; */

                $query = "INSERT INTO tournaments (`location_id`, `day`, `time`, `fee`, `added`, `game`, `race`, `bih`, `rule`, `handicapped`,";
                $query .= "`table_size`, `tables_used`, `cueball`, `equipment`, `noise`, `space`, `pots`, `description`, `created`) VALUES ";
                $query .= "(':location_id', ':day', ':time', ':fee', ':added', ':game', ':race', "; 
                $query .= "':bih', ':rule', ':handicapped', ':table_size', ':tables_used', ";
                $query .= "':cueball', ':equipment', ':noise', ':space', ':pots', ':description', ':created')"; 


                // prepare query for execution
                $stmt = $con->prepare($query);

                // bind the parameters
                $stmt->bindParam(':location_id', $_POST['location_id']);
                $stmt->bindParam(':day', $_POST['day']);
                $stmt->bindParam(':time', $_POST['time']);
                $stmt->bindParam(':fee', $_POST['fee']);
                $stmt->bindParam(':added', $_POST['added']);
                $stmt->bindParam(':game', $_POST['game']);
                $stmt->bindParam(':race', $_POST['race']);
                $stmt->bindParam(':bih', $_POST['bih']);
                $stmt->bindParam(':rule', $_POST['rule']);
                $stmt->bindParam(':handicapped', $_POST['handicapped']);
                $stmt->bindParam(':table_size', $_POST['table_size']);
                $stmt->bindParam(':tables_used', $_POST['tables_used']);
                $stmt->bindParam(':cueball', $_POST['cueball']);
                $stmt->bindParam(':equipment', $_POST['equipment']);
                $stmt->bindParam(':noise', $_POST['noise']);
                $stmt->bindParam(':space', $_POST['space']);
                $stmt->bindParam(':pots', $_POST['pots']);
                $stmt->bindParam(':description', $_POST['description']);

                // we need the created variable to know when the record was created
                // also, to comply with strict standards: only variables should be passed by reference
                $created=date('Y-m-d H:i:s');
                $stmt->bindParam(':created', $created);

                // Execute the query
                if($stmt->execute()){
                    echo "<div class='alert alert-success'>";
                        echo "Record was saved.";
                    echo "</div>";

                    // empty post array
                    $_POST=array();
                }else{
                    echo "<div class='alert alert-danger'>";
                        echo "\nPDO::errorInfo():\n";
                        print_r($con->errorInfo());
                        echo "Unable to save record. Please try again.";
                    echo "</div>";
                }
            }
        }

        // show error if any
        catch(PDOException $exception){
            die('ERROR: ' . $exception->getMessage());
        }
    }

    ?>

    <a href='read_tournaments.php' class='btn btn-primary pull-right margin-bottom-1em'>
        <span class='glyphicon glyphicon-list'></span> Read Records
    </a>

    <!--we have our html form here where user information will be entered-->
    <form action='create_tournament.php' method='post'>
        <table class='table table-bordered table-hover'>
            <tr>
                <td>Location</td>
                <td>
                <?php 
                // read the locations from the database

                // select all categories        
                $query = "SELECT id, name FROM locations ORDER BY name";    

                // prepare query statement and execute
                $stmt = $con->prepare( $query );
                $stmt->execute();

                // put them in a select drop-down
                echo "<select class='form-control' name='location_id'>";
                    echo "<option>Select location...</option>\n";

                    // loop through the locations
                    while ($row_location = $stmt->fetch(PDO::FETCH_ASSOC)){
                        extract($row_location);
                        echo "<option value='{$id}'>{$name}</option>\n";
                    }               
                echo "</select>";
                ?>
                </td>
            </tr>   
            <tr>
                <td>Day</td>
                <td>
                    <select class='form-control' name="day">
                        <option value="empty">Select Day...</option>
                        <option value="Monday">Monday</option>
                        <option value="Tuesday">Tuesday</option>
                        <option value="Wednesday">Wednesday</option>
                        <option value="Thursday">Thursday</option>
                        <option value="Friday">Friday</option>
                        <option value="Saturday">Saturday</option>
                        <option value="Sunday">Sunday</option>
                    </select>
                </td>
            </tr>
            <tr>
                <td>Time</td>
                <td><input type='text' name='time' class='form-control' required value="<?php echo isset($_POST['time']) ? htmlspecialchars($_POST['time'], ENT_QUOTES) : "";  ?>" /></td>
            </tr>       
            <tr>
                <td>Fee</td>
                <td><input type='text' name='fee' class='form-control' required value="<?php echo isset($_POST['fee']) ? htmlspecialchars($_POST['fee'], ENT_QUOTES) : "";  ?>" /></td>
            </tr>       
            <tr>
                <td>Added</td>
                <td>
                <select class='form-control' name="added">
                   <option value="empty">Money Added?</option>
                   <option value="No">No</option>
                   <option value="Yes">Yes</option>
                </select> 
                </td>
            </tr>       
            <tr>        

             <tr>
                <td>Game</td> 
                <td>
                    <select class='form-control' name="game">
                       <option value="empty">Select Game...</option>
                       <option value="8-Ball">8-Ball</option>
                       <option value="9-Ball">9-Ball</option>
                       <option value="10-Ball">10-Ball</option>
                       <option value="1-Pocket">1-Pocket</option>
                       <option value="Other">Other</option>
                    </select>
                </td>
            </tr>        
            <tr>
                <td>Side Pots</td> 
                <td>
                    <select class='form-control' name="pots">
                       <option value="empty">Select Pots...</option>                
                       <option value="10 Ball Pot">10 Ball Tickets</option>
                       <option value="Break Pot">Break Pot</option>
                       <option value="No">None</option>
                    </select> 
                </td>
            </tr>
            <tr>
                <td>Race</td>
                <td><input type='text' name='race' class='form-control' required value="<?php echo isset($_POST['race']) ? htmlspecialchars($_POST['race'], ENT_QUOTES) : "";  ?>" /></td>
            </tr>
            <tr>
                <td>Ball In Hand</td>
                <td>
                    <select class='form-control' name="bih">
                       <option value="empty">Select Ball In Hand...</option>                
                       <option value="yes">Ball In Hand</option>
                       <option value="no">No Ball In Hand</option>
                    </select> 
                </td>
            </tr>
            <tr>
                <td>Rule</td>
                <td>
                    <select class='form-control' name="rule">
                       <option value="empty">Select Rule...</option>
                       <option value="Call Pocket">Call Pocket</option>
                       <option value="Any Pocket">Any Pocket</option>   
                       <option value="Call Shot">Call Shot</option>
                    </select>           
                <td>
            </tr>
            <tr>
                <td>Handicapped?</td>
                <td>
                <select class='form-control' name="handicapped">
                  <option value="empty">Select Handicapped...</option>
                   <option value="No">No</option>
                   <option value="Yes">Yes</option>
                </select> 
                </td>
            </tr>
            <tr>
                <td>Table Size</td>
                <td>        
                    <select class='form-control' name="table_size">
                       <option value="empty">Select Table Size...</option>              
                       <option value="7 Ft. Bar">7 Ft. Bar</option>
                       <option value="9 Ft. Regulation">9 Ft. Regulation</option>
                    </select>   
                </td>
            </tr>   
            <tr>
                <td>Tables Used</td>
                <td><input type='text' name='tables_used' class='form-control' required value="<?php echo isset($_POST['tables_used']) ? htmlspecialchars($_POST['tables_used'], ENT_QUOTES) : "";  ?>" /></td>
            </tr>
            <tr>
                <td>Cueball</td>
                <td>
                    <select class='form-control' name="cueball">
                       <option value="empty">Select Cueball Size...</option>                    
                       <option value="Small Cue Ball">Small Cue Ball</option>
                       <option value="Large Cue Ball">Large Cue Ball</option>
                    </select>
                </td>
            </tr>
            <tr>
                <td>Equipment</td>
                <td>        
                    <select class='form-control' name="equipment">
                       <option value="empty">Rate Equipment...</option>                 
                       <option value="Excellent">Excellent</option>
                       <option value="Good">Good</option>
                       <option value="Poor">Poor</option>
                    </select>
                </td>
            </tr>
            <tr>
                <td>Noise</td>
                <td>        
                    <select class='form-control' name="noise">
                       <option value="empty">Rate Noise Level...</option>                   
                       <option value="Quiet">Quiet</option>
                       <option value="Moderate">Moderate</option>
                       <option value="Loud">Loud</option>
                    </select>
                </td>
            </tr>
            <tr>
                <td>Space</td>
                <td>        
                    <select class='form-control' name="space">
                       <option value="empty">Rate the Space...</option>                 
                       <option value="Excellent">Excellent</option>
                       <option value="Good">Good</option>
                       <option value="Poor">Poor</option>
                    </select>
                </td>
            </tr>       
            <tr>
                <td>Description</td>
                <td><textarea type='text' name='description' class='form-control' required><?php echo isset($_POST['description']) ? htmlspecialchars($_POST['description'], ENT_QUOTES) : "";  ?></textarea></td>
            </tr>
            <tr>
                <td></td>
                <td>
                    <input type='submit' value='Save' class='btn btn-primary' />
                </td>
            </tr>
        </table>
    </form>
    <!--<script type="text/javascript">$('#timepicker1').timepicker();</script> -->
    <?php 
    // page footer
    include_once "layout_foot.php";
    ?>

START SQL:

-- phpMyAdmin SQL Dump
-- version 4.2.7.1
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Nov 01, 2015 at 10:00 AM
-- Server version: 5.6.20
-- PHP Version: 5.5.15

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `ptms`
--

-- --------------------------------------------------------

--
-- Table structure for table `locations`
--

CREATE TABLE IF NOT EXISTS `locations` (
`id` int(11) NOT NULL,
  `name` varchar(128) NOT NULL,
  `address` varchar(128) NOT NULL,
  `city` varchar(50) NOT NULL,
  `state` varchar(50) NOT NULL,
  `zip` varchar(20) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `yelp` varchar(250) NOT NULL,
  `youtube` varchar(250) NOT NULL,
  `created` datetime NOT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

--
-- Dumping data for table `locations`
--

INSERT INTO `locations` (`id`, `name`, `address`, `city`, `state`, `zip`, `phone`, `yelp`, `youtube`, `created`, `modified`) VALUES
(1, 'Spring Valley Inn', '9034 Campo Rd', 'Spring Valley', 'CA', '91977', '(619) 303-7781', 'http://www.yelp.com/biz/spring-valley-inn-cocktail-lounge-spring-valley', 'https://www.youtube.com/watch?v=TkOHwpcvzjA', '0000-00-00 00:00:00', '2015-10-31 03:48:15'),
(2, 'Five Points Bar', '1881 S Escondido Blvd', 'Escondido', 'CA', '92025', '(760) 740-1139', 'http://www.yelp.com/biz/five-points-bar-escondido-2', 'https://www.youtube.com/watch?v=jEnd8JIMii4', '0000-00-00 00:00:00', '2015-10-31 03:48:15'),
(3, 'The Manhattan', ' 400 Broadway', 'Chula Vista', 'CA', '91910', '(619) 422-6641', 'http://www.yelp.com/biz/manhattan-cocktail-lounge-chula-vista', 'http://localhost/PTMS/create_location.php', '0000-00-00 00:00:00', '2015-10-31 03:48:15'),
(7, 'Manny''s Cocktail Lounge', '8729 Broadway', 'La Mesa', 'CA', '91941', '(619) 461-2170', 'http://www.yelp.com/biz/mannys-cocktail-lounge-la-mesa', 'http://localhost/PTMS/create_location.php', '0000-00-00 00:00:00', '2015-10-31 03:48:15');

-- --------------------------------------------------------

--
-- Table structure for table `tournaments`
--

CREATE TABLE IF NOT EXISTS `tournaments` (
`id` int(11) NOT NULL,
  `location_id` int(11) NOT NULL,
  `day` varchar(20) NOT NULL,
  `time` varchar(30) NOT NULL,
  `fee` varchar(30) NOT NULL,
  `added` varchar(30) NOT NULL,
  `game` varchar(20) NOT NULL,
  `race` varchar(30) NOT NULL,
  `rule` varchar(20) NOT NULL,
  `handicapped` varchar(20) NOT NULL,
  `table_size` varchar(30) NOT NULL,
  `tables_used` varchar(30) NOT NULL,
  `cueball` varchar(20) NOT NULL,
  `equipment` varchar(20) NOT NULL,
  `noise` varchar(20) NOT NULL,
  `space` varchar(20) NOT NULL,
  `pots` varchar(30) NOT NULL,
  `description` text NOT NULL,
  `created` datetime NOT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `locations`
--
ALTER TABLE `locations`
 ADD PRIMARY KEY (`id`);

--
-- Indexes for table `tournaments`
--
ALTER TABLE `tournaments`
 ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `locations`
--
ALTER TABLE `locations`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=8;
--
-- AUTO_INCREMENT for table `tournaments`
--
ALTER TABLE `tournaments`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

I applaud for all the inline syntax...beside that, I think you have syntax error in your mysql insert statement. I think SET is for update statement in MYSQL. you should INSERT INTO {table} (col, col,...) VALUES (val, val,...), for accuracy google search.. — Andrew
– Andrew, Commented Nov 1, 2015 at 7:25
As @Andrew stated, INSERT query is not like what you wrote ; dev.mysql.com/doc/refman/5.7/en/insert.html ; w3schools.com/sql/sql_insert.asp — Mi-Creativity
– Mi-Creativity, Commented Nov 1, 2015 at 7:28
Also, just a side note, this multi else if would work but not smartly, what happens if three entries are empty, it will check only one entry every time, instead, remove the else, add all error line to a variable, and in the end echo the error variables with all errors in it into the error page — Mi-Creativity
– Mi-Creativity, Commented Nov 1, 2015 at 7:33
Alternatively you can check all required fields in one if statement, and echo one error something like "all fields marked with an asterisk (*) are required ", of course you need to put the asterisk character next to each required label in the form — Mi-Creativity
– Mi-Creativity, Commented Nov 1, 2015 at 7:36
To what kind of database system is config/database.php connecting to? — VolkerK
– VolkerK, Commented Nov 1, 2015 at 8:19

Thorbear · Accepted Answer · 2015-11-01 09:41:15Z

1

You're printing error information from the connection object, but not looking at error information from the statement object. You might want to look at the manual entry for PDO::errorInfo().
You also catch PDOException, but we can't see any code where those exceptions are enabled, see the manual entry for PDO::setAttribute() in case you're not aware that you need to enable these.

There also appears to be a column in your statement called bih, but this column is nowhere in the table configuration you've pasted.

edited Nov 1, 2015 at 9:41

answered Nov 1, 2015 at 9:36

Thorbear

2,35328 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

OrionEllis Over a year ago

Thank you @Thorbear. Your advice lead me to change from print_r($con->errorInfo()); to print_r($stmt->errorInfo()); and gave me the same BIH error. I was, in fact, missing that column in the database. I learned something from you about difference in error reporting (connection vs. sql execution). Everything working now that I added BIH (Ball in hand). Pool player terminology.

Collectives™ on Stack Overflow

PDO Code Is not inserting data from Form into MySQL Database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related