Little How To? use PHP and PDO communication with MYSQL

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

sphere
Posts: 1145
Joined: Sat Sep 27, 2014 10:32 am
Location: Earth, Except when i Jump

Little How To? use PHP and PDO communication with MYSQL

Post by sphere » Wed Jun 22, 2016 9:15 pm

Little How To? use PHP and PDO for communication with a (not restricted to only MYSQL) database

LC has capabilities to communicate with SQL.
Works great when it's your own (local) server or if you are the only one who (via IP) are allowed to communicate with the database on a server at a host.
But if you need others to access that database too than you need something in between LC and the database.
Most used is a PHP file with MySQL commands in it. So you can POST data or Send a command to retrieve some data.
Now MySQL is deprecated since 2 years, but still used. But many providers will update their servers to PHP7.x.x (some providers now give a temporary option to use php5.x.x and PHP7.x.x).
So when they switch over fully to PHP7.x.x, you can't use MySQL any longer.
So you can choose then between MySQLi or PDO.
I believe the best option is PDO.
Why? Because you need to write only one script.
And by changing one little sentence in that script,
you can use it for up to 12 different types of databases.
Has a good protection against sql-injections, with the use of prepared statements.
And future proof.

To test locally, you can for example download Easyphp 16.1 which provides PHP7.x.x.
Once installed you need to open the php.ini file of the used PHP version(just let windows search

for it in the installation folder) and look for this line:
;extension=php_pdo_mysql.dll and change it to extension=php_pdo_mysql.dll (remove the semicolon in front) and save the file.
Then restart the local server.

With Easyphp comes phpmyadmin, access it via Easyphp's Dashboard.
With this you can Easily setup a Database, tables and columns.
Use InnoDB as the engine.
Set the collation of the DB to UTF8mb4 or just UTF8_generalci.
Do the same with the columns
Or use this demo database, unzip it and import the sql file into phpmyadmin
the_bd_name_sql.zip
test Database sql file
(566 Bytes) Downloaded 592 times
Put the PHP files which you can create from the examples below in the server root of Easyphp (or any other test server) like here:
drive:\EasyPHP-Devserver-16.1\eds-www\pdo_test\

This will just cover the simple basics, like INSERT, UPDATE and DELETE and getting info from the database. And the rest you have to figure out for yourself.

Bytheway, I'm not a PHP programmer, i just followed some things, asked some questions and broke my head about a tiny thing (i forgot an & character).
So there maybe other or better options.

In LC just create a little stack with 2 fields (one for id "nr" and one for a piece of "text")
and a few buttons to do simple basic commands.
Oh yeah and a field to read some "results" too.
or use this little stack
LC2PHP_PDO_MYSQL_4LCforum.zip
LC DB test stack
(2.11 KiB) Downloaded 612 times
__________________________________________________
GET ACCESS TO THE DATABASE

So now that you created a Database with a name like: the_db_name
a Table like: tablename
a column: id
another column: mytext
we can start with a small class which contains our access to the DB
This class we will call in each php file, so the server allows access also from other IP's

Create below php file in Notepad++ and save as connect_db.PHP, use characterset UTF-8 without BOM

Code: Select all

<?php
//PDO
$DBname = "the_db_name";
$servername = "localhost";
$username = "root";
$password = ""; //for localhost 127.0.0.1 Easyphp uses no password

try {
    $db = new PDO("mysql:host=$servername;dbname=$DBname;charset=utf8;'", $username, $password,
	array(PDO::ATTR_EMULATE_PREPARES => false,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
    }
catch(PDOException $e)
    {
    echo "Connection failed: " . $e->getMessage();
    }
?> 
________________________________________________________
INSERT
When you selected A_I in phpmyadmin when creating the id column then you do not need to send an

id nr, because the DB will do this for you.
But this example is without A_I (auto-increment).

Create below php file in Notepad++ and save as postinsert_by_id.PHP, use characterset UTF-8 without BOM

Code: Select all

<?php
// get the access
require_once 'connect_db.php';

try { 
  $stmt = $db->prepare("INSERT IGNORE INTO `tablename` (`id`, `mytext`) VALUES (:nr, :mytext)");
  $stmt->bindParam(':nr', $_POST['nr']);
   $stmt->bindParam(':mytext', $_POST['mytext']);

  $affected_rows = $stmt->rowCount();

 if($stmt->execute()) { echo "Success!"; } else { echo "Failure!"; };

 //var_dump($_POST)  // slash slash means uncommented
?>
Put below lc script in a button

Code: Select all

local tId,tTxt, varInsert,varResults
on mouseUp
   put fld"id" into tId
   put fld"text"into tTxt
   put "nr="& fld"id" & "&mytext=" & fld"txt" into varInsert #note the & before mytext= !!!
   post varInsert to url"http://127.0.0.1/pdo_test/postinsert_by_id.php"
    put it into varResults
    put varResults into fld"results"
   put empty into fld"id"
   put empty into fld"text"
   put empty into tId
   put empty into tTxt
end mouseUp


You can check in phpmyadmin if it is added to the DB, or later on below retrieve script
_________________________________________________________________________
UPDATE

Create below php file in Notepad++ and save as update_by_id.PHP, use characterset UTF-8 without BOM

Code: Select all

<?php
require_once 'connect_db.php';

try {
$nr = (isset($_POST['nr']) ? $_POST['nr'] : ''); //these 2 isset are not really neccessary
$mytext    = (isset($_POST["mytext"])    ? $_POST["mytext"]    : '');//only if you want to echo 

them. it can work without these 2 lines
	
$stmt = $db->prepare("UPDATE tablename SET mytext=:mytext WHERE id=:nr");
$stmt->bindParam(':nr', $_POST['nr'], PDO::PARAM_INT);
$stmt->bindParam(':mytext', $_POST['mytext'], PDO::PARAM_STR);
$affected_rows = $stmt->rowCount();

if($stmt->execute()) { echo "Success!"; } else { echo "Failure!"; };

	
//var_dump($_POST) // uncommenting this will show how the string was send to the php file
?>
Put below lc script in a button

Code: Select all

local tId,tTxt, varInsert,varResults
on mouseUp
   put fld"id" into tId
   put fld"text"into tTxt
   put "nr="& tId & "&mytext=" & tTxt into varInsert
   post varInsert to url"http://127.0.0.1/pdo_test/update_by_id.php"
   put it into varResults
   answer "Response from database:"&return&varResults
   put empty into fld"id"
   put empty into fld"text"
   put empty into tId
   put empty into tTxt
end mouseUp
_______________________________________________________________________________
Retrieving info from the DB

Create below php file in Notepad++ and save as get_by_id.PHP, use characterset UTF-8 without BOM

Code: Select all

<?php
require_once 'connect_db.php'; # hier komt $db vandaan

$nr = (isset($_POST['nr']) ? $_POST['nr'] : '');
$stmt = $db->prepare("SELECT mytext FROM tablename WHERE id=:nr");
$stmt->bindParam(':nr', $_POST['nr'],PDO::PARAM_INT);
$stmt->execute();
   
   	foreach($stmt as $row) {
      echo $row['mytext'];
    }
	
$db = null;
?>
put below lc script in a button

Code: Select all

local varResults, tId,varInsert
on mouseUp
   put empty into field "Field2"    
   put fld"id"into tId 
   put "nr=" & tId into varInsert
   post varInsert to URL "http://127.0.0.1/pdo_test/get_by_id.php"
   put it into varResults
   put varResults into field "Field2"
   end mouseUp
_______________________________________________________________________________

Delete
Note: that delete removes the whole row, so if you just want to empty a row and keep the id
record to fill later on then just UPDATE with empty values

Create below php file in Notepad++ and save as delete_by_id.PHP, use characterset UTF-8 without BOM

Code: Select all

<?php
// dit werkt correct, delete by id
require_once 'connect_db.php';

$nr   = (isset($_POST['nr'])    ? $_POST['nr']    : '');	

$stmt = $db->prepare("DELETE FROM tablename WHERE id=:nr");
$stmt->bindParam(':nr', $_POST['nr'], PDO::PARAM_INT);
$stmt->execute();
$affected_rows = $stmt->rowCount();
$db = NULL; 
?>
put below lc script in a button

Code: Select all

local tId,tTxt, varInsert,varResults
on mouseUp
   put fld"id" into tId
   #put fld"txt"into tTxt
   put "nr="& tId into varInsert
   post varInsert to url"http://127.0.0.1/pdo_test/delete_by_id.php"
   put it into varResults
   answer "Response from database:"&return&varResults
   put empty into fld"id"
   put empty into fld"txt"
   put empty into tId
   put empty into tTxt
end mouseUp
___________________________________
That's it.
The rest, like extend more tables and such you have to figure out for yourself.
Note that there are lots of examples and more ways to write the PHP code.
I found this the way to do the basics, look at examples and try and error.
Note that the php lines with isset in it are not really neccessary for communication with the
DB, but are needed if you want to echo it or something else.

If you need to access another type of DB than MySQL then search the web on how to change this line:
$db = new PDO("mysql:host=$servername;dbname=$DBname;charset=utf8;'", $username, $password,
array(PDO::ATTR_EMULATE_PREPARES => false,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
in the connect_db.php file.

One more important thing is the use of ticks ` or ' single ' and double ".
Ticks are used for `Tablenames` and `columnnames`, not always neccessary, but are if a tablename or columnname is a common used name in a DB, then you need to surround it with ticks ` (mostly found left to number 1 on your keyboard).

Good luck, Sphere.
I hope you can use it.

Dixie
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 1336
Joined: Sun Jul 12, 2009 10:53 am
Location: Bordeaux, France

Re: Little How To? use PHP and PDO communication with MYSQL

Post by Dixie » Tue Jan 17, 2017 9:21 pm

Now MySQL is deprecated since 2 years
really ?... lst stable release 5.7.17 / 12 December 2016; 23... doesn't time fly ..:-)

sphere
Posts: 1145
Joined: Sat Sep 27, 2014 10:32 am
Location: Earth, Except when i Jump

Re: Little How To? use PHP and PDO communication with MYSQL

Post by sphere » Tue Jan 17, 2017 9:46 pm

of course, it's still used but it's extension is deprecated as of 5.5.0 that's why you should use MySQLi or PDO

read here:
http://php.net/manual/en/changelog.mysql.php
and here
https://secure.php.net/manual/en/migrat ... -sapis.php

also
try to find out why Google and WikiPedia have moved from MySQL databases to MariaDB databases

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: Little How To? use PHP and PDO communication with MYSQL

Post by AxWald » Wed Jan 18, 2017 11:00 am

Hi,

Sphere is talking about an PHP extension/ API that has been declared deprecated and shouldn't be used anymore. Better explained here.
So if you don't use PHP this isn't of much interest for you.

Oracles owning of MySQL, the more political issues around it, and the resulting fork of MariaDB is another topic. But as both of these usually get forced upon you as part of a package ("*AMP*"), and are not chosen deliberately, this also isn't of much interest for the LC dev.

Have fun!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

teriibi
Posts: 254
Joined: Mon Nov 13, 2017 3:49 pm
Location: Bolivia

Re: Little How To? use PHP and PDO communication with MYSQL

Post by teriibi » Wed Jan 10, 2018 2:31 am

Hi Sphere,

Where can I find the syntax to build a php file that
also retreive content of all needed column in the row
- and then - fulfil the coresponding fields of my stack :?:

Your example below works well to retreive a single column and fill a single field :P ,
"mytext" >> Fld "text"
TKS :idea:

Code: Select all

<?php
require_once 'connect_db.php'; # hier komt $db vandaan

$nr = (isset($_POST['nr']) ? $_POST['nr'] : '');
$stmt = $db->prepare("SELECT mytext FROM tablename WHERE id=:nr");
$stmt->bindParam(':nr', $_POST['nr'],PDO::PARAM_INT);
$stmt->execute();
   
   	foreach($stmt as $row) {
      echo $row['mytext'];
    }
	
$db = null;
?>

sphere
Posts: 1145
Joined: Sat Sep 27, 2014 10:32 am
Location: Earth, Except when i Jump

Re: Little How To? use PHP and PDO communication with MYSQL

Post by sphere » Wed Jan 10, 2018 1:06 pm

Hi

well in the script you mention, there is a specific id requested, so that's why i post the id to get the data from it.
If you want data from all rows then you need something like this:

Code: Select all

<?php

require_once 'connect_to_db.php';

$stmt = $db->query('SELECT * FROM `tablename` ORDER BY `id`');
foreach ($stmt as $row)
{
	echo $row['id'] . "\t".$row['column1'] . "\t".$row['column2'] . "\t".$row['column3'] . "\n";
}
 $stmt->closeCursor();
$db = null;
?>
Still you need to mention all column names in the php file.
the \t creates tabs between every columndata and \n creates a new line for every row of data
so with itemdelimiter tab you can get every single item per row in lc

MaxV
Posts: 1579
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: Little How To? use PHP and PDO communication with MYSQL

Post by MaxV » Wed Jan 10, 2018 5:23 pm

revdataFromQuery has some sort of SQL injection defence. So you can connect directly to a MySql db.
Try this:

Code: Select all

put "red" into valueX
put "10" into valueY
put revDataFromQuery(tab,return,myID,"SELECT x,y FROM test WHERE x = :1 AND y = :2", "valueX", "valueY" ) into tResults
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

teriibi
Posts: 254
Joined: Mon Nov 13, 2017 3:49 pm
Location: Bolivia

Re: Little How To? use PHP and PDO communication with MYSQL

Post by teriibi » Wed Jan 10, 2018 6:09 pm

Ok, great ! Yes, a single row data retreiving is fine for this step..

I started reading about a Fetch function but cannot get it clear - yet - so i ll skip to this Row foreach.
thank you both for the scripts :P :P

sphere
Posts: 1145
Joined: Sat Sep 27, 2014 10:32 am
Location: Earth, Except when i Jump

Re: Little How To? use PHP and PDO communication with MYSQL

Post by sphere » Wed Jan 10, 2018 7:09 pm

MaxV wrote:
Wed Jan 10, 2018 5:23 pm
revdataFromQuery has some sort of SQL injection defence. So you can connect directly to a MySql db.
yes you can use it, but in another thread Teriibi mentioned, if i'm correct, he needed also other people to access the DB. If you only use LC and have the program installed on different locations, then you need every IP granted access to the DB. with the use of PHP you do not, because the db server sees it as local access.

teriibi
Posts: 254
Joined: Mon Nov 13, 2017 3:49 pm
Location: Bolivia

Re: Little How To? use PHP and PDO communication with MYSQL

Post by teriibi » Wed Jan 10, 2018 7:15 pm

@sphere,
exact, I will need to offer a public access.

MaxV
Posts: 1579
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: Little How To? use PHP and PDO communication with MYSQL

Post by MaxV » Fri Jan 12, 2018 1:56 pm

I my humble point of view you still don't need PHP.
You can use just livecode for all user clients, they connect directly to MySQL database.
Then on the server (Linux) install also fail2ban and then modify the file /etc/fail2ban/jail.local like this way:

Code: Select all

[mysqld-iptables]
enabled  = true
filter   = mysqld-auth
action   = iptables[name=mysql, port=3306, protocol=tcp]
       sendmail-whois[name=MySQL, dest=root, sender=fail2ban@example.com]
logpath  = /var/log/mysqld.log
maxretry = 5
This way after 5 attempts a user can't log in.
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

teriibi
Posts: 254
Joined: Mon Nov 13, 2017 3:49 pm
Location: Bolivia

Re: Little How To? use PHP and PDO communication with MYSQL

Post by teriibi » Fri Jan 12, 2018 2:19 pm

So cool, I could find how to parse the selected recordset from the getbyId.php in the following way... :mrgreen:

Is there is any more "efficient" way ref to cpu device resource or time syntax ?
(just post it next)
meanwhile this next works fine since its just meant for a single line record Id to retreive

Code: Select all

put it into toParse
   set itemdelimiter to tab
   repeat with i = 1 to the number of lines of toParse
      put item 1 of toParse into field name
      put item 2 of toParse into field fname
      put item 3 of toParse into field num
      put toParse into fld "information"
   end repeat
Wouhou ! :lol:

sphere
Posts: 1145
Joined: Sat Sep 27, 2014 10:32 am
Location: Earth, Except when i Jump

Re: Little How To? use PHP and PDO communication with MYSQL

Post by sphere » Fri Jan 12, 2018 8:30 pm

Hi MaxV,
i'm not familiar with that, never heard of.
It could work, but i'm not sure if you can use that on shared hosting sites, maybe only on a private server? i don't know.

It's also a matter of choice how someone wants to do it. Using any middleware like PHP is just a way to have easy acces to a database without you having to know IP's or any random person using your app and needs to retrieve data without having to log-in.

teriibi
Posts: 254
Joined: Mon Nov 13, 2017 3:49 pm
Location: Bolivia

Re: Little How To? use PHP and PDO communication with MYSQL

Post by teriibi » Fri Jan 12, 2018 8:39 pm

@MaxV

Code: Select all

action   = iptables[name=mysql, port=3306, protocol=tcp]
isn´t this way similar to accessing directly a DB from outside ?
My host and 1/2 million other ppl dont recomand direct access, unless maybe with an intranet.

..Whats the scientific name - :mrgreen: -for this type of DB access ?

FourthWorld
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 9801
Joined: Sat Apr 08, 2006 7:05 am
Location: Los Angeles
Contact:

Re: Little How To? use PHP and PDO communication with MYSQL

Post by FourthWorld » Sat Jan 13, 2018 6:00 am

teriibi wrote:
Fri Jan 12, 2018 8:39 pm
..Whats the scientific name - :mrgreen: -for this type of DB access ?
Exposed. ;)

Protecting a DB from the open Internet by using an intermediary scripting language offers many benefits for mitigating risk. There are ways to harden the DB, but with the unpredictable scope of potential vulnerabilities it never hurts to use a belt-and-suspenders approach.

Extra bonus points that setting up a REST API can also simply maintenance and enhancement of the server, and standardize client access for faster development.
Richard Gaskin
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn

Post Reply

Return to “Databases”