[solved] Inserting Forms Information into MySql Tables

[superhoops]

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.

[wildteen88]

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:
[code]if (!mysql_query($sql,$con))[/code]

to:
[code]if (!mysql_query($sql,$db))[/code]

[superhoops]

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

[superhoops]

anyone able to help?

[superhoops]

Please Please Please help.

[wildteen88]

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]')";

[superhoops]

Thanks.

I now get this message:

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

[craygo]

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

[superhoops]

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.

[craygo]

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.

[code]$textarea = "It's a beautiful day";[/code]

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.

[code] $textarea = "It's a beautiful day";
$newtext = addslashes($textarea);[/code]

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

Ray

[superhoops]

So for this set of code:

[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());
  }
?>[/code]

Is that correct? If not what would it become?

[.josh]

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

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

you also probably want to do that for your other variables as well.

[superhoops]

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

[.josh]

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.

[craygo]

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.

[code]<?
$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());
 }
?>[/code]

Ray

[superhoops]

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

[craygo]

Maybe you should post your form and the php code.

Ray

[superhoops]

Ok i will do. its long though.

[code]<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>[/code]

[craygo]

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
[code]<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>[/code]

insert.php
[code]<?
$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());
  }
?>[/code]

Let me know

Ray

[superhoops]

Wow thats brillant thanks.

Im sure this error is easy to fix

[b]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[/b]

[craygo]

ooops. :)

I changed the table name

Should be
[code]<?
$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']."'";
// echo $sql;  for testing purposes
if (!mysql_query($sql))
  {
  die('Error: ' . mysql_error());
  }
?>[/code]

I also noticed you have a Team field in your form but none in the table. ????
Ray

[superhoops]

Oh no, you are right, i have no part for the Team. Do i need to recreate the table in the database?

[craygo]

No you can insert it using phpmyadmin. You will have to alter the insert query also.

Ray

[superhoops]

Ok is this right:

Form:
[code]
<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>
[/code]

insert.php

[code]
<?
$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']."',
        Team = '".$_POST['Team']."'
        Position = '".$_POST['Pos']."',
        Age = '".$_POST['Age']."',
        Ka = '".$_POST['GK']."',
        Ta = '".$_POST['DEF']."',
        Pa = '".$_POST['MID']."',
        Sa = '".$_POST['ATT']."'";
// echo $sql;  for testing purposes
if (!mysql_query($sql))
  {
  die('Error: ' . mysql_error());
  }
?>
[/code]

[superhoops]

New error

[b]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 'Position = 'ATT', Age = '16', Ka = '5', Ta = '6', ' at line 6[/b]