DEV Community: Ryan P

CSV Reader - Links

Ryan P — Mon, 03 Apr 2023 03:38:48 +0000

In my last two posts I covered the basics of my CSV Reader and the Filter and Map functionality. In this post, I wanted to cover the Link functionality. Links are similar to Map. The main difference is that Maps return a formatted string. Links return whatever you’d like to return from the callback method. A good example is Address data. You have a CSV file that has customer data that includes an address. Let’s say you want to isolate that address into it’s own Address object within the Customer object.

First you create the Address object

class Address
{
    …

    public static function fromCSV(stdClass $data): Address
    {
        $me = new static();

        // you'll need to do validation on each field

        $me->address = $data->Address;
        $me->city = $data->City;
        $me->state = $data->State;
        $me->zip = $data->Zip;
        $me->country = $data->Country;

        return $me;
    }
}

Then you need to create the Link and add it to the Reader.

$link = new Link(
    'objAddress',  // Field to call that will trigger this link
    [
        'Address', 'City', 'State', 'Zip', 'Country'
    ], // The fields as they appear in the CSV after removing invalid characters
    [
        Address::class, 'fromCSV'
    ], // The *optional* callable method to pass the data to
);
$reader->addLink($link);

Then when you call the reader and reference the objAddress property it will call the method Address::fromCSV and it will pass in a stdClass object with each of the fields as a public property. The callback parameter is optional and if you omit it, the return will just be that stdClass object. This can be a great option as well.

CSV Reader - Filters and Maps

Ryan P — Fri, 28 Oct 2022 19:00:21 +0000

Intro

In my previous post, CSV Reader, I discussed the CSV Reader I created. In this post, I want to explain how the Filters and Maps work within the object.

Filters

Filters are a way that you filter content before ingesting it. After creating your object you have to add the filter.

$filter = new Filter(‘Phone’, [Customer:class, ‘sanitizePhone’]);
$csv->addFilter($filter);

The first parameter is the field that triggers the call. So in this case, when retrieving the Phone with $csv->Phone, it will call Customer::sanitizePhone(). It will automatically pass in the value from the file to that method. So then you will need to create the method for it to call.

/**
 * Method to sanitize the phone number from a CSV file
 * @param string $val
 * @return string
 */
public static function sanitizePhone(string $val): string
{
    return preg_replace("/[^0-9\+]/", ‘’, $val);
}

Maps

Maps are another really powerful feature. Maps allow you to combine multiple fields and concatenate them together into one formatted string. One easy example is an address or a name.

$map = new Map(‘Address’, “%0\n%1, %2 %3”, [‘add’, ‘city’, ‘state’, ‘zip’]);
$csv->addMap($map);

In this case, when reading $csv->Address it would return with a formatted string similar to

123 Main St
Anytown, ST  12345

CSV Reader Library

Ryan P — Fri, 22 Jan 2021 03:21:50 +0000

I absolutely despise parsing CSV files because you have to know the column indexes for all the fields you want to read, but they could be in a different order for a different file. This makes it to where you have to sanity check the file first and it's going to be different for every file you want to parse. I did kinda find a way around it.

$header = array_flip(fgetcsv($fh));

This will create an array where the header title is an key to the column index

Array(
    'column1' => 0,
    'column2' => 1,
    'column3' => 2,
    ...
)

This is helpful, but you still have to put something like...

while($data = fgetcsv($fh)) {
    $column1 = $data[$header['column1']];
...
}

That is nice, but not very simple and a pain to write every time you need to parse a file. So I did what any natural dev would do and wrote my own library that will do it all for me.

composer require godsgood33/csv-reader

Then all you have to do is create a reader object and start parsing.

So given the following file:

ID | Name   | Phone Number | Email            | State
1  | Ryan   | 123-456-7890 | ryan@example.com | IN
2  | John   | 234-567-8901 | john@example.com | GA

$csv = new Reader($file);
do
{
    $name = $csv->Name;
    $phone = $csv->PhoneNumber;
    $email = $csv->Email;
} while($csv->next());

The library removes any non-alphanumeric characters [A-Z, a-z, 0-9, _] from the header titles so that they are valid properties (so the space is removed from "Phone Number").

There are a couple different options you can pass in as an array when you create the object.

[
    'delimiter' => ',' // character that splits the fields
    'enclosure' => '"' // character the encloses a field should it contain the delimiter
    'header' => 0 // default 0-based index for what row the header is on
    'required_headers' => [] // array of headers (post sanitizing) of fields you want to require are present in the file before any parsing can take place
]

So if you wanted to require the ID, Name, and Email fields your object instantiation would look like:

$csv = new Reader($file, [
   'required_headers' => [
      'ID','Name','Email'
   ]
]);

Other fields would still be available for use, but are optional fields, so you'd want to validate them before using them. If a column header is not present, it simply returns NULL.

This library can also be used for remote files, but currently only supports http(s). However, it would not be recommended for large remote files.

godsgood33 / csv-reader

This library is to create a simpler way to parse CSV files.

CSV Reader

Intro

The purpose of this library is to simplify reading and parsing CSV files. I have parsed CSV files so many times and there is not really an easy way to do it. You have to know the index of the column you want to insert and it would be so much more readable if you could just use the header title itself as the field index. A while ago, I started reading the header row, flipping the array so that the index of the row elements is now the value of the field index.

$header = array_flip(fgetcsv($fh));
/*
 array(
     'column1' => 0,
     'column2' => 1,
     'column3' => 2,
     ...
 )
 */

This allows you the ability to use the header title as an index into the data array.

foreach($data = fgetcsv($fh)) {
  //

…

View on GitHub

PHP MySQL Library Pt3

Ryan P — Mon, 15 Jul 2019 04:08:45 +0000

In part 1 of this series, I talked about this Php_Db library that I created. In part 2 I showed a good number of the function and queries that you can create using it. In this part, I'm going to talk about the DDL queries that you can create with Php_Db

drop

returns mysqli_result

The drop function will create a drop statement that allows you to drop a table or view from your schema. By default it will pull drop a table

$this->drop('users')

DROP TABLE users

$this->drop('user_view', 'view')

DROP VIEW user_view

There is also a third parameter that allows you to specify that the table is a temporary table.

$this->drop('tmp_user', 'table', true)

DROP TEMPORARY TABLE tmp_user

truncate

returns mysqli_result

The truncate function allows you to create a truncate table statement to delete all records from a table

$this->truncate('users')

TRUNCATE TABLE users

createTable

returns mysqli_result

The createTable function allows you to create a table or temporary table either using an array or select statement (this can also use an existing saved select statement). The second parameter is weather this table will be a temporary table. The third parameter defines what fields will be created in this table.

$this->createTable('new_users', false, [
    [
        'field' => 'id',
        'datatype' => 'int(11)',
        'option' => 'AUTO_INCREMENT PRIMARY KEY'
    ],
    [
        'field' => 'name',
        'datatype' => 'varchar(255)',
        'default' => null
    ],
    [
        'field' => 'email',
        'datatype' => 'varchar(64)',
        'default' => null
    ]
]);

CREATE TABLE IF NOT EXISTS new_users (
id int(11) AUTO_INCREMENT PRIMARY KEY,
name varchar(255),
email varchar(255)
);

As I said above, you can also use a select query.

$this->createTable('new_users', false, "SELECT * FROM users");

CREATE TABLE IF NOT EXISTS new_users AS (SELECT * FROM users);

createTableJson

createTableJson allows you to pass a pre-formatted JSON string which will make the create table string. There is an example of the json file format in /vendor/godsgood33/php-db/examples/

alterTable

returns mysqli_result

The alterTable function allows you to create a statement to alter a database table by adding, dropping, or modifying a column. alterTable does this by having 3 parameters, the table to edit, the action to take (ADD_COLUMN, DROP_COLUMN, MODIFY_COLUMN, & ADD_CONSTRAINT), and the parameters. The actions are constants in the Database class.

ADD_COLUMN:

$phoneColumn = new stdClass();
$phoneColumn->name = 'phone';
$phoneColumn->dataType = 'varchar(20)';
$phoneColumn->default = null;

$this->alterTable('users', \Godsgood33\Php_Db\Database::ADD_COLUMN, [
    $phoneColumn
]);

ALTER TABLE users ADD COLUMN phone varchar(20) DEFAULT NULL

DROP_COLUMN:

$phoneColumn = new stdClass();
$phoneColumn->name = 'phone';

$this->alterTable('users', \Godsgood33\Php_Db\Database::DROP_COLUMN, [
    $phoneColumn
]);

ALTER TABLE users DROP COLUMN phone

MODIFY_COLUMN:

$emailColumn = new stdClass();
$emailColumn->name = 'email';
$emailColumn->new_name = 'user_email';
$emailColumn->dataType = 'varchar(255)';
$emailColumn->default = null;

$this->alterTable('users', \Godsgood33\Php_Db\Database::MODIFY_COLUMN, [
    $emailColumn
]);

ALTER TABLE users MODIFY COLUMN email user_email varchar(255) DEFAULT NULL

ADD_CONSTRAINT:

setVar

NULL return

Allows you to set a SQL variable

$this->setVar('foo', 'bar');

tableExists

returns boolean

Check to see if a table exists

$this->tableExists('schema', 'table');

fieldExists

PHP MySQLi Library Pt2

Ryan P — Mon, 15 Jul 2019 04:08:21 +0000

In my previous post Part 1, I talked about the basics of the PHP MySQL library that I wrote. In this post, I'm going to talk about the different queries you can run. I am going to assume that you are operating within your own database class that you extended with Php_Db.

A couple notes. By default, function calls will return the SQL that was built from the parameters that were passed in. If you change the $autorun static variable to true, it will automatically execute any calls and return the result of the query. So you would want to store the return in a variable or check it against an if() block. If $autorun remains false it returns the string SQL, but it also stores it (until another function is called). If you want to run it, call $this->execute().

select

Returns

`stdClass` or `array:stdClass`

Definition

select($strTableName, $fieldList = [], $whereClauses = [], $flags = [])

So a select statement obviously is what most people do. Just like the previous post showed, you need one parameter, with 3 more being optional. So a call to $this->select("users"); will pull ALL fields and ALL records from the table users. If you only want to pull the id and name fields for example, you would run $this->select("users", ['id', 'name']); Again, this would return the id and name fields for ALL records. So now, the real power! You want to pull a specific list of all users with the name containing 'George'. That would look like...

$where = new DBWhere('name', '%George%', DBWhere::LIKE);
// You have to set escape equal to false so that it doesn't escape the wildcards
$where->escape = false;

$this->select("users", ['id', 'name'], $where);

SELECT 'id', 'name' FROM users WHERE name LIKE '%George%'

Again, this can be expanded or refined further with the 'flags' parameter.

selectCount

Definition

selectCount($strTableName, $whereClauses, $flags)

Returns

number

A selectCount query will just return the number of rows that satisfy the WHERE clause. The big different is that there is no 'field list' in this call. To return the number of all users whose names contain the string 'George' you would call...

$where = new DBWhere('name', '%George%', DBWhere::LIKE);
$where->escape = false;

$this->selectCount("users", $where);

SELECT COUNT(1) FROM users WHERE name LIKE '%George%'

insert

Definition

insert($strTableName, $params, $blnIgnore = false)

Returns

number of affected rows

The insert query will create a single insert statement that allows you to insert a single row to a table.

$this->insert("users", [
    'id' => 1,
    'name' => 'George Jetson',
    'email' => 'george.jetson@spacelysprockets.com'
]);

INSERT INTO users ('id', 'name', 'email') VALUES ('1', 'George Jetson', 'george.jetson@spacelysprockets.com')

This also stores the insert id from the previous query, so you can retrieve it by accessing $this->_insertId Optionally, you can also specify a third boolean parameter if you want to add an "IGNORE" clause to the query

INSERT IGNORE INTO...

extendedInsert

Definition

extendedInsert($strTableName, $arrFields, $params, $blnToIgnore = false)

Returns

number of affected rows

This query will create a multi-insert statement that allows you to insert multiple rows at a time. This query is a little different in that it requires the addition of the field list for the second parameter and an array of arrays with the list of items to put in each field. The order must be the same!

$this->extendedInsert("users", ['id', 'name', 'email'], [
    ['1', 'George Jetson', 'george.jetson@spacelysprockets.com'],
    ['2', 'Fred Flintsone', null]
], true);

INSERT IGNORE INTO users ('id', 'name', 'email') VALUES ('1', 'George Jetson', 'george.jetson@spacelysprockets.com'), ('2', 'Fred Flintstone', NULL)

NOTE: As you see if null is the value of a field in most cases it will translate to NULL in MySQL.

update

Definition

update($strTableName, $params, $whereClauses = [], $flags = [])

Returns

number of affected rows

This query will create an update query. This is very similar to an insert query, but the 3rd parameter is a where clause. You can also use the flags to effect change in joined tables.

$this->update("users", ['email' => null], new DBWhere('id', 1));

UPDATE users SET email = NULL WHERE id = 1

extendedUpdate

Definition

extendedUpdate($strTableToUpdate, $strOriginalTable, $strLinkField, $fieldsToUpdate)

Returns

number of affected rows

The extended update query creates an update query that updates one table using another. So the best option is to create a TEMPORARY table that you insert updated data in. Then using this query to update permanent table data with the data from the temporary table.

The first parameter is the table to update. The second parameter is the source table for the update. The third parameter is the field that is common between the two table and used as the to decide what records to update. The fourth parameter can either be a string or array and is a list or particular field you want updated. So in this case only the name and email fields will be updated.

$this->extendedUpdate('users', 'tmp_users', 'id', ['name', 'email']);

UPDATE users tbu INNER JOIN tmp_users o USING ('id') SET tbu.name = o.name, tbu.email = o.email

replace

Definition

replace($strTableName, $params)

Returns

number of affected rows

The replace function is built exactly like the insert function, except as a replace function which runs a delete statement first, then an insert

extendedReplace

Definition

extendedReplace($strTableName, $fieldList, $param)

Returns

number of affected rows

The extendedReplace function is built exactly like the extendedInsert function

delete

returns number of affected rows

The delete function creates a delete query to remove records from the table. The second parameter is an array list of fields you want to remove. This is provided so that you can remove rows from a specific table and not a joined table

// true and false will automatically be converted to '1' and '0', respectfully
$where = new DBWhere('um.active', false);
// the backticks parameter must be set to false to not surround the field with backticks
// e.g. `um.active`, which is not valid
$where->backticks = false;

$this->delete('users u', ['u.*'], $where, [
    'joins' => [
        "JOIN user_meta um ON um.user_id = u.id"
   ]
]);

DELETE u.* FROM users u JOIN user_meta um ON um.user_id = u.id WHERE um.active = '0'

This covers most of the main queries that Php_Db can run. There are a few other DDL queries that it can also build, but I will go into those in the next part.

PHP MySQLi Library Pt1

Ryan P — Tue, 02 Jul 2019 14:33:17 +0000

I really love the way that the WordPress WPDB class works.

$wpdb->insert('table', ['field' => 'value']);

Done!

It does the statement creation and automatic escaping 'problematic' characters. But there's not an easy way to add it to your project. There are other variables like "posts", that contains the table name for the posts table, that are unnecessary for most. So I decided to create Php_Db that does most all the same things. I thought about using the library ezSQL that WPDB is based on, but it didn't include some very fundamental things that I wanted an extended insert, replace, and update functionality, along with some DDL capabilities. There were other things, but I saw a big need for those.
I'm going to take a couple days to talk about how Php_Db works because I put a lot of functionality in it. For full documentation you can look at my Docs or Source

So installation...
composer require godsgood33/php_db

Now you need to run the setup to generate some defined variables. Navigate to {project_dir}/vendor/godsgood33/php_db. Now if you would like to encrypt your DB password that Php_Db uses, run php bin/setup.php --algorithms to display the list of all your systems supported OpenSSL algorithms. Once you decide on one, rerun php bin/setup.php -a={algorithm} If you don't want to encrypt the password you can just run php bin/setup.php for the same questions.

It will then ask you a couple questions, MySQL server name/IP, user name, password, and schema. Then it prints out a list of constants you need to add to your project. Once you include those you can start using Php_Db. The best place to put them is in your bootstrap or index file. Just as long as it is done prior to instantiating your db class.

If you ever change your username or password, or you want to change your encryption algorithm, you can just rerun setup.php to get the new values, update your constants, and you're good. WARNING If you use Php_Db to encrypt any data you insert into your database you are going to have to decrypt it first, then change your algorithm, then you can re-encrypt it. Which all you have to do is call the encrypt / decrypt methods at the appropriate times.

I recommend using Php_Db to extended your database class
class db extends \Godsgood33\Php_Db\Database {}

So now, some basic functionality.

The field list, WHERE clauses, and "flags" all operate the same independent of which query you are running (select, insert, delete...)

Just like WPDB the first parameter in a query is the table name. This is just a string that you can put single or multiple tables and/or add aliases if you like. It just needs to look like it would if you were writing the SQL query itself.
"table" OR "table1 t1, table2 t2"

The next parameter in is the field list. This is simply an array of fields you want to ingest in your results. Again, just like the SQL query.
['id', 'name'] OR ['t1.id', 't2.name'] NOTE: There is no field list parameter in the selectCount method.

The third parameter is the WHERE clause list. This is an array of \Godsgood33\Php_Db\DBWhere objects.

DEFINITION:

$where = new DBWhere($field = null, $value = null, $operator = '=')

So let's say you wanted to find a user with a specific ID

$where = new DBWhere('id', 1234);
$this->select('users', null, $where);

This query would look like SELECT * FROM users WHERE id = '1234'. So what do you do when you want to provide multiple where clauses?

$where1 = new DBWhere('fname', 'Fred');
$where2 = new DBWhere('lname', 'Flintstone');
$this->select('users', null, [$where1, $where2]);

SELECT * FROM users WHERE fname = 'Fred' AND lname = 'Flintstone'

By default, DBWhere will use a 'AND' operator between the clauses. You can use any other SQL supported operator by updating the DBWhere::$sqlOperator property. So that could turn into...

$where1 = new DBWhere('lname', 'Flintstone');
$where2 = new DBWhere('lname', 'Jetson');
$where2->sqlOperator = 'OR';
$this->select('users', null, [$where1, $where2]);

SELECT * FROM users WHERE lname = 'Flintstone' OR lname = 'Jetson'

If you want to look for multiple values and use an IN operator you can call

$where = new DBWhere('id', [1,2,3], DBWhere::IN)
$this->select('users', null, $where);

SELECT * FROM users WHERE id IN ('1','2','3')

You can also use NOT_IN, BETWEEN, LIKE, NOT_LIKE, IS, or IS_NOT.
You can also use normal operators =, !=, >, <, <=, >=. If 'operator' parameter is not specified it uses '=' as the default operator.

DBWhere also does automatic conversion and escaping for the following:

null = NULL
DateTime = 'Y-m-d H:i:s'
boolean = '0' or '1'
array = each element is escaped
objects = if the object has a callable '_escape' method, it will call that and return the result

Lastly, there are flags, join, having, order, group, and limit.

join = The join key is an array of join strings
having = The having key is an array of DBWhere object just like the WHERE clauses
order = Can either be a string or an array list of ordering clauses
group = Can be a string or array just like order
limit = A string with the number you want to limit (can include the starting row)

So a potential flags array could look like:

[
    'joins' => [
        'JOIN table2 t2 ON t2.id = t1.id',
        'JOIN table3 t3 ON t3.id = t2.id'
    ],
    'having' => [
        new DBWhere('age', 18, '>=')
    ],
    'order' => 't1.name DESC',
    'group' => 't1.id',
    'limit' => '10'
]

Again, these are the main parameters that are allowed for most of the query types. I am going to go in more detail about the different queries themselves in the next post. And as always there is more detail in the full docs

Mobile streaming with a Raspberry Pi 3

Ryan P — Mon, 24 Jun 2019 01:58:56 +0000

I have 8 children (soon to be 9). Yes, I know what causes it (and I'm good at it)! I used to have a Ford E-350 that had an overhead DVD player. So when we would go on family trips we would just take an assortment of DVD's to play on it. That van is no longer with us, so I wanted another solution that didn't involve having to take a bunch of DVD's with us.

Then I had a great idea! I had a PlexPass membership and a couple extra Raspberry Pi's (model 3). A quick Google search later and I found RaspAP-WebGUI and I was off to the races!

After much consternation though, I determined there was a compatibility issue between Plex and RaspAP. I ended up putting it away while I thought about what else I could do.

After a couple of months, I realized all I needed was a DLNA server. So another Google search later and I found these instructions for MiniDLNA

Armed with all this new knowledge I set off, again.

Install Raspbian
Install RaspAP-WebGUI per README
Install MiniDLNA per link
Optimize and copy media
Install VLC and Enjoy!!

The first three are pretty self-explanatory, a couple notes though. When the Pi is booting the MiniDLNA service starts BEFORE my external media is mounted. This leads to it not finding the media. I changed /etc/init.d/minidlna and added a sleep 10 near the top of the script to give the Pi some time to mount the drive, before the service started, then once it did start it would find the media on the drive. If you have problems you can just restart the minidlna service (so make sure you setup SSH Server on the Pi and have an SSH client on your device ie. JuiceSSH). #2 I also had problems with it finding the media because of the permissions required. You may need to tweak the permissions that the MiniDLNA service runs with (I changed the user to root, I know, not secure, but it worked).

So with the 4th step, I used my Plex server to optimize a selection of MY LEGALLY OBTAINED movies (Optimize for Mobile) for my kids and then copied them to an NVMe SSD in an external USB-C enclosure. This helps a couple of different ways. 1. It makes it easier on the Pi so it doesn't have to transcode. 2. It shrinks the files so you can fit more on it.

For the last step, you'll connect your mobile devices to the new RaspAP network that you setup. Then using VLC (search your app store), you can go to "Stream" and enter upnp://http://10.3.141.1:8200 (provided you kept the defaults for RaspAP. You will see a couple files go past as it's reading the external media, but then you can select the "Playlist" from the top right of the screen and select any file on the external drive. On the iPad, you can select "Network Stream" and it will find the UPNP MiniDLNA server and then you can just navigate through that for your list.

In my testing, I was able to get 6 Kindles, 2 phones, and 1 iPad, all streaming at the same time! No drops, no hick-ups, perfect streaming! I did have another iPad I tried to connect, but it wasn't reliable. So it seemed that 9 is the upper capacity for the WiFi. It might do more if I purchased an AC router and direct connected the Pi to that.

Comment if you'd like me to do a video on how to set it up!