Inputting form data to SQL

Question

I'm just trying to send form data to an SQL database.

The dbconnect.php has the config options, and does work, as I am able to retrieve my tables on other pages.

I am able to "echo" the information into a table/div whatever, when I press submit whether I do PHP self, or submit it to another page ie. addevent.php

I cannot however, seem to figure out what I'm doing wrong to send it to the SQL database. Now, unfortunatley I've been going at this for endless hours, so my code may be jumbled up a bit and I could be using different forms of sql, pdo etc.. At this point my minds exploding haha.

Anywho, heres the code - first one is the form page ie. form.php, the second is the addevent.php which is called by the form.

<?php include 'header.php'?>
<?php include 'dbconnect.php'?>




<form id="addevent" action="addevent.php" method="post">
 <fieldset>
  <legend>Add an Event</legend>
   <label for="showtitle">Title of Show</label>
    <input type="text" id="showtitle" name="showtitle" value=""></br>
   <label for="h">Headliner</label>
    <input type="text" id="h" name="h" value="">
   </br>
   <label for="fone">First Feature Act Name</label>
    <input type="text" id="fone" name="fone" value="">
   </br>
   <label for="ftwo">Second Feature Act Name</label>
    <input type="text" id="ftwo" name="ftwo" value="">
   </br>
   <label for="fthree">Third Feature Act Name</label>
    <input type="text" id="fthree" name="fthree" value="">
   </br>
   <label for="host">Host Name</label>
    <input type="text" id="host" name="host" value="">
   </br>
   <label for="aone">First Additional Performer Name</label>
    <input type="text" id="aone" name="aone" value="">
   </br>
   <label for="atwo">Second Additional Performer Name</label>
    <input type="text" id="atwo" name="atwo" value="">
   </br>
   <label for="athree">Third Additional Performer Name</label>
    <input type="text" id="athree" name="athree" value="">
   </br>
   <label for="afour">Fourth Additional Performer Name</label>
    <input type="text" id="afour" name="afour" value="">
   </br>
   <label for="stimeu">Event Start Time</label>
    <input type="time" id="stimeu" name="stimeu" value="">
   <input type="text" id="stimef" name="stimef" class="nodisplay" value="No Fill"></br>
   <label for="etimeu">Event End Time</label>
    <input type="time" id="etimeu" name="etimeu" value="">
   <input type="text" id="etimef" name="etimef" class="nodisplay" value="No Fill"></br>
   <label for="dateu">Event Date</label>
    <input type="date" id="dateu" name="dateu" value="">
   <input type="text" id="datef" name="datef" class="nodisplay" value="No Fill"></br>
   <label for="loadd">Address</label>
    <input type="text" id="loadd" name="loadd" value="">
   <label for="locity">City</label>
    <input type="text" id="locity" name="locity" value="">
   <label for="loprov">Prov</label>
    <input type="text" id="loprov" name="loprov" value=""></br>
   <input type="text" id="lourl" name="lourl"value=""></br>
   <label for="price">Price</label>
    <input type="text" id="price" name="price" value="">
   <label for="turl">Buy Tickets URL</label>
    <input type="text" id="turl" name="turl" value=""></br>
   <label for="pname">Promoter Name</label>
    <input type="text" id="pname" name="pname" value=""></br>
   <label for="purl">Promoter URL</label>
    <input type="text" id="purl" name="purl" value="">
   <label for="ptel">Promoter Telephone</label>
    <input type="tel" id="ptel" name="ptel" value="">
   <label for="pemail">Promoter email</label>
    <input type="email" id="pemail" name="pemail" value=""></br>
    <input type="submit" id="submit" value="Add Event">
 </fieldset>
</form>
</div>

    
    
    
    
    
    
    
    
    
    
    
    
    
    <?php include 'footer.php'?>

<?php include 'dbconnect.php'?>
<?php 

if(isset($_POST['showtitle'], $_POST['h'], $_POST['fone'], $_POST['ftwo'], $_POST['fthree'], $_POST['aone'], $_POST['atwo'], $_POST['athree'], $_POST['afour'], $_POST['stimeu'], $_POST['stimef'], $_POST['etimeu'], $_POST['etimef'], $_POST['dateu'], $_POST['datef'], $_POST['loadd'], $_POST['locity'], $_POST['loprov'], $_POST['lourl'], $_POST['price'], $_POST['turl'], $_POST['pname'], $_POST['purl'], $_POST['ptel'], $_POST['pemail']))



$sql="INSERT INTO may (id, showtitle, headline, fone, ftwo, fthree, aone, atwo, athree, afour, stimeu, stimef, etimeu, etimef, dateu, datef, loadd, locity, loprov, lourl, price, turl, pname, purl, ptel, pemail, addedstamp) 
VALUES ('$showtitle','$h','$fone','$ftwo','$fthree','$aone','$atwo','$athree','$afour','$stimeu','$stimef','$etimeu','$dateu','$datef','$loadd','$locity','$loprov','$lourl','$price','$turl','$pname','$purl','$ptel','$pemail')";
$result = $conn->query($sql); 
}
if(!mysql_query($sql,$conn)){

    die('Error: ' . mysql_error());

} 
    echo("<br>Input data is a success");



$mysql_close($conn)
?>
<h1>Form Data</h1>
<?php echo $_POST['showtitle']; ?><br>
<?php echo $_POST['h']; ?><br>
<?php echo $_POST['fone']; ?><br>
<?php echo $_POST['ftwo']; ?><br>
<?php echo $_POST['fthree']; ?><br>
<?php echo $_POST['host']; ?><br>
<?php echo $_POST['aone']; ?><br>
<?php echo $_POST['atwo']; ?><br>
<?php echo $_POST['athree']; ?><br>
<?php echo $_POST['afour']; ?><br>
<?php echo $_POST['stimeu']; ?><br>
<?php echo $_POST['stimef']; ?><br>
<?php echo $_POST['etimeu']; ?><br>
<?php echo $_POST['etimef']; ?><br>
<?php echo $_POST['dateu']; ?><br>
<?php echo $_POST['datef']; ?><br>
<?php echo $_POST['loadd']; ?><br>
<?php echo $_POST['locity']; ?><br>
<?php echo $_POST['loprov']; ?><br>
<?php echo $_POST['lourl']; ?><br>
<?php echo $_POST['turl']; ?><br>
<?php echo $_POST['pname']; ?><br>
<?php echo $_POST['purl']; ?><br>
<?php echo $_POST['ptel']; ?><br>
<?php echo $_POST['pemail']; ?><br>

EDIT: My brain is half shut off so I forgot some pertinent information:

The main error I am getting is: Notice: Undefined variable: showtitle in C:...\addevent.php on line 12 For each of the variables/names/whatever you want to call em.

I've tried multiple ways from other peoples posts and answers and for some reason I just cannot get the insert sql to work. I can retrieve fine, echo fine, just inserting I'm having major issues with. If someone could even show me the basic format of code I should be using/basic structure/syntax... or point me in the right direction that would be awesome. A lot of stuff online is fairly outdated, using old apis, or are just minor tutorials which have a lot of SQL injection in them, I've found it difficult to find UP TO DATE tutorials on the internet for this.

This is the syntax that is working for me on my ".php" page in which I retrive the table rows. (It works perfectly).

$sql = "SELECT id, featuref, featurel, hostf, hostl, openf, openl, midf, midl, otherf, otherl, additionalf, additionall, time, datec, location, locationurl, price, ticketurl, showname FROM january ORDER by date ASC, time ASC";
$result = $conn->query($sql);




if ($result->num_rows > 0) {
 echo 'Show';
 
     // output data of each row
     while($row = $result->fetch_assoc()) {
         echo "<table class='col-lg-12' id='event-container_'><th><h1 class='event-title-words'>
". $row["showname"]. "</h1>
            </th>
   
     }
     echo "Done";
} else {
     echo "0 results";
}

$conn->close();

Here is a shortened version to make it easier if anyone wants to edit, or show me possibly the correct way to go about this.

<?php include 'dbconnect.php'?>
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
     die("Connection failed: " . $conn->connect_error);
} 


<?php 

if(isset($_POST['showtitle']))



$sql="INSERT INTO may (id, showtitle, addedstamp) 
VALUES ('$showtitle')";
$result = $conn->query($sql); 
}
if(!mysql_query($sql,$conn)){

    die('Error: ' . mysql_error());

} 
    echo("<br>Input data is a success");



$conn->close();
?>
<h1>Form Data</h1>
<?php echo $_POST['showtitle']; ?><br>

The line under php include dbconnect.php is just what's in the dbconnect.php file, excluding servername, username, db info ...

dbconnect DOES work. Just putting the info there to help since my code is such a bloddy mess.

The ID is for auto-increment, the addedstamp is supposed to just be a time stamp for when the form was submitted into the database.

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

$showtitle = $_POST['showtitle'];
$h = $_POST['h'];
$fone = $_POST['fone'];
$ftwo = $_POST['ftwo'];
$fthree = $_POST['fthree'];
$aone = $_POST['aone'];
$atwo = $_POST['atwo'];
$athree = $_POST['athree'];
$afour = $_POST['afour'];
$stimeu = $_POST['stimeu'];
$stimef = $_POST['stimef'];
$etimeu = $_POST['etimeu'];
$etimef = $_POST['etimef'];
$dateu = $_POST['dateu'];
$datef = $_POST['datef'];
$loadd = $_POST['loadd'];
$locity = $_POST['locity'];
$loprov = $_POST['loprov'];
$lourl = $_POST['lourl'];
$price = $_POST['price'];
$turl = $_POST['turl'];
$pname = $_POST['pname'];
$purl = $_POST['purl'];
$ptel = $_POST['ptel'];
$pemail = $_POST['pemail'];

if(isset($_POST['showtitle'], $_POST['h'], $_POST['fone'], $_POST['ftwo'], $_POST['fthree'], $_POST['aone'], $_POST['atwo'], $_POST['athree'], $_POST['afour'], $_POST['stimeu'], $_POST['stimef'], $_POST['etimeu'], $_POST['etimef'], $_POST['dateu'], $_POST['datef'], $_POST['loadd'], $_POST['locity'], $_POST['loprov'], $_POST['lourl'], $_POST['price'], $_POST['turl'], $_POST['pname'], $_POST['purl'], $_POST['ptel'], $_POST['pemail']))

{

$sql="INSERT INTO may (showtitle, h, fone, ftwo, fthree, aone, atwo, athree, afour, stimeu, stimef, etimeu, etimef, dateu, datef, loadd, locity, loprov, lourl, price, turl, pname, purl, ptel, pemail) 
VALUES ('$showtitle','$h','$fone','$ftwo','$fthree','$aone','$atwo','$athree','$afour','$stimeu','$stimef','$etimeu','$etimef,'$dateu','$datef','$loadd','$locity','$loprov','$lourl','$price','$turl','$pname','$purl','$ptel','$pemail')";
$result = $conn->query($sql); 
}
if($result){
 echo "Success"; 
}

else { 
trigger_error("there was an error....".$conn->error, E_USER_WARNING);
}

$conn->close()
?>

And the error I am at now is Warning: there was an error....You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2015-12-06','12-06-2015','123 Middle Road','London','ON','https://www.google.ca/' at line 2 in C:\xampp\htdocs\comedyscene\addevent.php on line 48

I'm under the impression that your DB connection is `mysqli_` using an object oriented method in your query `$result = $conn->query($sql);` - While `mysql_` does not support this method. I'm thinking that you're mixing MySQL APIs, which do not intermix with each other. This for instance `if(!mysql_query($sql,$conn)){` you would theoretically be querying twice. So you need to show us how you're connecting, with which MySQL API. — Funk Forty Niner, Jan 18 '15 at 04:03
Plus, your columns and values don't seem to match. Another thing, you're only checking `if(isset($_POST['showtitle']...` but not assigning anything. There's far too much to do here. — Funk Forty Niner, Jan 18 '15 at 04:07
What do you mean the columns and values don't match? The h and headline? Good catch, I fixed that, but same result. — Alan Hill, Jan 18 '15 at 04:10
Look over (reload) my comments again, especially the first one and the second about you only checking and not assigning anything. Then you have the `id` in `INSERT INTO may (id,` I don't see anything in your values matching "id". Then this `$mysql_close($conn)` that doesn't do anything. — Funk Forty Niner, Jan 18 '15 at 04:11
You also have a missing brace for `if(isset($_POST['showtitle'].....` - Check for errors and you'll see what's going on, or not. ` — Funk Forty Niner, Jan 18 '15 at 04:15
I editted my post, and showed my working .php page where I retrieve table values. That should lend you towards which syntax I am trying to use. As for your second comment, what would I need to do for "assigning" something. As for ID, it is set as auto increment, unique. Therefore I figured it will automatically add that ID as each row is inserted in the table. Am I wrong here? — Alan Hill, Jan 18 '15 at 04:16
I've posted something to look at below. Please go over it very carefully. — Funk Forty Niner, Jan 18 '15 at 04:29

score 1 · Accepted Answer · edited May 23 '17 at 11:43

I'm going to outline the errors.

This if(isset($_POST['showtitle'].... has a missing opening brace {, which should have caused a parse error when using error reporting.

You're also not assigning anything, you're only checking if your POSTs are set.

You need to do (assign your variables to POSTs):

$showtitle = $_POST['showtitle'];

which should follow if(isset($_POST['showtitle'], $_POST['h'],....{

and do the same for the others, following that same pattern.

The following block should be removed and for a few reasons. You're using mysqli_ to connect with and then using mysql_ functions which do not intermix with each other.

if(!mysql_query($sql,$conn)){

    die('Error: ' . mysql_error());

}

which should be (and most likely you are using mysqli_ to connect with, and not mysql_.

if($result){
 echo "Success"; 
}

else { 
trigger_error("there was an error....".$conn->error, E_USER_WARNING);
}

Then this $mysql_close($conn) that should read as $conn->close();.

You also have mismatched columns and values as I noted in comments, one being id which does not reflect in your VALUES

$sql="INSERT INTO may (id,

You have enough to go in order to fix your code.

Add error reporting to the top of your file(s) which will help find errors.

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

// rest of your code

Sidenote: Error reporting should only be done in staging, and never production.

I would also like to note that you are open to SQL injection when using this type of method, use mysqli with prepared statements, or PDO with prepared statements, they're much safer.

Edit:

As per your edit: https://stackoverflow.com/revisions/28006989/3

$sql="INSERT INTO may (id, showtitle, addedstamp) 
VALUES ('$showtitle')";

You're choosing 3 columns but only inserting 1 value. If id and the column for the timestamp are to be set automatically (auto_increment etc.), you don't need them in your INSERT.

Plus, still a missing brace { for if(isset($_POST['showtitle'])) and still not assigning anything.

and still using

if(!mysql_query($sql,$conn)){

    die('Error: ' . mysql_error());

}

Remove it

If you wish to add a current timestamp for addedstamp, the use MySQL's NOW() function:

I.e.:

.... ,'$ptel','$pemail', NOW())";

Escape your values:

$showtitle = stripslashes($_POST['showtitle']);
$showtitle = mysqli_real_escape_string($conn, $_POST['showtitle']);

and do the same for the others.

Another edit:

You have a missing quote for $etimef in your VALUES for '$etimef,'$dateu' which should be '$etimef','$dateu'

Thanks, I seem to be making progress. Do I need html special characters or something of the sort for "input" boxes with "url's" in them? I am getting this error now. Warning: there was an error....You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2015-12-06','12-06-2015','123 Middle Road','London','ON','https://www.google.ca/' at line 2 in C:\xampp\htdocs\comedyscene\addevent.php on line 48 — Alan Hill, Jan 18 '15 at 04:46
@AlanHill Reload my answer and look near the bottom under **Escape your values:** - and go over my entire answer again. — Funk Forty Niner, Jan 18 '15 at 04:48
@AlanHill You have a missing quote for `'$etimef` which should be `'$etimef'`in your VALUES - this is now a big debugging issue. You need to go over your code and check for missing quotes etc. I have to go now. Good luck. — Funk Forty Niner, Jan 18 '15 at 04:52
I appreciate all the help. It's at least moving along now. Onto a new error --> Fatal error: Cannot use isset() on the result of a function call (you can use "null !== func()" instead) in C:\xampp\htdocs\...\addevent.php on line 61 Have a good night though! Mucho appreciado. — Alan Hill, Jan 18 '15 at 05:01
You're welcome @AlanHill - I suggest you use `if(isset($_POST['submit'])){ // code to execute }` to start with. Use `empty()` with a conditional statement to check if they're empty. I really have to sleep now. If my answer did indeed resolve most or all of the problem, please mark it as solved by accepting it. But name your submit like this `` in order for it to work with the conditional I've told you to use. That will get you going for now. — Funk Forty Niner, Jan 18 '15 at 05:04

score 1 · Answer 2 · answered Jan 18 '15 at 05:03

There are 3 major problems occured in your code:

Syntax Error
Variable Using
MySQL APIs

Syntax Error

There are some braces placed incorrectly , which first of them occured in your code line 11 after "if(isset(...".

Variable Using

$showtitle is a undefined variable, because you cannot use this format to reciver post data if you dont turn on the PHP config option register_globals.This option has been deprecated in PHP 5.3.0 and will be removed in PHP 5.4.0.Actually after version 4.2.0, this option has been off as default instead of on.

MySQL APIs

Seems you didnt give your code named 'dbconnect.php', so I cant figure out which way are you using to connect the MySQL database.So I just write my version of connect and insert data, the full code is below(I cut some variables to make it short to read):

<?php //include 'dbconnect.php'?>
<?php 

if(isset($_POST['showtitle'], $_POST['h'], $_POST['fone'])){
    /*use your own variables to recive post data*/
    $showtitle = $_POST['showtitle'];
    $h = $_POST['h'];
    $fone = $_POST['fone'];
    //connect database
    $con = mysql_connect("localhost","root","");
    //check if connect success
    if (!$con)
    {
        die('Could not connect: ' . mysql_error());
    }
    //select your database
    mysql_select_db("temp_projects");
    //run insert query

    //if you do have an "id" column which is auto increasing, the sql string should NOT contain it
    $sql="INSERT INTO may (showtitle, headline, fone) VALUES ('$showtitle','$h','$fone')";
    //if not, use sql which matches every column with the value
    //$sql="INSERT INTO may (id, showtitle, headline, fone) VALUES ('$id', '$showtitle','$h','$fone')";
    $result = mysql_query($sql);
    if(!$result){
        die('Error: ' . mysql_error());
    } 
        echo("<br>Input data is a success");

    //close connect
    mysql_close($con);
}
else{
    echo ("No Input Data.");
}

?>
<h1>Form Data</h1>
<?php echo $_POST['showtitle']; ?><br>
<?php echo $_POST['h']; ?><br>
<?php echo $_POST['fone']; ?><br>

score 0 · Answer 3 · answered Jan 18 '15 at 04:25

Your variables are not defined and empty. As you can see in your code, you are using the variables in your query the first time.

The simpel solution is to load the values of your posts into the variables, like:

$showtitle = $_POST['showtitle'];

Do that for all your variables in your query and put the code above of your insert.

The next point is, that you are inserting an ID, but you have no appropriated value in your insert. If this column is an auto increment column, then just drop the ID in your query, otherwise put a value in.

After your insert is running, you should read about verification and sanitisation of sql queries. In your query the user can input every value and this is not that you might want.

I have tried that (your example code) and I came up with the same type of errors. Regardless of where I put the variables it doesn't seem to recognize them as variables. I will remove the ID from the query, I thought I needed it in there, but I'm assuming from what you said if it's auto increment then I don't need to tell it to put the ID in as it will know to do it. Thanks for that. — Alan Hill, Jan 18 '15 at 04:27

Inputting form data to SQL

3 Answers3

Syntax Error

Variable Using

MySQL APIs