Longtext, php and the thing that I did wrong?

[Child of Darkness]

Okay, so here's the problem:

I'm trying to get myself a form which is able of adding an enormous amount of text as the 'entry'-field into a database. the rest of code that you will find beneath work till I found out I had to use longtext or blob or something to get great amounts of data in the DB.

POEMinput is the field containing the precious data.

This is my code of the addpoem.php:

[code]<?php
include('connect.php');
$max_ID = mysql_result(mysql_query('SELECT MAX(ID) from poems'),0) +1;

if(isset($_GET['commented']))
{
$IDinput = $max_ID;
$NAMEinput = $_GET['NAMEinput'];
$AUTHinput = $_GET['AUTHinput'];
$POEMinput = nl2br($_GET['POEMinput']);
$LANGinput = $_GET['LANGinput'];
$sql = "INSERT INTO poems (ID,NAME,AUTHOR,POEM,LANGUAGE) values ('$IDinput', '$NAMEinput', '$AUTHinput' , '$POEMinput', '$LANGinput')";

mysql_query($sql) or die(mysql_error('Error'));
echo"Your comment has been posted<br><a href='addpoem.php'>Add another Poem</a><br><a href='admin.php'>Back to Admin-area</a>";
}
else
{
?>
<form method='get' action="<? echo $PHP_SELF ?>">

<h1><img src="icons/AddIcon.png" border="0" width="32">Add a Poem</h1>
<table class="details" border="0" cellspacing="10">
<tr>
        <td class="id">ID: <?php echo $max_ID; ?></td>
        <td class="language">Language: <input type="text" style="width:150px" name="LANGinput" /></td>
</tr>
<tr>
        <td class="name">Name: <input type="text" style="width:150px" name="NAMEinput" /></td>
        <td class="author">Author: <input type="text" style="width:150px"  name="AUTHinput"/></td>
</tr>
</table>
<div class="poem">
<textarea class="poem" name="POEMinput">
</textarea>
</div>
<div class="buttons"><input type="submit" value="OK"/><input type="reset" value="Reset" onClick="reset();"/><input type= 'hidden' name= 'commented' value= 'set' >&nbsp;<a href="admin.php" class="button">Back</a><br></div>
</form>
<?
}
?>[/code]

the problem with it is it worked with text, now I use longtext it suddenly doesn't anymore.
I searched internet for days looking for an answer and then found out my account here was still open so I'm asking you. what am I doing wrong?
tips about layout and styling are welcome too but the main thing is to get it working:)


ps. sorry for the messed-up code, it's copy-pasted, so....

[fenway]

First, you should probably be using an AUTO_INCREMENT field for your IDs -- using MAX() is not recommended, nor desired. It's entirely possible to get UID collisions this way.

Second, AFAIK, there's no difference at all -- you can store anything in TEXT/LONGTEXT, and DB shouldn't know the difference, and neither should PHP. What do you mean by "not working anymore"? Nothing gets inserted? An error is produced? Please clarify.

[Child of Darkness]

[!--quoteo(post=348787:date=Feb 23 2006, 10:05 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 23 2006, 10:05 PM) [snapback]348787[/snapback][/div][div class=\'quotemain\'][!--quotec--]
First, you should probably be using an AUTO_INCREMENT field for your IDs -- using MAX() is not recommended, nor desired. It's entirely possible to get UID collisions this way.

Second, AFAIK, there's no difference at all -- you can store anything in TEXT/LONGTEXT, and DB shouldn't know the difference, and neither should PHP. What do you mean by "not working anymore"? Nothing gets inserted? An error is produced? Please clarify.
[/quote]

The max_id field isn't there because of the incrementing:)
It's there to show the new number in a textfield so that's no problem.
As the form uses self it refreshes and when I tried to change it mysql gave an error, but I'll change that the next time.


About the error:
It doesn't say anything but it just doesn't insert.
with a normal text is showed till it became >255 chars,
now it doesn't do anything anymore.

thanks,

[wickning1]

Unless magic quotes is turned on, you probably have a ' in the text that is screwing up the query. Use echo get_magic_quotes_gpc() to find out if magic quotes is turned on for your script.

[Child of Darkness]

[!--quoteo(post=348814:date=Feb 23 2006, 10:58 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Feb 23 2006, 10:58 PM) [snapback]348814[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Unless magic quotes is turned on, you probably have a ' in the text that is screwing up the query. Use echo get_magic_quotes_gpc() to find out if magic quotes is turned on for your script.
[/quote]

I already hoped that was the problem but the problem also shows up when using no ' at all.

[fenway]

That is odd -- either it inserts, or it complains. It can't be neither.

[Child of Darkness]

[!--quoteo(post=348844:date=Feb 24 2006, 12:21 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 24 2006, 12:21 AM) [snapback]348844[/snapback][/div][div class=\'quotemain\'][!--quotec--]
That is odd -- either it inserts, or it complains. It can't be neither.
[/quote]

there's nothing more than the code I already gave.
So this means I have to trash it and start over??
or could there be another mistake in my db?

the field the code refers to is:
POEM longtext utf8_general_ci

and I found out the short texts can still be inserted but the longer just don't
so when it really becomes a longtext it does not work any longer

[shoz]

[quote]
First, you should probably be using an AUTO_INCREMENT field for your IDs -- using MAX() is not recommended, nor desired. It's entirely possible to get UID collisions this way.
[/quote]
[quote]
The max_id field isn't there because of the incrementing:)
It's there to show the new number in a textfield so that's no problem.
[/quote]
[quote]
[code]
$max_ID = mysql_result(mysql_query('SELECT MAX(ID) from poems'),0) +1;
$IDinput = $max_ID;
$sql = "INSERT INTO poems (ID,NAME,AUTHOR,POEM,LANGUAGE) values ('$IDinput', '$NAMEinput', '$AUTHinput' , '$POEMinput', '$LANGinput')";
[/code]
[/quote]
You're using the max_id (MAX(id)+1) to insert a new record. As fenway suggested make the column AUTO_INCREMENT;
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']ALTER TABLE[/span] poems [color=green]CHANGE[/color] ID ID INT AUTO_INCREMENT PRIMARY KEY; [!--sql2--][/div][!--sql3--]
You can then remove the line
[code]
$max_ID = mysql_result(mysql_query('SELECT MAX(ID) from poems'),0) +1;
[/code]
And instead use [a href=\"http://www.php.net/mysql_insert_id\" target=\"_blank\"]mysql_insert_id()[/a]. To find the ID of the record you last inserted.

You'll also have to change the insert line. Removing the ID as one of the columns to be inserted in.
[code]
$sql = "INSERT INTO poems (NAME,AUTHOR,POEM,LANGUAGE) values ('$NAMEinput', '$AUTHinput' , '$POEMinput', '$LANGinput')";
[/code]

[quote]
[code]
mysql_query($sql) or die(mysql_error('Error'));
[/code]
[/quote]
[a href=\"http://www.php.net/mysql_error\" target=\"_blank\"]mysql_error()[/a] doesn't take a 'string' argument. Change it to
[code]
or die(mysql_error());

/**
* You may also want to see the query that gave
* the error if one occurs.  In which case you can
* change it to the following.
*
* Note that your users will also be able to see the
* query and error when one is generated. You should
* find another way to handle errors after you've finished
* debugging.
*/
or die(mysql_error()."<br />\n".$sql);
[/code]
Put the following at the very top of your script as well. You'll be able to see NOTICE errors that by default are usually not shown, but can sometimes help find a/the problem.
[code]
error_reporting(E_ALL);
[/code]
If you're still having problems, post the script to show the changes you've made in addition to any errors shown.

Btw, how are you determining that the POEM is not inserted. If you're not directly querying the database, post the relevant portions of the script that should show it.

[Child of Darkness]

[!--quoteo(post=349039:date=Feb 24 2006, 03:37 PM:name=shoz)--][div class=\'quotetop\']QUOTE(shoz @ Feb 24 2006, 03:37 PM) [snapback]349039[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Btw, how are you determining that the POEM is not inserted. If you're not directly querying the database, post the relevant portions of the script that should show it.
[/quote]

before I start coding my viewer pages, I always check my code with my database management system, PhpMyAdmin in this case:).. and when I use the current inserting this doesn't show up in pma, all the other fields do but the poem itself doesn't

[Child of Darkness]

Stats:

when 255 chars: no notices, no errors; inserting takes place and everything works fine
when 255+ chars:
varies from time to time what notice or weird stuff is showing up, most common:

Undefined index: POEMinput in /home/xaddict/public_html/admin/addpoem.php on line 33
on that line is the statement: $POEMinput = nl2br($_GET['POEMinput']);

when a very large text is inserted:
414 Request-URI Too Large

[Child of Darkness]

Okay, I solved the problem myself this time so if anyone feels like patting me for this:p I won't stop you:P

final code, I changed GET into POST and made the whole form post data instead of GETting the URl;

CODE:
[code]
<?php
error_reporting(E_ALL);
include('connect.php');

if(isset($_POST['commented']))
{
$NAMEinput = $_POST['NAMEinput'];
$AUTHinput = $_POST['AUTHinput'];
$POEMinput = nl2br($_POST['POEMinput']);
$LANGinput = $_POST['LANGinput'];
$sql = "INSERT INTO poems (NAME,AUTHOR,POEM,LANGUAGE) values ('$NAMEinput', '$AUTHinput' , '$POEMinput', '$LANGinput')";

mysql_query($sql) or die(mysql_error()."<br />\n".$sql);

echo"Your comment has been posted<br>
<a href='addpoem.php'>Add another Poem</a><br>
<a href='admin.php'>Back to Admin-area</a>";
}
else
{
?>
<form method='post' action="<? echo $PHP_SELF ?>">

<h1><img src="icons/AddIcon.png" border="0" width="32">Add a Poem</h1>
<table class="details" border="0" cellspacing="10">
<tr>
<td class="id">ID: <?php echo mysql_insert_id() ?></td>
<td class="language">Language: <input type="text" style="width:150px" name="LANGinput" /></td>
</tr>
<tr>
<td class="name">Name: <input type="text" style="width:150px" name="NAMEinput" /></td>
<td class="author">Author: <input type="text" style="width:150px"  name="AUTHinput"/></td>
</tr>
</table>
<div class="poem">
<textarea class="poem" name="POEMinput">
</textarea>
</div>
<div class="buttons">
<input type="submit" value="OK"/>
<input type="reset" value="Reset" onClick="reset();"/>
<input type= 'hidden' name= 'commented' value= 'set' >
<a href="admin.php" class="button">Back</a>
<br>
</div>
</form>
<?
}
?>
[/code]

thanks for all the help. glad it finally works. if anyone has any hints about coding left, tell me.

one more question; what's the code to make sure the ID that the poem is going to have is visible in the form? do I need a new selecting statement for it?

[shoz]

mysql_insert_id() is used to show the id of the last insert you made. There's usually no reason to show the Id ahead of time and the MAX(id)+1 shouldn't be depended on to give the correct id. I'd recommend you don't try to give that information until after the user submits the form.

To do it reliably would probably mean inserting a row before showing the form and then updating when the form is submitted. Using an auth string to make sure users don't clobber other users submissions and possibly having some code to remove records that haven't had any poems submitted after a specific timeout.

[fenway]

You were passing it via a GET paramater? Well, yeah, that would explain it.

[Child of Darkness]

[!--quoteo(post=350352:date=Feb 28 2006, 09:36 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 28 2006, 09:36 PM) [snapback]350352[/snapback][/div][div class=\'quotemain\'][!--quotec--]
You were passing it via a GET paramater? Well, yeah, that would explain it.
[/quote]

haha, it was like 10 times in my code
but everything works now, so...
thanks to everyone who helped me with this
glad I found the worst mistake myself though:)