MySQL help

a2jfreak

This query works:
[php]
$query = "INSERT\n";
$query .= "INTO\n";
$query .= "mails\n";
$query .= "(userID,UIDL,body,subject,reply,date,header)\n";
$query .= sprintf("VALUES (1,\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\")",
mysql_real_escape_string(str_replace("\n", "", str_replace("\r", "" , $pop3->mailuidl))),
mysql_real_escape_string("\$mBody"),
mysql_real_escape_string("\$mSubject"),
mysql_real_escape_string("\$mReply"),
mysql_real_escape_string("$mDate:00"),
mysql_real_escape_string("\$mHeader")
// mysql_real_escape_string("\$mTo"),
// mysql_real_escape_string("\$mFrom")
);
[/php]

QUERY:
INSERT INTO mails (userID,UIDL,body,subject,reply,date,header) VALUES (1,"1091112768.14464.unix24,S=2393","$mBody","$mSubject","$mReply","2004/07/29 09:58:00","$mHeader")

This query does not work:
[php]
$query = "INSERT\n";
$query .= "INTO\n";
$query .= "mails\n";
$query .= "(userID,UIDL,body,subject,reply,date,header,to,from)\n";
$query .= sprintf("VALUES (1,\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\")",
mysql_real_escape_string(str_replace("\n", "", str_replace("\r", "" , $pop3->mailuidl))),
mysql_real_escape_string("\$mBody"),
mysql_real_escape_string("\$mSubject"),
mysql_real_escape_string("\$mReply"),
mysql_real_escape_string("$mDate:00"),
mysql_real_escape_string("\$mHeader"),
mysql_real_escape_string("\$mTo"),
mysql_real_escape_string("\$mFrom")
);
[/php]

QUERY:
INSERT INTO mails (userID,UIDL,body,subject,reply,date,header,to,from) VALUES (1,"1091112768.14464.unix24,S=2393","$mBody","$mSubject","$mReply","2004/07/29 09:58:00","$mHeader","$mTo","$mFrom")

Query Failed! 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 'to,from) VALUES (1,"1091112768.14464.unix24,S=2393","$mBody","$

"to" and "from" are columns in the "mails" table so I'm not sure what the problem is. Both colums are varchar(255) and can be null.



BTW: Know any companies that are looking to hire for $50K a year?

Shorty

First thing:

$query .= "(userID,UIDL,body,subject,reply,date,header,to,fro m)\n";

Would not be valid in that syntax.

Second thing:

[php]
<?php
function realEscape($string)
{
$string = mysql_real_escape_string($string);
return $string;
}
function makeUIDL($string)
{
$uidl = str_replace( "\n", "", str_replace("\r", "" , $string) );
return $uidl;
}
$query = "INSERT INTO
mails
SET
userID = '1',
UIDL = '".realEscape( makeUIDL($pop3->mailuidl) )."',
body = '".realEscape($mBody)."',
subject = '".realEscape($mSubject)."',
reply = '".realEscape($mReply)."',
date = '".realEscape($mDate)."',
header = '".realEscape($mHeader)."',
to = '".realEscape($mTo)."',
from = '".realEscape($mFrom)."'
";
?>
[/php]

Make your syntax much cleaner and visually easier to follow

Edit::

Why is this:

[php]
userID = '1',
[/php]

In there?? Does your table not increment manually??

a2jfreak

The \n between INSERT, INTO, etc. were there for the MySQL error message to tell me which line the error was on.

As for "from" not being a whole word, it actually is. vB messed it up. I even checked my post to see if somehow a space or a tab had been inserted. It's a vB error/bug/glitch. Kinda odd.

The userID field isn't a field that should be incrementing, that's why it's set to 1. Later on the field will be set to whomever is accessing the page, but for now it's just me so I have it set to userID 1.

I'll try your suggestion and see if it works. Thanks Dan!

Shorty

a2jfreak wrote:

The \n between INSERT, INTO, etc. were there for the MySQL error message to tell me which line the error was on.

Ah.. got ya... however, I have a really tidy MySQL class you can have if you'd like.. that does the same and has loads of debug stuff built in

a2jfreak wrote:

As for "from" not being a whole word, it actually is. vB messed it up. I even checked my post to see if somehow a space or a tab had been inserted. It's a vB error/bug/glitch. Kinda odd.

Must just be yet another bug in the parser.. *sigh*. My bad.

a2jfreak wrote:

The userID field isn't a field that should be incrementing, that's why it's set to 1. Later on the field will be set to whomever is accessing the page, but for now it's just me so I have it set to userID 1.

Makes total sense now.. cool

a2jfreak wrote:

I'll try your suggestion and see if it works. Thanks Dan!

Hope it helps!

a2jfreak

This works
[php]
$query = "INSERT INTO
mails
SET
userID = '1',
UIDL = '".realEscape(makeUIDL($pop3->mailuidl))."',
body = '".realEscape("\$mBody")."',
subject = '".realEscape("\$mSubject")."',
reply = '".realEscape("\$mReply")."',
date = '".realEscape("\$mDate")."',
header = '".realEscape("\$mHeader")."'
";
[/php]

QUERY:
INSERT INTO mails SET userID = '1', UIDL = '1091129014.26168.unix24,S=4711', body = '$mBody', subject = '$mSubject', reply = '$mReply', date = '$mDate', header = '$mHeader'

This does not
[php]
$query = "INSERT INTO
mails
SET
userID = '1',
UIDL = '".realEscape(makeUIDL($pop3->mailuidl))."',
body = '".realEscape("\$mBody")."',
subject = '".realEscape("\$mSubject")."',
reply = '".realEscape("\$mReply")."',
date = '".realEscape("\$mDate")."',
header = '".realEscape("\$mHeader")."',
to = '".realEscape("\$mTo")."',
from = '".realEscape("\$mFrom")."'
";
[/php]

QUERY:
INSERT INTO mails SET userID = '1', UIDL = '1091129014.26168.unix24,S=4711', body = '$mBody', subject = '$mSubject', reply = '$mReply', date = '$mDate', header = '$mHeader', to = '$mTo', from = '$mFrom'

Query Failed! 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 'to = '$mTo', from = '$mFrom'' at line 11

a2jfreak

Yes, I would love your handy-dandy MySQL class.

Shorty

I didn't write it as quite frankly, it's so damn efficent.. I see zero point in reqwriting the wheel plus the guy is a buddy of mine .. and awesome!

http://www.hotscripts.com/Detailed/18290.html

EzSQL rocks

Then lose the /n and the "" in your statements, it's bad practice.