Weird MySQL problem

[baffled_in_the_UK]

Hi,

 

My first post so I hope I can explain this correctly...

 

I have a table that looks like this ...

 

CREATE TABLE IF NOT EXISTS `kb_services` (

  `serv_id` mediumint(7) NOT NULL AUTO_INCREMENT,

  `serv_text` varchar(100) NOT NULL,

  `serv_cost` decimal(5,2) NOT NULL,

  `serv_freq` char(1) NOT NULL,

  `serv_desc` longtext NOT NULL,

  `serv_type` char(1) NOT NULL,

  PRIMARY KEY (`serv_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=26 ;

 

I am updating/inserting to this table from the results of a form.

 

My insert code looks like this ..

$db = @mysql_connect("localhost","xxxxx","xxxxx") or header("Location: xxxt.php");
mysql_select_db("xxxx",$db) or header("Location: xxx.php");

$a = addslashes(trim($_POST['serv_text']));
$b = addslashes(trim($_POST['serv_freq']));
$c = addslashes(trim($_POST['serv_cost']));
$d = addslashes(trim($_POST['serv_desc']));

mysql_query($sql = "INSERT INTO kb_services (serv_text,serv_freq,serv_cost,serv_desc,serv_type) VALUES ('$a','$b','$c','$d','D')"); 

 

this inserts a record but all the variables are blank except for serv_type.  I echoed the $sql immediately after the insert and it looks like this

INSERT INTO kb_services (serv_text,serv_freq,serv_cost,serv_desc,serv_type) VALUES ('Test Text','O','19.99','Test Description','D') 

 

If I go directly into the Db (using phpmyadmin) and paste the echoed code into a sql statement then the record is inserted with all values recorded correctly.

 

Any ideas please?

 

Many thanks

John G

[TeNDoLLA]

Try this, and make sure you got error reporting on.

 

mysql_query($sql = "INSERT INTO kb_services (serv_text,serv_freq,serv_cost,serv_desc,serv_type) VALUES ($a, $b, $c, $d,'D')"); 

[AMcHarg]

mysql_query($sql = "INSERT INTO kb_services (serv_text,serv_freq,serv_cost,serv_desc,serv_type) VALUES ('$a','$b','$c','$d','D')");

 

Why $sql = ? It's not needed.

[WebStyles]

it worked for me. (your exact same code) except I set $a,$b,$c,$d variables manually (so I wouldn't have to create a page to post stuff.

 

could you, by any change, have a copy of the database for test reasons, and are connecting to the wrong one?

[baffled_in_the_UK]

Thanks for replying. 

 

No, I only have the one Db for this project.  I could kind of understand if a record was not being written due to coding error/data problems but not being written with the variables not recorded, the sql statement is valid syntax and it works within phpmyadmin.  I just cannot understand this.

 

(to answer the other question... I used $sql to make it simple to echo it).

[WebStyles]

since it worked fine for me, the only thing I can think of is maybe your database is not the EXACT same as the CREATE TABLE code you posted here. I basically, dumped that into phpmyadmin, created the database, copied your code, set the variables manually, and everything worked.

 

That's about all I can suggest right now.

[WebStyles]

all I can suggest is replicate what I did. If it works for me, it HAS to work for you.

 

Delete database, create new one, set variables manually, test.

 

this is the code I used: (the only thing that's different is that I use a function to handle the database connection)

require_once('_conn.php');
$conn = conn('phpfreaksTests');
$a = 'testing';
$b = 'O';
$c = '19.99';
$d = 'Test Description';

mysql_query($sql = "INSERT INTO kb_services (serv_text,serv_freq,serv_cost,serv_desc,serv_type) VALUES ('$a','$b','$c','$d','D')",$conn);
@mysql_close($conn);

[baffled_in_the_UK]

Thanks Enthusiast.

 

It actually does work as a standalone script so I guess there is something strange going on within my actual page and yet the echoed $sql value looks fine!

 

I'll have another look. Thanks again.