Little How To? use PHP and PDO communication with MYSQL

Organizing tasks to work on, New Features Ideas, Building LCS & LCB Libraries & Widgets, Redecorating and Modifying the IDE, Hacking / Editing Tools, Compiling the Engine from Source, etc.
Locked
Sphere
Posts: 3
Joined: Sun Sep 12, 2021 12:42 pm
Contact:

Little How To? use PHP and PDO communication with MYSQL

Post by Sphere »

Little How To? use PHP and PDO for communication with a (not restricted to only MYSQL) database
(I copied it over from LC forum where it had more than 35860 views, so I think it helped a lot of people. All this info is freely findable on the web. But I did a bunch of trial and error back then. No need for further discussion on this as on the LC forum. So this thread may be locked and/or put at a Database sub, as I saw there is none yet.)

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 PHP extension/ API 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 PHP extension/ API 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
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
__________________________________________________
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.
Locked

Who is online

Users browsing this forum: No registered users and 1 guest