Author Archives: fullstackdev

How to create a scrollable list of items on an html page

If you have a long list of items, they can be displayed very easily using an HTML list.

Some people don’t like the way that looks, though, and they’d rather print out the list in one div after another, like this:

<div>Dashing through the snow</div>
<div>In a one-horse open sleigh</div>
<div>O'er the fields we go</div>
<div>Laughing all the way.</div>

That’s all great, but suppose your list is very long? Maybe most of your users don’t really care about the entire list, and will be satisfied to see just a few lines of it. You don’t want your users to have to scroll pages down to get past all the list items and see more content below it. It’s kind of a tricky problem, but one easy way to solve it is to follow a few simple steps:

(1) List all of your items in individual divs.
(2) Put all items into a containing div, and limit the height of that div to a fixed size.
(3) Then, style the containing div to set overflow: scroll.

The result is that your content is displayed inside a scrollable div. Click that link to see a demo page of how it works; you can take a look at the source code by right-clicking and choosing the view page source menu item. Here’s a screenshot of what it looks like:

Got comments? Send me an email at fullstackdev@fullstackoasis.com. If you found this interesting, you might want to hit the subscribe button above. I write a new post about once a week.

PHP in_array or array_search vs JavaScript includes or indexOf

In my previous post, I’d created a bug in my PHP code by improper use of PHP’s array_search function. If you only write PHP, this kind of thing will probably never happen to you. I also write Java and JavaScript code, and at some point, your memory banks overflow, and you forget stuff!

JavaScript has a couple of different methods for searching through an array to find an item. Let’s use the array method includes to search for an item in JavaScript, just like I did in my PHP example.

var foods = new Array();
foods.push('ice cream');
foods.push('hamburger');
foods.push('brussels sprouts');

var do_important_things = function() { console.log('Important!'); };

if (foods.includes('ice cream')) {
    do_important_things();
}
...

If you print out foods, you can verify that ‘ice cream’ is located at index 0. The code foods.includes('ice cream') returns true because that string is found in the array. No confusion there – a boolean is returned, not an index.

If I’m doing a more complicated comparison, I might use JavaScript’s some, like this:

if (foods.some(function(el) { return el.toLowerCase() === 'ice cream'; })) {
    do_important_things();
}

The some method loops over all elements in the array until the callback method returns true, at which point it stops, and returns true. If no item is found which satisfies the condition in the callback, then false is returned. Again, since a boolean is returned and not an index, there’s no room for error.

There’s a JavaScript method which is the equivalent of the PHP array_search function: indexOf. It returns the index of the matching item, if found, and otherwise returns -1. Here’s an example:

if (foods.indexOf('ice cream')) {
    do_important_things();
}

Oops, this method of searching exhibits the same bug as array_search in PHP! In my opinion, the indexOf JavaScript method is more appropriately named – it makes it clear that you are going to get an index as a return value. So it makes it less likely that the all-too-human developer will create the bug that I demonstrated in my previous post.

Now, when researching this topic, I found that PHP has a function that is more appropriate than array_search for my use case. It’s the in_array function. It returns TRUE if the item being sought is found in the input array. That’s really what I wanted! Here’s the example code that I used in my previous post, only now it uses in_array:

$foods = Array();
$foods[] = 'ice cream';
$foods[] = 'hamburger';
$foods[] = 'brussels sprouts';

function do_important_things() {
    echo 'Important!';
};

if (in_array('ice cream', $foods)) {
    do_important_things();
}

In this example, do_important_things is called.

Got comments? Send me an email at fullstackdev@fullstackoasis.com. If you found this interesting, you might want to hit the subscribe button above. I write a new post about once a week.

The Coding Blacklist. No 52: PHP array_search

PHP has a few “gotchas” which have bitten me in the past. I just got bitten again, today!

I used this PHP code:

$foods = Array();
$foods[] = 'ice cream';
$foods[] = 'hamburger';
$foods[] = 'brussels sprouts';

function do_important_things() {
	echo 'Important!';
};

if (array_search('ice cream', $foods)) {
	do_important_things();
}

I banged my head against the wall for a few minutes when the do_important_things function never got called. I printed out the $foods array and could see that 'ice cream' was in it. So, array_search should return true, right?

So what was going on? Go figure it out for yourself if you want…. or scroll down to get the quick answer.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

PHP’s array_search returns the key of the first value that was matched in the array. Since 'ice cream' had key zero (first item in the array), 0 was returned. In PHP, 0 evaluates to FALSE when it is used in a boolean sense. Like here. They kind of warn you about that in the array_search documentation, but who looks up the docs every time they use an array function?

I kind of went ‘Doh!’ when this happened, because I’ve had this experience once before. I’d just forgotten it. This is a case where an editor plugin might help, but I don’t know of any good solution, except to be careful when using PHP array functions.

Got comments? Send me an email at fullstackdev@fullstackoasis.com. If you found this interesting, you can hit the subscribe button above. I write a new post about once a week.

Quirks of HTML forms – can you GET and POST at the same time?

TL;DR: You can effectively GET and POST information using a single form. The trick is to set a query string in your action – the form is still POSTed, but the server will see the query string as GET data. Also, if a user comes to your web page with a query string already set, that query string will be retained when POSTing a form, as long as you use the empty action (action="") for your form.


HTML forms are a rudimentary part of web deveopment, but you can be productive implementing them without a lot of specialized knowledge. That’s a plus and a minus, since there are some “gotchas”. Here is a quick review of some things that might have escaped your notice.

an empty action attribute?

You can add a form to a page simply by enclosing some content with the <form> tag. This tag does not require any attributes. Here’s an example:

<form>
  <div><label>topping: <input></label></div>
</form>

In this example, nether the method nor the action attribute are specified. What happens in this case? Can this form be submitted? Here’s a sample page which has only this form on it so you can experiment with it. (The quick answer is yes, the form submits in Chrome, but no actual data is submitted, so the form is not useful.)

In this next example, the form also has no attributes. However, the input is named – it has a name attribute, like this:

<input name="topping" value="pepperoni">

Take a look to see if this example with a named input works to send request data to the server. (It does! You will have to type something into the form and hit enter for it to work.)

Here’s a page which submits two variables, “topping” and “crust”, using a blank form (<form>), again. Does it work? Try it! (The quick answer is “no!” – even though both inputs are named.) I only tested this in Chrome, and I don’t think the spec is clear about how to deal with this case. It does show you that it’s important to be very clear about the contents of your forms. In this case, adding a submit button fixes the problem. Here’s a demo which fixes the problem by adding the submit button.

be specific

Given the quirkiness of those forms above, usually you will want to specify the form’s method and action attributes, and also name all the inputs, like this:

<form action="topping.php" method="POST">
  <div><label>topping: <input name="topping"></label></div>
</form>

Notes about the action:

  • The action should be a valid URI; the spec doesn’t say what will happen if your URI is not valid.
  • URIs can be relative, like ../pizza.cgi or customer.php.
  • Important: the URI can include a query string, like ../pizza.cgi?topping=pepperoni.
  • If the form’s action is the empty string, then the action defaults to the URL of the document that contains the form – the URL of the page that you’re currently looking at. I couldn’t find anything in the spec that says what happens if the form does not even specify the action. I think most browsers treat this as the empty string.

Here’s a page which contains several different forms, with different actions set. You can play around with that demo to see how the different types of action attributes work.

what about that query string?

If a user comes to a web page that has a query string, and your form’s action is empty, then when you submit the form, the URL for the resulting page will contain the original query string (the address bar will show the URL with the query string). Here’s an example – this link has a query string, and the form on the page has an empty action. Notice that when you submit the form, the server sees both GET data and POST data. Try it!

So this is a quirk when using a form with an empty action attribute. It will retain the query string with which the user came to the page – this can be useful when sharing links. Some users will copy the URL in the address bar for sharing, and if that query string is important for what displays on the page, you want it to remain even after a form submission.

It’s also useful for reloading a page. If a user submits a form using a POST, and then tries to reload the page, they’ll get a question asking them if they want to re-POST the data. If the user doesn’t want to re-POST, but does want to reload the page, then they can copy the URL – including its query string – and paste it into the browser’s address bar, and hit enter to avoid the re-POST.

Got comments? Send me an email at fullstackdev@fullstackoasis.com. If you found this interesting, you can hit the subscribe button above. I write a new post about once a week.

How to Hide Warnings in PHP

Ugh! As PHP developers, we’ve all seen something like this more than we’d like:

Warning: include(included.php): failed to open stream: No such file or directory in /var/www/html/include1.php on line 6

As a general rule, you don’t want to hide warnings like this. You want to be aware of them, and fix them, before your users see them. If you want to handle warnings in another way, I’ll talk about that below. But first, let’s look at the standard, “best practices” way to deal with them.

In your development environment, you want PHP warnings to be displayed and logged. In production, you want them hidden, but logged.

In order to accomplish this, open your php.ini file in your editor of choice, and search for display_errors. Change the setting to On for development, and Off for production. Like this:

display_errors = On

or

display_errors = Off

You may also want to check that errors are being logged as desired in both environments, like this (also in php.ini):

log_errors = On

Also, make sure that your error_reporting setting is sensible. If you look in php.ini, you’ll find that the recommended settings for error_reporting are:

error_reporting = E_ALL
;   Default Value: E_ALL & ~E_NOTICE & ~E_STRICT & ~E_DEPRECATED
;   Development Value: E_ALL
;   Production Value: E_ALL & ~E_DEPRECATED & ~E_STRICT

After making changes to php.ini, usually you will need to restart Apache in order for the changes to take effect.

If you don’t see the change taking place, you should double check a few things:

  • Make sure there are no duplicate occurrences of display_errors in your ini file.
  • Look around to see if there are any other php.ini files with this setting that might be overriding the main one.
  • Check that the code is not using ini_set to override the php.ini file.

Now, sometimes a warning is expected, and you just want the warning logged, and not displayed. In that case, you can write your code to work around the warning, like so:

$display_errors = ini_get('display_errors'); // get the initial value
ini_set('display_errors', 0); // do not display errors or warnings. Yup, it turns off warnings, too.
$result = function_that_causes_a_warning(); // the warning will not be displayed because of the above line.
ini_set('display_errors', $display_errors); // reset display_errors to the initial value.

This use case is very unusual. If you find yourself needing to do this, ask yourself if it’s really the best thing to do. Normally, you don’t want to “hack” the global settings that have been applied in php.ini.

Got comments? Send me an email at fullstackdev@fullstackoasis.com. Interested in more posts like this? Hit the subscribe button above. I write a new post about once a week.

How to access an AWS RDS using JDBC in your Android app – Part II

In my last post, I described a quick way to set up an Amazon MySQL RDS (Relational Database Service).

In this post, I’m going to build an Android app which uses JDBC to search that database, and list results.

Caveat: As I mentioned in my previous post, this is a “quick and dirty” way of doing things, and it’s not recommended to do things exactly this way. However, this method is fine when you’re building a proof of concept or a demo and you need to get things done quickly. It took me an afternoon to throw together a working demo using this method!

To get started on your app, fire up Android Studio and create a “New Project” with an “Empty Activity”. Accept all the defaults, but make sure your app is for Java (unless you want to work with Kotlin).

We just want to add a few simple items for the user interface: a text input for searching on a term, a button to submit the search term, and a scrollview that can be used to display results. Let’s do that now.

When I created my empty activity, a new layout file was added called activity_main.xml. I opened that up in the design view, and added the widgets that I wanted. Eventually, I finished the layout by customizing it in the text view. Here’s the final layout:

<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity">
    <EditText
        android:id="@+id/editText"
        android:layout_width="357dp"
        android:layout_height="48dp"
        android:ems="10"
        android:hint="Enter Search term and hit button for results"
        android:inputType="text"
        app:layout_constraintTop_toTopOf="parent"
        android:layout_marginTop="8dp"
        android:layout_marginBottom="8dp"
        android:layout_marginRight="8dp"
        app:layout_constraintRight_toRightOf="parent"
        android:layout_marginLeft="8dp"
        app:layout_constraintLeft_toLeftOf="parent"
        app:layout_constraintHorizontal_bias="0.513"
        android:layout_marginStart="8dp"
        android:layout_marginEnd="8dp"/>
    <Button
        android:id="@+id/btnSearch"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Search"
        android:layout_marginRight="8dp"
        app:layout_constraintRight_toRightOf="parent"
        android:layout_marginLeft="8dp"
        app:layout_constraintLeft_toLeftOf="parent"
        app:layout_constraintHorizontal_bias="0.502"
        app:layout_constraintTop_toBottomOf="@+id/editText"
        android:layout_marginStart="8dp"
        android:layout_marginEnd="8dp"
        android:layout_marginTop="8dp"/>

    <ScrollView
        android:id="@+id/scrollview"
        android:layout_width="wrap_content"
        android:layout_height="0dp"
        android:layout_margin="8dp"
        app:layout_constraintBottom_toTopOf="@+id/textView"
        app:layout_constraintLeft_toLeftOf="parent"
        app:layout_constraintRight_toRightOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/btnSearch">

        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="match_parent"
            android:orientation="vertical">

            <TextView
                android:id="@+id/tvResults"
                android:layout_width="match_parent"
                android:layout_height="match_parent"
                android:text=""></TextView>
        </LinearLayout>
    </ScrollView>

    <TextView
        android:id="@+id/textView"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginBottom="8dp"
        android:text="Type in text, click a button to search"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintLeft_toLeftOf="parent"
        app:layout_constraintRight_toRightOf="parent" />

</androidx.constraintlayout.widget.ConstraintLayout>

It looks like a lot, but it isn’t. Android layout files are quite verbose! One comment: notice that the ScrollView has a layout height of 0dp. It took me a few minutes of searching to figure out that this was necessary. Prior to doing that, the ScrollView results overlapped the search button and instructional text.

Notice that I’ve set Android @+ids for the parts that I need to access programmatically. I need to be able to click the search Button (@+id/btnSearch), get text from the input EditText (@+id/editText), and display text in the ScrollView‘s TextView (@+id/tvResults).

Next, I opened the MainActivity class, and added the methods needed to click the button, get results, and display them – like this:

package com.fullstackoasis.myapplication;

import androidx.appcompat.app.AppCompatActivity;

import android.os.Bundle;
import android.text.Editable;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;

public class MainActivity extends AppCompatActivity implements AsyncResponse {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        Button b = (Button)this.findViewById(R.id.btnSearch);
        b.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                searchByName();
            }
        });
    }

    protected void searchByName() {
        EditText et = (EditText)findViewById(R.id.editText);
        Editable editable = et.getText();
        String s = editable.toString();
        Log.d("MainActivity", "searchByName " + s);
        if (s.length() > 2) {
            MySQLAsyncTask mySQLAsyncTask = new MySQLAsyncTask();
            mySQLAsyncTask.setDelegate(this);
            mySQLAsyncTask.execute(s);
        } else {
            displayResults("Please type in at least 3 letters, for example 'Italian'");
        }
    }

    public void processFinish(String result) {
        if (result.length() > 502) {
            Log.d("MainActivity:", "processFinish " + result.substring(0, 500));
        } else {
            Log.d("MainActivity:", "processFinish " + result);
        }
        displayResults(result);
    }

    private void displayResults(String res) {
        TextView tvResults = (TextView)findViewById(R.id.tvResults);
        tvResults.setText(res);
    }
}

Now I only needed one more crucial bit, the Java class which contacts the Amazon RDS. I added a new Java class by clicking the menu item File > New > Java Class, and chose the name MySQLAsyncTask. I had it extend AsyncTask. The source for that class is shown next. If you copy this code for your own working demo, you will have to edit the url string to use your own RDS endpoint. Also, notice the big warnings about checking in files into source control if they contain hard-coded strings that would make your credentials publicly available! I’m not going to go into how to handle that here, but just don’t do it.

package com.fullstackoasis.myapplication;

import android.os.AsyncTask;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.PreparedStatement;

final class MySQLAsyncTask extends AsyncTask<String, Void, String> {
    private static final String url = "jdbc:mysql://healthdata-1.c84gpzpanfrn.us-east-1.rds.amazonaws.com:3306/food_inspections";
    private static final String user = "MY_WONDERFUL_USER_NAME"; // WARNING! DO NOT CHECK IN YOUR CREDENTIALS INTO PUBLIC SOURCE CONTROL
    private static final String pass = "MY_WONDERFUL_PASSWORD"; // WARNING! DO NOT CHECK IN YOUR CREDENTIALS INTO PUBLIC SOURCE CONTROL
    private static String res;
    private AsyncResponse delegate = null;

    void setDelegate(AsyncResponse d) {
        delegate = d;
    }

    @Override
    protected void onPreExecute() {
        super.onPreExecute();
    }
    @Override
    protected String doInBackground(String... params) {
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            Connection con = DriverManager.getConnection(url, user, pass);
            System.out.println("Database Connection success "  + params);

            String result = "Database Connection Successful\n";
            // Let's search by dba_name and / or aka name.
            // Limit results by to top 10 results, and let user scroll

            PreparedStatement ps = con.prepareStatement("SELECT * FROM health_reports" +
                    " WHERE " +
                    "dba_name LIKE ? OR aka_name LIKE ? LIMIT 10");
            String searchPartial = params[0] + "%"; // LIKE 'Blah%'
            ps.setString(1, searchPartial);
            ps.setString(2, searchPartial);

            ResultSet rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();

            String sep = " | ";

            while (rs.next()) {
                result += rs.getInt(1) + sep + // id
                        rs.getInt(2) + sep + // inspection_id
                        rs.getString(3) + sep + // dba_name
                        rs.getString(4) + sep + // aka_name
                        rs.getInt(5) + sep + // license_num
                        rs.getString(6) + sep + // facility_type
                        rs.getString(7) + sep + // risk
                        rs.getString(8) + sep + // address
                        rs.getString(9) + sep + // city
                        rs.getString(10) + sep + // state
                        rs.getString(11) + sep; // zip
                try {
                    result += rs.getString(12).toString() + sep; // inspection_date
                } catch (Exception e) {
                    // e.printStackTrace();
                }
                result += rs.getString(13) + sep + // inspection_type
                        rs.getString(14) + sep + // results
                        rs.getString(15) + sep + // violations
                        rs.getString(16) + sep // location
                        ;
                result += System.lineSeparator();
                result += "------------";
                result += System.lineSeparator();
            }
            res = result;
            if (res.length() > 502) {
                Log.d("Task:", "Database Result success " + result.substring(0, 500));
            } else {
                Log.d("Task:", "Database Result success " + result);
            }
        } catch (Exception e) {
            e.printStackTrace();
            res = e.toString();
        }
        return res;
    }
    @Override
    protected void onPostExecute(String result) {
        Log.d("Task:", "onPostExecute");
        this.res = result;
        delegate.processFinish(result);
    }
}

This class uses the MySQL JDBC driver. You have to add the MySQL database connector as a module to your project. The instructions to do that are in StackOverflow – click that link and follow the instructions, which were pretty easy, at least with Android Studio 3.5.

For test purposes, I ran this demo in the Android Emulator. I typed in ‘Italian’ for the search term, and got back a bunch of results. It took a short while, because I never added any indexes to my database table, but that’s something to fine-tune later.

As a finishing touch, I built the Android APK, and loaded it onto my phone. Here’s a screenshot of the result:

Now, as mentioned earlier, you shouldn’t use a direct connection to the database in production code. A hacker might crack open your app, find the user name and password to your database, and do bad things! Ideally, you’ll want to connect to your database using some middleware which fields requests to the database, and makes sure that things like access permissions are enforced. That’s why this little Android app is just for demonstration purposes. The good part is that it can be built quickly, so you don’t have to waste time building middleware until you’re 100% sure you’re going to need it in a publicly available app!

Got comments? Send me an email at fullstackdev@fullstackoasis.com. Interested in more posts like this? Hit the subscribe button above. I write a new post about once a week.

How to access an AWS RDS using JDBC in your Android app – Part I

You’ve got a huge spreadsheet that has a lot of data in it, and you’ve built an Android app which works like a search engine on the data. Nice! But there’s a problem: when you build your app with all of that data in it, the APK is huge! You want to reduce the size of the app. And you also want to offload the search functionality onto a relational database, which is probably going to provide a more efficient search. How do you start?

This blog post explores one way to do it. It’s “quick and dirty”, and it’s not recommended to do things exactly this way. I’ll talk about why in Part II. But this method will give you a start.

Here’s a quick sketch of the idea: You put your data in the cloud using the Amazon Relational Database Service (RDS). Then you add JDBC calls to your app to access the cloud. It’s pretty quick. Here are the steps, using a simple example that I tried for myself.

Technical Details: My development environment runs Ubuntu 16.04, and I have a MySQL client and the MySQL database already installed on my local machine. I use Android Studio 3.5 IDE for building Android apps. Also, I have an Amazon AWS account set up already. You can follow this tutorial if you don’t have any of that, but then specific steps will differ for you.

Get Your Data Source Ready

For my data source, I downloaded some food inspection data from healthdata.gov in a csv (“comma-separated values”) format. I opened the csv file in a spreadsheet, selected some of the columns that I wanted, and exported them to another file, also in csv format. You can use the csv file that I generated by starting with this small, truncated version of the data. Later, you can use or create your own, very large data source for experiments.

Create an Amazon RDS MySQL Database

Visit the Amazon MySQL RDS page and click “Get Started”. If you don’t have an AWS account, you will need to sign up for one, first. Check out the pricing, if you are worried. There’s a free tier, great!

If you’re already signed in, another way to get started is to visit the AWS Management Console, search for “RDS”, and click the result for “Managed Relational Database Service”.

At this point, you’ll see a “Create Database” button. Choose “MySQL”, and click the “free tier”. Type in healthdata-1 for the name. Choose a username when requested. I’m using fullstackdev. Pick a secure password. The other parts of the form are straightforward. You can think about using IAM based authentication later. For this proof-of-concept piece of work, let’s keep it simple, and use password based authentication. For the rest, accept all defaults.

At this point, a page opens which says the database is being created.

AWS RDS creating database

Click the “modify” button. You’ll see that you can modify various things about the database later, if you want. Just be aware of this. For right now, you’ll need to “modify” the RDS so that it can be accessed from external sources – so choose “Public accessibility” and set it to Yes, and make sure to click the “Continue” button at the bottom of the page to save your changes. You need to do this so that you can create a database, load data into it, and access it via JDBC.

Now we’ve got an RDS in the cloud, and it’s accessible from our home environment. Next, we need to create a database.

Create Your Database and Manage Access

If you click the DB identifier in your RDS console, you will see an area called “Connectivity & security”. That area tells you what your endpoint is, and what your port is. The port defaults to 3306. Your endpoint will be something like healthdata-1.c84gpzpanfrn.us-east-1.rds.amazonaws.com. This is a URL you can use to access the database from another machine.

In the ‘Security’ pane, at the right, you will see your VPC (Virtual Private Cloud) security groups with a link to the default. Click that. It will take you to your Security Groups area. The default VPC security group should be preselected. Look at the bottom panel, where you should see the “Description”, “Inbound”, “Outbound”, and “Tags” tabs. Click “Inbound” and hit the “Edit” button. Click the “Add Rule” button, select MySQL/Aurora, make sure that the protocol is set to TCP/IP and the port to 3306, thne choose “MyIP” as the source. Your IP address will be set when doing this. Then hit the “Save” button.

Remember that you’ve added this rule just for your own IP address! You’re doing this for test purposes. Later, if you want, you can make different inbound rules, but this setup is good for a proof-of-concept.

Now the RDS is accessible. I am comfortable using the command line for MySQL client, so I used this to step into the cloud, and create my database. You can use whatever tool you want to do this.

First, I connected via this command:

mysql -u fullstackdev -P 3306 -p -h healthdata-1.c84gpzpanfrn.us-east-1.rds.amazonaws.com healthdata-1

The -p option tells the client to ask for a password interactively. I gave the password that I had set up earlier, and immediately, I was connected. This is what I saw:

Type: MySQL/Aurora,
Protocol: TCP
Port Range: 3306
Source: MyIP
Description: MySQL client

show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| innodb             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.03 sec)

It’s the usual default MySQL database setup.

I had already designed a database around the food inspection data that I had decided to import. I created my own database like this:

CREATE DATABASE food_inspections;
USE food_inspections;
DROP TABLE health_reports;
CREATE TABLE health_reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
	inspection_id INT,
	dba_name TEXT,
	aka_name TEXT,
	license_num INT,
	facility_type TEXT,
	risk TEXT, address TEXT,
	city TEXT, state TEXT,
	zip TEXT, inspection_date DATE,
	inspection_type TEXT, results TEXT,
	violations TEXT, location TEXT
);

I didn’t add any indexes for the columns other than the primary key. That can all be added later, when performance tuning.

Push Your Data to Amazon RDS MySQL Database

AWS provides instructions for pushing data to a MySQL RDS in the cloud. Since we have a new RDS which is already set up, we can skip straight to step 5, “Load the Data”.

They tell you to use the mysqlimport command, and you can do that if you want. There are other tools that can be used to import data, too. However, since I was already in the MySQL client, I used the LOAD DATA command, like so:

LOAD DATA LOCAL INFILE 'Food_Inspections_small.csv' INTO TABLE health_reports
	FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    LINES TERMINATED BY '\n' (@inspection_id, @dba_name, @aka_name,
		@license_num, @facility_type, @risk, @address, @city, @state, @zip,
		@inspection_date, @inspection_type, @results, @violations, @location)
	SET inspection_id = @inspection_id, dba_name = @dba_name, aka_name = @aka_name,
		license_num = @license_num, facility_type = @facility_type, risk = @risk,
		address = @address, city = @city, state = @state, zip = @zip,
		inspection_date = @inspection_date, inspection_type = @inspection_type,
		results = @results, violations = @violations, location = @location;

Keep in mind that you may need to modify this command for your own purposes. I had launched the MySQL client from within the same directory where my Food_Inspections_small.csv was located, , so this command worked for me straightaway.

Now, my RDS is all set up, complete with data! That is half the battle. In my next blog post, I’ll cover how to access the RDS using an Android app.

Got comments? Send me an email at fullstackdev@fullstackoasis.com. Interested in more posts like this? Hit the subscribe button above. I write a new post about once a week.

PHP exec fails with rsync

PHP has an exec function which lets you run a program. Why would you want to use a PHP program to run a different program? You might want to run a file listing using ls, or copy files using cp, for example. When using exec, you need to be thoughtful of the environment in which PHP is running. A command line script that you run from a terminal, like php test.php, runs in a different environment than a php file run by Apache in responding to a web request. This post will take a look at the difference.

Suppose we have a little PHP file called whoami.php which runs the Linux whoami command:

<?php
  $result = exec("whoami", $output, $retVar);
  echo "Output:".print_r($output, TRUE)."\n";
  echo "retVar:".print_r($retVar, TRUE)."\n";
?>

When I run this as a script from the command line, php whoami.php, I see this output:

Output:Array
(
    [0] => fullstackdev
)

retVar:0

As expected, whoami returns fullstackdev – that’s my user name on my computer.

If I put this file into my Apache root directory and run it by surfing to http://localhost/whoami.php, I see something different in my browser:

Output:Array ( [0] => www-data ) retVar:0

If you have Apache up and running, you might want to try this for yourself.

At first, seeing this output may seem strange. Why does whoami return a different value when I run this script in the browser? The answer is that it’s not “me” running the script. It’s Apache, or more accurately, the “Apache user”. When Apache was installed on my system (Ubuntu 16.04), an Apache “user” was created which is responsible for running the Apache web server process. This user is named “www-data”.

You can also see who the Apache user is by listing the Apache processes from the command line with ps -ef|grep apache (or ps -ef|grep httpd). Here’s the output on my system, which again shows the Apache user being “www-data”:

ps -ef|grep apache
..
www-data 29172 29963  0 07:35 ?        00:00:00 /usr/sbin/apache2 -k start
...

This is something to keep in mind when comparing the results of a PHP script that you run in the browser versus one you run from the command line.

Usually, when running PHP scripts as Apache, it’s not relevant that “www-data” is running that script.

But there are cases where it does matter, and the PHP exec function can be one of them. For example, if your PHP script uses exec to ssh, rsync, or touch files in your own personal .ssh directory, you will probably run into trouble. The “www-data” user does not have access to your .ssh files. That user may not even have its own home directory, where .ssh would normally be located.

Here’s one final tip. If you’re trying to run commands that use passwordless ssh from Apache, you may want to rethink what you’re trying to do. There may be other ways to do what you want: cron jobs, or watch scripts.

Got comments? Send me an email at fullstackdev@fullstackoasis.com. If you found this interesting, you can hit the subscribe button above. I write a new post about once a week.

How to fix that PHP bug using error logging

Imagine you’ve got 20 lines of PHP code. There’s an UPDATE SQL statement deep within some nested logic statements. That UPDATE is just not working, even though you expect it to. You know that because you check the database, and the values in your table are not being updated.

The code looks like this:

<?php
...
if ($blah == 0) {
    ...
    $banana = !empty($_POST['banana']) ? $_POST['banana'] : '';
    $coconut = $_POST['coconut'];
    ...
    if (!empty($banana)) {
        $res = $db->query("SELECT ... SOME SQL THING");
        ...
        if ($res->num_rows > 0) {
            // avoid duplicates
            exit();
        } else {
            $insert = $db->query("INSERT INTO .... MORE SQL");

            if ($insert && !empty($coconut)) {
                $update = $db->query("UPDATE ... ANOTHER TABLE");
                // Gnash!! The UPDATE does not take place as expected!
                ...
// etc.
?>

How do you debug something like this? Here’s my process.

First, I look at the PHP logs. If error logging is enabled, it may be that some error is being logged which points to the problem right away. In that case, I might not have to do any further debugging. If I’m lucky, the fix is quick and easy! Maybe I’ll see some SQL error, like Undefined index or foreign key constraint fails, that makes the problem clear.

But suppose error logging is disabled. Then I enable it, at least for this script.

To do that, I add error_reporting(E_ALL); to the top of the script. Then, as a good test, I also add error_log('bananas'); to the top of the script, run the script, and check to make sure that my comment, “bananas”, was logged in the expected error log file. If it wasn’t, I’ll need to hunt down the file where errors are being logged! I won’t go into that here, though. Let’s assume I’ve found my error log, and I can print statements to it.

If I don’t see any errors in the log, I add a bunch of logging statements to figure out exactly where in the code things go wrong. Look at the code above, again. There are so many possible ways that the UPDATE statement might not have run – every logic branch needs to be tested to see where execution stops. If I expect that my UPDATE statement is definitely being run, I can log a short comment just before that, run the code, and check to see if that comment appears in the logs.

If my framework already has some form of logging enabled, great, I might just use that to add debugging comments. If not, that’s okay. As mentioned above, PHP comes with built-in error logging. I might add something like error_log("before UPDATE"), run the script, and check to see if my comment appears in the error log. If I don’t see “before UPDATE” in the logs, I’d probably add a bunch of error_log statements immediately after every branch in the logic: after each if and else statement in the code above.

Usually, by adding logging statements, I can quickly figure out where the problem occurs. Where does execution stop? Then, I add more debug statements to figure out why, maybe printing out variables.

A word of caution: before adding logging, I often copy the file that I’m going to be working on to a safe place. I do that something like this: cp -p myfile.php myfile.php.2019-10-21 (the -p option to cp preserves the file’s mode, permissions, and timestamps). This is true even if I’m working on code that is checked into a version control system such as git. It’s just a really quick way to be able to revert my changes, if needed.

There are other things that I might do to figure out what’s causing this bug, too. If the source code has a test framework in place, it can be super helpful to add tests which exercise the code. That way, I can isolate the buggy code outside of the website, and test if it works on its own. Usually, when a bug is reported, I do like to add at least one test which exhibits the error by failing, and which then passes when the bug is fixed. But I don’t always have the luxury of working with source code that has a test framework set up.

Note: I’m usually working in a Linux environment, so YMMV with any commands (cp) mentioned above.

Got comments? Send me an email at fullstackdev@fullstackoasis.com. I value your feedback! If you found this interesting, you can hit the subscribe button above. I post new content about once a week.

How to build your Android app without opening the IDE

When developing Android apps, I’m almost always working in the Android Studio development environment (IDE).

I have a pretty solid dev computer, but even so, it just takes a little while for Android Studio to start up. There are times when I have to make a teeny tiny change to the source code, and it’s just a chore to open the IDE, change the code, and rebuild my app.

For example, suppose my client has requested a change to a string that’s displayed in a TextView. I’d much rather open my strings.xml file using a text editor like vi, edit the string as requested, and rebuild the app without ever touching Studio.

In fact, it’s possible to do this. Open a terminal, and go to your Android Studio project root. For me, this is in my home directory under ~/StudioProjects/. After editing the strings file (e.g. ./app/src/main/res/values/strings.xml), run ./gradlew build. You’ll see a bunch of output like this:

./gradlew build
:buildSrc:compileJava UP-TO-DATE
:buildSrc:compileGroovy UP-TO-DATE
...
:app:test UP-TO-DATE
:app:check
:app:build

BUILD SUCCESSFUL

Total time: 4.855 secs

Your new apk will be in the default location for your project. If you don’t recall where that is, you can find it quickly by running find . -name "*.apk".

Note: this demo was done using Ubuntu 16.04 OS with Android Studio 2.3. I’m not sure if it will work for Android Studio 3, but I think it will.

Got comments? Send me an email at fullstackdev@fullstackoasis.com. I value your feedback! If you found this interesting, you can hit the subscribe button above. I post new content about once a week.