[Resolved] Form data not posting to db, ugh

Barb54

I WILL get this form working, lol. Ok, I have the form now and the insert data file. I fixed all the syntax errors that came up and now it comes up with the Success Page but nothing posted to the database.

I filled out the form and left some fields blank to see if it was working. In one sense it may be working because the data didn't post with the blank fields, but I got no error msgs on any of the checked fields.

I "think" the problem is in the form checks so I will post that here and see if anyone can see what my problem is. Please go easy on me and remember I am a complete newbie and designing this from scratch, lol.

<?php 
include("header.php");
include("dbconnect.php"); 
?>
<?
if(isset($_POST["submit"])) 

//set variables for the form post
$nick=$_POST['nick'];
$pwdb=$_POST['pwdb'];
$date=$_POST['joindate("m,d,Y")'];
$fname =$_POST['fname'];
//combine birthdate mm/dd selections into 1 field
$bday = $_POST['bdaymm'||'bdaydd'];
$ovr13=$_POST['ovr13'];
$email=$_POST['email'];
$country=$_POST['country'];
$state=$_POST['abbrev'];
$otherNicks=$_POST['otherNicks'];
$newZone=$_POST['newZone'];
$zoneStatus=$_POST['zoneStatus'];
$plusNick=$_POST['plusNick'];
$referredby=$_POST['referredby'];
$reason=$_POST['reason'];
$coc=$_POST['coc'];
$status=$_POST['status'];
$rank=$_POST['rank'];

//do the form field checks before accepting the post

//certain fields should not be "blank"
if ($nick==""||$pwdb==""||$email==""||$country=="none selected"|| $state=="none selected"||$referredby==""||$reason=="")  {
     echo "All fields with an "*" are required fields.  Please try again.";
}

//check the response to yes/no questions
     if ($ovr13=='No'){
     echo "You must be over 13 yrs of age to join this club!";
}
     if ($coc=='No'){
     echo "We're sorry you don't agree to our Rules :-(.  Your Application is rejected.";
}

//check to see if email already exists in database
$sql="SELECT * FROM players";
$result=mysql_query($sql,$dblink);
if ($email=='email') {
  echo "You already have an account with us.  You can only have 1 account.  Application is rejected.";
}
 else {

$sql="insert into players (nick,pwdb,joindate,fname,bday,bdaydd,ovr13,email,country,state,otherNicks,newZone,zoneStatus,plusNick,referredby,reason,coc,status,rank) VALUES ('$nick','$pwdb','$date','$fname','$bday','$ovr13','$email','$country','$state','$otherNicks','$newZone','$zoneStatus','$plusNick','$referredby','$reason','$coc','$status','$rank')";

$result = mysql_query($sql);
}
?>

Success Msg

Thanks in advance again, lol.

Barb

nemonoman

Well, here are some suggestions:
First, this is wrong:

$sql="SELECT * FROM players";

//this statement would select ALL the player records
$result=mysql_query($sql,$dblink);

//This simply runs the query, put you haven't fetched any results

if ($email=='email') {
// as you've coded it, this only checks if the user's form input was the word 'email'

What you MEANT to say is:

$sql="SELECT * FROM players where email=$email";
$result=mysql_query($sql,$dblink);
$row=mysql_fetch_array($result);
if ($email==$row['email']) {

As for the insert: it's clearly broken, but we don't yet understand how:

add this code to diagnose the problem:

$result = mysql_query($sql);

if(!$result){ //query failed:

echo "The failed query was:<BR>$sql<BR>:
echo mysql_error,"<BR>";
echo mysql_errno, "<BR>";
}

Barb54

Thanks,
I corrected the code as you suggested and inserted the code you suggested to insert at the end. I had to fix it but now this is what I get:

Remember, I am processing a "blank" form so I can test the form field checks and got no errors on those so far and they were blank.

The success page comes up with this error at top:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\location\apps.php on line 48
You already have an account with us. You can only have 1 account. Application is rejected.The failed query was:
SELECT * FROM players where email=
:mysql_error
mysql_errno

$sql="SELECT * FROM players where email=$email"; 
$result=mysql_query($sql,$dblink); 
$row=mysql_fetch_array($result);      //LINE 48
if ($email==$row['email']) {
  echo "You already have an account with us.  You can only have 1 account.  Application is rejected.";
}

//leaving out code for the insert here

//here is code you had me insert
if(!$result){ //query failed: 

echo "The failed query was:<BR>$sql<BR>": 
echo mysql_error,"<BR>";    //line 61
echo mysql_errno, "<BR>"; 
}

Ok, what do I need to start looking at?

Thanks,
Barb

nemonoman

Well, first, have you defined '$dblink'?
Have you actually connected to a database with some code that contains 'mysql_connect('

More likely the problem is this: Need quotes around $email...

SELECT * FROM players where email='$email'

the IF statement checks if the variable $email==$row['email']

Next have you defined $email with a value?
If not, you might find a players record
WHERE email=''

So you want to use a value for $email that is not in the players table. Otherwise you'll find the record with a blank email every time and never insert the new data.

The code I suggested had an error: Please use this code:

if(!$result){ //query failed:

echo "<P>SQL ERROR: The failed query was:<BR>$sql<BR>";
echo mysql_errno() . ": " . mysql_error() . "<BR>";

}

Barb54

Thanks for your help so far. I filled in a couple of fields but still left some blank. I filled in the email field this time as well.

Yes, $dblink is set in the "include('dbconnect.php')" statement at the top of the original post. That IS the db connect code.

In my form I have a box to enter email into, ie NAME="email", field in table called email and $email set at top of the script.

0
SQL ERROR: The failed query was:
insert into players (nick,pwdb,joindate,fname,bday,bdaydd,ovr13,email,country,state,otherNicks,newZone,zoneStatus,plusNick,referredby,reason,coc,status,rank) 
VALUES ('Joan75','','','Joan','','Yes','tom@hotmail.com','none selected','','','NO','NO','n/a','','something','Yes','New','Token')

1136: Column count doesn't match value count at row 1

Ok, I'm lost now but I think one of the issues may be with my "joindate". In the form I have the joindate as a hidden field with NAME = "joindate". I defined $date at the top of the insert script but I don't think it will give me what I want. I want the current date and can't seem to get CURDATE to work. I want it in the m,d,Y format as well, in the database.

Right now, that is all I can think of. Some of the other fields are empty because I didn't fill those in, still it didn't give me an error on them yet, lol. I guess I got to get the initial code working right first huh? lol

Thanks again,
Barb

Barb54

Hi again,
Ok I had 2 fields for bday and should only have had 1 so I removed the bad guy BUT that bit of code didn't go anyway, lol.

Some information went into the database this time, yayyyyyy, lol. BUT, my form checks are definitely not working. Any ideas?

If you need more info just let me know. The fields that did not work properly are as follows:

$pwdb  left blank, no error msg, empty
$date   it now has "0000-00-00" in that field so it did something
$bday   selected month, but left dd as "please choose", nothing posted for this field
$country/$state WORKED, lol
$referredby   I left this one blank, its blank in the table but I got no error msg

If we can get the error msgs working I think this will be FINISHED, lol. I started on this thing yesterday and am determined to stay at it until it doesn't just work, but that it works RIGHT, lol.

thanks bunches,
Barb

nemonoman

for joindate don't do that thing you're doing with $date:

Instead:

VALUES ('$nick','$pwdb',CURDATE(),

I'll bet you forgot the ()

$_POST post option VALUES for dropdowns.
<option value=1>ONE</option>
<option value=2>TWO</option>

$_POST will contain '1' or '2', not ONE or TWO

If you say <option>THREE</option> and selected it, POST would contain 'THREE'

My guess is you have "please choose" as your first choice on a drop down. This choice displays by default. Why it isn't passed in $_POST is you didn't select it. Non-selected dropdowns don't get passed, nor do unchecked checkboxes.

To select a dropdown item programmatically without a mouse click
instead of this
<option>Please Choose</option>

add this code:

<option SELECTED>Please Choose</option>

OR
<option value=0 SELECTED>Please Choose</option>

Barb54

Thank you so much for your help! Ok $Date is now working. I sat here and chuckled at your response "don't do that thing you are doing with $date" hahaha. And yes, you were right, I had never put the () in there, lol. OMG! I have SO MUCH to learn, lol.

On the option selects some are working and some aren't lol.
$country worked but $state did not because I don't want state spelled out, I want the abbrev code which is in another field in the table.

Here's what I am trying to do with this:
connect to the State table, view the States using the 'state' field (where they are spelled out and I can sort in alpha order)but POST the 'abbrev' field to $state. I am connecting to the table fine but not passing the 'abbrev' field at all.

Another thing not working is:
I have option selects(data is in the form, not in a table)for 'bdaymm' and 'bdaydd'. I want to combine these into $bday. The "please choose" line value is set to "00/" for Month and "00" for day. I want the "/" between them in the table. What do I need to change in my code to make this work or did I completely miss it? lol

Thanks muchos,
Barb

oops here is sample code:
<SELECT NAME="bdaymm">
<OPTION value="00/" SELECTED>Please Choose
<OPTION value="01/">January
<OPTION value="02/">February
<OPTION value="03/">March
</OPTION>

</SELECT>  
<SELECT NAME="bdaydd">
<OPTION value="00" SELECTED>Please Choose
<OPTION value="01">01

<OPTION value="02">02

<?
$dblink=MYSQL_CONNECT($MySqlHostname, $MySqlUsername,

$MySqlPassword) OR DIE("Unable to connect to database");

@mysql_select_db("$MySqlDatabase") or die( "Unable to select
database");

$sql="SELECT * FROM states ORDER BY state";
$result=mysql_query($sql,$dblink);
echo "<ol>";
while($row = mysql_fetch_array($result)) {
echo '<option>' . $row['state'] . '</option>';
} // end while($row = mysql_fetch_array($result))
echo "</ol>";
?>
</SELECT></P>

nemonoman

assuming you have a dropdown $birthyear and another $birthdate you capture them from $_POST and then combine them in the select

('$nick','$pwdb','$date','$fname','$birthyear/$birthdate',

or something similar...

You leave the '/' out of the dropdowns, combine the elements with the / once the values get returned to the server. You could do it your way, but I think mine is better practice.

Do you know how you'll select by birthdate?

SELECT * from players where date_format(bday,'%Y/%m')='1992/2'

You'll have to select based on the format you're using to save.

as for states:
<option value='AK'>Alaska</option>
<option value='AZ'>Arizona</option>

$_POST['state'] will contain 'AZ' not 'Arizona' (or whatever...)

nemonoman

You edited your post as I was answering:

Your states table needs 2 columns:
abbreviation
name

You fill up the dropdown:

$sql="SELECT abbreviation, name FROM states ORDER BY name";
$result=mysql_query($sql,$dblink);
echo "<ol>";
while($row = mysql_fetch_array($result)) {
echo '<option value='.$row['abbreviation'].'>' . $row['state'] . '</option>';
} // end while($row = mysql_fetch_array($result))
echo "</ol>";

Barb54

Thanks for your help, nemonoman. I think I got it now. Geeez, I been on this forum ALL day long, lol. I will probably be back again tomorrow so you guys might as well get used to seeing me here, lol. I won't ask for help until I have tried it first on my own.

I will go ahead and mark this one resolved because I think I can fix up the form with what you shared with me here. If I have any probs I will start over again tomorrow lol. OMG!

Baby it's SNOWING OUTSIDE! lol

Thanks again hun,
Barb