Cannot Insert Records Into a MySQL Table

[Mahares]

I cannot insert any records in the MySQL table. Below is the simple PHP connection script and I have attached my very simple B4A project. When a button is clicked the record should be saved. I hope someone can set me straight. Thank you.

<?
$databasehost = "xxxxxx.db.1342534.zzzzzzz.com";
$databasename = "proddb";
$databaseusername ="produser";
$databasepassword = "mypassword";

$con = mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
mysql_select_db($databasename) or die(mysql_error());

if (mysql_errno()) { 
    echo mysql_error(); 
}else{    
    print "Successful Connection.";   
}
?>

 

[mc73]

But, Mahares, where is the query command in your php? I only see the connection.
I would suggest following Erel's mysql tutorial to the letter. It's simple code and effective. Worked for me, with the minimum effort in coding

 

[Mahares]

Please take a look at my B4A code. I only make the connection via PHP. The query is in the b4A code as I understand it in an insert query. Erel's example does not have an INSERT feature, only SELECT. I already figured the SELECT part out. If you think I am not understanding the process, please correct the code and post it. I really need some concrete answers.
Thanks

 

[Erel]

The PHP code should support INSERT and UPDATE statements. Which error do you get?

 

[mc73]

You don't have the mysql_query in your php, neither the variable sent. You have to get the post data, either by using Erel's example (file_get_contents) or by using the $_POST['yourquery'] type.
You simply connect to your database, but do nothing with the query sent from your b4a, which by the way, should use just the 'post' option, not the 'get' one. When the query is executed, a 'true' or 'false' will be returned as the result of the transaction, if successful or not. I still insist that you don't change Erel's php code. Just in the hc_responseSuccess (what so ever, I am not in front of a machine with b4a right now), you should parse the returning value (true or false) and thus know whether your 'insert' was successful. I have no better working example than the one provided at the mysql tutorial, which I use successfully for selecting, updating, inserting, deleting.

 

[bluedude]

Better to use PDO and bind, more safe

Hi,

I know lots of people in here still use the "old" way to connect to MySQL but PDO is the only way to go to be very safe and secure.

Ever heard of SQL injection? The way you do it right now is wide open because it does not use parameterized queries? You know Coding horror? Take a look here: Coding Horror: Give me parameterized SQL, or give me death

Anyway, below should do it with PDO:

//new database object connection
$db = new PDO(mysql:dbname=<your database>;host=localhost, 'your db username, 'your db password');

$value1='a';
$value2='b';

$sql = "INSERT INTO table (field1,field2) VALUES field1,:field2)";
$dbex = $db->prepare($sql);
$dbex->execute(array(':field1=>$value1,:field2=>$value2));

BTW, this is not the best sample because I prefer to use bind with PDO but this will help you to take the first steps.

In the end you are probably better of creating a safe REST (JSON response) API around all your DB calls.

Cheers,

 

[Mahares]

@mc73: You are corerct. I should have used the code as it is shown in Erel's tutorial, instead of trying to tamper with it.

@bluedude: This is my first exposure to PHP and MySQL. My next step is to use the local SQLite database as the source for the MySQL records. Every time I save a record to the SQLite table, I would save the same record to a remote MySQL database table to have instant transfer. In the solution you propose, how will you export the data from the SQLite table to the PHP script and then from PHP to MySQL. Can you provide some simple real world snippett as I would like to pursue all the possibility to make the data safer.
Thank you very guys for your generosity with your time.

 

[bluedude]

Hi Mahares,

I cannot give you the code, currently it is part of a complete solution with my own JSON and http handlers.

Basically on every local save you post data to the PHP script, I would also advice to use https instead. There have been enough samples of plain text breaches through mobile apps.

I use my own custom api's to do that but the PDO sample would work for inserting.

Anyway, out of scope to provide you a complete sample unfortunately.

I think there is enough information in this forum to get started. Erel also provided one although I think it should use PDO and parameterized queries. These are faster and saver.

Cheers.

 

[Dogbonesix]

Can Not Insert Record in MySql

I read I need to use Post instead of Get, but I get a syntax error with the code below

req.InitializePost2("http://www.dogbone6.org/rintintin/myphp/PhpTest1.php", Query.PostBytes("UTF8"))

What would be the correct syntax?

I do connect using GetBytes but then I get a Code 500 error which kinda makes sense

 

[Erel]

Which error do you get?

 

[Dogbonesix]

This works for me.

I just beat on the code until it worked - I really don't know what I am doing - just persistent.

Sub Process_Globals
'These global variables will be declared once when the application starts.
'These variables can be accessed from all modules.

Dim hc As HttpClient
Dim Q_SELECT, Q_UPDATE As Int, Q_INSERT
Q_SELECT = 1
Q_UPDATE = 2
Q_INSERT = 3
End Sub
=============================
Sub Process_Globals
'These global variables will be declared once when the application starts.
'These variables can be accessed from all modules.

Dim hc As HttpClient
Dim Q_SELECT, Q_UPDATE As Int, Q_INSERT
Q_SELECT = 1
Q_UPDATE = 2
Q_INSERT = 3
End Sub
===============================
Sub Activity_Create(FirstTime As Boolean)
If FirstTime Then
hc.Initialize("hc")
End If
InsertLine
End Sub
===============================
Sub InsertLine
ProgressDialogShow("Inserting Note data...")
Dim f1 As String
f1 = "TestSuccess2" ' So one can use other data such as a Label.Text as varible - which is handy.
ExecuteInsertQuery("INSERT INTO y1_users(theuser)" & " VALUES (" & "'" & f1 & "'" & ")", Q_INSERT)
End Sub
===============================

 

[vdituro]

UPDATE statement

The PHP code should support INSERT and UPDATE statements. Which error do you get?

Hello Erel, I am new at b4a. I have looked at your excelent mysql tutorial which has helped me a lot.

With the SELECT statement I had a problem which took me a while to figure out, and would like to share with you people. Some of my database fields names are a mix of upper and lower case letters. I have a field "idODS" on the "ods" table. (probably a bad practice)
So
ExecuteRemoteQuery("SELECT idods, ......... did not work
ExecuteRemoteQuery("SELECT idODS, ......... worked ok

Probably it has to do with the mysql version, I dont know.

Just to let you people know about it

Best Regards to you all