B4J Question String toSQL DateTime formatDim Unit As String Dim Weight, Height, Target, BMI, BMIa, BMIb As Int Dim Hft, Hin, Wst, Wlbs, Tst, Tlb As Int Unit = s

Colin Evans · Jul 27, 2020

Hi, is there any way to convert a date and time strings to a format acceptable to an SQL database holding it in DateTime type / format, i.e. I have two input fields Date and Time which the user can fill in, i.e. Date: 2020-07-27 and Time: 18:33 but I wish to save them to a MySql database which holds the date and time in that field format (datetime type not varchar() type)
usually the data held in the database would resemble 2020-07-27 18:33:00

TILogistic · Jul 27, 2020

an alternative is to send a date as string and convert it with mysql
When saving in the database

MySQL.

MySQL STR_TO_DATE() Function

This tutorial shows you how to use the MySQL STR_TO_DATE() function to convert a string into a date and/or time value with various practical examples.

www.mysqltutorial.org

.

PHP.

PHP: DateTime::format - Manual

PHP is a popular general-purpose scripting language that powers everything from your blog to the most popular websites in the world.

www.php.net

PHP: DateTime::createFromFormat - Manual

PHP is a popular general-purpose scripting language that powers everything from your blog to the most popular websites in the world.

www.php.net

aeric · Jul 27, 2020

What is the issue when you try to save the value as

Colin Evans said:
2020-07-27 18:33:00

?

B4X:

strSQL = "INSERT INTO Sales (CustomerName, SalesDate) VALUES ('Bob', '2020-07-27 18:33:00')"

Colin Evans · Jul 27, 2020

Hi once again, I've managed to get the Update and delete functions working in my PHP API along with showing all people in the database with the help of Aeric, DOnManfred, oparra etc the one last bit is the entry of a new person via my app and not the third party app.

I'm basically sending the following

<code>
"Action":"InsertPerson", "_sfm_form_submision_time_":mSQLDate, "Name":NameTF.Text,"Phone"honeTF.Text,"Party"artyTF.Text, "Email":EmailTF.Text
</code>

to the PHP code

<code>
Case "InsertPerson":
$pdate=stripslashes(mysqli_real_escape_string($con,$jsone["_sfm_form_submision_time_"]));
$pname=stripslashes(mysqli_real_escape_string($con,$jsone["Name"]));
$pphone=stripslashes(mysqli_real_escape_string($con,$jsone["Phone"]));
$pparty=stripslashes(mysqli_real_escape_string($con,$jsone["Party"]));
$pemail=stripslashes(mysqli_real_escape_string($con,$jsone["Email"]));
$stmt = $con->prepare("INSERT INTO login (_sfm_form_submision_time_, Name, Phone, Party, Email) VALUES (?, ?, ?, ?, ?)");
$rc=$stmt->bind_param("sss", $pdate, $pname, $pphone, $pparty, $pemail);
$rc=$stmt->execute();
$iid=$stmt->insert_id;
if ($iid == 0)
{
exit (json_encode(array(array('InsertPerson' => 'failed'))));
}
else
{
exit (json_encode(array(array('InsertPerson' => 'ok', 'pid' => $iid))));
}

break;
</code>

the field _sfm_form_submision_time_ contains the date, and I thought it was this that was causing the problem but when I remove it I still get the error InsertPerson Failed

I believe it's something to do with the 'bind_param("sss"' because I changed this to 'bind_param("sssss"' and it now works

From what I can determine I assume 's' equals string and 'i' would be integer are there other codes I should be aware of?

Thanks again for your quick responses

keirS · Jul 27, 2020

B4X:

Sub Process_Globals
    Private fx As JFX
    Private MainForm As Form
    Dim JOSQLDateTime As JavaObject
    Dim JSQL As SQL
End Sub

Sub AppStart (Form1 As Form, Args() As String)
    MainForm = Form1
    'MainForm.RootPane.LoadLayout("Layout1") 'Load the layout file.
    MainForm.Show
    DateTime.DateFormat = "yyyy-MM-dd"
    Dim DT As Long = DateTime.DateTimeParse("2020-07-27","18:33:00")
    Log(DT)
    JOSQLDateTime.InitializeNewInstance("java.sql.Timestamp",Array (DT))
    Log (JOSQLDateTime.RunMethod("toString",Null))
    JSQL.ExecNonQuery2("INSERT INTO mydatetable VALUES(?)",Array As Object(JOSQLDateTime))
End Sub

aeric · Jul 28, 2020

If the date and time is based on insert, what I normally do is set the column as timestamp and default value to CURRENT_TIMESTAMP. Another method is using MySQL now() function to insert into datetime field.

aeric · Jul 28, 2020

Colin Evans said:
I believe it's something to do with the 'bind_param("sss"' because I changed this to 'bind_param("sssss"' and it now works

Correct, you must match the parameters with 5 s for 5 string parameters.

B4J Question String toSQL DateTime formatDim Unit As String Dim Weight, Height, Target, BMI, BMIa, BMIb As Int Dim Hft, Hin, Wst, Wlbs, Tst, Tlb As Int Unit = s

Colin Evans

Active Member

TILogistic

Expert

MySQL STR_TO_DATE() Function

PHP: DateTime::format - Manual

PHP: DateTime::createFromFormat - Manual

aeric

Expert

Colin Evans

Active Member

keirS

Well-Known Member

aeric

Expert

aeric

Expert

Similar Threads

B4J Question String toSQL DateTime formatDim Unit As String Dim Weight, Height, Target, BMI, BMIa, BMIb As Int Dim Hft, Hin, Wst, Wlbs, Tst, Tlb As Int Unit = s

Active Member

Expert

Expert

Active Member

Well-Known Member

Expert

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency