Jump to content


Photo

[solved] Inserting Forms Information into MySql Tables


  • Please log in to reply
28 replies to this topic

#1 superhoops

superhoops
  • Members
  • PipPipPip
  • Advanced Member
  • 65 posts

Posted 27 August 2006 - 10:40 PM

I have added the table to my mysql database and i have a form with the required php on it to post it into my insert.php page but when i click submit i get this message:

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /home/www/fmprotasy.com/Test/insert.php on line 11
Error:

Here is the code of the insert.php page

<?php
$db = mysql_connect("db5.awardspace.com:3306", "fmpsite_reg",
"password") or die("Could not connect.");
if(!$db)
die("no db");
if(!mysql_select_db("fmpsite_reg",$db))
die("No database selected.");
if(!get_magic_quotes_gpc())
mysql_select_db("fmprotasy_reg", $con);
$sql="INSERT INTO Market (Player, Price, Type, Position, Age, Ka, Ta, Pa, Sa)
VALUES ('$_POST[Player]', '$_POST[Price]', '$_POST[Type]' '$_POST[Position]', '$_POST[Age]', '$_POST[GK]', '$_POST[DEF]', '$_POS[MID]',
'$_POST[ATT]',)";
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";
?>

Would anyone be able to tell me what is wrong? Help would be much appreictaed.

#2 wildteen88

wildteen88
  • Staff Alumni
  • Advanced Member
  • 10,482 posts
  • LocationUK, Bournemouth

Posted 27 August 2006 - 10:48 PM

Your query is good. The problem is you are using a non-existant MySQL-link resource. $db is the link resource. However in the mysql_query function you usr $con which isnt the link resource so change:
if (!mysql_query($sql,$con))

to:
if (!mysql_query($sql,$db))


#3 superhoops

superhoops
  • Members
  • PipPipPip
  • Advanced Member
  • 65 posts

Posted 27 August 2006 - 10:56 PM

O stupid me, thanks very much m8.

I now get this message

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 ')' at line 2

#4 superhoops

superhoops
  • Members
  • PipPipPip
  • Advanced Member
  • 65 posts

Posted 27 August 2006 - 11:02 PM

anyone able to help?

#5 superhoops

superhoops
  • Members
  • PipPipPip
  • Advanced Member
  • 65 posts

Posted 28 August 2006 - 10:02 AM

Please Please Please help.

#6 wildteen88

wildteen88
  • Staff Alumni
  • Advanced Member
  • 10,482 posts
  • LocationUK, Bournemouth

Posted 28 August 2006 - 10:07 AM

From what I see, you have an extra comma at the end of the query which seems to be the problem, try this:

$sql="INSERT INTO Market (Player, Price, Type, Position, Age, Ka, Ta, Pa, Sa)
VALUES ('$_POST[Player]', '$_POST[Price]', '$_POST[Type]' '$_POST[Position]', '$_POST[Age]', '$_POST[GK]', '$_POST[DEF]', '$_POS[MID]', '$_POST[ATT]')";

#7 superhoops

superhoops
  • Members
  • PipPipPip
  • Advanced Member
  • 65 posts

Posted 28 August 2006 - 11:04 AM

Thanks.

I now get this message:

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

#8 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 28 August 2006 - 02:31 PM

you forgot the comma between type and position so it takes it as one entry.
Fix this

'$_POST[Type]' '$_POST[Position]'

to this

'$_POST[Type]', '$_POST[Position]'

Ray



#9 superhoops

superhoops
  • Members
  • PipPipPip
  • Advanced Member
  • 65 posts

Posted 28 August 2006 - 02:34 PM

Ye i saw that earlier, thanks.

The new problem i have now is the information submitted to the database.

Do you know why when people submit info in a text area/box it doesn't display it on the database but the information all comes up in the database for the other fields which are all drop down menus?

Any help would be very much appriectaed.

#10 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 28 August 2006 - 02:51 PM

well a textarea can contain characters which php interprets as syntax. An example would be the single quote. If you do not echo it out correctly php will think the single quote is the end of the line, which it is not. You can use the php command addslashes() to help in your insert or output. Make sure you enclose the statement in quotations.

example.

$textarea = "It's a beautiful day";

the above statement may work in your insert but will error when you go to output it. Or if you are using single quotes when you insert it will give an error because it thinks the apostrophy is the end of the text.

$textarea = "It's a beautiful day";
$newtext = addslashes($textarea);

This will add slashes so it is inserted into the database correctly

Ray

#11 superhoops

superhoops
  • Members
  • PipPipPip
  • Advanced Member
  • 65 posts

Posted 28 August 2006 - 03:03 PM

So for this set of code:

<?php
$db = mysql_connect("db5.awardspace.com:3306", "fmpsite_reg", "pitstop") or die("Could not connect.");
if(!$db) 
	die("no db");
if(!mysql_select_db("fmpsite_reg",$db))
 	die("No database selected.");
if(!get_magic_quotes_gpc())
mysql_select_db("fmprotasy_reg", $con);
$sql="INSERT INTO Market (id,
Player, 
Price, 
Type, 
Position, 
Age, 
Ka, 
Ta, 
Pa, 
Sa)
VALUES ('',
'$_POST[Player]',
'$_POST[Price]',
'$_POST[Type]',
'$_POST[Pos]',
'$_POST[Age]',
'$_POST[GK]',
'$_POST[DEF]',
'$_POST[MID]',
'$_POST[ATT]')";
if (!mysql_query($sql,$db))
  {
  die('Error: ' . mysql_error());
  }
?>

Would this become:

<?php
$db = mysql_connect("db5.awardspace.com:3306", "fmpsite_reg", "pitstop") or die("Could not connect.");
if(!$db) 
	die("no db");
if(!mysql_select_db("fmpsite_reg",$db))
 	die("No database selected.");
if(!get_magic_quotes_gpc())
mysql_select_db("fmprotasy_reg", $con);
[color=red]$player = '$_POST[Player]';
$price = '$_POST[Price]';[/color]
$sql="INSERT INTO Market (id,
Player, 
Price, 
Type, 
Position, 
Age, 
Ka, 
Ta, 
Pa, 
Sa)
VALUES ('',
[color=red]'addslashes($player)',
'addslashes($price)',[/color]
'$_POST[Type]',
'$_POST[Pos]',
'$_POST[Age]',
'$_POST[GK]',
'$_POST[DEF]',
'$_POST[MID]',
'$_POST[ATT]')";
if (!mysql_query($sql,$db))
  {
  die('Error: ' . mysql_error());
  }
?>

Is that correct? If not what would it become?

#12 .josh

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

Posted 28 August 2006 - 03:11 PM

$player = addslashes($_POST[Player]);
$price = addslashes($_POST[Price]);

.
.
.
... VALUES ('$player','$price','.....

you also probably want to do that for your other variables as well.
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 :)

#13 superhoops

superhoops
  • Members
  • PipPipPip
  • Advanced Member
  • 65 posts

Posted 28 August 2006 - 03:12 PM

The other ones work though so if they stop working ill do that. Thanks alot everyone who has helped me.

#14 .josh

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

Posted 28 August 2006 - 03:15 PM

well it's a security measure. you shouldn't insert $_POST variables directly into a query. it opens up a security hole: sql injection.  you should always "sanitize" your variables by using certain functions like addslashes and/or mysql_real_escape_string.  Case in point: someone can enter in a ' and it closes your brackets. they can then insert their own sql query and your script will run it.  their query can do anything from deleting your info, dumping other info (like usernames and pws), etc.. just because something "works" doesn't mean it's the best or most secure way to do it.
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 :)

#15 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 28 August 2006 - 03:15 PM

Another thing, you do not have to set a value for an autoincrement field. The value will be set automatically. I like to use the SET command personally. It help with having the wrong number of fields and values.

<?
$db = mysql_connect("db5.awardspace.com:3306", "fmpsite_reg", "pitstop") or die("Could not connect.");
if(!$db)
   die("no db");
if(!mysql_select_db("fmpsite_reg",$db))
    die("No database selected.");
if(!get_magic_quotes_gpc())
mysql_select_db("fmprotasy_reg", $con);
$player = addslashes($_POST[Player]);
$price = addslashes($_POST[Price]);
$sql="INSERT INTO Market SET
        Player = '".$player."',
        Price = '".$price."',
        Type = '".$_POST[Type]."',
        Position = '".$_POST[Pos]."',
        Age = '".$_POST[Age]."',
        Ka = '".$_POST[GK]."',
        Ta = '".$_POST[DEF]."',
        Pa = '".$_POST[MID]."',
        Sa = '".$_POST[ATT]."'";
if (!mysql_query($sql,$db))
  {
  die('Error: ' . mysql_error());
  }
?>

Ray

#16 superhoops

superhoops
  • Members
  • PipPipPip
  • Advanced Member
  • 65 posts

Posted 28 August 2006 - 03:21 PM

I cannot believe it, it still doesnt show the 2 fields in the databse the information for them? I am bambozzled.

#17 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 28 August 2006 - 03:24 PM

Maybe you should post your form and the php code.

Ray

#18 superhoops

superhoops
  • Members
  • PipPipPip
  • Advanced Member
  • 65 posts

Posted 28 August 2006 - 03:26 PM

Ok i will do. its long though.

<tr>
					<td align="center" width="119">
					<font face="Arial">
					<form action="insert.php" method="post">
					<input type="text" name="Player" size="10"></font></td>
					<td align="center" width="95">
					<font face="Arial">
					<input type="text" name="Price" size="7"></font></td>
					<td align="center" width="184"><select size="1" name="Team">Team
					<optgroup label="Premiership">
					<option value="Asnl">Arsenal
					<option value="AV">Aston Villa
					<option value="Birm">Birmingham City
					<option value="Bkbn">Blackburn Rovers
					<option value="Bltn">Bolton Wanderers
					<option value="Cltn">Charlton Athletic
					<option value="Chel">Chelsea
					<option value="Evtn">Everton
					<option value="Flhm">Fulham
					<option value="Lpol">Liverpool
					<option value="Manc">Manchester City
					<option value="Manu">Manchester United
					<option value="Boro">Middlesbrough
					<option value="Newc">Newcastle United
					<option value="Pomp">Portsmouth
					<option value="Sund">Sunderland
					<option value="Sprs">Tottenham Hotspur
					<option value="WBA">West Bromwich Albion
					<option value="WHam">West Ham United
					<option value="Wign">Wigan Athletic
					<optgroup label="Championship">
					<option value="burn">Burnley
					<option value="cardiff">Cardiff City
					<option value="cov">Coventry
					<option value="cry p">Crystal Palace
					<option value="derby">Derby
					<option value="hull">Hull City
					<option value="ips">Ipswich
					<option value="leeds">Leeds
					<option value="leicester">Leicester
					<option value="luton">Luton
					<option value="norwich">Norwich
					<option value="Plymouth">Plymouth
					<option value="preston">Preston
					<option value="qpr">Queens Park Rangers
					<option value="reading">Reading
					<option value="sheff u">Sheffield United
					<option value="south">Southampton
					<option value="stoke">Stoke
					<option value="watford">Watford
					<option value="wolves">Wolves</select></td>
					<td align="center" width="116"><font face="Arial"><select size="1" name="Type">
					Points
					<option value="Transfer">Transfer
					<option value="Loan">Loan
					<option value="Release">Release</select></font></td>
					<td align="center" width="177"><font face="Arial"><select size="1" name="Pos">
					Points
					<option value="GK">GK
					<option value="DEF">DEF
					<option value="MID">MID
					<option value="ATT">ATT</select></font></td>
					<td align="center" width="42"><font face="Arial"><select size="1" name="Age">
					Age
					<option value="15">15
					<option value="16">16
					<option value="17">17
					<option value="18">18
					<option value="19">19
					<option value="20">20
					<option value="21">21
					<option value="22">22
					<option value="23">23
					<option value="24">24
					<option value="25">25
					<option value="26">26
					<option value="27">27
					<option value="28">28
					<option value="29">29
					<option value="30">30
					<option value="31">31
					<option value="32">32
					<option value="33">33
					<option value="23">23
					<option value="34">34
					<option value="35">35
					<option value="36">36
					<option value="37">37
					<option value="38">38</select></font></td>
					<td align="center" width="42"><font face="Arial"><select size="1" name="GK">GK
					<option value="1">1
					<option value="2">2
					<option value="3">3
					<option value="4">4
					<option value="5">5
					<option value="6">6
					<option value="7">7
					<option value="8">8
					<option value="9">9
					<option value="10">10
					<option value="11">11
					<option value="12">12
					<option value="13">13
					<option value="14">14</select></font></td>
					<td align="center" width="42"><font face="Arial"><select size="1" name="DEF">
					DEF
					<option value="1">1
					<option value="2">2
					<option value="3">3
					<option value="4">4
					<option value="5">5
					<option value="6">6
					<option value="7">7
					<option value="8">8
					<option value="9">9
					<option value="10">10
					<option value="11">11
					<option value="12">12
					<option value="13">13
					<option value="14">14</select></font></td>
					<td align="center" width="42"><font face="Arial"><select size="1" name="MID">
					MID
					<option value="1">1
					<option value="2">2
					<option value="3">3
					<option value="4">4
					<option value="5">5
					<option value="6">6
					<option value="7">7
					<option value="8">8
					<option value="9">9
					<option value="10">10
					<option value="11">11
					<option value="12">12
					<option value="13">13
					<option value="14">14</select></font></td>
					<td align="center" width="42"><font face="Arial"><select size="1" name="ATT">
					ATT
					<option value="1">1
					<option value="2">2
					<option value="3">3
					<option value="4">4
					<option value="5">5
					<option value="6">6
					<option value="7">7
					<option value="8">8
					<option value="9">9
					<option value="10">10
					<option value="11">11
					<option value="12">12
					<option value="13">13
					<option value="14">14</select></font></td>
				</tr>


#19 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 28 August 2006 - 04:02 PM

OK I simplified a few things for you. I recreated your table and such and everything works fine here.

your form page which should now be a php with my script. I named it form.php
<form action="insert.php" method="post">
<table>
  <tr>
    <td align="center" width="119">Player<br /><font face="Arial"><input type="text" name="Player" size="10"></font></td>
	<td align="center" width="95">Price<br /><font face="Arial"><input type="text" name="Price" size="7"></font></td>
	<td align="center" width="184">Team<br />
      <select size="1" name="Team">
					<optgroup label="Premiership">
					<option value="Asnl">Arsenal
					<option value="AV">Aston Villa
					<option value="Birm">Birmingham City
					<option value="Bkbn">Blackburn Rovers
					<option value="Bltn">Bolton Wanderers
					<option value="Cltn">Charlton Athletic
					<option value="Chel">Chelsea
					<option value="Evtn">Everton
					<option value="Flhm">Fulham
					<option value="Lpol">Liverpool
					<option value="Manc">Manchester City
					<option value="Manu">Manchester United
					<option value="Boro">Middlesbrough
					<option value="Newc">Newcastle United
					<option value="Pomp">Portsmouth
					<option value="Sund">Sunderland
					<option value="Sprs">Tottenham Hotspur
					<option value="WBA">West Bromwich Albion
					<option value="WHam">West Ham United
					<option value="Wign">Wigan Athletic
					<optgroup label="Championship">
					<option value="burn">Burnley
					<option value="cardiff">Cardiff City
					<option value="cov">Coventry
					<option value="cry p">Crystal Palace
					<option value="derby">Derby
					<option value="hull">Hull City
					<option value="ips">Ipswich
					<option value="leeds">Leeds
					<option value="leicester">Leicester
					<option value="luton">Luton
					<option value="norwich">Norwich
					<option value="Plymouth">Plymouth
					<option value="preston">Preston
					<option value="qpr">Queens Park Rangers
					<option value="reading">Reading
					<option value="sheff u">Sheffield United
					<option value="south">Southampton
					<option value="stoke">Stoke
					<option value="watford">Watford
					<option value="wolves">Wolves
      </select>
    </td>
	<td align="center" width="116">Points<br /><font face="Arial">
      <select size="1" name="Type">
					<option value="Transfer">Transfer
					<option value="Loan">Loan
					<option value="Release">Release
      </select></font>
    </td>
	<td align="center" width="177">Position<br /><font face="Arial">
      <select size="1" name="Pos">
					<option value="GK">GK
					<option value="DEF">DEF
					<option value="MID">MID
					<option value="ATT">ATT
      </select></font>
    </td>
	<td align="center" width="42">Age<br /><font face="Arial">
      <select size="1" name="Age">
      <?
        for($age = 15; $age<=38; $age++){
          echo "<option value=\"$age\">$age</option>";
        }
      ?>
      </select></font>
    </td>
	<td align="center" width="42">GK<br /><font face="Arial">
      <select size="1" name="GK">
	  <?
        for($gk=1; $gk<=14; $gk++){
          echo "<option value=\"$gk\">$gk</option>";
        }
      ?>
      </select></font>
    </td>
	<td align="center" width="42">DEF<br /><font face="Arial">
      <select size="1" name="DEF">
	  <?
        for($def=1; $def<=14; $def++){
          echo "<option value=\"$def\">$def</option>";
        }
      ?>
      </select></font>
    </td>
	<td align="center" width="42">MID<br /><font face="Arial">
      <select size="1" name="MID">
	  <?
        for($mid=1; $mid<=14; $mid++){
          echo "<option value=\"$mid\">$mid</option>";
        }
      ?>
      </select></font>
    </td>
	<td align="center" width="42">ATT<font face="Arial">
      <select size="1" name="ATT">
	  <?
        for($att=1; $att<=14; $att++){
          echo "<option value=\"$att\">$att</option>";
        }
      ?>
      </select></font>
    </td>
  </tr>
  <tr>
    <td colspan=10 align=center>
      <input type=submit name=submit value=submit />
    </td>
  </tr>
</table>
</form>

insert.php
<?
$db = mysql_connect("db5.awardspace.com:3306", "fmpsite_reg", "pitstop") or die("Could not connect.");
if(!$db)
   die("no db");
if(!mysql_select_db("fmpsite_reg",$db))
    die("No database selected.");
if(!get_magic_quotes_gpc())
mysql_select_db("fmprotasy_reg", $con);
$player = addslashes($_POST['Player']);
$price = addslashes($_POST['Price']);
$sql="INSERT INTO project SET
        Player = '".$player."',
        Price = '".$price."',
        Type = '".$_POST['Type']."',
        Position = '".$_POST['Pos']."',
        Age = '".$_POST['Age']."',
        Ka = '".$_POST['GK']."',
        Ta = '".$_POST['DEF']."',
        Pa = '".$_POST['MID']."',
        Sa = '".$_POST['ATT']."'";
echo $sql;
if (!mysql_query($sql))
  {
  die('Error: ' . mysql_error());
  }
?>

Let me know

Ray

#20 superhoops

superhoops
  • Members
  • PipPipPip
  • Advanced Member
  • 65 posts

Posted 28 August 2006 - 04:16 PM

Wow thats brillant thanks.

Im sure this error is easy to fix

INSERT INTO project SET Player = 'Michael', Price = 'Hamilton', Type = 'Release', Position = 'DEF', Age = '18', Ka = '5', Ta = '7', Pa = '8', Sa = '3'Error: Table 'fmpsite_reg.project' doesn't exist




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users