[SOLVED] Basic SQL problem

[vote-for-pedro]

Hi there im having a bit of trouble with entering a small amount of info in to my sql database.

 

the table sructure looks like so.  im not sure if i need enter a value in to 'id' to create the auto_increment

 

-- Table structure for table `mirrorcam`
-- 

CREATE TABLE `mirrorcam` (
  `id` int(255) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  `address1` varchar(255) NOT NULL default '',
  `address2` varchar(255) NOT NULL default '',
  `address3` varchar(255) NOT NULL default '',
  `address4` varchar(255) NOT NULL default '',
  `postcode` varchar(255) NOT NULL default '',
  `fitting` varchar(255) NOT NULL default '',
  UNIQUE KEY `id` (`id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

-- 
-- Dumping data for table `mirrorcam`
-- 

 

and my php like so..  it echos the results out fine  so it's posting ok.

<?
//check for required fields
if ((!$_POST[id]) || (!$_POST[format]) || (!$_POST[title])) {
   header( "Location: /show_addrecord.html");
   exit;
}

//set up database and table names
$db_name = "test";
$table_name = "mirrorcam";

//connect to MySQL and select database to use
$connection = @mysql_connect("xxx.xxxxxxx.co.uk", "xxxx", "xxxx")
or die(mysql_error());
$db = @mysql_select_db($db_name, $connection) or
die(mysql_error());

//create SQL statement and issue query
$sql = "INSERT INTO $table_name ('name, address1, address2, address3, address4, postcode, fitting') VALUES ('$_POST[name]', '$_POST[address1]', '$_POST[address2]', '$_POST[address3]', '$_POST[address4]', '$_POST[postcode]', '$_POST[fitting]')";

$result = @mysql_query($sql,$connection) or die(mysql_error());
?>




<p>Name: <br /><? echo "$_POST[name]"; ?></p><br />
<p>address:<br />
<? echo "$_POST[address1]"; ?><br />
<? echo "$_POST[address2]"; ?><bR />
<? echo "$_POST[address3]"; ?><br />
<? echo "$_POST[address4]"; ?></p><br />




<p> </p>
<p>postcode: <br /><? echo "$_POST[postcode]"; ?></p><br />
<p>fitting: <br /><? echo "$_POST[fitting]"; ?></p><bR />
<p>  </p>

 

Thanks for your help

[Illusion]

AUTO_INCREMENT defaults to 1, you no need to specify that and also no need to enter the value in id column.

 

is ur sql query is working fine? It seems it has some syntax problems

[vote-for-pedro]

ithink your right about the syntax problems how can i see where the errors appear

 

[Illusion]

try this

$sql = "INSERT INTO '$table_name' (name, address1, address2, address3, address4, postcode, fitting) VALUES ('{'$_POST['name']}', '{$_POST['address1']}', '{$_POST['address2']}', '{$_POST['address3']}', '{$_POST['address4']}', '{$_POST['postcode']}', '{$_POST['fitting']}')";

 

 

[vote-for-pedro]

thanks but it didnt work, Solved by removing  ' ' from around the table names worked.

 

//New Query
$sql = "INSERT INTO $table_name (name, address1, address2, address3,
address4, postcode, fitting) VALUES ('$_POST[name]',
'$_POST[address1]', '$_POST[address2]', '$_POST[address3]',
'$_POST[address4]', '$_POST[postcode]',
'$_POST[fitting]')";

 

//Old Query
$sql = "INSERT INTO $table_name ('name, address1, address2, address3, 
address4, postcode, fitting') VALUES ('$_POST[name]', 
'$_POST[address1]', '$_POST[address2]', '$_POST[address3]',
'$_POST[address4]', '$_POST[postcode]',
'$_POST[fitting]')";

 

simple really very annoying when you cant see it

thanks for your help.

[Illusion]

Did you see what values were inserted.

 

$_POST[address1] what is it? unless it is something like this $_POST['address1'].

[vote-for-pedro]

//New Query
$sql = "INSERT INTO $table_name (name, address1, address2, address3,
address4, postcode, fitting) VALUES ('$_POST[name]',
'$_POST[address1]', '$_POST[address2]', '$_POST[address3]',
'$_POST[address4]', '$_POST[postcode]',
'$_POST[fitting]')";

 

and all fields were filled correctly in the database with the name , lines of address and post code.

 

[bubblegum.anarchy]

Consider this:

 

$query = sprintf("
    INSERT INTO {$table_name} (name, address1, address2, address3, address4, postcode, fitting)
    VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s')"
        , mysql_real_escape_string($_POST['name'])
        , mysql_real_escape_string($_POST['address1'])
        , mysql_real_escape_string($_POST['address2'])
        , mysql_real_escape_string($_POST['address3'])
        , mysql_real_escape_string($_POST['address4'])
        , mysql_real_escape_string($_POST['postcode'])
        , mysql_real_escape_string($_POST['fitting']));