Query to add form data to MySQL not working properly...

floorfiller · April 14, 2011

Hey guys,

New to the forum and a newer user of PHP / MySQL. I am having trouble with some code I've written up. I don't seem to get any errors when running it, but it's not updating my database the way that it should. hopefully a simple fix. I am thinking that it must be on the MySQL side of things.

Couple of things to start.

My html form is comprised completely of drop down list inputs. I'm the only user so I thought this would be the easiest approach.

Because of that I've made my PHP as follows:

<?php

$season = $_POST['season'];
$month = $_POST['month'];
$day = $_POST['day'];
$year = $_POST['year'];
$time = $_POST['time'];
$event = $_POST['event'];
$game = $_POST['game'];
$buyin = $_POST['buyin'];

$connect = mysql_connect('localhost','root','') or die('can not connect');

if ($connect)
{
    echo "connected to database";
}

$db = mysql_select_db('dpl') or die('can not find database');

if ($db)
{
    echo "DPL Selected";
}

$query = sprintf("INSERT INTO events (season , month , day , year , time , event , game , buyin)
    VALUES ('%s' , '%s' , '%s' , '%s' , '%s' , '%s' , '%s' , '%s')",
       $season ,
       $month ,
       $day ,
       $year ,
       $time ,
       $event ,
       $game ,
       $buyin
       );

if ($query)
{
    echo "Your event has been added";
}

?>

My connection is working, my database is selected and I'm even now getting confirmation that my query is working, but when i go to check my database there are no entries in it?

any thoughts?

I've tried the drop down variables as both VARCHAR and TEXT inputs in MySQL, but I can't seem to get it to work.

Any help is greatly appreciated.

floorfiller · April 14, 2011

Ok I've been working on this a little more and I'm starting to think that the issue is that my POST variables are not being passed along properly.

Does anyone know if there is something special that needs to be done in order to pass a drop down list variable to the php page?

Nodral · April 14, 2011

Can you post the form code and also the query you are running.

As it stands there is too much code missing to be able to decue where the error is.

Have you actually run $query as a mysql_query?

PaulRyan · April 14, 2011

Try the following...

<?php

$season = $_POST['season'];
$month = $_POST['month'];
$day = $_POST['day'];
$year = $_POST['year'];
$time = $_POST['time'];
$event = $_POST['event'];
$game = $_POST['game'];
$buyin = $_POST['buyin'];

$connect = mysql_connect('localhost','root','') or die('can not connect');

if ($connect)
{
    echo "connected to database";
}

$db = mysql_select_db('dpl') or die('can not find database');

if ($db)
{
    echo "DPL Selected";
}

$query = sprintf("INSERT INTO events (season , month , day , year , time , event , game , buyin)
    VALUES ('%s' , '%s' , '%s' , '%s' , '%s' , '%s' , '%s' , '%s')",
       $season ,
       $month ,
       $day ,
       $year ,
       $time ,
       $event ,
       $game ,
       $buyin
       );

$query = mysql_query($query);

if (mysql_affected_rows())
{
    echo "Your event has been added";
} else {
  echo 'Nothing has happened.';
}

?>

Not tested but should work

Regards, PaulRyan.

floorfiller · April 15, 2011

hey guys,

alright appreciate the help thus far. Paul Ryan I did make your change, however it still isn't working. I think because the form isn't passing the variables correctly i dunno.

here is the code for my form:

<html>

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

<table>

<th>Season</th><th>Month</th><th>Day</th><th>Year</th><th>Time</th><th>Event</th><th>Game</th><th>Buy-In</th>

<tr>

<td><select name="season">


<option>Season 01:</option>
<option>Season 02:</option>

</select></td>

<td><select name="month">

<option>January</option>
<option>February</option>
<option>March</option>
<option>April</option>
<option>May</option>
<option>June</option>
<option>July</option>
<option>August</option>
<option>September</option>
<option>October</option>
<option>November</option>
<option>December</option>

</select></td>

<td><select name="day">


<option>01</option>
<option>02</option>
<option>03</option>
<option>04</option>
<option>05</option>
<option>06</option>
<option>07</option>
<option>08</option>
<option>09</option>
<option>10</option>
<option>11</option>
<option>12</option>
<option>13</option>
<option>14</option>
<option>15</option>
<option>16</option>
<option>17</option>
<option>18</option>
<option>19</option>
<option>20</option>
<option>21</option>
<option>22</option>
<option>23</option>
<option>24</option>
<option>25</option>
<option>26</option>
<option>27</option>
<option>28</option>
<option>29</option>
<option>30</option>
<option>31</option>

</select></td>

<td><select name="year">

<option>2011</option>
<option>2012</option>

</select></td>


<td><select name="time">

<option>00:00 EST</option>
<option>01:00 EST</option>
<option>02:00 EST</option>
<option>03:00 EST</option>
<option>04:00 EST</option>
<option>05:00 EST</option>
<option>06:00 EST</option>
<option>07:00 EST</option>
<option>08:00 EST</option>
<option>09:00 EST</option>
<option>10:00 EST</option>
<option>11:00 EST</option>
<option>12:00 EST</option>
<option>13:00 EST</option>
<option>14:00 EST</option>
<option>15:00 EST</option>
<option>16:00 EST</option>
<option>17:00 EST</option>
<option>18:00 EST</option>
<option>19:00 EST</option>
<option>20:00 EST</option>
<option>21:00 EST</option>
<option>22:00 EST</option>
<option>23:00 EST</option>
<option>24:00 EST</option>

</select></td>
<td><select name="event">

<option>Event 01:</option>
<option>Event 02:</option>
<option>Event 03:</option>
<option>Event 04:</option>
<option>Event 05:</option>
<option>Event 06:</option>
<option>Event 07:</option>
<option>Event 08:</option>
<option>Event 09:</option>
<option>Event 10:</option>

</select></td>

<td><select name="game">

<option>No Limit Hold'em</option>
<option>No Limit Hold'em - Heads up</option>
<option>No Limit Hold'em - Short Handed</option>
<option>No Limit Hold'em - Shootout</option>

</select></td>

<td><select name="buyin">

<option>Freeroll</option>
<option>Invite Only</option>
<option>$5.00 + $.50</option>
<option>$10.00 + $1</option>
<option>$20.00 + $2</option>

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

</tr>

</table>

</form>


</html>

in an effort to try and isolate the problem i just tried to name a variable from my form and echo it such as:

$season = $_POST['season'];

echo $season;

and I get the following error message:

Notice: Undefined index: season in C:\xampp\htdocs\DPL\create_event.php on line 3

i guess i can only assume then that my form isn't passing along the variables correctly yes?

any further help is appreciated

Pikachu2000 · April 15, 2011

None of your <option></option> tags have values. They need them to be valid markup: <option value="value here">Some Value</option>

It would also save you some typing to build those form fields dynamically, using for() loops or arrays and foreach() loops . . .

floorfiller · April 15, 2011

AHA!!

thank you very much sir. I've got it working great now. such a simple solution I probably never would've found it.

thanks again

floorfiller · April 17, 2011

hey everyone,

sorry to open this back up, but i am having a very similar problem with another form that i've made. please take a look at this one as well. I've been looking around online and seeing some ways of maybe doing this, but Net Beans keeps telling me the syntax is wrong.

here is my html form. i think this is the problem. once again i don't think i'm setting the variables correctly:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

</head>

<body>


<form action='signup.php' method='post'>
<table>
<tr>
<td>Username: </td><td><input type='text' name='username' value=""></td>
</tr>
<tr>
<td></td><td></td>
<tr>
<td>Email: </td><td><input type='text' name='email' value=""></td>
</tr>
<tr>
<td>Country: </td><td><select name='country'>

<option value="1">United States</option>
<option value="2">Canada</option>

</select></td>
</tr>
<tr>
<td>Password: </td><td><input type='password' name='password' value=""></td>
</tr>
<tr>
<td></td><td></td>
</tr>
<tr>
<td></td><td><input type='submit' name="submit" value='Join Now'></td>
</tr>
</table>
</form>

</body>

</html>

and here is my php form. this should be ok it seems to be working properly just the variables aren't been recognized.


<?php

$username = $_POST['username'];
$email = $_POST['email'];
$country = $_POST['country'];
$password = $_POST['password'];

$connect = mysql_connect('localhost','root', '') or die('can not connect to localhost');

if ($connect)
{
    echo 'connected';
}

$db = mysql_select_db('dpl') or die('can not connect to DPL');

if ($db)
{
    echo 'DPL selected';
}

$query = mysql_query("INSERT INTO users ('userID' , 'username' , 'email' , 'password' , 'countryID')
    VALUES (NULL , '$username' , '$email' , '$password' , '$country' )");

if (mysql_affected_rows())
{
    echo "Thank you for Registering";
}


?>

now looking around I've seen a couple of things that might solve this problem. similarly to my last issue i think it has to do with the variables not being assigned a value.

i saw in one tutorial to do something similar to this:

<td>Username: </td><td><input type='text' name='username' value="<? =$_POST['username'];?>"></td>

but i am not getting this to work properly. i think the syntax is in error. if anyone has any ideas i would appreciate it

Pikachu2000 · April 17, 2011

I'm thinking your query is failing due to the fact that it appears you're trying to insert a NULL value into the table's primary key index. Separate the query string from the query execution, and add logic to handle any errors returned by MySQL. See what errors this gives you.

$query = "INSERT INTO users ('userID' , 'username' , 'email' , 'password' , 'countryID')
    VALUES (NULL , '$username' , '$email' , '$password' , '$country' )";
if( $result = mysql_query($query) ) {
     if( mysql_affected_rows() < 1 ) {
          echo "<br>Query succeeded, but no record was inserted by query: $query<br>";
     }
} else {
     echo "<br>Query string: $query<br>Failed with errror: " . mysql_error() . '<br>';
}

Regarding the use of <?= syntax, to echo a value use <?php echo instead. In any event, I doubt that is related to this problem.

floorfiller · April 17, 2011

Hey Pikachu,

Thank you for your continued help.

Here is the error message i received. I tried doing a couple of things including taking out the userID and NULL fields, but i get this same basic error still.

Query string: INSERT INTO users (username , email , password , countryID) VALUES (floorfiller , [email protected] , asdfg , 1 )
Failed with errror: 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 '@gmail.com , asdfg , 1 )' at line 2

dcro2 · April 17, 2011

It looks like you need some single quotes around those values so it would look like:

VALUES ('floorfiller', '[email protected]', 'asdfg' , 1 )

floorfiller · April 17, 2011

thanks dcro, but i've tried it both ways. still getting this error

Query string: INSERT INTO users ('username' , 'email' , 'password' , 'countryID') VALUES ('foam' , '[email protected]' , 'asdfg' , '1' )
Failed with errror: 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 ''username' , 'email' , 'password' , 'countryID') VALUES ('foam' , 'jason@gma' at line 1

Pikachu2000 · April 17, 2011

Table and field names don't get quoted; if you feel compelled to enclose them in something, use `backticks`. String values get 'quoted'.

INSERT INTO `table` (`field1`, `field2`) VALUES ('string1', 'string2')

floorfiller · April 17, 2011

thanks again Pikachu. that fixed it.

sorry for such basic problems. i'm trying to self teach all this stuff.

svabhishek · August 4, 2011

Hello everyone ... I am trying out something similar too but just cant get it to work

The code I have been using is as follows :

* My database is FB and the table in it is profs. The field in profs is rating

** The include/connection.php file is absolutely fine and it also echo me that the connection to the database has been successful

 FOR exp.php

			<?php include 'includes/connection.php'; ?>

				<h2>POST</h2>
				<form action="exp_process.php" method="post">
			    <select name="rating">
		                          <option value="1" selected="selected">1</option>
		                          			                          
		        <?php     
		                          for($i=2; $i<=10 ; $i++) {
		                          	echo '<option value="'.$i.'"> '.$i.' </option>';
		                          }
				?>			                          
		        </select><br>
                    <input type="button" value="submit" />
                </form>

 FOR exp_process.php

			<?php include 'includes/connection.php'; ?>		
			<?php

						$rating = $_POST['rating'];
						$query = "INSERT INTO `profs` (`rating`) VALUES (`rating`)";
						$result = mysql_query($query, $connection);

						if ($result) {
							// Success!
							echo " Success ";							
						} else {
							// Display error message.
							echo "<p>Updation Failed</p>";
							echo "<p>" . mysql_error() . "</p>";							
						}							

					?>

I tried to put in values to buttons and tried everything but dont know why but just can't get it to work the way it should. Any kind of help would be appreciated Thank you

floorfiller · August 4, 2011

Hello svabhishek,

The first thing I noticed when looking at your code is the query looks to be a little off. I would change that to this:

$query = "INSERT INTO `profs` (`rating`) VALUES ('$rating')";

Remember that you named your POST data to a variable called $rating. That's the variable that holds the VALUES that you're inputting. That would be the first thing I'd change.

Do that and let us know if you still have any issues.

floorfiller · August 4, 2011

also note that I quoted a little differently. One of the things I had trouble with was just the stupid syntax details. As Pickachu mentioned:

Table and field names don't get quoted; if you feel compelled to enclose them in something, use `backticks`. String values get 'quoted'.

INSERT INTO `table` (`field1`, `field2`) VALUES ('string1', 'string2')

svabhishek · August 4, 2011

Thanks a lot for the advice .. though my problem is solved at the moment ... I'll surely remember to quote properly ..

Sign In

Query to add form data to MySQL not working properly...

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information