MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘PHP’ Category

MySQL PNG Files

with one comment

LAMP (Linux, Apache, MySQL, Perl/PHP/Python) Architecture is very flexible. All the components can be positioned on the same server or different servers. The servers are divided into two types. The types are known as the Application or database tiers. Generally, the application tier holds the Apache Server, any Apache Modules, and local copies of Server Side Includes (SSI) programs.

In many development environments, you also deploy the client to the same machine. This means a single machine runs the database server, the application server, and the browser. The lab for this section assumes these configurations.

Before you test an installation, you should make sure that you’ve started the database and Apache server. In an Oracle LAMP configuration (known as an OLAP – Oracle, Linux, Apache, Perl/PHP/Python), you must start both the Oracle Listener and database. MySQL starts the listener when you start the database. You must also start the Apache Server. The Apache Server also starts an Apache Listener, which listens for incoming HTTP/HTTPS requests. It listens on Port 80 unless you override that setting in the httpd.conf file.

The URI reaches the server and is redirected to an Apache Module based on configuration information found in the httpd.conf file. Spawned or child processes of the Apache Module then read programs into memory from the file system and run them. If you’ve uploaded a file the locally stored program can move it from a secure cache location to another local area for processing. The started programs can run independently or include other files as libraries, and they can communicate to the database server.

Working though PHP test cases against the MySQL database for my AlmaLinux installation and configuration, I discovered that the php-gd library weren’t installed by default. I had to add it to get my PHP programs to upload and display PNG files.

The log file for applying the php-gd packages:

Display detailed console log →

Last metadata expiration check: 3:59:15 ago on Wed 28 Dec 2022 08:17:58 PM EST.
Dependencies resolved.
================================================================================
 Package         Architecture    Version               Repository          Size
================================================================================
Installing:
 php-gd          x86_64          8.0.20-3.el9          appstream           43 k
 
Transaction Summary
================================================================================
Install  1 Package
 
Total download size: 43 k
Installed size: 110 k
Downloading Packages:
php-gd-8.0.20-3.el9.x86_64.rpm                  196 kB/s |  43 kB     00:00    
--------------------------------------------------------------------------------
Total                                            39 kB/s |  43 kB     00:01     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                        1/1 
  Installing       : php-gd-8.0.20-3.el9.x86_64                             1/1 
  Running scriptlet: php-gd-8.0.20-3.el9.x86_64                             1/1 
  Verifying        : php-gd-8.0.20-3.el9.x86_64                             1/1 
 
Installed:
  php-gd-8.0.20-3.el9.x86_64                                                    
 
Complete!

The balance of this page demonstrates how to upload, store, and manage Text (Character Large Data Streams) and BLOBs (Binary Large Objects). It provides MySQL equivalent instructions to those for manaing LOBs in an Oracle database. As covered in Chapter 8 in my Oracle Database 11g PL/SQL Programming book.

Before you begin these steps, you should have already installed Zend Server Community Edition. If you haven’t done so, please click here for instructions.

Create directories or folders, and position code →

This section provides you with instructions on how to position the code components in Windows, at least for the newbie. If you’re on Linux, you probably know how to do most if not all of this already. Likewise, if you already know how to put things in the right place, please choose your own locations.

  1. Create a LOB (Large Object) directory for the PHP files inside the htdocs directory.

oraclelobsetup1

    You can down the MySQL PHP Upload LOB Web Code zip file and unzip it into the directory you just created. It can co-exist with the Oracle equivalent if you’ve done that already.

Load a TEXT (like an Oracle CLOB) column to the MySQL database →

This is a copy of the three files required to load a large string to a MySQL database into a mediumtext data type. The code is in clear text because somebody asked for it. They’re nervous about zip files. Click the title above to expand all the code text.

MySQLCredentials.inc

1
2
3
4
5
6
7

<?php
  // Connection variables.
  define('HOSTNAME',"localhost");
  define('USERNAME',"student");
  define('PASSWORD',"student");
  define('DATABASE',"sampledb");
?>

UploadItemDescMySQLForm.htm

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

<html>
<head>
<title>
  UploadItemDescMySQLForm.htm
</title>
</head>
<body>
<form id="uploadForm"
      action="UploadItemDescMySQL.php"
      enctype="multipart/form-data"
      method="post">
  <table border=0 cellpadding=0 cellspacing=0>
    <tr>
      <td width=125>Item Number</td>
      <td>
        <input id="id" name="id" type="text">
      </td>
    </tr>
    <tr>
      <td width=125>Item Title</td>
      <td>
        <input id="title" name="title" type="text">
      </td>
    </tr>
    <tr>
      <td width=125>Select File</td>
      <td>
        <input id="uploadfilename" name="userfile" type="file">
      </td>
    </tr>
    <tr>
      <td width=125>Click Button to</td>
      <td><input type="submit" value="Upload File"></td>
    </tr>
  </table>
</form>
</body>
</html>

UploadItemDescMySQL.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129

<?php
  // Set database credentials.
  include_once("MySQLCredentials.inc");
 
  // Displayed moved file in web page.
  $item_desc = process_uploaded_file();
 
  // Return successful attempt to connect to the database.
  if (!$c = @mysqli_connect(HOSTNAME,USERNAME,PASSWORD,DATABASE)) {
    // Print user message.
    print "Sorry! The connection to the database failed. Please try again later.";
 
    // Assign the mysqli_error() and format double and single quotes.
    print mysqli_error();
 
    // Kill the resource.
    die();
  }
  else {
    // Declare input variables.
    $id = (isset($_POST['id']))    ? (int) $_POST['id'] : $id = 21;
    $title = (isset($_POST['title'])) ? $_POST['title'] : $title = "Harry #1";
 
    // Initialize a statement in the scope of the connection.
    $stmt = mysqli_stmt_init($c);
 
    // Declare a PL/SQL execution command.
    $sql = "Update item  set item_desc = ? where item_id = ?";
 
    // Prepate statement and link it to a connection.
    if (mysqli_stmt_prepare($stmt,$sql)) {
      mysqli_stmt_bind_param($stmt,"si",$item_desc,$id);
 
      // Execute it and print success or failure message.
      if (mysqli_stmt_execute($stmt)) {
        query_insert($id,$title);
      }
      else {
        print "You're target row doesn't exist.";
      }
    }
    // Disconnect from database.
    mysqli_close($c);
  }
 
  // Query results afret an insert.
  function query_insert($id,$title)
  {
    // Return successful attempt to connect to the database.
    if (!$c  = @mysqli_connect(HOSTNAME,USERNAME,PASSWORD,DATABASE)) {
      // Print user message.
      print "Sorry! The connection to the database failed. Please try again later.";
 
      // Assign the OCI error and format double and single quotes.
      print mysqli_error();
 
      // Kill the resource.
      die();
    }
    else {
 
      // Initialize a statement in the scope of the connection.
      $stmt = mysqli_stmt_init($c);
 
      // Declare a SQL SELECT statement returning a CLOB.
      $sql = "SELECT item_desc FROM item WHERE item_id = ?";
 
      // Prepare statement.
      if (mysqli_stmt_prepare($stmt,$sql)) {
        mysqli_stmt_bind_param($stmt,"i",$id);
 
        // Execute it and print success or failure message.
        if (mysqli_stmt_execute($stmt)) {
 
          // Bind result to local variable.
	      mysqli_stmt_bind_result($stmt, $desc);
 
          // Read result.
          mysqli_stmt_fetch($stmt);
 
          // Format HTML table to display biography.
          $out = '<table border="1" cellpadding="3" cellspacing="0">';
          $out .= '<tr>';
          $out .= '<td align="center" class="e">'.$title.'</td>';
          $out .= '</tr>';
          $out .= '<tr>';
          $out .= '<td class="v">'.$desc.'</td>';
          $out .= '</tr>';
          $out .= '</table>';
 
          // Print the HTML table.
          print $out;
        }
      }
 
      // Disconnect from database.
      mysqli_close($c);
    }
  }
 
  // Manage file upload and return file as string.
  function process_uploaded_file()
  {
    // Declare a variable for file contents.
    $contents = "";
 
    // Define the upload file name for Windows or Linux.
    if (preg_match(".Win32.",$_SERVER["SERVER_SOFTWARE"]))
      $upload_file = "C:\\temp\\".$_FILES['userfile']['name'];
    else
      $upload_file = "/tmp/".$_FILES['userfile']['name'];
 
    // Check for and move uploaded file.
    if (is_uploaded_file($_FILES['userfile']['tmp_name']))
      move_uploaded_file($_FILES['userfile']['tmp_name'],$upload_file);
 
    // Open a file handle and suppress an error for a missing file.
    if ($fp = @fopen($upload_file,"r"))
    {
      // Read until the end-of-file marker.
      while (!feof($fp))
        $contents .= fgetc($fp);
      // Close an open file handle.
      fclose($fp);
    }
    // Return file content as string.
    return $contents;
  }
?>

Load a BLOB column to the MySQL database →

This is a copy of the four files required to load a large image to a MySQL database into a MEDIUMBLOB data type. The fourth file reads the binary image and translates it into an HTML header and image that can be read through a call to the src attribute of an img tag. You can find the call to the forth file in the UploadItemBlobMySQL.php.

The code is in clear text because somebody asked for it. They’re nervous about zip files. Click the title above to expand all the code text.

MySQLCredentials.inc

1
2
3
4
5
6
7

<?php
  // Connection variables.
  define('HOSTNAME',"localhost");
  define('USERNAME',"student");
  define('PASSWORD',"student");
  define('DATABASE',"sampledb");
?>

UploadItemBlobMySQLForm.htm

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

<html>
<head>
<title>
  UploadItemBlobMySQLForm.htm
</title>
</head>
<body>
<form id="uploadForm"
      action="UploadItemBlobMySQL.php"
      enctype="multipart/form-data"
      method="post">
  <table border=0 cellpadding=0 cellspacing=0>
    <tr>
      <td width=125>Item Number</td>
      <td>
        <input id="id" name="id" type="text">
      </td>
    </tr>
    <tr>
      <td width=125>Item Title</td>
      <td>
        <input id="title" name="title" type="text">
      </td>
    </tr>
    <tr>
      <td width=125>Select File</td>
      <td>
        <input id="uploadfilename" name="userfile" type="file">
      </td>
    </tr>
    <tr>
      <td width=125>Click Button to</td>
      <td><input type="submit" value="Upload File"></td>
    </tr>
  </table>
</form>
</body>
</html>

UploadItemBlobMySQL.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147

<?php
  // Set database credentials.
  include_once("MySQLCredentials.inc");
 
  // Displayed moved file in web page.
  $item_blob = process_uploaded_file();
 
  // Return successful attempt to connect to the database.
  if (!$c = @mysqli_connect(HOSTNAME,USERNAME,PASSWORD,DATABASE)) {
    // Print user message.
    print "Sorry! The connection to the database failed. Please try again later.";
 
    // Assign the mysqli_error() error and format double and single quotes.
    print mysqli_error();
 
    // Kill the resource.
    die();
  }
  else {
    // Declare input variables.
    $id = (isset($_POST['id'])) ? (int) $_POST['id'] : 1021;
    $title = (isset($_POST['title'])) ? $_POST['title'] : "Harry #1";
 
    // Initialize a statement in the scope of the connection.
    $stmt = mysqli_stmt_init($c);
 
   // Declare a PL/SQL execution command.
    $sql = "UPDATE item SET item_blob = ? WHERE item_id =  ?";
 
    // Prepare statement and link it to a connection.
    if (mysqli_stmt_prepare($stmt,$sql)) {
      mysqli_stmt_bind_param($stmt,"bi",$item_blob,$id);
 
      $start = 0;
      $chunk = 8192;
      while ($start < strlen($item_blob)) {
        mysqli_stmt_send_long_data($stmt,0,substr($item_blob,$start,$chunk));
        $start += $chunk;
      }
 
      // Execute the PL/SQL statement.
      if (mysqli_stmt_execute($stmt)) {
        query_insert($id,$title);
      }
      else {
        print "Your target row doesn't exist.";
      }
    }
    else { print "mysqli_stmt_prepare() failed."; }
    // Disconnect from database.
    mysqli_close($c);
  }
 
  // Query results afret an insert.
  function query_insert($id,$title)
  {
    // Return successful attempt to connect to the database.
    if (!$c = @mysqli_connect(HOSTNAME,USERNAME,PASSWORD,DATABASE)) {
      // Print user message.
      print "Sorry! The connection to the database failed. Please try again later.";
 
      // Assign the OCI error and format double and single quotes.
      print mysqli_error();
 
      // Kill the resource.
      die();
    }
    else {
 
      // Initialize a statement in the scope of the connection.
      $stmt = mysqli_stmt_init($c);
 
      // Declare a SQL SELECT statement returning a CLOB.
      $sql = "SELECT item_desc FROM item WHERE item_id = ?";
 
      // Prepare statement and link it to a connection.
      if (mysqli_stmt_prepare($stmt,$sql)) {
        mysqli_stmt_bind_param($stmt,"i",$id);
 
        // Execute the PL/SQL statement.
        if (mysqli_stmt_execute($stmt)) {
 
          // Bind result to local variable.
	      mysqli_stmt_bind_result($stmt, $data);
 
          // Read result.
          mysqli_stmt_fetch($stmt);
 
          // Format HTML table to display BLOB photo and CLOB description.
          $out = '<table border="1" cellpadding="5" cellspacing="0">';
          $out .= '<tr>';
          $out .= '<td align="center" class="e">'.$title.'</td>';
          $out .= '</tr>';
          $out .= '<tr><td class="v">';
          $out .= '<div>';
          $out .= '<div style="margin-right:5px;float:left">';
          $out .= '<img src="ConvertMySQLBlobToImage.php?id='.$id.'">';
          $out .= '</div>';
          $out .= '<div style="position=relative;">'.$data.'</div>';
          $out .= '</div>';
          $out .= '</td></tr>';
          $out .= '</table>';
 
          // Print the HTML table.
          print $out;
        }
        else {
          print "You're target row doesn't exist.";
        }
      }
 
      // Disconnect from database.
      mysqli_close($c);
    }
  }
 
  // Manage file upload and return file as string.
  function process_uploaded_file()
  {
    // Declare a variable for file contents.
    $contents = "";
 
    // Define the upload file name for Windows or Linux.
    if (preg_match(".Win32.",$_SERVER["SERVER_SOFTWARE"]))
      $upload_file = "C:\\TEMP\\".$_FILES['userfile']['name'];
    else
      $upload_file = "/tmp/".$_FILES['userfile']['name'];
 
    // Check for and move uploaded file.
    if (is_uploaded_file($_FILES['userfile']['tmp_name']))
      move_uploaded_file($_FILES['userfile']['tmp_name'],$upload_file);
 
    // Open a file handle and suppress an error for a missing file.
    if ($fp = @fopen($upload_file,"r"))
    {
      // Read until the end-of-file marker.
      while (!feof($fp))
        $contents .= fgetc($fp);
 
      // Close an open file handle.
      fclose($fp);
    }
 
    // Return file content as string.
    return $contents;
  }
?>

ConvertMySQLBlobToImage.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

<?php
  // Database credentials must be set manually because an include_once() function
  // call puts something ahead of the header, which causes a failure when rendering
  // an image.
 
  // Return successful attempt to connect to the database.
  if (!$c = @mysqli_connect("localhost","student","student","sampledb")) {
 
    // Print user message.
    print "Sorry! The connection to the database failed. Please try again later.";
 
    // Assign the OCI error and format double and single quotes.
    print mysqli_error();
 
    // Kill the resource.
    die();
  }
  else {
 
    // Declare input variables.
    $id = (isset($_GET['id'])) ? (int) $_GET['id'] : 1023;
 
    // Initialize a statement in the scope of the connection.
    $stmt = mysqli_stmt_init($c);
 
    // Declare a SQL SELECT statement returning a MediumBLOB.
    $sql = "SELECT item_blob FROM item WHERE item_id = ?";
 
    // Prepare statement and link it to a connection.
    if (mysqli_stmt_prepare($stmt,$sql)) {
      mysqli_stmt_bind_param($stmt,"i",$id);
 
      // Execute the PL/SQL statement.
      if (mysqli_stmt_execute($stmt)) {
 
        // Bind result to local variable.
	      mysqli_stmt_bind_result($stmt, $image);
 
        // Read result.
        mysqli_stmt_fetch($stmt);
      }
    } 
 
    // Disconnect from database.
    mysqli_close($c);
 
    // Print the header first.
    header('Content-type: image/x-png');
    imagepng(imagecreatefromstring($image));
  }
?>
  1. Create a temp directory for the upload target location, as qualified in the PHP code. The PHP code works regardless of whether you’re on Windows or Linux, but it does depend on the creation of this directory.

oraclelobsetup2

  1. Create a directory or folder for the large file source directories. This directory is probably on your test machine (laptop) but it mimics a client laptop and would work if your server was on a different machine.

oraclelobsetup3

  1. Inside the Upload directory, you should create the following two directories:

oraclelobsetup4

    You should download the CLOB Text File zip file and unzip it into the textfiles directory; then download the BLOB Image File zip file and unzip it into the imagefiles directory.

Assuming you’ve downloaded the zip files and extracted them into the correct locations, this section is done.

Prepare the MySQL database →

This section provides you with instructions on how to ensure everything will work once the PHP programs call the database. Even if you have one of my sample Video Store databases, you should verify and add appropriate columns. This post assumes you’ve downloaded the one of my basic Video Store models

  1. Navigate to the directory that you created for SQL scripts, which should be /home/student/Data/mysql. In that directory at the command prompt, connect as the student user, which should be student. You connect to the MySQL database, with the following syntax as student (if you need more help, check this blog post on configuring MySQL).
mysql -ustudent -pstudent

Once connected to the database, you run the files to create the database, like:

mysql> source /Data/mysql/create_mysql_store.sql
mysql> source /Data/mysql/seed_mysql_store.sql
  1. Navigate to the directory that you created for SQL scripts, which should be /home/student/Data/mysql. In that directory at the command prompt, connect as the student user, or whichever account you’re using. You should confirm that you have a item_desc column of TEXT data type, and an item_blob column of MEDIUMBLOB type in the item table. If you don’t have those columns, you can add them with the following statement:
ALTER TABLE item
ADD (item_desc TEXT, item_blob MEDIUMBLOB);

After ensuring that you have those two columns, you’ve completed this section.

Test the Configuration →

This section shows you how to test all that you’ve done. It works provided you created the directories and extracted the zip file contents to their respective directories. The virtual URL actually maps to the /var/www/html/lob directory.

  1. Enter the http://localhost/lob/UploadItemDescMySQLForm.htm URL, and complete the form by choosing a valid item_id column value and text file from your /home/student/Upload/TextFiles directory. Then, click the Upload File button (you can see a larger version of the image by clicking on it).

mysql_lob1

  1. This page displays after you successfully upload the text file to the database.

mysql_lob2

  1. Enter the http://localhost/lob/UploadItemBlobFormMySQL.htm URL, and complete the form by choosing a valid item_id column value and image file from your /home/student/Upload/ImageFiles directory. Then, click the Upload File button (you can see a larger version of the image by clicking on it).

mysql_lob3

  1. This page displays after you successfully upload the image file to the database.

mysql_lob4

Troubleshooting the Configuration →

This section shows you how to check why something isn’t working.

  1. The first thing to check are the credentials. They’re in the MySQLCredentials.inc file. They’re posted with a localhost machine name, student username, student password, and sampledb database.
  1. Not to be funny, but the second thing to check are credentials. Specifically, you need to check the credentials in the ConvertBlobToImage.php file. They’re individually entered in the connect string of this file because otherwise they put something in front of the header, which is disallowed to render the image.
  1. Check to see if the text or image file made it to the /var/www/html/lob/temp directory. If they made it that far but no further, check to see if you have valid procedures in the student schema.
  1. Check whether the TEXT and MEDIUMBLOB are loaded into the database. You use the LENGTH function, like this:
SELECT   i.item_id
,        length(i.item_desc)
,        length(i.item_blob)
FROM     item i
WHERE    i.item_desc IS NOT NULL
OR       i.item_blob IS NOT NULL;
  1. Check if the item_id value is found in the list of values.
  1. If you’re stumped, add a comment and explain what’s up.

If you find any problems, please let me know. I’ll be happy to fix them.

Written by maclochlainn

December 28th, 2022 at 10:59 pm

Posted in Database,LAMP,Linux,MySQL,MySQL 8,PHP,Unix

Tagged with , ,

AlmaLinux Install & Configuration

without comments

This is a collection of blog posts for installing and configuring AlmaLinux with the Oracle, PostgreSQL, MySQL databases and several programming languages. Sample programs show how to connect PHP and Python to the MySQL database.

I used Oracle Database 11g XE in this instance to keep the footprint as small as possible. It required a few tricks and discovering the missing library that caused folks grief eleven years ago. I build another with a current Oracle Database XE after the new year.

If you see something that I missed or you’d like me to add, let me know. As time allows, I’ll try to do that. Naturally, the post will get updates as things are added later.

Written by maclochlainn

December 21st, 2022 at 5:29 pm

Posted in AlmaLinux,Apache,Database,DBA,Java,Java 17,LAMP,Linux,MySQL,MySQL 8,MySQL Connect/J,Oracle,Oracle 11g,Oracle DBA,Oracle Developer,Oracle XE,pdo,Perl,Perl DBI,pgAdmin4,PHP,Postgres,PostgreSQL,PostgreSQL 15,Python 3.x,Rust,VMWare,VSCode

Tagged with , , , , , , , ,

mysqli Strict Standards

with 2 comments

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);
ini_set('display_startup_errors',1);
error_reporting(E_ALL);

You can read more about error handling at this web page. The new and strict compliance standard for mysqli managing rows is:

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

As always, I hope this helps those looking for an answer.

Written by maclochlainn

September 14th, 2019 at 10:30 pm

Posted in LAMP,MySQL,MySQL 8,mysqli,PHP

Tagged with ,

MySQL Update in mysqli

without comments

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, $credit_card_number, $credit_card_type) {
 
  // Include the credentials file if omitted.
  include_once("MySQLCredentials.inc");
 
  // Assign credentials to connection.
  $mysqli = new mysqli(HOSTNAME, USERNAME, PASSWORD, DATABASE);
 
  // Check for connection error and print message.
  if ($mysqli->connect_errno) {
    print $mysqli->connect_error."<br />";
    print "Connection not established ...<br />";
  }
  else {
    // Initial statement.
    $stmt = $mysqli->stmt_init();
 
    /* Disabling auto commit when you want two or more statements executed as a set.
    || ------------------------------------------------------------
    ||  You would add the following command to disable the default
    ||  of auto commit.
    ||  ------------------------------
    ||   $mysqli->autocommit(FALSE);
    || ------------------------------------------------------------
    */
 
    // Declare a static query.
    $sql = "UPDATE   member\n"
         . "SET      member_type = ?\n"
         . ",        credit_card_number = ?\n"
         . ",        credit_card_type = ?\n"
         . "WHERE    account_number = ?\n";
 
    /* Prepare statement.
    || ------------------------------------------------------------
    ||  Please note that the bind_param method is a position 
    ||  rather than named notation, which means you must provide
    ||  the variables in the same order as they are found in
    ||  the defined $sql variable as "?".
    || ------------------------------------------------------------
    ||  print($sql);
    ||  print("Member Type:      [1][".$member_type."]\n");
    ||  print("Credit Card No:   [2][".$credit_card_number."]\n");
    ||  print("Credit Card Type: [3][".$credit_card_type."]\n");
    ||  print("Account Number:   [4][".$account_number."]\n");
    || ------------------------------------------------------------
    */
    if ($stmt->prepare($sql)) {
      $stmt->bind_param("ssss",$member_type,$credit_card_number,$credit_card_type,$account_number); } 
 
    // Attempt query and exit with failure before processing.
    if (!$stmt->execute()) {
 
      // Print failure to resolve query message.
      print $mysqli->error."<br />";
      print "Failed to resolve query ...<br />";
    }
    else {   
      /*  Manually commiting writes when you have disabled the
      ||  default auto commit setting, explained above.
      || ------------------------------------------------------------
      ||  You would add the following command to commit the 
      ||  transaction.
      ||  ------------------------------
      ||   $mysqli->commit();
      || ------------------------------------------------------------
      */
    }
  }
}
 
// Test case
update_member('US00011', '1006', '6011-0000-0000-0078', '1007');
?>

I put this logic in a function.php file. If you do the same, you can run the test case like this from the command line:

php function.sql

As always, I hope this helps.

Written by maclochlainn

September 14th, 2019 at 6:20 pm

Posted in Database Design,LAMP,MySQL,MySQL 8,mysqli,PHP

Tagged with

Apache on Fedora 30

with one comment

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 → /usr/lib/systemd/system/httpd.service.

However, that command only starts the Apache server the next time you boot the server. You use the following command as the root user to start the Apache server:

apachectl start

You can verify the installation with the following command as the root user:

ps -ef | grep httpd | grep -v grep

It should return:

root      5433     1  0 17:03 ?        00:00:00 /usr/sbin/httpd -DFOREGROUND
apache    5434  5433  0 17:03 ?        00:00:00 /usr/sbin/httpd -DFOREGROUND
apache    5435  5433  0 17:03 ?        00:00:00 /usr/sbin/httpd -DFOREGROUND
apache    5436  5433  0 17:03 ?        00:00:00 /usr/sbin/httpd -DFOREGROUND
apache    5437  5433  0 17:03 ?        00:00:00 /usr/sbin/httpd -DFOREGROUND
apache    5438  5433  0 17:03 ?        00:00:00 /usr/sbin/httpd -DFOREGROUND
apache    5442  5433  0 17:03 ?        00:00:00 /usr/sbin/httpd -DFOREGROUND

and, then verify the listening port with the following command as the root user:

netstat -tulpn | grep :80

It should return the following when both the Apache server is listening on port 80 and the Oracle multi-protocol server is listening on port 8080:

tcp6       0      0 :::80                   :::*                    LISTEN      119810/httpd        
tcp6       0      0 :::8080                 :::*                    LISTEN      1403/tnslsnr

You can also enter the following URL in the browser to see the Apache Test Page:

http://localhost

It should display the test page, like this:

You can also create a hello.htm file in the /var/www/html directory to test the ability to read an HTML file. I would suggest the traditional hello.htm file:

<html>
<body>
Hello World!
</body>
</html>

You can call it by using this URL in the browser:

http://localhost/hello.htm

It should display the test page, like this:

Now, let’s install PHP. You use the following command as a privileged user, which is one found in the sudoer’s list:

yum install -y php

Display detailed console log →

Last metadata expiration check: 0:37:02 ago on Fri 16 Aug 2019 11:03:54 AM MDT.
Dependencies resolved.
=============================================================================
 Package               Arch        Version                Repository    Size
=============================================================================
Installing:
 php                   x86_64      7.3.8-1.fc30           updates      2.8 M
Installing dependencies:
 nginx-filesystem      noarch      1:1.16.0-3.fc30        updates       11 k
 php-cli               x86_64      7.3.8-1.fc30           updates      4.3 M
 php-common            x86_64      7.3.8-1.fc30           updates      1.1 M
Installing weak dependencies:
 php-fpm               x86_64      7.3.8-1.fc30           updates      1.5 M
 
Transaction Summary
=============================================================================
Install  5 Packages
 
Total download size: 9.6 M
Installed size: 43 M
Downloading Packages:
(1/5): nginx-filesystem-1.16.0-3.fc30.noarch  34 kB/s |  11 kB     00:00    
(2/5): php-common-7.3.8-1.fc30.x86_64.rpm    1.1 MB/s | 1.1 MB     00:00    
(3/5): php-7.3.8-1.fc30.x86_64.rpm           2.0 MB/s | 2.8 MB     00:01    
(4/5): php-fpm-7.3.8-1.fc30.x86_64.rpm       2.2 MB/s | 1.5 MB     00:00    
(5/5): php-cli-7.3.8-1.fc30.x86_64.rpm       1.7 MB/s | 4.3 MB     00:02    
-----------------------------------------------------------------------------
Total                                        3.0 MB/s | 9.6 MB     00:03     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                     1/1 
  Installing       : php-common-7.3.8-1.fc30.x86_64                      1/5 
  Installing       : php-cli-7.3.8-1.fc30.x86_64                         2/5 
  Running scriptlet: nginx-filesystem-1:1.16.0-3.fc30.noarch             3/5 
  Installing       : nginx-filesystem-1:1.16.0-3.fc30.noarch             3/5 
  Installing       : php-fpm-7.3.8-1.fc30.x86_64                         4/5 
  Running scriptlet: php-fpm-7.3.8-1.fc30.x86_64                         4/5 
  Installing       : php-7.3.8-1.fc30.x86_64                             5/5 
  Running scriptlet: php-7.3.8-1.fc30.x86_64                             5/5 
  Running scriptlet: php-fpm-7.3.8-1.fc30.x86_64                         5/5 
  Verifying        : nginx-filesystem-1:1.16.0-3.fc30.noarch             1/5 
  Verifying        : php-7.3.8-1.fc30.x86_64                             2/5 
  Verifying        : php-cli-7.3.8-1.fc30.x86_64                         3/5 
  Verifying        : php-common-7.3.8-1.fc30.x86_64                      4/5 
  Verifying        : php-fpm-7.3.8-1.fc30.x86_64                         5/5 
 
Installed:
  php-7.3.8-1.fc30.x86_64                     php-fpm-7.3.8-1.fc30.x86_64    
  nginx-filesystem-1:1.16.0-3.fc30.noarch     php-cli-7.3.8-1.fc30.x86_64    
  php-common-7.3.8-1.fc30.x86_64             
 
Complete!

Before you test the installation of PHP in a browser, you must restart the Apache HTTP Server. You can do that with the following command as a privileged user:

sudo apachectl restart

After verifying the connection, you can test it by creating the traditional info.php program file in the /var/www/http directory. The file should contain the following:

1
2
3

<?php
  phpinfo();
?>

It should display the PHP Version 7.3.8 web page, which ships with Fedora 30:

The next step shows you how to install mysqli and pdo with the yum utility. While it’s unnecessary to check for the older mysql library (truly deprecated), its good practice to know how to check for a conflicting library before installing a new one. Also, I’d prefer newbies get exposed to using the yum utility’s shell environment.

You start the yum shell, as follows:

yum shell

With the yum shell, you would remove a mysql package with the following command:

> remove php-mysql

The command will remove the package or tell you that there is no package to remove. Next, you install the php-mysqli package with this command:

install php-mysqli

You will then be prompted to confirm the installation of the php-mysqli library. Finally, you exit the yum shell with this command:

> quit

If you want to see the whole interactive shell, click on the link below.

Display detailed console log →

Last metadata expiration check: 0:53:05 ago on Fri 16 Aug 2019 11:03:54 AM MDT.
> remove php-mysql
No match for argument: php-mysql
No packages marked for removal.
> install php-mysqlnd
> run
=============================================================================
 Package            Architecture  Version               Repository      Size
=============================================================================
Installing:
 php-mysqlnd        x86_64        7.3.8-1.fc30          updates        195 k
Installing dependencies:
 php-pdo            x86_64        7.3.8-1.fc30          updates         91 k
 
Transaction Summary
=============================================================================
Install  2 Packages
 
Total download size: 286 k
Installed size: 1.4 M
Is this ok [y/N]: y
Downloading Packages:
(1/2): php-pdo-7.3.8-1.fc30.x86_64.rpm       136 kB/s |  91 kB     00:00    
(2/2): php-mysqlnd-7.3.8-1.fc30.x86_64.rpm   183 kB/s | 195 kB     00:01    
-----------------------------------------------------------------------------
Total                                         24 kB/s | 286 kB     00:11     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                     1/1 
  Installing       : php-pdo-7.3.8-1.fc30.x86_64                         1/2 
  Installing       : php-mysqlnd-7.3.8-1.fc30.x86_64                     2/2 
  Running scriptlet: php-mysqlnd-7.3.8-1.fc30.x86_64                     2/2 
  Verifying        : php-mysqlnd-7.3.8-1.fc30.x86_64                     1/2 
  Verifying        : php-pdo-7.3.8-1.fc30.x86_64                         2/2 
 
Installed:
  php-mysqlnd-7.3.8-1.fc30.x86_64         php-pdo-7.3.8-1.fc30.x86_64        
 
Last metadata expiration check: 0:53:54 ago on Fri 16 Aug 2019 11:03:54 AM MDT.
> quit
Leaving Shell
The downloaded packages were saved in cache until the next successful transaction.
You can remove cached packages by executing 'dnf clean packages'.

You need to restart the Apache HTTP listener for these changes to take place, which you do with the same command as shown earlier:

sudo apachectl restart

I wrote the mysqli_check.php script to verify installation of both the mysqli and pdo libraries. The full code should be put in a mysqli_check.php file in the /var/www/html directory for testing.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

<html>
<header>
<title>Static Query Object Sample</title>
<style type="text/css">
  /* HTML element styles. */
  table {background:white;border-style:solid;border-width:3px;border-color:black;border-collapse:collapse;}
  th {text-align:center;font-style:bold;background:lightgray;border:solid 1px gray;}
  td {border:solid 1px gray;}
 
  /* Class tag element styles. */
  .ID {min-width:50px;text-align:right;}
  .Label {min-width:200px;text-align:left;}
</style>
</header>
<body>
<?php
  if (!function_exists('mysqli_init') && !extension_loaded('mysqli')) {
    print 'mysqli not installed.'; }
  else {
    print 'mysqli installed.'; }
  if (!function_exists('pdo_init') && !extension_loaded('pdo')) {
    print '<p>pdo not installed.</p>'; }
  else {
    print '<p>pdo installed.</p>'; }
?>
</script>
</body>
</html>

You can test it with the following URL from the local browser:

http://localhost/mysqli_check.php

It should print the following to the web page when you’ve successfully install the mysqli and pdo libraries:

mysqli installed.
pdo installed.

If you plan to use PHP to display and render graphics, you need to install php-gd library. You can do that with the yum utility and this prior blog post explains it. Don’t forget to restart the Apache HTTP Server after you add the php-gd library.

For example, one of my sample PHP programs loads a PNG image into a BLOB column as raw binary text. Then, the program reads it and renders it with PHP to produce the following web page.

As always, I hope this helps those looking for a complete solution without cost.

Written by maclochlainn

August 16th, 2019 at 12:26 pm

Posted in Apache,LAMP,Linux,pdo,PHP,Unix

Tagged with ,

Convert JSON with PHP

without comments

Sometimes I get poorly thought or just naive questions. That is naive questions because they didn’t read the documentation or don’t understand the semantics of a given programming language. The question this time said they tried to implement what they found on a web page but their sample json_decode function example failed after they followed directions.

Surprise, it didn’t fail because they followed directions. They overlooked part of the example because they didn’t understand how to read a nested array in PHP. The actual example sets up an array of JSON objects, then print_r to read the Array, but the student tried to read it in a foreach loop. Naturally, their sample program raised an error because the base object was an Array not a String, and their target JSON object was nested inside the base Array.

I rewrote the example file to simply convert a JSON structure to an associative array, as follow:

<?php
  // Assign a JSON object to a variable.
  $someJSON = '{"name":"Joe","moniker":"Falchetto"}';
 
  // Convert the JSON to an associative array.
  $someArray = json_decode($someJSON, true);
 
  // Read the elements of the associative array.
  foreach ($someArray as $key =--> $value) {
    echo "[" . $key . "][" . $value . "]";
  }
?>

When you call the program, like this

php test.php

It displays

[name][Joe][moniker][Falchetto]

As always, I hope this helps those looking to display a JSON structure in PHP.

Written by maclochlainn

May 6th, 2019 at 8:45 pm

Posted in JSON,PHP,Uncategorized

MySQL 5.7.* and mysqli

without comments

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

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

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
> run
================================================================================================
 Package                 Arch               Version                   Repository           Size
================================================================================================
Installing:
 php-mysqlnd             x86_64             7.1.20-1.fc27             updates             246 k
Upgrading:
 php                     x86_64             7.1.20-1.fc27             updates             2.8 M
 php-cli                 x86_64             7.1.20-1.fc27             updates             4.2 M
 php-common              x86_64             7.1.20-1.fc27             updates             1.0 M
 php-fpm                 x86_64             7.1.20-1.fc27             updates             1.5 M
 php-json                x86_64             7.1.20-1.fc27             updates              73 k
 php-pdo                 x86_64             7.1.20-1.fc27             updates             138 k
 php-pgsql               x86_64             7.1.20-1.fc27             updates             135 k
 
Transaction Summary
================================================================================================
Install  1 Package
Upgrade  7 Packages
 
Total download size: 10 M
Is this ok [y/N]: y

After you type y and the return key, you should see a detailed log of the installation. Click the link below to see the yum installation log detail.

Display detailed console log →

Downloading Packages:
(1/8): php-pdo-7.1.20-1.fc27.x86_64.rpm                         214 kB/s | 138 kB     00:00    
(2/8): php-mysqlnd-7.1.20-1.fc27.x86_64.rpm                     325 kB/s | 246 kB     00:00    
(3/8): php-json-7.1.20-1.fc27.x86_64.rpm                        342 kB/s |  73 kB     00:00    
(4/8): php-pgsql-7.1.20-1.fc27.x86_64.rpm                       1.1 MB/s | 135 kB     00:00    
(5/8): php-common-7.1.20-1.fc27.x86_64.rpm                      1.0 MB/s | 1.0 MB     00:01    
(6/8): php-7.1.20-1.fc27.x86_64.rpm                             3.7 MB/s | 2.8 MB     00:00    
(7/8): php-fpm-7.1.20-1.fc27.x86_64.rpm                         1.6 MB/s | 1.5 MB     00:00    
(8/8): php-cli-7.1.20-1.fc27.x86_64.rpm                         3.7 MB/s | 4.2 MB     00:01    
------------------------------------------------------------------------------------------------
Total                                                           4.2 MB/s |  10 MB     00:02     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                        1/1 
  Running scriptlet: php-json-7.1.20-1.fc27.x86_64                                          1/1 
  Upgrading        : php-json-7.1.20-1.fc27.x86_64                                         1/15 
  Upgrading        : php-common-7.1.20-1.fc27.x86_64                                       2/15 
  Upgrading        : php-pdo-7.1.20-1.fc27.x86_64                                          3/15 
  Upgrading        : php-cli-7.1.20-1.fc27.x86_64                                          4/15 
  Upgrading        : php-7.1.20-1.fc27.x86_64                                              5/15 
  Installing       : php-mysqlnd-7.1.20-1.fc27.x86_64                                      6/15 
  Upgrading        : php-pgsql-7.1.20-1.fc27.x86_64                                        7/15 
  Upgrading        : php-fpm-7.1.20-1.fc27.x86_64                                          8/15 
  Running scriptlet: php-fpm-7.1.20-1.fc27.x86_64                                          8/15 
  Cleanup          : php-7.1.17-1.fc27.x86_64                                              9/15 
  Cleanup          : php-cli-7.1.17-1.fc27.x86_64                                         10/15 
  Running scriptlet: php-fpm-7.1.17-1.fc27.x86_64                                         11/15 
  Cleanup          : php-fpm-7.1.17-1.fc27.x86_64                                         11/15 
  Running scriptlet: php-fpm-7.1.17-1.fc27.x86_64                                         11/15 
  Cleanup          : php-pgsql-7.1.17-1.fc27.x86_64                                       12/15 
  Cleanup          : php-pdo-7.1.17-1.fc27.x86_64                                         13/15 
  Cleanup          : php-common-7.1.17-1.fc27.x86_64                                      14/15 
  Cleanup          : php-json-7.1.17-1.fc27.x86_64                                        15/15 
  Running scriptlet: php-json-7.1.17-1.fc27.x86_64                                        15/15 
Running as unit: run-ra7f965317617476a93de3931549ab242.service
Running as unit: run-r1272914e525d42798b0c3a76d4e2ba67.service
  Verifying        : php-mysqlnd-7.1.20-1.fc27.x86_64                                      1/15 
  Verifying        : php-pdo-7.1.20-1.fc27.x86_64                                          2/15 
  Verifying        : php-common-7.1.20-1.fc27.x86_64                                       3/15 
  Verifying        : php-json-7.1.20-1.fc27.x86_64                                         4/15 
  Verifying        : php-pgsql-7.1.20-1.fc27.x86_64                                        5/15 
  Verifying        : php-fpm-7.1.20-1.fc27.x86_64                                          6/15 
  Verifying        : php-cli-7.1.20-1.fc27.x86_64                                          7/15 
  Verifying        : php-7.1.20-1.fc27.x86_64                                              8/15 
  Verifying        : php-cli-7.1.17-1.fc27.x86_64                                          9/15 
  Verifying        : php-common-7.1.17-1.fc27.x86_64                                      10/15 
  Verifying        : php-fpm-7.1.17-1.fc27.x86_64                                         11/15 
  Verifying        : php-json-7.1.17-1.fc27.x86_64                                        12/15 
  Verifying        : php-pdo-7.1.17-1.fc27.x86_64                                         13/15 
  Verifying        : php-pgsql-7.1.17-1.fc27.x86_64                                       14/15 
  Verifying        : php-7.1.17-1.fc27.x86_64                                             15/15 
 
Installed:
  php-mysqlnd.x86_64 7.1.20-1.fc27                                                              
 
Upgraded:
  php.x86_64 7.1.20-1.fc27       php-cli.x86_64 7.1.20-1.fc27  php-common.x86_64 7.1.20-1.fc27
  php-fpm.x86_64 7.1.20-1.fc27   php-json.x86_64 7.1.20-1.fc27 php-pdo.x86_64 7.1.20-1.fc27   
  php-pgsql.x86_64 7.1.20-1.fc27
 
Last metadata expiration check: 2:02:29 ago on Wed 22 Aug 2018 08:05:50 PM MDT.

After you install the mysqli library, you exit the yum interactive shell with the quit command as shown:

> quit
Leaving Shell
The downloaded packages were saved in cache until the next successful transaction.
You can remove cached packages by executing 'dnf clean packages'.

You can now retest by re-running the mysqli_check.php program with the following URL:

http://localhost/mysqli_check.php

Image processing is not generally installed by default. You should use the following yum command to install the PHP Image processing library:

yum install -y php-gd

Or, you can use dnf (Dandified yum), like:

dnf install -y php-gd

Click the link below to see the yum installation log detail.

Display detailed console log →

Dependencies resolved.
================================================================================================
 Package             Arch                Version                     Repository            Size
================================================================================================
Installing:
 php-gd              x86_64              7.1.20-1.fc27               updates               89 k
 
Transaction Summary
================================================================================================
Install  1 Package
 
Total download size: 89 k
Installed size: 200 k
Is this ok [y/N]: y
Downloading Packages:
php-gd-7.1.20-1.fc27.x86_64.rpm                                  96 kB/s |  89 kB     00:00    
------------------------------------------------------------------------------------------------
Total                                                            55 kB/s |  89 kB     00:01     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                        1/1 
  Installing       : php-gd-7.1.20-1.fc27.x86_64                                            1/1 
  Verifying        : php-gd-7.1.20-1.fc27.x86_64                                            1/1 
 
Installed:
  php-gd.x86_64 7.1.20-1.fc27                                                                   
 
Complete!

If you encounter an error trying to render an image like this:

Call to undefined function imagecreatefromstring() in ...

The php-gd package is not enabled. You can verify the contents of the php-gd package with the following rpm command on Fedora or CentOS:

rpm -ql php-gd

On PHP 7.1, it should return:

/etc/php-zts.d/20-gd.ini
/etc/php.d/20-gd.ini
/usr/lib/.build-id
/usr/lib/.build-id/50
/usr/lib/.build-id/50/11f0ec947836c6b0d325084841c05255197131
/usr/lib/.build-id/b0/10bf6f48ca6c0710dcc5777c07059b2acece77
/usr/lib64/php-zts/modules/gd.so
/usr/lib64/php/modules/gd.so

Then, you might choose to follow some obsolete note from ten or more years ago to include gd.so in your /etc/php.ini file. That’s not necessary.

The most common reason for incurring this error is tied to migrating old PHP 5 code forward. Sometimes folks used logic like the following to print a Portable Network Graphics (png) file stored natively in a MySQL BLOB column:

  header('Content-Type: image/x-png');
  imagepng(imagecreatefromstring($image));

If it was stored as a Portable Network Graphics (png) file, all you needed was:

  header('Content-Type: image/x-png');
  print $image;

As always, I hope this helps those looking for a solution.

Written by maclochlainn

August 23rd, 2018 at 11:47 am

Posted in Apache,Fedora,LAMP,Linux,MySQL 5.7,PHP,PHPMyAdmin

Tagged with ,

Oracle 12c and PHP

without comments

This answers “How you connect PHP programs to an Oracle 12c multitenant database. This shows you how to connect your PHP programs to a user-defined Container Database (CDB) and Pluggable Database (PDB). It presupposes you know how to provision a PDB, and configure your Oracle listener.ora and tnsnames.ora files.

CDB Connection:

This assumes you already created a user-defined c##plsql CDB user, and granted an appropriate role or set of privileges to the user. Assuming the demonstration database Oracle TNS Service Name of orcl, you would test your connection with this script:

<?php
  // Attempt to connect to your database.
  $c = @oci_connect("video", "video", "localhost/orcl");
  if (!$c) {
    print "Sorry! The connection to the database failed. Please try again later.";
    die();
  }
  else {
    print "Congrats! You've connected to an Oracle database!";
    oci_close($c);
  }
?>

PDB Connection:

This assumes you already created a user-defined videodb PDB, and video user in the PDB, and granted an appropriate role or set of privileges to the video user. Assuming the user-defined videodb PDB uses an Oracle TNS Service Name of videodb, you would test your connection with this script:

<?php
  // Attempt to connect to your database.
  $c = @oci_connect("video", "video", "localhost/videodb");
  if (!$c) {
    print "Sorry! The connection to the database failed. Please try again later.";
    die();
  }
  else {
    print "Congrats! You've connected to an Oracle database!";
    oci_close($c);
  }
?>

Line 3 above uses the TNS Service Name from the tnsnames.ora file, which is also the SID Name from the listener.ora file after the slash that follows the localhost. That’s the only trick you should need.

You should note that because the tnsnames.ora file uses a video service name, the connection from the command line differs:

sqlplus video@video/video

Hope this helps those trying to sort it out.

Written by maclochlainn

December 10th, 2017 at 12:42 pm

Posted in Oracle,Oracle 12c,PHP

Fedora LAMP Steps

without comments

I posted earlier in the year how to configure a Fedora instance to test PHP code on a local VM. However, I’ve got a few questions on how to find those posts. Here’s a consolidation with links on those steps:

  1. Go to this blog post and install the httpd and php libraries with the yum installer.
  2. In the same blog post as step 1 (you can put the sample PHP code into the /var/www/html directory for testing), connect to the yum shell and remove the php-mysql library and then install the mysqlnd library.
  3. Go to this blog post and install the php-gd libraries, which enable you to render PNG images stored as binary streams in MySQL.

As always, I hope that helps.

Written by maclochlainn

December 9th, 2015 at 9:44 am

Posted in Apache,Fedora,LAMP,Linux,MySQL,mysqli,PHP

Tagged with , ,

LAMP php-gd Libraries

with one comment

Everything seemed complete after configuring my standalone MySQL instance to a LAMP installation, but last night I started playing with the image files. It turns out that I failed to install the php-gd library.

There’s very little feedback when you try to troubleshoot why you can’t read an image. In fact, the error message for reading the BLOB from MySQL was only available on the local Firefox browser:

The image "http://localhost/ConvertMySQLBlobToImage.php" cannot be displayed because it contains errors.

The fix requires root to install the php-gd library with the yum utility:

yum install php-gd

You’ll need to answer y to one question during the installation:

Loaded plugins: langpacks, refresh-packagekit
mysql-connectors-community                                  | 2.5 kB  00:00     
mysql-tools-community                                       | 2.5 kB  00:00     
mysql56-community                                           | 2.5 kB  00:00     
pgdg93                                                      | 3.6 kB  00:00     
updates/20/x86_64/metalink                                  |  16 kB  00:00     
Resolving Dependencies
--> Running transaction check
---> Package php-gd.x86_64 0:5.5.22-1.fc20 will be installed
--> Processing Dependency: libt1.so.5()(64bit) for package: php-gd-5.5.22-1.fc20.x86_64
--> Running transaction check
---> Package t1lib.x86_64 0:5.1.2-14.fc20 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
================================================================================
 Package         Arch            Version                 Repository        Size
================================================================================
Installing:
 php-gd          x86_64          5.5.22-1.fc20           updates           89 k
Installing for dependencies:
 t1lib           x86_64          5.1.2-14.fc20           updates          164 k
 
Transaction Summary
================================================================================
Install  1 Package (+1 Dependent package)
 
Total download size: 252 k
Installed size: 629 k
Is this ok [y/d/N]: y
Downloading packages:
(1/2): php-gd-5.5.22-1.fc20.x86_64.rpm                      |  89 kB  00:00     
(2/2): t1lib-5.1.2-14.fc20.x86_64.rpm                       | 164 kB  00:01     
--------------------------------------------------------------------------------
Total                                              157 kB/s | 252 kB  00:01     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction (shutdown inhibited)
  Installing : t1lib-5.1.2-14.fc20.x86_64                                   1/2 
  Installing : php-gd-5.5.22-1.fc20.x86_64                                  2/2 
  Verifying  : php-gd-5.5.22-1.fc20.x86_64                                  1/2 
  Verifying  : t1lib-5.1.2-14.fc20.x86_64                                   2/2 
 
Installed:
  php-gd.x86_64 0:5.5.22-1.fc20                                                 
 
Dependency Installed:
  t1lib.x86_64 0:5.1.2-14.fc20                                                  
 
Complete!

After the installation, you can run the info.php program, which contains the following:

1
2
3

<?php
  phpinfo();
?>

You’ll find the following gd library display in the result from the info.php program:

Linux_php_gd_library

After retesting, we get both large text and blob files displayed in the web page:

LAMPBlobTextPage

As always, I hope this helps others. Especially, those who are working with your LAMP stack implementation of images.

Written by maclochlainn

March 31st, 2015 at 6:20 pm

Posted in Apache,Fedora,LAMP,MySQL,mysqli,PHP

Tagged with , ,

« Older Entries