Jump to content

Archived

This topic is now archived and is closed to further replies.

superhoops

[solved] Inserting Forms Information into MySql Tables

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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]')";

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
$player = addslashes($_POST[Player]);
$price = addslashes($_POST[Price]);

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

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

Share this post


Link to post
Share on other sites
The other ones work though so if they stop working ill do that. Thanks alot everyone who has helped me.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
I cannot believe it, it still doesnt show the 2 fields in the databse the information for them? I am bambozzled.

Share this post


Link to post
Share on other sites
Maybe you should post your form and the php code.

Ray

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
No you can insert it using phpmyadmin. You will have to alter the insert query also.

Ray

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.