problems inserting data into mysql db


#1

made a php bb style editor with javascript, and i’m having some problems submitting the data (after the javascript has been used to transform the phpbb tags into html) into a mysql table.

here’s the script:
mysql_query( “LOCK TABLES article WRITE” ) or die( "Couldn’t LOCK: ".mysql_error() );
mysql_query( "INSERT INTO article VALUES ".$query ) or die( "Couldn’t INSERT: ".mysql_error() );
mysql_query( “UNLOCK TABLES” ) or die( "Couldn’t UNLOCK: ".mysql_error() );

The results it produces can be seen by going to http://www.time2changefbcy.com/comment.php?type=a&id=9 (you’ll have to copy and past the link. for some reason the page is forbidden when accessing from this link) (don’t forget that last part or the form won’t get shown. doesn’t matter what the id is as long as it’s a number, and what the type is as long as it’s “a” or “p”.) Just type something in the three boxes and click submit. it goes to the script above and just stops. oh, and i have a mysql_error() shindig in there to print the mysql error.


#2

Could you show the code for the query?

You presented the
[php]
mysql_query( "INSERT INTO article VALUES ".$query ) or die( "Couldn’t INSERT: ".mysql_error() );
[/php]
but I would like to see the code on how you assigned $query


#3

the code for the first page, which contains the form (and all names for the form items) is:
[php]
$type = $HTTP_GET_VARS[‘type’];

if( $type == “a” ) {
$id == $GET[‘id’];
printform( $id, $type );
} elseif( $type == “p” ) {
$id == $GET[‘id’];
printform( $id, $type );
};

function printform( $id, $type ) {
print “<form action=“process.php?id=”.$id.”&type=".$type."" method=“post” name=“comment”> n

n n n n n
Title:
Author:
Style: n n n n n
n n  Help n
Post: n
"; };

// the code for process.php, which tries to insert the data, is this:
if ($_POST[‘post’]) {

// title of comment
$title = $_POST['title'];
// author of comment
$author = $_POST['author'];
// body text of comment
$body = "<p>".$_POST['post']."</p>";

// patterns to search for - styles
$patterns[0] = "|[b](.*?)[/b]|s";
$patterns[1] = "|[i](.*?)[/i]|s";
$patterns[2] = "|[u](.*?)[/u]|s";
$patterns[3] = "|[center](.*?)[/center]|s";
$patterns[4] = "|[link](.*?)[/link]|s";
$patterns[5] = "|[link=(.*?)](.*?)[/link]|s";

// replacements for styles
$replacements[0] = "<b>$1</b>";
$replacements[1] = "<i>$1</i>";
$replacements[2] = "<u>$1</u>";
$replacements[3] = "<center>$1</center>";
$replacements[4] = "<a href="$1">$1</a>";
$replacements[5] = "<a href="$1">$2</a>";

// sort arrays by key name
ksort($patterns);
ksort($replacements);
$replaced = preg_replace($patterns, $replacements, $body);

$type = $HTTP_GET_VARS['type'];
$entry[0] = $HTTP_GET_VARS['id'];
$entry[1] = date("dS M");
$entry[2] = stripslashes($author);
$entry[3] = stripslashes($title);
$entry[4] = stripslashes($replaced);
$entry[5] = $_SERVER['REMOTE_ADDR'];

foreach( $entry as $temp ) {
	print "$temp<BR>";
};

$query = $entry[0].",null,'".$entry[1]."','".$entry[2]."','".$entry[3]."','".$entry[4]."','".$entry[5]."'";

$user = "time2ch_conn";
$password = "***";
$conn = mysql_connect( "localhost", $user, $password );
mysql_select_db( "time2ch_comments" );

if( $type = "a" ) {
	mysql_query( "LOCK TABLES article WRITE" ) or die( "Couldn't LOCK: ".mysql_error() );
	mysql_query( "INSERT INTO article VALUES ".$query ) or die( "Couldn't INSERT: ".mysql_error() );
	mysql_query( "UNLOCK TABLES" ) or die( "Couldn't UNLOCK: ".mysql_error() );
	mysql_close( $conn );
	header( "Location: http://www.time2changefbcy.com/index.ph ... rticle&id=".$id );
} elseif( $type = "p" ) {
	mysql_query( "LOCK TABLES poll WRITE" ) or die( "Couldn't LOCK: ".mysql_error() );
	mysql_query( "INSERT INTO poll VALUES ".$poll ) or die( "Couldn't INSERT: ".mysql_error() );
	mysql_query( "UNLOCK TABLES" ) or die( "Couldn't UNLOCK: ".mysql_error() );
	mysql_close( $conn );
	header( "Location: http://www.time2changefbcy.com" );
};

};
[/php]
MOD EDIT: Added PHP bb code tags

also, the table in mysql (at least for the articles, the poll table is a bit modified) has these columns (in order)
id (int 6) [this is the id of the article. in the comment.php GET line, this is “id”
commentid (int 6) auto increment primary key
date (varchar 10) [generated using Date() function - text date]
author (varchar 35) [author’s name]
title (varchar 35) [title of post]
post (text) [text of post]
ip (varch 15) [ip of poster, found with remote address thingy]

the poll comments table is modified slightly so that id is an int 4.
thanks!


#4

You really don’t need to lock the table manually…


#5

still won’t help me insert data though, correct?


#6

A couple of notes :
[php]
// Note the right parenthisis at the end of this statement
$query = $entry[0].",null,’".$entry[1]."’,’".$entry[2]."’,’".$entry[3]."’,’".$entry[4]."’,’".$entry[5]."’)";
[/php]

// Note the left parenthisis after the VALUES
[php]
mysql_query( “INSERT INTO article VALUES (”.$query ) or die( "Couldn’t INSERT: ".mysql_error() );
[/php]

The insert statement should be in the form of :
INSERT INTO table VALUES (‘Value1’, ‘Value2’, ‘Value3’, ‘etc’);

Although you do not need to quote numeric type fields (and others), it won’t hurt to do it. The only real exception is if the field is to contain a NULL value. If you quote it, it will contain the string of NULL. If the field is NUMERIC, then you will get an error.

Just another note. It makes things a bit easier (Debugging wise) if you create your entire query as a single variable. For instance:

[php]
$query = “INSERT INTO article VALUES (”.$entry[0].",null,’".$entry[1]."’,’".$entry[2]."’,’".$entry[3]."’,’".$entry[4]."’,’".$entry[5]."’)";
mysql_query( $query ) or die( "Couldn’t INSERT: ".mysql_error() );
[/php]
That way you can just echo $query to the screen to see the complete Query. Then errors tend to pop right out.

Hope this helps.
Good luck.


#7

Yup, that does it. I don’t know why, but I had forgotten that you needed parantheses around the values. Also, I normally would do what you mentioned - put the entire query in one variable - but if you look carefully, the if statement decides which table to insert data into. It’s easier to make a general “values” variable and then insert that into the INSERT script cause the two are different. :-) Thanks!


#8

Cool,

Glad I could help…

Don’t forget to make the adjustment for the type P also (with the parenthisis).

Also regarding that last comment on a single variable… I understand what you are saying about the if statement. You could concat them into a single variable just prior to the insert as well. (I know a lot of extra typing), but to me, it just helps to see the entire SQL statement as one chunk.

Anyway, again, glad I could help.