5 replies to this topic

[JamesH]

Hi there.

I am using php to simply execute a mySQL query, but it just won't have it.

To make the table I used

CREATE TABLE `emails` (
`from` varchar(255),
`owner` varchar(255),
`subject` varchar(255),
`message` text NOT NULL,
`attach` varchar(255) default 'none',
`unread` varchar(6) NOT NULL default 'unread',
PRIMARY KEY (`id`)
) TYPE=MyISAM;

..to connect to the database I am using (in config.php):

mysql_connect('xxxxx','xxxxx','xxxxx');
mysql_select_db('xxxxx') or die(mysql_error());

.. and to execute the query I am using

<?php
ob_start();
require_once("config.php")
// then some stuff getting the messages

// setting variables - I assure you they are set

$query = mysql_query("INSERT INTO emails (from, owner, subject, message, attach, unread)VALUES('$from','$username','$subject','$message','$attach','unread')") or die(mysql_error()); 

?>

and the error I get is:

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 'from, owner, subject, message, attach, unread)VALUES('(removed)','webmail_test@k3o' at line 1

..webmail_test@k3o is not the full address, it should actually be webmail_test@k3o.co.uk.

Does anyone know what could be wrong?

[cheerio]

$query = mysql_query("INSERT INTO emails (`from`, `owner`, `subject`, `message`, `attach,` `unread`)VALUES('$from','$username','$subject','$message','$attach','unread')") or die(mysql_error());

[Demonslay]

Just to explain what cheerio posted, it's because one of your fields is 'from', which is a SQL command, thus it couldn't figure out what the heck you were trying to do. The '`' characer signifies a field/column/table name.
I had this same problem yesterday that drove me completely nuts. I had a field called 'limit', which of course is a SQL command, an kept throwing me an error which I just couldn't figure out until I changed it.

[JamesH]

Thanks - it's working great now. But one more thing. How can I set it to insert this data only if it doesn't already exist? When I am refreshing the page I am inserting lots of duplicate data into my database.

Thanks.

[Av-]

yup ive had the same problem before, i called one of my fields 'desc' (as in description) and it wouldnt run either

[Demonslay]

Simply run a SELECT query to attempt to see if the data exists, and if it fails, add the data.

$query = mysql_query("SELECT * FROM `email` WHERE `from`='$from' AND `username`='$username' AND `subject`='$subject' AND `message`='$message' AND `attach`='$attach')") or die(mysql_error());
if(!mysql_num_rows($query)){
$query = mysql_query("INSERT INTO emails (`from`, `owner`, `subject`, `message`, `attach,` `unread`)VALUES('$from','$username','$subject','$message','$attach','unread')") or die(mysql_error());
}
else{
// Don't insert and maybe echo some kind of error saying it already exists
}

Of course make adjustments as needed.