CODEWITHSUNDEEP
phpsqlformssql-insert
Alan Hill
Alan Hill

Reputation: 27

Inputting form data to SQL

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

Upvotes: 0

Views: 1255

Answers (3)

Bandon
Bandon

Reputation: 809

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>

Upvotes: 1

Funk Forty Niner
Funk Forty Niner

Reputation: 74217

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'

Upvotes: 1

Joerg
Joerg

Reputation: 3101

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.

Upvotes: 0

Related Questions