INSERT Won't Work

[intargc]

I'm trying to insert something into a Table that was created like this:

CREATE TABLE Tasks (post_date DOUBLE NOT NULL, close_date DOUBLE NOT NULL, last_edit DOUBLE NOT NULL, subject CHAR(254) NOT NULL, body BLOB NOT NULL);

From PHP, the query string is:

INSERT INTO Tasks(post_date, closed_date, last_edit, closed, subject, body) VALUES($currdate, $closed_date, $currdate, '{$taskarr['subject']}', '{$taskarr['body']}')

After it's parsed by PHP, it is:

INSERT INTO Tasks(post_date, closed_date, last_edit, closed, subject, body) VALUES(1090772574, 0, 1090772574, 0, 'Blah', 'Blah');

Now, mysql_query() doesn't return ANY error. It acts as if it puts it in the database. However, Nothing is put in the database!!

If I copy and paste the line above and put it into the mysql command line tool, it works fine!

From PHP, if I set post_date to 0 instead of $currdate, then it works! I'm not sure what's going on... Can anyone help?

[TruckStuff]

Originally posted by intargc
Now, mysql_query() doesn't return ANY error. It acts as if it puts it in the database. However, Nothing is put in the database!!

That's because mysql_query() doesn't return errors; that's the job of mysql_error(). Try something like follows to see what the error is:

<?
mysql_query ($query) or die ($query.'<br>Could not execute query: '.mysql_error());
?>

[devinemke]

you are trying to insert 6 fields into a table that only has 5 (according to your "create table" SQL). there is no "closed" field into your table.

[intargc]

Originally posted by TruckStuff
That's because mysql_query() doesn't return errors; that's the job of mysql_error(). Try something like follows to see what the error is:

<?
mysql_query ($query) or die ($query.'<br>Could not execute query: '.mysql_error());
?>

[/B]

mysql_query() returns FALSE on error. I'm doing this:

$retval = mysql_query($query);
if (! $retval) {
echo mysql_error();
}

That's not the issue.

[intargc]

Originally posted by devinemke
you are trying to insert 6 fields into a table that only has 5 (according to your "create table" SQL). there is no "closed" field into your table.

Sorry, that was a mistype on my behalf. I was in a hurry when posting this and typed "closed" twice for some reason. What Really is going on is this:

CREATE TABLE Tasks (post_date DOUBLE NOT NULL, close_date DOUBLE NOT NULL, last_edit DOUBLE NOT NULL, subject CHAR(254) NOT NULL, body BLOB NOT NULL);

INSERT INTO Tasks(post_date, closed_date, last_edit, subject, body) VALUES($currdate, $closed_date, $currdate, '{$taskarr['subject']}', '{$taskarr['body']}')

After it's parsed by PHP, it is:

INSERT INTO Tasks(post_date, closed_date, last_edit, closed, subject, body) VALUES(1090772574, 0, 1090772574, 0, 'Blah', 'Blah');

So, I'm not really doing the 6 elements in my code... I just mistyped that on this forum.

Any other ideas?

Here is the function:

function postNewTask($taskarr)
{
    $conn = mysql_connect("localhost", "organizer", "organizer");
    mysql_select_db("Organizer");
    $currdate = time();

if ($taskarr['closed'] != "")
{
    $taskarr['closed'] = 1;
    $closed_date = $currdate;
}
else
{
    $taskarr['closed'] = 0;
    $closed_date = 0;
}

/* insert into database. */
$q = "INSERT INTO Tasks(post_date, closed_date, last_edit, subject, body)" .
    " VALUES($currdate, $closed_date, $currdate, " .
    " '{$taskarr['subject']}', '{$taskarr['body']}');";

echo "$q<BR><BR>";
mysql_query($q);
mysql_close($conn);
}

It's called like this:

postNewTask($_POST);

[tsinka]

Hi,

in your create table query you use close_date but in the query closed_date.

What do you get if you change

mysql_query($q);

to

mysql_query($q) or die("SQL error: ".mysql_error());

Thomas

[intargc]

Originally posted by tsinka
Hi,

in your create table query you use close_date but in the query closed_date.

What do you get if you change

mysql_query($q);

to

mysql_query($q) or die("SQL error: ".mysql_error());

Thomas

Thomas,

mysql_query($q) or die() Does nothing... mysql_query() is returning true. The code now looks like this:

mysql_query($q) or die("mysql_query failed: " . mysql_error());

That's my delmia... mysql_query() seems to think it inserted the data properly... However, it's nowhere to be found. 😕

[intargc]

Originally posted by tsinka
Hi,

in your create table query you use close_date but in the query closed_date.

Yeah, sorry about that. Please disregard that... I had a lot of mistypes in my first post. I even double checked my code and dropped and recreated the table to make sure that was not the issues.

[tsinka]

Was that close_date/closed_date difference in your last post a typo ?

Thomas

[intargc]

Originally posted by tsinka
Was that close_date/closed_date difference in your last post a typo ?

Thomas

Yes. It was a typo. Sorry. I just noticed that.

[intargc]

Originally posted by tsinka
Was that close_date/closed_date difference in your last post a typo ?

Thomas

OH! Lord... Nevermind... Yes, that's a typo. Man, I'm doing awful today. I bet my problem is that I can't type! 😃

The good news is, that the CRATE TABLE is in a mysql script file so I'm not retyping that every time I drop and recreate the table... so, it's not a typo

Man... sorry about that. I'll watch my postings before I submit.

[devinemke]

i think you need to re-check then re-post your data structure and PHP code. i for one am now very confused as to what is a typo in your post and what may be a typo in your actual code.

[intargc]

Originally posted by devinemke
i think you need to re-check then re-post your data structure and PHP code. i for one am now very confused as to what is a typo in your post and what may be a typo in your actual code.

Ok, I've dropped and recrated the table. This is a copy and paste of what I just did:

mysql> DROP TABLE Tasks;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE Tasks (post_date DOUBLE NOT NULL, closed_date DOUBLE NOT NULL, last_edit DOUBLE NOT NULL, subject CHAR(254) NOT NULL, body BLOB NOT NULL);
Query OK, 0 rows affected (0.01 sec)

mysql> QUIT

Here is my function (copy and pasted):

function postNewTask($taskarr)
{
    $conn = mysql_connect("localhost", "organizer", "organizer");
    mysql_select_db("Organizer");
    $currdate = time();

if ($taskarr['closed'] != "")
    $closed_date = $currdate;
else
    $closed_date = 0;

/* insert into database. */
$q = "INSERT INTO Tasks(post_date, closed_date, last_edit, subject, body)" .
    "VALUES($currdate, $closed_date, $currdate, " .
    "'{$taskarr['subject']}', '{$taskarr['body']}');";

echo "$q<BR><BR>";
mysql_query($q) or die("Couldn't insert date: " . mysql_error());
mysql_close($conn);
}

[intargc]

If I set post_date to 0 instead of $currdate, it inserts properly... Very weird.

[devinemke]

try this:

function postNewTask($taskarr) 
{ 
    $conn = mysql_connect('localhost', 'organizer', 'organizer');
		mysql_select_db('Organizer');
		$currdate = time();

	if ($taskarr['closed'] != '') {$closed_date = $currdate;}
	else {$closed_date = 0;}

$q = "INSERT INTO Tasks SET
	post_date = '$currdate',
	closed_date = '$closed_date',
	last_edit = '$currdate',
	subject = '" . $taskarr['subject'] . "',
	body = '" . $taskarr['body'] . "'";

echo "$q<BR><BR>";
mysql_query($q) or die("Couldn't insert date: " . mysql_error());
mysql_close($conn);
}

[tsinka]

Additionally, you might want to change the column type from double to int or bigint.

Thomas

[intargc]

Originally posted by devinemke
try this:

function postNewTask($taskarr) 
{ 
    $conn = mysql_connect('localhost', 'organizer', 'organizer');
		mysql_select_db('Organizer');
		$currdate = time();

	if ($taskarr['closed'] != '') {$closed_date = $currdate;}
	else {$closed_date = 0;}

$q = "INSERT INTO Tasks SET
	post_date = '$currdate',
	closed_date = '$closed_date',
	last_edit = '$currdate',
	subject = '" . $taskarr['subject'] . "',
	body = '" . $taskarr['body'] . "'";

echo "$q<BR><BR>";
mysql_query($q) or die("Couldn't insert date: " . mysql_error());
mysql_close($conn);
}

[/B]

Got an error:

Couldn't Query Table: 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 'SET post_date = '1090793467', closed_date = '0', last_edit = '1

This is the code (copy and paste):

$q = "INSERT INTO SET post_date = '$currdate', closed_date = '$closed_date', last_edit = '$currdate', subject = '" . $taskarr['subject'] . "', body = '" . $taskarr['body'] . "';";

mysql_query($q) or die("Couldn't Query Table: " . mysql_error());

[intargc]

Originally posted by tsinka
Additionally, you might want to change the column type from double to int or bigint.

Thomas

I had it at an INT at first, it didn't work, so I went to a BIGINT... That didn't work, so I went to a DOUBLE... :queasy:

So, that has been tried...

[intargc]

Originally posted by intargc
Got an error:

Couldn't Query Table: 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 'SET post_date = '1090793467', closed_date = '0', last_edit = '1

This is the code (copy and paste):

$q = "INSERT INTO SET post_date = '$currdate', closed_date = '$closed_date', last_edit = '$currdate', subject = '" . $taskarr['subject'] . "', body = '" . $taskarr['body'] . "';";

mysql_query($q) or die("Couldn't Query Table: " . mysql_error());

[/B]

Typo in my code. I changed this to:

$q = "INSERT INTO Tasks SET post_date = '$currdate', closed_date = '$closed_date', last_edit = '$currdate', subject = '" . $taskarr['subject'] . "', body = '" . $taskarr['body'] . "';";

mysql_query($q) or die("Couldn't Query Table: " . mysql_error());

I'm not getting the error anymore, but it's still not inserting into the table.

[intargc]

Originally posted by intargc
Typo in my code. I changed this to:

$q = "INSERT INTO Tasks SET post_date = '$currdate', closed_date = '$closed_date', last_edit = '$currdate', subject = '" . $taskarr['subject'] . "', body = '" . $taskarr['body'] . "';";

mysql_query($q) or die("Couldn't Query Table: " . mysql_error());

I'm not getting the error anymore, but it's still not inserting into the table. [/B]

This is the actual string going into the table:

INSERT INTO Tasks SET post_date = 1090794010, closed_date = 0, last_edit = 1090794010, subject = 'fdfd', body = 'fdfdfd';