Showing entries 1 to 10 of 91
10 Older Entries »
Displaying posts with tag: LAMP (reset)
MySQL PHP Transaction

My students liked the MySQL Transaction post but wanted one that showed how an external web application would interact with MySQL in the scope of a transaction. So, I put a little PHP function together that write across two related tables in the context of a transaction. It uses mysqli (MySQL Improved Extension) to connect PHP to the MySQL database.

The function is barebones and uses the oldest approach of hidden inputs to maintain context between rendered forms using an HTML POST method. The hidden inputs are preceded with “h_” and snake case is used for variable names.

The function only writes to two tables. It writes to the member table and when that completes successfully to the contact table. The function:

  • Submits credentials from a file and raises an error when they don’t work.
  • Initializes a …
[Read more]
MySQL Transaction Scope

The idea of ACID transactions are a basic feature of SQL’s individual Data Manipulation Language (DML) commands, like the INSERT, UPDATE, and DELETE statements. Transactions across two or more tables are a natural extension of ACID compliance features provided by DML commands. However, they require a structured programming approach, like a store procedure or like API implemented in an imperative language.

Surprisingly, transaction management wasn’t covered well in Alan Beaulieu’s Learning SQL because he only provided pseudo code logic. While I thought troubleshoot some broken MySQL SQL/PSM logic would be a good learning experience for students, it wasn’t. So, I wrote this sample code to show how to achieve an all or nothing transaction across four tables.

The code for this example on transaction management lets you perform the important tasks necessary to effect transaction …

[Read more]
PHP CRUD Application – Portfolio Piece

I am super pleased to share that I have completed and uploaded my first (that I can share at least) personal portfolio piece written in PHP to a subdomain on my personal hosting server located at walk.openlamp.tech. Over the better part of the last year, I have developed a custom reporting dashboard written in PHP for my (current) employer, but do not share any of that work as it is proprietary and not owned by me. However, for a personal project, I can share far and wide. In this post, I provide a brief overview of my simple (in theory at least) application/site, built on the LAMP stack using the MVC (Model-View-Controller) design pattern in core PHP along with Bootstrap 4, jQuery, and MySQL.

Image by …

[Read more]
MySQL Self-Join

I’m switching to MySQL and leveraging Alan Beaulieu’s Learning SQL as a supporting reference for my Database Design and Development course. While reviewing Alan’s Chapter 5: Querying Multiple Tables, I found his coverage of using self-joins minimal.

In fact, he adds a prequel_film_id column to the film table in the sakila database and then a single row to demonstrate a minimal self-join query. I wanted to show them how to view a series of rows interconnected by a self-join, like the following:

SELECT   f.title AS film
,        fp.title AS prequel
FROM     film f LEFT JOIN film fp
ON       f.prequel_id = fp.film_id
WHERE    f.series_name = 'Harry Potter'
AND      fp.series_name = 'Harry Potter'
ORDER BY f.series_number;

It returns the following result set:

[Read more]
Java & MySQL 8.0.19

It’s the in-between term time and we’re all stuck at home. I decided to update the image for my Fedora 30 virtual machine. I had a work around to the update issue that I had encountered last October in Bug #96969 but it was not required with the current version. However, after updating from MySQL 8.0.17 to MySQL 8.0.19, I found that my Java connection example failed.

The $CLASSPATH value was correct:

/usr/share/java/mysql-connector-java.jar:.

The first error that I got was the my reference to MySQL JDBC driver was incorrect. The error message is quite clear:

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
Cannot connect to database server:
The server time zone …
[Read more]
mysqli Strict Standards

Six years ago I wrote a common lookup post to illustrate the effectiveness of things used throughout your applications. Now, I’m updating my student image with a more complete solution to show how to avoid update anomalies.

In the prior post, I used a while loop in PHP, like the following:

do {
      ...
} while($stmt->next_result());

Using PHP Version 7.3.8 and MySQL 8.0.16, that now raises the following error message:

Strict Standards: mysqli_stmt::next_result(): There is no next result set. Please, call mysqli_stmt_more_results()/mysqli_stmt::more_results() to check whether to call this function/method in /var/www/html/app/library.inc on line 81

You can see this type of error when you set the following parameters in your file during testing:

ini_set('display_errors',1); …
[Read more]
MySQL Update in mysqli

Somebody didn’t like the MySQLi Update Query example on the tutorialspoint.com website because it use the procedure mysqli_query style. Here’s a simple example of using the object-oriented method version. More or less, instead of query it uses the more intuitive execute() method.

The update_member function contains the logic and below it is a call to the test the function. It relies on a MySQLCredentials.inc file that contains the hostname, user name, password, and database name. You can create create member table, like my example in MySQL 8, or any other table in your MySQL database.

<?php /*
||  Function Name: update_member
*/
function update_member($account_number, $member_type, …
[Read more]
Apache on Fedora 30

There was an option during the Fedora 30 Workstation installation to add the Apache Web Server, but you need to set it to start automatically. Unfortunately, there was no option to install PHP, which I thought odd because of how many web developers learn the trade first on PHP with a LAMP (Linux, Apache, MySQL, Perl/PHP/Python) stack. You see how to fix that shortcoming in this post and how to install and test PHP, mysqli, and pdo to support MySQL 8.

Before you do that make sure you install MySQL 8. You can find my prior blog post on that here.

You set Apache to start automatically, on the next boot of the operating system, with the following command:

chkconfig httpd on

It creates a symbolic link:

Created symlink /etc/systemd/system/multi-user.target.wants/httpd.service → …
[Read more]
MySQL 5.7.* and mysqli

After installing MySQL 5.7.22 and PHP 7.1.17 on Fedora 27, you need to install the mysqli library. You need to verify if the mysqli library is installed. You can do that with the following mysqli_check.php program:


Check mysqli Install<?php if (!function_exists('mysqli_init') && !extension_loaded('mysqli')) {
    print 'mysqli not installed.'; }
  else {
    print 'mysqli installed.'; }
?>

You test preceding PHP program with the following URL in a browser:

http://localhost/mysqli_check.php

If the mysqli program isn’t installed, you can install it as follows by opening the yum interactive shell:

[root@localhost html]# yum shell
Last metadata expiration check: 1:26:46 ago on Wed 22 Aug 2018 08:05:50 PM MDT.
> remove php-mysql
No match for argument: php-mysql
Error: No packages marked for removal.
> install php-mysqlnd
> …
[Read more]
Linux User-Group Console

This post shows you how to add the menu option and GUI to set users and groups. It’s quite a bit easier than mastering all the command-line syntax. It makes setting up the required user and group accounts for an Oracle Enterprise or MySQL database solution much easier.

You add the utility by calling the yum (Yellowdog Updater, Modified) utility like this:

yum installed -y system-config_users

You should see the following:

Loaded plugins: langpacks
adobe-linux-x86_64                                       |  951 B     00:00     
ol7_UEKR3                                                | 1.2 kB     00:00     
ol7_latest                                               | 1.4 kB     00:00     
Resolving Dependencies
--> Running transaction check
---> Package system-config-users.noarch 0:1.3.5-2.el7 will be installed
--> Processing …
[Read more]
Showing entries 1 to 10 of 91
10 Older Entries »