PHP and MySQL trouble.

[theweaselking]

So, I have this PHP code, talking to an SQL database.

The first part creates a text box with a button. When you put something in the box and click the button, it inserts it into an SQL database. The second half, after the form, reads all entries in that SQL database and displays them.

The second part works perfectly.
The first part fails with absolutely no errors - it just never adds anything to the table.

This example is RIGHT out of the book, literally.

Any PHP geeks out there who can tell me what the hell I'm doing wrong?

And yes, I've munged the brackets. Setting those back to angles is trivial. Making Livejournal obey a PRE tag is not trivial.



[html]

[?php
if ($comment){
mysql_connect("localhost","USERNAME","PASSWORD" )
or die("Could not connect to MySQL as USERNAME");



@mysql_select_db("test_database")
or die("could not select the test_database");



$string="INSERT INTO comment_table VALUES ('0','$comment')";
mysql_query($string)
or die(mysql_error( ));}
?]


[? echo $PHP_SELF ?]


[form action="[? echo $PHP_SELF ?]" method="get"]
[input type="text" name="comment" size="80"] [br]
[input type="submit"]
[/form]





[?php
$conn=mysql_connect("localhost","USERNAME","PASSWORD" )
or die("Could not connect to MySQL as USERNAME");



mysql_select_db("test_database", $conn)
or die("could not select the test_database");



$string="SELECT * FROM comment_table";
$result= mysql_query($string)
or die(mysql_error( ));



$numbers_cols= mysql_num_fields($result);



print "[b]query: $string[/b]";
print "[table border =1]\n";
print "[tr]";
print "[td] ID[/td]";
print "[td] Comment [/td]";
print "[/tr]";
while (list($id,$comment) = mysql_fetch_array($result)){
print "[tr]";
print "[td]$id[/td]";
print "[td]$comment[/td]";
print "[/tr]";}
print "[/table]";


?]


[/html]

EDIT: I've found the problem, I think. $PHP_SELF is not working. Now I just need to fix it...
EDIT THE SEQUEL: Fixed that, but still can't submit data. At least I'm getting an error, now.
EDIT PART THREE: ELECTRIC BOOGALOO: Fixed. The problem is that modern PHP can't just read comments submitted through the form by default, and you need to use $_REQUEST("comment") to grab the data that's fed into the form from a form named "comment"

Comments

[giza.livejournal.com]

See this line?
@mysql_select_db("test_database")

The @ sign suppresses errors from the function. Try removing it and see if you get an error.

You could also try:
error_reporting(E_ALL);
for maximum error/warning levels.

As far as "best practices" go, the return values of ALL functions should be checked for errors. But I'll be the first to admit that I do not do this 100% of the time.

D'oh!

[giza.livejournal.com]

I just noticed the "or die" clause, which does error checking. Silly me.

I'd still suggest turning up error reporting, though.

[theweaselking.livejournal.com]

Adding that error reporting line gives me:

Notice: Undefined index: comment in /var/www/test.php on line 4

Notice: Undefined variable: PHP_SELF in /var/www/test.php on line 18

[giza.livejournal.com]

Okay, those aren't a big deal. Like the text says that're just notices. PHP's equivilent of saying, "this isn't 100% correct and you may want to fix it, but it's not a deal breaker either".

register globals may be off

[giza.livejournal.com]

The code "if ($comment)" may be failing, because register globals could be turned off. The default was off start in PHP 4.2.(mumble) or so.

Try this:

if ($_REQUEST["comment"])

The bonus with $_REQUEST and friends is that you can use print_r() on them to get a dump of everything that was submitted for debugging purposes.

Re: register globals may be off

[theweaselking.livejournal.com]

Result of testing:
"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 ''')' at line 1"

Re: register globals may be off

[giza.livejournal.com]

Progress! Looks like it's gone into the branch where the MySQL insertion code is. Looking at your query:

$string="INSERT INTO comment_table VALUES ('0','$comment')";

Are there any parenthesis in your comment? That would bork the query. You can avoid this by using mysql_real_escape_string() (http://us3.php.net/manual/en/function.mysql-real-escape-string.php):

$string="INSERT INTO comment_table VALUES ('0', '" . mysql_real_escape_string($comment) . "')";

Also, from a security standpoint, I should warn you that ANY input coming from the outside world should be considered "untrusted" by default and sanitized in this manner. Otherwise you'll leave yourself wide open to a SQL injection attack.

If you want to see the query that's being choked on, you could also put an "echo $string" in there to see what query is being sent.

Re: register globals may be off

[theweaselking.livejournal.com]

There are no parentheses. THere's just random text, inputted by me from a keyboard.

Security: Thanks, but at the moment, I'm not worried about that. I'm just trying to make it *work* on a system that has no internet access, and *then* I'll worry about making it safe. Noted for the record, though.

Re: register globals may be off

[giza.livejournal.com]

Actually, single quotes in $comments could cause problems too. I still recommend calling mysql_real_escape_string() just to keep innocent users from accidentally breaking things.

If no luck, can you echo the query and paste it here?

Re: register globals may be off

[theweaselking.livejournal.com]

Echo'd query for a submission of gfdsgfdsgre:

INSERT INTO comment_table VALUES (,'')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 ''')' at line 1

So, I think we see the problem.

Re: register globals may be off

[scifantasy.livejournal.com]

Waitasec, yeah...you need to append the $comment string, stringwise.

$string="INSERT INTO comment_table VALUES ('0','".$comment."')";

or some such, to get the value of $comment put into its place.

Re: register globals may be off

[theweaselking.livejournal.com]

I'm also getting an error that it doesn't know what $comment is. I think it's appending the comment string just fine, and simply has no idea what $comment IS and so is barfing on that.

Re: register globals may be off

[theweaselking.livejournal.com]

Okay:
THIS:

mysql_query("INSERT INTO comment_table VALUES (0,'Totally, dude!')")
or die(mysql_error( ));

Works fine. It adds the comment "totally, dude" at place X, where X is the maximum current index plus one (because the 0 is taken and it's set to auto-increment.

The problem is that $comment is not being read. It doesn't know what $comment is.

Re: register globals may be off

[giza.livejournal.com]

Did you replace that instance of $comment with $_REQUEST["comment"]? :-)

Re: register globals may be off

[theweaselking.livejournal.com]

...no. Should I have?

Re: register globals may be off

[giza.livejournal.com]

Yep.

Just to clarify: anything you submit from a form will be in the $_REQUEST associative array.

More information on why the old method or just using variable names for form elements will not work anymore: http://us2.php.net/register_globals

Re: register globals may be off

[theweaselking.livejournal.com]

Good to know. I think the book I'm taking the examples from is older than what I'm doing.

Re: register globals may be off

[giza.livejournal.com]

PHP, still being a "young" programming language, has the nasty of habit of their developers saying, "Gee, that was a stupid design choice we made, LET'S CHANGE IT!" and not telling anyone or incrementing the major revision number. This isn't the first time them changing stuff like that has caused stuff to break in all sorts of interesting ways.

That being said, it's still my favorite language to program in.

Re: register globals may be off

[theweaselking.livejournal.com]

And how do I get $_REQUEST["comment"] into the middle of an already doubly-nested string without either the " or the ' escaping and giving me this error:
Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /var/www/test.php on line 12

Re: register globals may be off

[giza.livejournal.com]

Close off the partial string and do concatenation like this:

$string = "INSERT INTO comment_table VALUES ('0', "
   . "'" . mysql_real_escape_string($_REQUEST["comment"]) . "'"
   . ")";

Re: register globals may be off

[theweaselking.livejournal.com]

I did scifantasy's version while I was waiting. I'm new to PHP, but I need to remember that it really is just a programming language.

Re: register globals may be off

[scifantasy.livejournal.com]

$tobeadded = $_REQUEST["comment"];

Re: register globals may be off

[theweaselking.livejournal.com]

Yeah, I did that.
$comment = $_REQUEST["comment"];

Re: register globals may be off

[theweaselking.livejournal.com]

Real escape string is turning ' into \\' in the query and hence \' in the actual database.

I think magic_quotes is on. Now I need to find out how to turn it off.

Re: register globals may be off

[giza.livejournal.com]

magic_quotes is the spawn of SantaSatan.

http://us3.php.net/manual/en/security.magicquotes.disabling.php

Re: register globals may be off

[theweaselking.livejournal.com]

Yeah, I'm already there.

[dungeoneer.livejournal.com]

Hrm.

No semicolons following the echo statements?

<?echo $PHP_SELF; ?>

[theweaselking.livejournal.com]

That's right out of the book, though. Adding the semicolons produces errors.

[scifantasy.livejournal.com]

A few thoughts...

You do realize that all the added values have the same ID? If the ID is a primary key, this might wreak merry havoc.

In the second example, you use the resource link identifier in the select_db function; in the first, you don't. Maybe you should put it in [you know, $conn = mysql_connect() and then select_db(name, $conn)] and see if it helps.

[theweaselking.livejournal.com]

ID is set to autoincrement. No, it shouldn't creted havok, but even if it did, I'd be okay with that, because it would be adding SOMETHING to the database.

I'll try that.

[scifantasy.livejournal.com]

Except that in the tests I'm running (not knowing the format of the table, I'm using a quick one which I think mirrors the important parts), trying to add a duplicate key generates an error and does not add anything. Even if the id is set "autoincrement," telling it what the value is (such as saying 0) overrides that.

Try saying
$string="INSERT INTO comment_table (comment) VALUES '$comment'";

assuming the name of the second column is comment. That'll engage autoincrementing.

Why this isn't showing the error in the or die() statement, dunno.

[theweaselking.livejournal.com]

The table is dead simple - in fact, here's the creation details.

mysql> CREATE TABLE comment_table(
-> id INT NOT NULL auto_increment,
-> comment TEXT,
-> PRIMARY KEY(id));


I can add and remove things from here using SQL commands inside SQL or from the linux command line all day long, and it works perfectly. It's only through the PHP that it's failing.

[scifantasy.livejournal.com]

*nod* I see. Looks like I missed something in my recreation; the incrementing works fine in the table as described above. And looks like you found the problem.

[theweaselking.livejournal.com]

Kind of. I can SEE the problem. I just can't make it work, yet.

It's the problem of running into three different new things all at once. I've never used PHP before, or mySQL, or had to make HTML forms talk to them.

[fortysevenbteg.livejournal.com]

Try $_SERVER['PHP_SELF']. I just had to fix up some older code to work with PHP 5 where they cleaned up some of the globals.

[theweaselking.livejournal.com]

That works - it returns /test.php on a page named test.php.

[fortysevenbteg.livejournal.com]

Woot. :D

[thathatedguy.livejournal.com]

Actually, part two should be Electric Boogaloo, like the famous movie Star Trek II: Electric Boogaloo.