Inserting into online database

[jonbonazza]

ok, so an android app I am working on needs to read from an online database and display the results dynamically. This works fine, but then when I press a button in my UI, I need it to add a new row into the same database. When I test the app and click the button, it doesn't throw any exceptions or anything and once it finsihes the process goes right back to letting me do w/e I need on the UI. When I test the PHP script through the browser, it goes through all the echos I have in the script and reaches the bottom of the script no problem, however it doesn't add the row... I am at a loss here... Maybe you all can help.

 

Here is my PHP file:

[syntax=php]

<?php

mysql_connect("localhost","XXXXX","XXXXX");

mysql_select_db("bonafie0_mm");

echo "connected to database";

mysql_query("INSERT INTO Comments (user, comment) VALUES ('".$_REQUEST['user']."', ".$_REQUEST['comment']."')");

echo "done. ";

mysql_close();

?>

[/syntax]

 

and for those of you who are experienced with java and/or the android SDK, here is my android code:

[syntax=java]

private void postComment()

{

String user = editName.getText().toString();

String comment = editComment.getText().toString();

OutputStream os = null;

ArrayList<NameValuePair> nameValuePair = new ArrayList<NameValuePair>();

nameValuePair.add(new BasicNameValuePair("user", user));

nameValuePair.add(new BasicNameValuePair("comment", comment));

try

{

HttpClient client = new DefaultHttpClient();

HttpPost post = new HttpPost(ROTM_POST_URL);

post.setEntity(new UrlEncodedFormEntity(nameValuePair));

HttpResponse response = client.execute(post);

 

}

catch(IOException e)

{

Toast.makeText(RoTM.this, "Unable to post comments", Toast.LENGTH_SHORT).show();

}

editName.setText("");

editComment.setText("");

}

[/syntax]

 

Any ideas? I posted this on the only two reputable android dev forums I could find, but no one has been able to help me as of yet... Since I am fairly confident in my java abilities, I figured my problem probably lies in my PHP code.

[MatthewJ]

Add some code to the query to display the mysql errors...

 

The query is probably just failing, and mysql won't tell you that unless you add something like

mysql_query("SELECT statement", $connection) or die (mysql_error);

 

There are more elegant ways to display the error, but this should help you find an answer

 

 

[jonbonazza]

Ok, so I did as you suggested and youa re correct the query is failing. It's weird though, because it even fails if I put

"http://bonafide-software.com/mitsu-media/post_comment_rotm.php?user=name&comment=TestComment" into the browser bar.

Am I doing something wrong in my script?

[MatthewJ]

I would suggest generating a query (send in the data in the url, and just echo out the sql statement that it is passing to the server.)

 

then take and run that query directly against the database to see if it runs. That should help see why it is failing, then you can manipulate it there until it works, then transfer that to the code.

 

Also, you should use mysql_real_escape_string() on the data fields to be safe

[jonbonazza]

Pardon my ignorance, but I am a noob when it comes to PHP lol. I am not really understanding what you mean by "send in the data in the url and just echo out the sql statement that it is passing to the server". Could you elaborate on this?

 

Thanks,

Jon

[MatthewJ]

$sql = "INSERT INTO Comments (user, comment) VALUES ('".mysql_real_escape_string($_REQUEST['user'])."', ".mysql_real_escape_string($_REQUEST['comment'])."')";
mysql_query($sql) or die ("Query: ".$sql."<br /><br />".mysql_error());
echo "done. ";
mysql_close();

 

Something like that

[jonbonazza]

Mmkay, I edited my code to do what you suggested and I get the following result:

 

Query: INSERT INTO Comments (user, comment) VALUES ('name', TestComment')

 

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

 

What is considered line 1? If it means the mysql_connect() function, it was copied from my grab_comment PHP file that works... why is it throwing an error on this one?

 

edit: here is my updated script:

<?php
mysql_connect("localhost","bonafie0_mm","mm121289");
mysql_select_db("bonafie0_mm");
echo "connected to database";
$sql = "INSERT INTO Comments (user, comment) VALUES ('".mysql_real_escape_string($_REQUEST['user'])."', 

".mysql_real_escape_string($_REQUEST['comment'])."')";
mysql_query($sql) or die ("Query: ".$sql."<br /><br />".mysql_error());
echo "done. ";
mysql_close();
?>

 

[DavidAM]

Actually, that error message came from the database server.  The 'line 1' is referring to line 1 of the query.  Since your query contains only one line, it is not helpful here.

 

The problem is that there is a quote character missing just before the comment string.  The code should be:

$sql = "INSERT INTO Comments (user, comment) VALUES ('".mysql_real_escape_string($_REQUEST['user'])."', 
'".mysql_real_escape_string($_REQUEST['comment'])."')";

 

or use sprintf to build the query, I've recently discovered this helps keep the code cleaner and easier to read

$sql = sprintf("INSERT INTO Comments (user, comment) VALUES ('%s', '%s')", 
mysql_real_escape_string($_REQUEST['user']),
mysql_real_escape_string($_REQUEST['comment']));

 

 

EDIT: I mean the line number is not helpful.  The error message was very helpful.

[MatthewJ]

You're missing a single quote in front of your second value

[jonbonazza]

Awesome it works thanks! Unfortuantely, there must still be an error in my android code as it works find int he browser, but fails when doing it fro my android device.. bah... oh well.. Thanks for the help guys!