MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘MySQL DBA’ tag

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 , ,

MySQL bind-address

without comments

While I try to keep things simple, sometimes eliminating options and explanations comes back to haunt me. After posting how to open a Fedora firewall port for a LAMP stack, somebody got trapped by my instructions for installing MySQL on Fedora. They got stuck because they had the following setting in their /etc/my.cnf file:

bind-address=localhost.localdomain

I’d suggested using that bind-address value for a DHCP VMware Fedora installation in Step #7. I was trying to create an example for an isolated testing instance, which is why I set the bind-address to a localhost.localdomain value. They raised the following error when they tried to connect their base operating system’s version of MySQL Workstation to the Fedora VM:

Failed to Connect to MySQL at 192.168.2.168:3306 with user student

or, this dialog image:

MySQLBindAddr01

Before you do the next step, please ensure you’re using the right IP address. You can find that by running this command as an authorized sudoer:

ifconfig | grep inet.*netmask.*broadcast

In this case, the command returns:

        inet 192.168.2.168  netmask 255.255.255.0  broadcast 192.168.2.255

I’ve since added instructions to the older post to set the bind-address value in the my.cnf file as follows when they want to support external connections (naturally that means authorizing port 3306):

bind-address=0.0.0.0

After you reset the /etc/my.cnf file, you must stop and start, or restart the mysqld service. You can do that as the root user like this:

systemctl restart mysqld

Then, you can test a student user connection from MySQL Workbench like this:

MySQLBindAddr02

If the student user is authorized and the password is correct, you’ll see that the connection now works:

MySQLBindAddr03

As always, I hope this helps those working through similar issues.

Written by maclochlainn

March 29th, 2015 at 10:30 pm

Posted in Fedora,LAMP,Linux,MySQL,MySQL Workbench

Tagged with ,

Open Fedora Port 80

with 4 comments

After installing the LAMP stack on Fedora, you need to open port 80 in the Firewall to access the PHP programs on the Fedora instance from external servers. You can open a firewall port by launching the firewall-config application as the root user with the following syntax:

firewall-config

The firewall-config utility opens the following dialog:

FedoraFirewall1

Click on the Ports tab, and you’ll see the following:

FedoraFirewall2

Skip this step if you only want to set the runtime privilege to the port. Click on the Runtime tab and change it to Permanent if you want the port to be accessible when you reboot your OS.

FedoraFirewallPermanent

Click on Add button to add a port exception, and you’ll see the following:

FedoraFirewall3

Enter Port 80 for the Apache server unless you used a different value for the Apache server’s listener port. If you’re not sure open the /etc/httpd/conf/httpd.conf file and check for the following line (default value shown):

Listen 80

Click the OK button to set the port exception. Then, you can connect to the Linux machine with the IP address, a DNS name, or a name you resolve in your local hosts file, like:

http://192.168.2.1/query.php

You can find the IP address of your Fedora image by inspecting the /etc/hosts file or capture a DHCP assigned address with the following command as the root user (or with sudo as a valid sudoer user):

ifconfig -a

It should return the following image, which is based on the data stored in MySQL’s studentdb database, as qualified in yesterday’s blog post:

ExternalWebPage

I hope this helps those setting up a LAMP instance to work with the MySQL database.

Written by maclochlainn

March 29th, 2015 at 12:35 am

Posted in Firefox,LAMP,Linux,MySQL,Oracle 11g,PHP,sql

Tagged with ,

Fedora Install LAMP

with 11 comments

My students wanted an extra credit assignment, so I thought a LAMP configuration and test would be appropriate. The only problem was I hadn’t added it to their course VMware instance. So, here are the instructions to install Apache2, PHP, and MySQLi for a complete LAMP stack when MySQL is already installed.

The post builds on my Fedora Install of MySQL and MySQL Workbench on Fedora posts from last year. It also presumes that you’ve installed a studentdb database but you need to know how to do that let me know (but it hasn’t changed much from the example at the bottom of this old MySQL 5.1 blog post).

You install Apache2 with the following command as the root user, or with the sudo command as a sudoer-list user:

yum install httpd

The following displays the results of starting the yum utility to install httpd, and you need to reply with a y to complete 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     
updates                                                     | 4.9 kB  00:00     
updates/20/x86_64/primary_db                                |  13 MB  00:04     
(1/2): updates/20/x86_64/updateinfo                         | 1.9 MB  00:02     
(2/2): updates/20/x86_64/pkgtags                            | 1.4 MB  00:01     
Resolving Dependencies
--> Running transaction check
---> Package httpd.x86_64 0:2.4.10-2.fc20 will be installed
--> Processing Dependency: httpd-tools = 2.4.10-2.fc20 for package: httpd-2.4.10-2.fc20.x86_64
--> Processing Dependency: system-logos-httpd for package: httpd-2.4.10-2.fc20.x86_64
--> Running transaction check
---> Package fedora-logos-httpd.noarch 0:21.0.1-1.fc20 will be installed
---> Package httpd-tools.x86_64 0:2.4.10-2.fc20 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
================================================================================
 Package                  Arch         Version              Repository     Size
================================================================================
Installing:
 httpd                    x86_64       2.4.10-2.fc20        updates       1.2 M
Installing for dependencies:
 fedora-logos-httpd       noarch       21.0.1-1.fc20        fedora         28 k
 httpd-tools              x86_64       2.4.10-2.fc20        updates        79 k
 
Transaction Summary
================================================================================
Install  1 Package (+2 Dependent packages)
 
Total download size: 1.3 M
Installed size: 4.0 M
Is this ok [y/d/N]: y
Downloading packages:
(1/3): fedora-logos-httpd-21.0.1-1.fc20.noarch.rpm          |  28 kB  00:00     
(2/3): httpd-2.4.10-2.fc20.x86_64.rpm                       | 1.2 MB  00:01     
(3/3): httpd-tools-2.4.10-2.fc20.x86_64.rpm                 |  79 kB  00:00     
--------------------------------------------------------------------------------
Total                                              815 kB/s | 1.3 MB  00:01     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction (shutdown inhibited)
  Installing : httpd-tools-2.4.10-2.fc20.x86_64                             1/3 
  Installing : fedora-logos-httpd-21.0.1-1.fc20.noarch                      2/3 
  Installing : httpd-2.4.10-2.fc20.x86_64                                   3/3 
  Verifying  : httpd-2.4.10-2.fc20.x86_64                                   1/3 
  Verifying  : fedora-logos-httpd-21.0.1-1.fc20.noarch                      2/3 
  Verifying  : httpd-tools-2.4.10-2.fc20.x86_64                             3/3 
 
Installed:
  httpd.x86_64 0:2.4.10-2.fc20                                                  
 
Dependency Installed:
  fedora-logos-httpd.noarch 0:21.0.1-1.fc20  httpd-tools.x86_64 0:2.4.10-2.fc20 
 
Complete!

Next, you install php as the root user with the following command:

yum install php

The following displays when you install php, and you need to reply with a y to complete the installation:

Loaded plugins: langpacks, refresh-packagekit
Resolving Dependencies
--> Running transaction check
---> Package php.x86_64 0:5.5.22-1.fc20 will be installed
--> Processing Dependency: php-common(x86-64) = 5.5.22-1.fc20 for package: php-5.5.22-1.fc20.x86_64
--> Processing Dependency: php-cli(x86-64) = 5.5.22-1.fc20 for package: php-5.5.22-1.fc20.x86_64
--> Running transaction check
---> Package php-cli.x86_64 0:5.5.22-1.fc20 will be installed
---> Package php-common.x86_64 0:5.5.22-1.fc20 will be installed
--> Processing Dependency: php-pecl-jsonc(x86-64) for package: php-common-5.5.22-1.fc20.x86_64
--> Running transaction check
---> Package php-pecl-jsonc.x86_64 0:1.3.6-1.fc20 will be installed
--> Processing Dependency: /usr/bin/pecl for package: php-pecl-jsonc-1.3.6-1.fc20.x86_64
--> Processing Dependency: /usr/bin/pecl for package: php-pecl-jsonc-1.3.6-1.fc20.x86_64
--> Running transaction check
---> Package php-pear.noarch 1:1.9.5-6.fc20 will be installed
--> Processing Dependency: php-xml for package: 1:php-pear-1.9.5-6.fc20.noarch
--> Processing Dependency: php-posix for package: 1:php-pear-1.9.5-6.fc20.noarch
--> Running transaction check
---> Package php-process.x86_64 0:5.5.22-1.fc20 will be installed
---> Package php-xml.x86_64 0:5.5.22-1.fc20 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
================================================================================
 Package               Arch          Version               Repository      Size
================================================================================
Installing:
 php                   x86_64        5.5.22-1.fc20         updates        2.6 M
Installing for dependencies:
 php-cli               x86_64        5.5.22-1.fc20         updates        3.9 M
 php-common            x86_64        5.5.22-1.fc20         updates        1.0 M
 php-pear              noarch        1:1.9.5-6.fc20        updates        343 k
 php-pecl-jsonc        x86_64        1.3.6-1.fc20          updates         34 k
 php-process           x86_64        5.5.22-1.fc20         updates         77 k
 php-xml               x86_64        5.5.22-1.fc20         updates        247 k
 
Transaction Summary
================================================================================
Install  1 Package (+6 Dependent packages)
 
Total download size: 8.2 M
Installed size: 32 M
Is this ok [y/d/N]: y
Downloading packages:
(1/7): php-5.5.22-1.fc20.x86_64.rpm                         | 2.6 MB  00:03     
(2/7): php-cli-5.5.22-1.fc20.x86_64.rpm                     | 3.9 MB  00:03     
(3/7): php-common-5.5.22-1.fc20.x86_64.rpm                  | 1.0 MB  00:00     
(4/7): php-pear-1.9.5-6.fc20.noarch.rpm                     | 343 kB  00:00     
(5/7): php-pecl-jsonc-1.3.6-1.fc20.x86_64.rpm               |  34 kB  00:00     
(6/7): php-process-5.5.22-1.fc20.x86_64.rpm                 |  77 kB  00:00     
(7/7): php-xml-5.5.22-1.fc20.x86_64.rpm                     | 247 kB  00:00     
--------------------------------------------------------------------------------
Total                                              1.1 MB/s | 8.2 MB  00:07     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction (shutdown inhibited)
  Installing : php-cli-5.5.22-1.fc20.x86_64                                 1/7 
  Installing : php-process-5.5.22-1.fc20.x86_64                             2/7 
  Installing : php-xml-5.5.22-1.fc20.x86_64                                 3/7 
  Installing : 1:php-pear-1.9.5-6.fc20.noarch                               4/7 
  Installing : php-common-5.5.22-1.fc20.x86_64                              5/7 
  Installing : php-pecl-jsonc-1.3.6-1.fc20.x86_64                           6/7 
  Installing : php-5.5.22-1.fc20.x86_64                                     7/7 
  Verifying  : php-5.5.22-1.fc20.x86_64                                     1/7 
  Verifying  : php-common-5.5.22-1.fc20.x86_64                              2/7 
  Verifying  : php-cli-5.5.22-1.fc20.x86_64                                 3/7 
  Verifying  : 1:php-pear-1.9.5-6.fc20.noarch                               4/7 
  Verifying  : php-process-5.5.22-1.fc20.x86_64                             5/7 
  Verifying  : php-xml-5.5.22-1.fc20.x86_64                                 6/7 
  Verifying  : php-pecl-jsonc-1.3.6-1.fc20.x86_64                           7/7 
 
Installed:
  php.x86_64 0:5.5.22-1.fc20                                                    
 
Dependency Installed:
  php-cli.x86_64 0:5.5.22-1.fc20        php-common.x86_64 0:5.5.22-1.fc20      
  php-pear.noarch 1:1.9.5-6.fc20        php-pecl-jsonc.x86_64 0:1.3.6-1.fc20   
  php-process.x86_64 0:5.5.22-1.fc20    php-xml.x86_64 0:5.5.22-1.fc20         
 
Complete!

After installing the software, you can set the Apache server to start automatically with the following command:

chkconfig httpd on

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      5433/httpd          
tcp6       0      0 :::8080                 :::*                    LISTEN      1505/tnslsnr

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();
?>

You can test it by opening the Firefox browser and entering the following URL from the Fedora Linux image:

http://localhost/info.php

It should display the typical diagnostic page. This verifies the configuration of the Apache and PHP servers. The next step verifies whether you have the mysqli library to connect to the MySQL database.

You create a mysqli_check.php script, like this:

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

<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.'; }
?>
</script>
</body>
</html>

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

http://localhost/mysqli_check.php

If it’s installed you can skip the next step, but if not you need to run yum in expert mode as follows (the check for php-mysql isn’t really necessary because it’s too old a version but good practice):

[root@localhost etc]# yum shell
Loaded plugins: langpacks, refresh-packagekit
> remove php-mysql
No Match for argument: php-mysql
> install php-mysqlnd
> run
--> Running transaction check
---> Package php-mysqlnd.x86_64 0:5.5.22-1.fc20 will be installed
--> Processing Dependency: php-pdo(x86-64) = 5.5.22-1.fc20 for package: php-mysqlnd-5.5.22-1.fc20.x86_64
--> Running transaction check
---> Package php-pdo.x86_64 0:5.5.22-1.fc20 will be installed
--> Finished Dependency Resolution
 
================================================================================
 Package             Arch           Version               Repository       Size
================================================================================
Installing:
 php-mysqlnd         x86_64         5.5.22-1.fc20         updates         293 k
Installing for dependencies:
 php-pdo             x86_64         5.5.22-1.fc20         updates         141 k
 
Transaction Summary
================================================================================
Install  1 Package (+1 Dependent package)
 
Total download size: 433 k
Installed size: 1.4 M
Is this ok [y/d/N]: y
Downloading packages:
(1/2): php-mysqlnd-5.5.22-1.fc20.x86_64.rpm                 | 293 kB  00:00     
(2/2): php-pdo-5.5.22-1.fc20.x86_64.rpm                     | 141 kB  00:00     
--------------------------------------------------------------------------------
Total                                              427 kB/s | 433 kB  00:01     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction (shutdown inhibited)
  Installing : php-pdo-5.5.22-1.fc20.x86_64                                 1/2 
  Installing : php-mysqlnd-5.5.22-1.fc20.x86_64                             2/2 
  Verifying  : php-pdo-5.5.22-1.fc20.x86_64                                 1/2 
  Verifying  : php-mysqlnd-5.5.22-1.fc20.x86_64                             2/2 
 
Installed:
  php-mysqlnd.x86_64 0:5.5.22-1.fc20                                            
 
Dependency Installed:
  php-pdo.x86_64 0:5.5.22-1.fc20                                                
 
Finished Transaction
> quit

You should note that this also installed PDO. One caveat, before you rerun the mysqli_check.php script from a browser, you need to restart the Apache server. You can do that as the root user with the following syntax:

apachectl restart

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

http://localhost/mysqli_check.php

At this point you should have everything installed to test your connection the MySQL database. As mentioned, this example extends my instructions for installing MySQL on the Fedora instance.

The following query.php file tests your ability to connect to the MySQL database with the mysqli driver, and it uses the studentdb and video store example from my Oracle Database 11g and MySQL 5.6 Developer Handbook:

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

<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
  // Assign credentials to connection.
  $mysqli = new mysqli("localhost", "student", "student", "studentdb");
 
  // Check for connection error and print message.
  if ($mysqli->connect_errno) {
    print $mysqli->connect_error."<br />";
    print "Connection not established ...<br />";
  }
  else {
 
    // Declare a static query.
    $query = "SELECT au.system_user_id, au.system_user_name FROM system_user au" ;
 
    // Loop through a result set until completed.  
    do {
 
      // Attempt query and exit with failure before processing.
      if (!$stmt = $mysqli->query($query)) {
 
        // Print failure to resolve query message.
        print $mysqli->error."<br />";
        print "Failed to resolve query ...<br />";
      }     
      else {
 
        // Print the opening HTML table tag.
        print '<table><tr><th class="ID">ID</th><th class="Label">User Role Name</th></tr>';
 
        // Fetch a row for processing.
        while( $row = $stmt->fetch_row() ) {
 
          // Print the opening HTML row tag.
          print "<tr>";
 
          // Loop through the row's columns.
          for ($i = 0;$i < $mysqli->field_count;$i++) {
            // Handle column one differently.
            if ($i == 0)
              print '<td class="ID">'.$row[$i]."</td>";
            else
              print '<td class="Label">'.$row[$i]."</td>";
          }
          // Print the closing HTML row tag.
          print "</tr>"; 
        }
      }
    } while( $mysqli->next_result());
 
  // Print the closing HTML table tag.
  print "</table>"; 
 
  // Release connection resource.
  $mysqli->close(); }
?>
</script>
</body>
</html>

This should display the following in the browser:

FedoraConfigMySQLPHP

You can see how to open port 80 for the Apache server in this blog post. If you want to work with blob data types, you’ll also need to use yum to install the php-gd library. You can read my LAMP php-gd library blog post to learn how to install the libraries. As always, I hope a step-by-step approach without assumptions helps those learning MySQL.

Written by maclochlainn

March 28th, 2015 at 7:41 pm

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

Tagged with ,

Lowercase Table Names

with 6 comments

A student posed the question about why table names are case sensitive. That’s because case sensitive table names are the default installation, as qualified in the MySQL documentation. You can verify that with the following query:

SELECT CASE
         WHEN @@lower_case_table_names = 1 THEN
           'Case insensitive tables'
         ELSE
           'Case sensitive tables.'
         END AS "Table Name Status";

The default value returned on Linux is:

+------------------------+
| Table Name Status      |
+------------------------+
| Case sensitive tables. |
+------------------------+
1 row in set (0.00 sec)

The default value for the lower_case_table_names value on the Windows OS is 1 not 0 because you can inadvertently create a lowercase and case sensitive table when you write an INSERT statement and use a lowercase table name. I’ve provided that detail in a reply comment to this blog post.

You can change the default by adding the following parameter in the my.cnf file on Linux or the my.ini file on Windows:

# Make all tables case insensitive.
lower_case_table_names=1

This lets you enter tables in upper or mixed case, and stores them in the data catalog as lowercase table names.

Written by maclochlainn

March 22nd, 2015 at 11:53 am

Posted in LAMP,MySQL,sql

Tagged with , ,

Querying InnoDB Tables

without comments

Somebody ran into the following error message trying to query the innodb_sys_foreign and innodb_sys_foreign_cols tables from the information_schema database:

ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege(s) for this operation

It’s easy to fix the error, except you must grant the PROCESS privilege. It’s a global privilege and it should only be granted to super users. You grant the privilege global PROCESS privilege to the student user with the following command:

GRANT PROCESS ON *.* TO student;

Then, you can run this query to resolve foreign keys to their referenced primary key column values:

SELECT   SUBSTRING_INDEX(f.id,'/',-1) AS constraint_name
,        CONCAT(SUBSTRING_INDEX(for_name,'/',-1),'.',SUBSTRING_INDEX(for_col_name,'/',-1)) AS foreign_key_column
,        CONCAT(SUBSTRING_INDEX(ref_name,'/',-1),'.',SUBSTRING_INDEX(ref_col_name,'/',-1)) AS primary_key_column
FROM     innodb_sys_foreign f INNER JOIN innodb_sys_foreign_cols fc
ON       f.id = fc.id
WHERE    SUBSTRING_INDEX(f.for_name,'/',-1) = 'system_user_lab'
ORDER BY CONCAT(SUBSTRING_INDEX(for_name,'/',-1),'.',SUBSTRING_INDEX(for_col_name,'/',-1))
,        CONCAT(SUBSTRING_INDEX(ref_name,'/',-1),'.',SUBSTRING_INDEX(ref_col_name,'/',-1));

It returns the following:

+---------------------+--------------------------------------+------------------------------------+
| constraint_name     | foreign_key_column                   | primary_key_column                 |
+---------------------+--------------------------------------+------------------------------------+
| system_user_lab_fk1 | system_user_lab.created_by           | system_user_lab.system_user_id     |
| system_user_lab_fk2 | system_user_lab.last_updated_by      | system_user_lab.system_user_id     |
| system_user_lab_fk3 | system_user_lab.system_user_group_id | common_lookup_lab.common_lookup_id |
| system_user_lab_fk4 | system_user_lab.system_user_type     | common_lookup_lab.common_lookup_id |
+---------------------+--------------------------------------+------------------------------------+
4 rows in set (0.00 sec)

However, you can get the same information without granting the global PROCESS privilege. You simply use the table_constraints and key_column_usage tables, like this:

SELECT   tc.constraint_name
,        CONCAT(kcu.table_name,'.',kcu.column_name) AS foreign_key_column
,        CONCAT(kcu.referenced_table_name,'.',kcu.referenced_column_name) AS primary_key_column
FROM     information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu
ON       tc.constraint_name = kcu.constraint_name
AND      tc.constraint_schema = kcu.constraint_schema
WHERE    tc.constraint_type = 'foreign key'
AND      tc.table_name = 'system_user_lab'
ORDER BY tc.table_name
,        kcu.column_name;

It prints the same output:

+---------------------+--------------------------------------+------------------------------------+
| constraint_name     | foreign_key_column                   | primary_key_column                 |
+---------------------+--------------------------------------+------------------------------------+
| system_user_lab_fk1 | system_user_lab.created_by           | system_user_lab.system_user_id     |
| system_user_lab_fk2 | system_user_lab.last_updated_by      | system_user_lab.system_user_id     |
| system_user_lab_fk3 | system_user_lab.system_user_group_id | common_lookup_lab.common_lookup_id |
| system_user_lab_fk4 | system_user_lab.system_user_type     | common_lookup_lab.common_lookup_id |
+---------------------+--------------------------------------+------------------------------------+
4 rows in set (0.00 sec)

Hope this helps.

Written by maclochlainn

December 27th, 2014 at 1:18 am

Posted in MySQL,sql

Tagged with , ,

MySQL Non-unique Indexes

with one comment

Somebody wanted to know how to find any non-unique indexes in information_schema of the MySQL. The query takes a session variable with the table name and returns the non-unique indexes by column names. It uses a correlated subquery to exclude the table constraints. A similar query lets you find unique indexes in MySQL. Both queries are in this post.

You set the session variable like this:

SET @sv_table_name := 'member_lab';

You can query the indexes result with the following query:

SELECT   s.table_name
,        s.index_name
,        s.seq_in_index
,        s.column_name
FROM     information_schema.statistics s
WHERE    s.table_name = @sv_table_name
AND      s.non_unique = TRUE
AND      NOT EXISTS
          (SELECT   null
           FROM     information_schema.table_constraints tc
           WHERE    s.table_name = tc.table_name
           AND      s.index_name = tc.constraint_name)
ORDER BY s.table_name
,        s.seq_in_index;

You can also reverse the logic and exclude implicit unique indexes on auto incrementing columns, like

SELECT   s.table_name
,        s.index_name
,        s.seq_in_index
,        s.column_name
FROM     information_schema.statistics s
WHERE    s.table_name = @sv_table_name
AND      s.non_unique = FALSE
AND NOT  s.index_name = 'primary' 
AND      EXISTS
          (SELECT   null
           FROM     information_schema.table_constraints tc
           WHERE    s.table_name = tc.table_name
           AND      s.index_name = tc.constraint_name)
ORDER BY s.index_name
,        s.seq_in_index;

Hope this helps those trying to find non-unique indexes for a table in MySQL.

Written by maclochlainn

December 24th, 2014 at 1:14 am

Posted in MySQL,sql

Tagged with , ,

Using MySQL Workbench

with 2 comments

I’ve been setting up a simplified lab environment to let my students learn use in class. This added content will show them how to do reverse engineering with MySQL Workbench.

It’s a complete Fedora image with MySQL and Oracle Database 11g for the course. The uncompressed image is 14GB and the compressed image is 5.3GB. I chose Fedora because it’s the smallest open source image that supports both environments, and Fedora is the closest to Red Hat and Oracle Unbreakable Linux. I’m inclined to make the instance available generally but haven’t figured out the best way to do that.

Here are the new instructions I’m adding and if you have any input leave it as a comment. 😉

You connect as the student user, which puts you in the /home/student directory. Once connected to the Fedora OS, you open a Terminal session by clicking on Activities in the upper right hand corner, and then you type terminal in the search box. When you’re in the Terminal session, use the following command to verify that the apply_mysql_lab1.sql file is correct:

cat Data/cit225/mysql/lab1/apply_mysql_lab1.sql

It should display the following commands:

\. /home/student/Data/cit225/mysql/lib/cleanup.sql
\. /home/student/Data/cit225/mysql/lib/create_mysql_store_ri.sql
\. /home/student/Data/cit225/mysql/lib/seed_mysql_store_ri.sql

You can run the apply_mysql_lab1.sql to create the tables in the studentdb database, and seed them with data. Assuming you’re in the same /home/student directory, you connect to the studentdb database with the following syntax:

mysql -ustudent -pstudent studentdb

or, more securely:

mysql -ustudent studentdb -p

Having connected to the studentdb database, you can run the following command:

\. /home/student/Data/cit225/mysql/lab1/apply_mysql_lab1.sql

It creates a Video store image and seeds it with some basic data. You can use the show command to see the tables you’ve created, like:

show tables;

It displays:

+---------------------+
| Tables_in_studentdb |
+---------------------+
| address             |
| common_lookup       |
| contact             |
| contacts            |
| current_rental      |
| item                |
| member              |
| rental              |
| rental_item         |
| street_address      |
| system_user         |
| telephone           |
+---------------------+
12 rows in set (0.00 sec)

After successfully creating and seeding the studentdb database, you can run MySQL Workbench by launching it from the search field (recommended). Alternatively, you can open it from a terminal session with the following command. Unfortunately, a command-line launch links the terminal and the MySQL Workbench processes and closing the terminal will close the MySQL Workbench.

mysql-workbench

Here are the instructions for the lab with MySQL Workbench:

FedoraMySQLWorkbenchHome

  1. The first displayed page of MySQL Workbench is the home page (click on it or any of the others to see the full size image). Click the ⊕ symbol to the right of the MySQL Workbench title.

MySQL_Lab3_01

  1. After clicking the the ⊕ symbol, it launches the Startup New Connection dialog. Enter a name for your new connection. I recommend you enter MySQLConnection.

MySQL_Lab3_02

  1. Click the Test Connection button to see if it works.

MySQL_Lab3_03

  1. When you click the Test Connection button, MySQL Workbench prompts you for a password. After entering a password and verifying the connection, click the OK button to test the connection.

MySQL_Lab3_04

  1. Click the OK button to continue.

MySQL_Lab3_05

  1. Click the gray highlighted MySQLConnection connection icon below the MySQL Connection title to launch the MySQL Workbench application.

MySQL_Lab3_03

  1. When you click the gray highlighted MySQLConnection connection icon, MySQL Workbench prompts you for a password. After entering a password and verifying the connection, click the OK button to connect to the MySQL Workbench application.

MySQL_Lab3_06

  1. The MySQL Workbench launches in the default view.

MySQL_Lab3_07

  1. Click on the Database menu option and then the Reverse Engineering… option, as shown in the illustration.

MySQL_Lab3_08

  1. This displays the Set Parameters for Connecting to a DBMS dialog. Click the Stored Connection list of values. Choose the MySQLConnection (if you used my suggestion) or the one you created from the list of values of the Stored Connection element. Click the Next button to continue.

MySQL_Lab3_09

  1. Enter the password and click the OK button to connect to the MySQL database.

MySQL_Lab3_10

  1. This is an in-progress display, it runs waiting for the password and until the step of the wizard completes.

MySQL_Lab3_11

  1. This dialog displays when the MySQL Workbench application connects to the database, retrieves a schema (database) list from the database management system, and checks the common server configuration issues. Click the Next button to move to the next step of the wizard.

MySQL_Lab3_12

  1. This Select Schemas to Reverse Engineer dialog displays any available schemas. You check the schemas that you want. Click the Next button to move to the next step of the wizard.

MySQL_Lab3_09

  1. Enter the password and click the OK button to connect to the MySQL database to retrieve objects from the database management system.

MySQL_Lab3_14

  1. This dialog displays when the MySQL Workbench application retrieves objects from the database. Click the Next button to move to the next step of the wizard.

MySQL_Lab3_15

  1. This dialog displays when the MySQL Workbench application retrieves objects from the schemata and checks the result. Click the Next button to move to the next step of the wizard.

MySQL_Lab3_15

  1. This dialog displays the tables, views, and routines to import. Click the Execute button to move to the next step of the wizard.

MySQL_Lab3_16

  1. This dialog displays the tables, views, and routines to import. Click the Execute button to move to the next step of the wizard.

MySQL_Lab3_17

  1. This dialog displays shows the reverse engineering objects and puts them in the object image. Click the Next button to move to the next step of the wizard.

MySQL_Lab3_18

  1. This dialog displays a summary of reverse engineering objects. Click the Close button to move to the next step of the wizard.

MySQL_Lab3_19

  1. The MySQL Model dialog displays after you complete the reverse engineering process. Click the EER Diagram icon or EER Diagram tab to see visual depiction of the database objects.

MySQL_Lab3_20

  1. The EER Diagram is equivalent to the EER Diagram tab. The Navigator displays the tables as blue rectangles. You can scroll through the Canvas Panel to work with the display of tables and views.

MySQL_Lab3_21

  1. The next image shows EER Diagram with the tables moved for display purposes. Sometimes there are too many relationship lines, MySQL Workbench lets you split the lines so they don’t clutter the diagram.

MySQL_Lab3_22

  1. The next image shows you the Property tab of EER Diagram. You should see that the drawSplit checkbox is checked, which suppresses the bottom-most relationship line from displaying in the EER from display on the Canvas Panel.

You can now save the MySQL Workbench file in the Lab 3 directory.

Written by maclochlainn

September 25th, 2014 at 5:10 pm

Posted in Database Design,MySQL,MySQL Workbench

Tagged with ,

MySQL Workbench on Fedora

with 7 comments

The early release of Fedora 20 disallowed installation of MySQL Workbench but the current version allows it. Almost like Tom Cruise’s Edge of Tomorrow without the drama. All you need to do is follow my earlier instructions for installing MySQL on Fedora 20. I’d check your kernel to know whether it’s supported. You can check that with this command:

<shell> uname -r

My Fedora is at the following version:

3.14.8-200.fc20.x86_64

Then, you can install MySQL Workbench with yum, like this:

<shell> sudo yum install mysql-workbench

It generates the following log file, and if you have Oracle 11g XE installed you can ignore the mime-type error:

Loaded plugins: langpacks, refresh-packagekit
Resolving Dependencies
--> Running transaction check
---> Package mysql-workbench-community.x86_64 0:6.1.7-1.fc20 will be installed
--> Processing Dependency: libzip.so.2()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Processing Dependency: libvsqlitepp.so.3()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Processing Dependency: libtinyxml.so.0()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Processing Dependency: liblua-5.1.so()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Processing Dependency: libgtkmm-2.4.so.1()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Processing Dependency: libgdkmm-2.4.so.1()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Processing Dependency: libctemplate.so.2()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Running transaction check
---> Package compat-lua-libs.x86_64 0:5.1.5-1.fc20 will be installed
---> Package ctemplate.x86_64 0:2.2-5.fc20 will be installed
---> Package gtkmm24.x86_64 0:2.24.4-2.fc20 will be installed
---> Package libzip.x86_64 0:0.11.2-1.fc20 will be installed
---> Package tinyxml.x86_64 0:2.6.2-4.fc20 will be installed
---> Package vsqlite++.x86_64 0:0.3.13-3.fc20 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
================================================================================
 Package                    Arch    Version        Repository              Size
================================================================================
Installing:
 mysql-workbench-community  x86_64  6.1.7-1.fc20   mysql-tools-community   24 M
Installing for dependencies:
 compat-lua-libs            x86_64  5.1.5-1.fc20   updates                158 k
 ctemplate                  x86_64  2.2-5.fc20     fedora                 174 k
 gtkmm24                    x86_64  2.24.4-2.fc20  fedora                 748 k
 libzip                     x86_64  0.11.2-1.fc20  updates                 59 k
 tinyxml                    x86_64  2.6.2-4.fc20   updates                 49 k
 vsqlite++                  x86_64  0.3.13-3.fc20  updates                 58 k
 
Transaction Summary
================================================================================
Install  1 Package (+6 Dependent packages)
 
Total download size: 26 M
Installed size: 119 M
Is this ok [y/d/N]: y
Downloading packages:
(1/7): compat-lua-libs-5.1.5-1.fc20.x86_64.rpm              | 158 kB  00:01     
(2/7): ctemplate-2.2-5.fc20.x86_64.rpm                      | 174 kB  00:01     
(3/7): tinyxml-2.6.2-4.fc20.x86_64.rpm                      |  49 kB  00:00     
(4/7): gtkmm24-2.24.4-2.fc20.x86_64.rpm                     | 748 kB  00:01     
(5/7): vsqlite++-0.3.13-3.fc20.x86_64.rpm                   |  58 kB  00:00     
(6/7): libzip-0.11.2-1.fc20.x86_64.rpm                      |  59 kB  00:02     
(7/7): mysql-workbench-community-6.1.7-1.fc20.x86_64.rpm    |  24 MB  00:08     
--------------------------------------------------------------------------------
Total                                              2.9 MB/s |  26 MB  00:08     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : gtkmm24-2.24.4-2.fc20.x86_64                                 1/7 
  Installing : libzip-0.11.2-1.fc20.x86_64                                  2/7 
  Installing : vsqlite++-0.3.13-3.fc20.x86_64                               3/7 
  Installing : ctemplate-2.2-5.fc20.x86_64                                  4/7 
  Installing : compat-lua-libs-5.1.5-1.fc20.x86_64                          5/7 
  Installing : tinyxml-2.6.2-4.fc20.x86_64                                  6/7 
  Installing : mysql-workbench-community-6.1.7-1.fc20.x86_64                7/7 
Error in file "/usr/share/applications/oraclexe-startdb.desktop": "Application/database" is an invalid MIME type ("Application" is an unregistered media type)
  Verifying  : tinyxml-2.6.2-4.fc20.x86_64                                  1/7 
  Verifying  : compat-lua-libs-5.1.5-1.fc20.x86_64                          2/7 
  Verifying  : ctemplate-2.2-5.fc20.x86_64                                  3/7 
  Verifying  : vsqlite++-0.3.13-3.fc20.x86_64                               4/7 
  Verifying  : mysql-workbench-community-6.1.7-1.fc20.x86_64                5/7 
  Verifying  : libzip-0.11.2-1.fc20.x86_64                                  6/7 
  Verifying  : gtkmm24-2.24.4-2.fc20.x86_64                                 7/7 
 
Installed:
  mysql-workbench-community.x86_64 0:6.1.7-1.fc20                               
 
Dependency Installed:
  compat-lua-libs.x86_64 0:5.1.5-1.fc20     ctemplate.x86_64 0:2.2-5.fc20       
  gtkmm24.x86_64 0:2.24.4-2.fc20            libzip.x86_64 0:0.11.2-1.fc20       
  tinyxml.x86_64 0:2.6.2-4.fc20             vsqlite++.x86_64 0:0.3.13-3.fc20    
 
Complete!

After successfully installing MySQL Workbench, you can launch it with the following command:

<shell> mysql-workbench

It should launch the following MySQL Workbench home page (click on it to see the full size image):

FedoraMySQLWorkbenchHome

Written by maclochlainn

September 13th, 2014 at 12:00 am

Posted in Fedora,MySQL,MySQL Workbench

Tagged with , , , ,

Common Lookup Tables 2

with 2 comments

Last October I posted an example and description of a common_lookup table. It was intended to show how common_lookup tables support drop down selections in web forms. However, it wasn’t adequate to show how they work with existing data, and the function only supported fresh queries.

This post goes to the next level, and shows how to use foreign keys to preselect values for display in web forms. It also rewrites the prior function so that it supports querying existing data and inserting new data.

Let’s start with data stored in join between two tables – the member and contact tables. The internal lookup uses the customers name from the contact table to find the membership account information in the member table.

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

SELECT   m.account_number
,        m.member_type         -- A fk to common_lookup table.
,        m.credit_card_number
,        m.credit_card_type    -- A fk to common_lookup table.
,        c.first_name
,        c.middle_name
,        c.last_name
,        c.contact_type        -- A fk to common_lookup table.
FROM     member m INNER JOIN contact c
ON       m.member_id = c.member_id
WHERE    c.first_name = 'Harry'
AND      c.middle_name = 'James'
AND      c.last_name = 'Potter'\G

It returns the results on the left, while a set of joins against the common_lookup table returns the results on the right (both use the \G in SQL Monitor to display the data vertically).

CommonLookupQueryResults01CommonLookupQueryResults01b

The member_type, credit_card_type, and contact_type columns in the data set on the left hold foreign key values. They’re copies of values found in the primary key column of the common_lookup table. You have the option of using these values to connect the data through a join or through function calls. A join requires three copies of the common_lookup table and yields the data displayed on the right above. The query to get the meaningful business information from the common_lookup table is:

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

SELECT   m.account_number
,        cl1.common_lookup_meaning
,        m.credit_card_number
,        cl2.common_lookup_meaning
,        c.first_name
,        c.middle_name
,        c.last_name
,        cl3.common_lookup_meaning
FROM     member m INNER JOIN contact c
ON       m.member_id = c.member_id INNER JOIN common_lookup cl1
ON       cl1.common_lookup_id = m.member_type INNER JOIN common_lookup cl2
ON       cl2.common_lookup_id = m.credit_card_type INNER JOIN common_lookup cl3
ON       cl3.common_lookup_id = c.contact_type
WHERE    c.first_name = 'Harry'
AND      c.middle_name = 'James'
AND      c.last_name = 'Potter'\G

The data returned from any query is symmetrical, which means all columns return the same number of rows. The results of the preceding query are the business results. Although, they’re not what you’d want to display in a web form that presents the ability to change values, like the member, credit card, or contact types. You need to get that information by using the foreign key as a parameter to a function call, and in this case three function calls. One for each of the foreign keys from the original query results. The result is an asymmetric collection of data would look like the following conceptually with Lily Luna Potter as the customer (note the green row is the symmetrical return set from the preceding query):

AsymmetricalQueryData

The f(x) represents a generic function call where the x substitutes for the foreign key value as a lookup key to the primary key value of the common_lookup table. The function in this case is a query that returns a two column list. One column holds the primary key value of the (common_lookup_id) and the other holds the customer facing description (from the common_lookup_meaning column).

These steps describe the process:

  1. Use the natural key (the user name) to find the data in the contact table.
  2. Use the member_id foreign key column in the contact table to link to the same name column primary key in the member table by joining the two rows.
  3. Use the foreign keys in the new row (combining columns from the contact and member tables) as call parameters to a PHP function that returns all possible foreign key values and their business descriptions in a web form.

The vertical choices displayed above map to OPTION tag elements of an HTML SELECT tag. The blue highlighted value contains an instruction, Select Type, in the display of an HTML OPTION tag, and it holds a null as the value of the VALUE attribute for the OPTION tag. The other displayed rows are the possible values. The green highlighted value is the currently selected value and the yellow highlighted values are alternate possibilities for an end-user to select. The logic for that process is in the PHP get_common_lookup function below.

Having discussed the structure of the data and problem, we’ll present the result in a dynamically constructed web form below. The display form shows a member account with customer information.

CommonLookupQueryResults02

You should note that the primary and foreign keys aren’t displayed because they’re irrelevant to the business process. Primary and foreign keys only serve to support relationships when we use surrogate keys as the primary key of a table. Only the meaningful information from the common_lookup table are displayed in the preceding form. Behind the web form, the primary and foreign key values are critical to maintaining anomaly free table data.

Each of the drop downs contains the full selection of possibilities from the common_lookup table, and an end-user could choose a new value by clicking on any of the drop down fields. For example, the following displays the selection of a type of credit card:

CommonLookupQueryResults03

The user can click on the drop down, and then navigate from the selection to a new selection. Assuming we don’t change anything, submitting the form should transmit the foreign key column values. The following shows that’s exactly what it does:

CommonLookupQueryResults04

As you can see from the screen shot it works easily. Below is the PHP code for a MySQL credentials file and the get_lookup function. The function lets you find an existing value or returns a set of unique values for you to choose from.

You should rename the following credentials.php file as MySQLCredentials.inc for it to work in the file below it.

Assuming you’ve implemented the credentials.php file as the MySQLCredentials.inc file, you can now implement the following file. The get_common_lookup function returns a <SELECT> tag with a list embedded of <OPTION> tags with values; one <OPTION> tag is selected when the foreign key matches a valid primary key value in the common_lookup table; and no <OPTION> tag is selected when the foreign key doesn’t match a a valid primary key value in the common_lookup table. The last possibility means a user must choose a new valid value for the foreign key column when the foreign key column is constrained as a mandatory or not null column.

The code for the web form is a bit more complex, as shown below. It contains three separate calls to the modified get_common_lookup function (on lines 104, 111, and 126). Each call to the get_common_lookup function selects the list of possible values and highlights the value associated with the foreign key value.

Here’s the web form code. You should note that it only returns a single row of data from the query by using a natural key from the contact table.

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

<html>
<header>
<title>Select Option Sample</title>
<style type="text/css">
  /* Class tag element styles. */
  .box {border:1px solid;padding:0px;width:392px;background-color:silver;}
  .bottomBox {border-left:1px solid;border-right:1px solid;border-bottom:1px solid;padding:5px;width:380px;background-color:silver;}
  .middleBox {border:1px solid;padding:5px;width:380px;background-color:silver;}
  .topBox {border-left:1px solid;border-right:1px solid;border-top:1px solid;padding:5px;width:380px;background-color:silver;}
  .button {margin:5px;background-color:lightblue;font-weight:bold;align:right;}
  .clear {clear:left;}
  .dropDown {min-width:50px;display:block;float:left;text-align:left;color:black;}
  .formDownLabel {width:90px;display:block;float:left;margin:5px;text-align:right;vertical-align:bottom;color:black;}
  .formAcrossLabel {width:80px;display:block;float:left;padding-bottom:0px;margin:5px 5px 0px;text-align:left;vertical-align:bottom;font-style:italic;font-size:90%;color:black;}
  .formInput {min-width:150px;margin:5px;text-align:left;}
  .formShortInput {width:80px;margin:5px;text-align:left;}
  .title1 {margin-left:0px;font-weight:bold;font-style:italic;font-size:125%;color:black;}
  .title2 {margin-left:5px;font-weight:bold;font-style:italic;font-size:105%;color:black;}
</style>
</header>
<body>
<?php
 
  // Include libraries.
  include_once("MySQLCredentials.inc");
  include_once("library.inc");
 
  // Define a HTML page string.
  $out = '';
 
  // Declare input variables.
  $first_name = (isset($_GET['first_name'])) ? $_GET['first_name'] : $first_name = "Harry";
  $middle_name = (isset($_GET['middle_name'])) ? $_GET['middle_name'] : $middle_name = "James";
  $last_name = (isset($_GET['last_name'])) ? $_GET['last_name'] : $last_name = "Potter";
 
  // Declare output variables.
  $member_account_number = null;
  $credit_card_number = null;
 
  // Declare lookup input and output (initialized as nulls to suppress warnings) variables.  
  $member_table = 'member';
  $member_type = 'member_type';
  $member_type_id = null;
  $credit_card_type = 'credit_card_type';
  $credit_card_type_id = null;
  $contact_table = 'contact';
  $contact_type = 'contact_type';
  $contact_type_id = null;
 
  // 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();
 
    // Declare a static query.
    $sql = "SELECT   m.account_number\n"
         . ",        m.member_type\n"
         . ",        m.credit_card_number\n"
         . ",        m.credit_card_type\n"
         . ",        c.first_name\n"
         . ",        c.middle_name\n"
         . ",        c.last_name\n"
         . ",        c.contact_type\n"
         . "FROM     member m INNER JOIN contact c\n"
         . "ON       m.member_id = c.member_id\n"
         . "WHERE    c.first_name = ?\n"
         . "AND      c.middle_name = ?\n"
         . "AND      c.last_name = ?\n";
 
    // Prepare statement.
    if ($stmt->prepare($sql)) {
      $stmt->bind_param("sss",$first_name,$middle_name,$last_name); } 
 
    // 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 {   
      // This query only returns one row, and an empty block follows the while logic.
      $stmt->bind_result($member_account_number, $member_type_id, $credit_card_number, $credit_card_type_id, $first_name, $middle_name, $last_name, $contact_type_id);
      while ($stmt->fetch()) {}
    }
  }
 
  // Print the query form.
  $out .= '<form method="post" name="myForm" action="submitItemType.php">';
  $out .= '<label class="title1">Membership Account Information</label><br />';
  $out .= '<div class="box">';
  $out .= '<div class="topBox">';
  $out .= '<label class="formDownLabel">Account #</label><input class="formInput" type="text" value="'.$member_account_number.'" />';
  $out .= '<select name="member_type" size="1" onChange="change(this.form.member_type)">';
 
  // Get dynamic membership type lookup string fragment.
  $out .= get_common_lookup($member_table, $member_type, $member_type_id);
 
  $out .= '</select><br />';
  $out .= '<label class="formDownLabel">Credit Card #</label><input class="formInput" type="text" value="'.$credit_card_number.'" />';
  $out .= '<select name="credit_card_type" size="1" onChange="change(this.form.credit_card_type)">';
 
  // Get dynamic credit card type lookup string fragment.
  $out .= get_common_lookup($member_table, $credit_card_type, $credit_card_type_id);
 
  // Print the closing HTML table tag.
  $out .= '</select><br />';
  $out .= '</div>';
  $out .= '<div class="middleBox">';
  $out .= '<label class="formDownLabel">&nbsp;</label>';
  $out .= '<label class="formAcrossLabel">First Name</label><label class="formAcrossLabel">Middle Name</label><label class="formAcrossLabel">Last Name</label><br class="clear" />';
  $out .= '<label class="formDownLabel">Customer</label><input class="formShortInput" type="text" value="'.$first_name.'" />';
  $out .= '<input class="formShortInput" type="text" value="'.$middle_name.'" />';
  $out .= '<input class="formShortInput" type="text" value="'.$last_name.'" /><br />';
  $out .= '<label class="formDownLabel">Type</label>';
  $out .= '<select style="margin:5px" name="contact_type" size="1" onChange="change(this.form.contact_type)">';
 
  // Get dynamic membership type lookup string fragment.
  $out .= get_common_lookup($contact_table, $contact_type, $contact_type_id);
 
  $out .= '</select><br />';
  $out .= '</div>';
  $out .= '<div class="bottomBox">';
  $out .= '<input class="button" style="margin-left:300px" name="submit" type="submit" value="Submit">';
  $out .= '</div>';
  $out .= '</form>';
  $out .= '</body>';
  $out .= '</html>';
 
  print $out;
?>
</body>
</html>

The submit button simply runs another web page that prints the actual values for the drop down selections. Here’s the code to print that:

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

<html>
<head>
</head>
<body>
<?php
  // Print the surrogate key values for the common_lookup_id column.
  print "MEMBER_TYPE -> [".$_POST['member_type']."]<br />";
  print "CREDIT_CARD_TYPE -> [".$_POST['credit_card_type']."]<br />";
  print "CONTACT_TYPE -> [".$_POST['contact_type']."]<br />";
?>
</body>
</html>

I hope this helps those who are interested in sorting how to implement a common_lookup table.

Written by maclochlainn

February 1st, 2013 at 1:47 am

Posted in HTML,LAMP,MySQL,Oracle,Oracle 11g,Oracle 12c,PHP

Tagged with , ,

« Older Entries
Newer Entries »