Jump to content


Photo

Trying to get info from visitor to mySQL db via PHP form


  • Please log in to reply
11 replies to this topic

#1 Stegs

Stegs
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 09 May 2006 - 02:26 AM

This was the first time I had to do both a SQL and php form from scratch and I got a little lost.
Basically I am just trying to do a NCAA tournament bracketology to be submitted by visitors.
The form SEEMS to be working but no data gets from the form to my SQL database.

Here is the code (I snipped some useless spacing):
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]
<?
$page = "2006 NCAA Contest";
include ('./includes/top.php');
?>


<td width="750" valign="top" align="left" bgcolor="#ffffff">
<?
if($action == "update") {
$dbusername = "b**";
$dbpassword = "b***";
$db_name = "laxnation_com_-_fantasyplayers";

$db = mysql_connect( "localhost", "$dbusername", "$dbpassword") or die( "Unable to connect to server!");
mysql_select_db("$db_name",$db);

$query="INSERT INTO `2006ncaabracket` ( `FirstName` , `LastName` , `Address` , `City` , `State` , `Zip` , `Email` , `Tiebreaker` , 'G1' , 'G2' , 'G3' , 'G4' , 'G5' , 'G6' , 'G7' , 'G8' , 'G9' , 'G10' , 'G11' , 'G12' , 'G13' , 'G14' , 'G15')
VALUES ('','".$FirstName."', '".$LastName."', '".$Address."', '".$City."', '".$State."', '".$Zip."', '".$Email."', '".$Tiebreaker."', '".$G1."', '".$G2."','".$G3."','".$G4."','".$G5."','".$G6."','".$G7."','".$G8."','".$G9."','".$G10."','".$G11."','".$G12."','".$G13."','".$G14."','".$G15."')";
mysql_query($query);

echo ("Picks Created! <a href=\"http://laxnation.com\">Back to LN</a>.");
}
else {
echo ("
<head>
<title>2006 NCAA Championship</title>
</head>

<body>

<form method=\"post\" action=\"./updatebracket2.php\">

<select size=\"1\" name=\"G1\">
<option selected>--Select--</option>
<option>Virginia</option>
<option>Notre Dame</option>
</select>&nbsp
<select size=\"1\" name=\"G5\">
<option selected>--Select--</option>
<option>Hofstra</option>
<option>Providence</option>
</select><br>
<br>&nbsp;
<select size=\"1\" name=\"G9\">
<option selected>--Select--</option>
<option>Notre Dame</option>
<option>UVA</option>
<option>Georgetown</option>
<option>Navy</option>
</select>&nbsp
<select size=\"1\" name=\"G11\">
<option selected>--Select--</option>
<option>Hofstra</option>
<option>Providence</option>
<option>Cornell</option>
<option>UMass</option>
</select><br>
<br>
<select size=\"1\" name=\"G2\">
<option selected>--Select--</option>
<option>Georgetown</option>
<option>Navy</option>
</select>&nbsp;
<select size=\"1\" name=\"G6\">
<option selected>--Select--</option>
<option>Cornell</option>
<option>UMass</option>
</select><br>
<br>
&nbsp;
<select size=\"1\" name=\"G13\">
<option selected>--Select--</option>
<option>UVA</option>
<option>Notre Dame</option>
<option>Georgetown</option>
<option>Navy</option>
<option>Syracuse</option>
<option>Harvard</option>
<option>Hopkins</option>
<option>Penn</option>
</select>&nbsp;
<select size=\"1\" name=\"G14\">
<option selected>--Select--</option>
<option>Hofstra</option>
<option>Providence</option>
<option>Cornell</option>
<option>UMass</option>
<option>Princeton</option>
<option>UMBC</option>
<option>Maryland</option>
<option>Denver</option>
</select><br>
<br>
<select size=\"1\" name=\"G3\">
<option selected>--Select--</option>
<option>Syracuse</option>
<option>Harvard</option>
</select>&nbsp;
<select size=\"1\" name=\"G15\">
<option selected>--Select--</option>
<option>UVA</option>
<option>Notre Dame</option>
<option>Georgetown</option>
<option>Navy</option>
<option>Syracuse</option>
<option>Harvard</option>
<option>Hopkins</option>
<option>Penn</option>
<option>Hofstra</option>
<option>Providence</option>
<option>Cornell</option>
<option>UMass</option>
<option>Princeton</option>
<option>UMBC</option>
<option>Maryland</option>
<option>Denver</option>
</select>&nbsp;
<select size=\"1\" name=\"G7\">
<option selected>--Select--</option>
<option>Princeton</option>
<option>UMBC</option>
</select><br>
<br>&nbsp;
<b>Champions</b><br>
&nbsp;
<select size=\"1\" name=\"G10\">
<option selected>--Select--</option>
<option>Harvard</option>
<option>Syracuse</option>
<option>Hopkins</option>
<option>Penn</option>
</select>&nbsp;
<select size=\"1\" name=\"G12\">
<option selected>--Select--</option>
<option>Princeton</option>
<option>UMBC</option>
<option>Maryland</option>
<option>Denver</option>
</select><br>
<br>
<select size=\"1\" name=\"G4\">
<option selected>--Select--</option>
<option>Hopkins</option>
<option>Penn</option>
</select>&nbsp;
<select size=\"1\" name=\"G8\">
<option selected>--Select--</option>
<option>Maryland</option>
<option>Denver</option>
</select></p>
<p>
&nbsp;</p>
<p>
<input type=\"text\" name=\"FirstName\" size=\"20\" value=\"First Name\">
<input type=\"text\" name=\"LastName\" size=\"20\" value=\"Last Name\">
<input type=\"text\" name=\"Address\" size=\"20\" value=\"Street Address\">
<input type=\"text\" name=\"City\" size=\"20\" value=\"City\">
<input type=\"text\" name=\"State\" size=\"5\" value=\"State\" maxlength=\"2\">
<input type=\"text\" name=\"Zip\" size=\"5\" value=\"Zip\" maxlength=\"5\">
<input type=\"text\" name=\"Email\" size=\"50\" value=\"Email Address\"><br>
Tiebreaker - Guess the total number of goals scored in the whole tournament
<input type=\"text\" name=\"Tiebreaker\" size=\"5\" value=\"0\" maxlength=\"4\"></p>
<p align=\"center\"><input type=\"hidden\" name=\"username\" value=\"".$dbusername."\"><input type=\"hidden\" name=\"password\" value=\"".$dbpassword."\"><input type=\"submit\" value=\"update\" name=\"action\"></p>
</form>

</body>

</html>");
}
?>[/quote]

#2 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 09 May 2006 - 02:40 AM

you need to change $FirstName to $_POST['FirstName']

now you need to do that for every single variable.

if it still doesn't work, then add ammend this:

mysql_query($query) or die(msyql_error());

to see if there is an error in inserting the row into the db
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#3 Stegs

Stegs
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 09 May 2006 - 03:42 AM

OK...I changed from: [!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]VALUES ('','".$FirstName."',[/quote] to [!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]VALUES ('','".$_POST['FirstName']."',[/quote] and still nothing.

Then when I added the [!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]mysql_query($query) or die(msyql_error());[/quote] i get [!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]Fatal error: Call to undefined function: msyql_error() in /var/www/html/home/updatebracket3.php on line 19[/quote]




#4 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 09 May 2006 - 03:48 AM

oops. typo.

should be:

mysql_query($query) or die([!--coloro:red--][span style=\"color:red\"][!--/coloro--]mysql[!--colorc--][/span][!--/colorc--]_error());

also underneath that, do this:

echo "<br><br>". $query;

and post what the query string that displays on the screen
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#5 Stegs

Stegs
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 09 May 2006 - 03:52 AM

[!--quoteo(post=372504:date=May 8 2006, 11:48 PM:name=Crayon Violent)--][div class=\'quotetop\']QUOTE(Crayon Violent @ May 8 2006, 11:48 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
oops. typo.

should be:

mysql_query($query) or die([!--coloro:red--][span style=\"color:red\"][!--/coloro--]mysql[!--colorc--][/span][!--/colorc--]_error());

also underneath that, do this:

echo "<br><br>". $query;

and post what the query string that displays on the screen
[/quote]

Thank you for all your help with this...hopefully i can get more than 2 hours sleep tonight...

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]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 ''G1' , 'G2' , 'G3' , 'G4' , 'G5' , 'G6' , 'G7' , 'G8' , 'G9' ,[/quote]

Can anyone give me some help on this last part? I know its probably something simple i am missing but I am so close (I think) and the deadline is even closer.

#6 eddedwards

eddedwards
  • Members
  • PipPipPip
  • Advanced Member
  • 31 posts

Posted 09 May 2006 - 09:28 AM

this might be a stupid suggestion but in the example you give above. your ` marks change half way through to '. is that the same in your script? also im not sure if <options> default value to there contents. have you tried <option value=WHATEVER>WHATEVER</option> it might be that your options arent passing anything back.

#7 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 09 May 2006 - 11:53 AM


please put this underneath your query string and copy/paste what it prints to the webpage


echo "**startquery**".$query."**end query**";




Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#8 Stegs

Stegs
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 09 May 2006 - 12:13 PM

[!--quoteo(post=372554:date=May 9 2006, 07:53 AM:name=Crayon Violent)--][div class=\'quotetop\']QUOTE(Crayon Violent @ May 9 2006, 07:53 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
please put this underneath your query string and copy/paste what it prints to the webpage
echo "**startquery**".$query."**end query**";
[/quote]
I get the same message
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]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 ''G1' , 'G2' , 'G3' , 'G4' , 'G5' , 'G6' , 'G7' , 'G8' , 'G9' ,[/quote]
Could my SQL database be set up incorrectly?


#9 danelkayam

danelkayam
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 09 May 2006 - 01:27 PM

You also want to use the escape_string function. That script is really prone to sql injection.

#10 Stegs

Stegs
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 09 May 2006 - 02:35 PM

FYI...here is mySQL database setup:
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]
Field Type Null Default
Time timestamp(14) Yes NULL
Date date No 0000-00-00
FirstName varchar(20) No
LastName varchar(20) No
Address varchar(20) No
City varchar(20) No
State char(2) No
Zip varchar(5) No
Email varchar(50) No
Tiebreaker varchar(5) No
G1 text No
etc etc...

[/quote]

#11 Stegs

Stegs
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 10 May 2006 - 10:55 PM

UPDATE...
By splitting into 2 steps, the form still works, and the drop down selections are getting loaded into the sql database...this is great.
Last problem is for some reason none of my text fields are going in...I need help ASAP like tonight...

Here is form code:
<?
$page = "2006 NCAA Contest";
include ('./includes/top.php');
?>


<td width="750" valign="top" align="left" bgcolor="#ffffff">

<html>
<head>
<title>2006 NCAA Championship</title>
</head>

<body>

<FORM action="bracket4.php" method="post">

  <select size="1" name="GameA">
  <option selected>--Select--</option>
  <option>Virginia</option>
  <option>Notre Dame</option></select>
&nbsp;
  <select size="1" name="GameE">
  <option selected>--Select--</option>
  <option>Hofstra</option>
  <option>Providence</option>
  </select><br>
  <br>
&nbsp;
  <select size="1" name="GameI">
  <option selected>--Select--</option>
  <option>Notre Dame</option>
  <option>UVA</option>
  <option>Georgetown</option>
  <option>Navy</option> </select>
  &nbsp;
 <select size="1" name="GameK">
  <option selected>--Select--</option>
  <option>Hofstra</option>
  <option>Providence</option>
  <option>Cornell</option>
  <option>UMass</option>
  </select><br>
  <br>
   <select size="1" name="GameB">
  <option selected>--Select--</option>
  <option>Georgetown</option>
  <option>Navy</option>
  </select>&nbsp;
<select size="1" name="GameF">
  <option selected>--Select--</option>
  <option>Cornell</option>
  <option>UMass</option>
  </select><br>
  <br>
  &nbsp;
  <select size="1" name="GameM">
  <option selected>--Select--</option>
  <option>UVA</option>
  <option>Notre Dame</option>
  <option>Georgetown</option>
  <option>Navy</option>
  <option>Syracuse</option>
  <option>Harvard</option>
  <option>Hopkins</option>
  <option>Penn</option>
  </select>&nbsp;
  <select size="1" name="GameN">
  <option selected>--Select--</option>
  <option>Hofstra</option>
  <option>Providence</option>
  <option>Cornell</option>
  <option>UMass</option>
  <option>Princeton</option>
  <option>UMBC</option>
  <option>Maryland</option>
  <option>Denver</option>
  </select><br>
  <br>
  <select size="1" name="GameC">
  <option selected>--Select--</option>
  <option>Syracuse</option>
  <option>Harvard</option>
  </select>&nbsp;
  <select size="1" name="GameO">
  <option selected>--Select--</option>
  <option>UVA</option>
  <option>Notre Dame</option>
  <option>Georgetown</option>
  <option>Navy</option>
  <option>Syracuse</option>
  <option>Harvard</option>
  <option>Hopkins</option>
  <option>Penn</option>
  <option>Hofstra</option>
  <option>Providence</option>
  <option>Cornell</option>
  <option>UMass</option>
  <option>Princeton</option>
  <option>UMBC</option>
  <option>Maryland</option>
  <option>Denver</option>
  </select>&nbsp;
  <select size="1" name="GameG">
  <option selected>--Select--</option>
  <option>Princeton</option>
  <option>UMBC</option>
  </select><br>
  <br>&nbsp;
  <select size="1" name="GameJ">
  <option selected>--Select--</option>
  <option>Harvard</option>
  <option>Syracuse</option>
  <option>Hopkins</option>
  <option>Penn</option>
  </select>&nbsp;
  <select size="1" name="GameL">
  <option selected>--Select--</option>
  <option>Princeton</option>
  <option>UMBC</option>
  <option>Maryland</option>
  <option>Denver</option>
  </select><br>
  <br>
  <select size="1" name="GameD">
  <option selected>--Select--</option>
  <option>Hopkins</option>
  <option>Penn</option>
  </select>
&nbsp;
  <select size="1" name="GameH">
  <option selected>--Select--</option>
  <option>Maryland</option>
  <option>Denver</option>
  </select>
  <br><br>
  <input type="text" name="FirstName"> First Name <br>
  <input type="text" name="LastName" size="20"> Last Name <br>
  <input type="text" name="Address" size="20"> Address <br>
  <input type="text" name="City" size="20" value="City">
  <input type="text" name="State" size="2" value="State" maxlength="2">
  <input type="text" name="Zip" size="5" value="Zip" maxlength="5">
  <input type="text" name="Email" size="50" value="Email"><br>
  Tiebreaker - Guess the total number of goals scored in the whole tournament
  <input type="text" name="Tiebreaker" size="5" value="0" maxlength="4"></p>
    <p align="center"><input type="submit" value="submit to bracket4.php"></p>
</FORM></body></html>

Here is my submit code
<?php
//Collect form data and assign to scalar variables
$FirstName = $POST[FirstName];
$LastName = $POST[LastName];
$Address = $POST[Address];
$City = $POST[City];
$State = $POST[State];
$Zip = $POST[Zip];
$Email = $POST[Email];
$Tiebreaker = $POST[Tiebreaker];
$GameA = $_POST[GameA];
$GameB = $_POST[GameB];
$GameC = $_POST[GameC];
$GameD = $_POST[GameD];
$GameE = $_POST[GameE];
$GameF = $_POST[GameF];
$GameG = $_POST[GameG];
$GameH = $_POST[GameH];
$GameI = $_POST[GameI];
$GameJ = $_POST[GameJ];
$GameK = $_POST[GameK];
$GameL = $_POST[GameL];
$GameM = $_POST[GameM];
$GameN = $_POST[GameN];
$GameO = $_POST[GameO];

$dbusername = "b***";
$dbpassword = "b****";
$db_name = "laxnation_com_-_fantasyplayers";
$connection = mysql_connect( "localhost", "$dbusername", "$dbpassword") or die( "Unable to connect to server!");
$db=mysql_select_db("$db_name",$connection) or die( "Unable to connect to server!");

$sql = "INSERT INTO 2006ncaabracket " .
    "(id,Time,FirstName,LastName,Address,City,State,Zip,Email,Tiebreaker,GameA,GameB,GameC,GameD,GameE,GameF,GameG,GameH,GameI,GameJ,GameK,GameL,GameM,GameN,GameO) VALUES ('''', NOW(),'$FirstName', '$LastName', '$Address', '$City', '$State', '$Zip', '$Email', '$Tiebreaker', '$GameA', '$GameB', '$GameC', '$GameD', '$GameE', '$GameF', '$GameG', '$GameH', '$GameI', '$GameJ', '$GameK', '$GameL', '$GameM', '$GameN', '$GameO')";
$sql_result = mysql_query($sql,$connection) or die ('Could not insert data');
echo('Data inserted successfully. <a href="http://www.laxnation.com">"Return to LN</a>');
mysql_close($connection);
?>


#12 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 11 May 2006 - 05:38 AM

$FirstName = $_POST[FirstName];
$LastName = $_POST[LastName];
$Address = $_POST[Address];
$City = $_POST[City];
$State = $_POST[State];
$Zip = $_POST[Zip];
$Email = $_POST[Email];

you forgot the underscore between the $ and the POST

also, it's not technically a requirement to put quotes around the $_POST array keys like so:

$_POST['FirstName']

but i always thought it to be good coding practice to do it.

anyways, if you put some underscores in there it should work fine.
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users