PHP, PDO, and dealing with transactions properly with mysql

Although PHP.net can be a useful documentation site to know what the functions are supposed to do, not always are there the comments that tell you how to use them. And I’m talking about proper implementation: one of the most difficult parts of programming, in my opinion. There are always better ways to implement something, if not over time when new methods come along.

With PDO, you can use mysql(for, example) in an object oriented manor. This gives better structure and more flexibility and security to your queries. However, it may be complicated at first to understand how it should be used. Sure, you can research the functions and what they do, but implementation isn’t always easy without some research on stack overflow or otherwise. But as a rule of thumb, we should be using parameterized queries where possible.

There are different scenarios of which to use transactions, error handling, and if-blocks.

In the following example, we can use an if-block to see if the statement has produced a successful result:

$stmt = $db->prepare("select * from users where id=:id");
$stmt->bindValue(":id",$the_user_id,PDO::PARAM_INT);
if ($stmt->execute())
{
// success
}

This does its job and with success we can begin to pull results from the database. However, there could be errors within the prepare statement all the way to the execute statement that are not handled and will be displayed as-is.

Here is an example of catching a PDO error. This time I used question mark placeholders instead of named placeholders like last time:

try
{
$stmt = $db->prepare("select * from users where id=?");
$stmt->bindValue(1,$the_user_id,PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
// Do something with result set
}
catch (PDOException $e)
{
var_dump($e);
}

Please note that selecting * and dumping the exception should probably only be used when trying this out. Now if we have any errors, such as a null user id number on a non-nullable column or an error with the query, it can be caught and processed with a specific error method. I like to have a method where I can choose to log the error to the database or simply report the error back with a consistent structure. Very useful in production where you would rather not have the users getting raw php or mysql errors sent back to them. They don’t know what to do with that information!

Finally, sometimes we want to do a few queries at once but do not want any errors happening halfway through the process, resulting in corrupted data. Transactions help keep the data consistent, and should be used where all of the queries in the transaction must all succeed or else all fail. Also note that error catching can be tricky, as putting the try-catch block around all of the statements can catch on any of the queries. Lets see how this works:

$db->beginTransaction();
try
{
$stmt = $db->prepare("UPDATE users SET value=1");
$stmt->execute();
$stmt = $db->prepare("INSERT INTO users (value,value2) VALUES(0,0)");
$stmt->execute();
$db->commit();
}
catch (PDOException $e)
{
$db->rollback();
var_dump($e);
}

Notice that each query inside of the try block could trigger the exception, and if it is triggered, the transaction will be rolled back instead of reaching the commit. Again, this is a crude use-case scenario but the implementation is the focus. With PDO, we can create the transaction and catch any errors and rollback all at once.

Now it is likely that a production project will be using some sort of framework to develop the application, which makes sense. Instead of the extra execute() and bindValue() or bindParam() functions over and over again, we can pipeline objects and values to functions within a framework to handle the process for us, and then pipe any errors back. It is still helpful to know these underlining procedures that run the features of popular frameworks.

As always, if there is anything to note, please leave a comment with any improvements on the code or procedures written here. This was written with regards to PHP 5.6, and PHP 7 Alpha has now been released.

A Programming Pickle Overcome by Branching

While working on a feature that will be present in several of my projects, I noticed that finishing only part of the component is necessary but will leave other portions unfinished that will have to be continued later.

This leaves a few options: Simply code what is necessary for the project outside of the component, code the component until what is necessary and ‘branch’ it from the main component, or build the component entirely.

I feel that the branching method is best to have results for immediate use, while it can still be picked up on later when more features are needed. However, to do this, there will need to be more effort put into the planning stages for the component and its entirety.

The planning will need to be split up into 3 categories now: One for the preliminary functionality of the project, another for the long term functionality of the component that will be used in it, and finally, planning for what functionality is needed at the stage of development it is being used in the project.

This ‘branch’ of the component should reduce any errors of not having required pieces of it missing for the project, and not having to re-work from the point that it was left off at to branch.

il_570xN.425180131_d6zs

Using ID’s instead of Digital Wallets?!

mclovin_id_01Digital wallets was a hot topic back when phones were exploding with new ways to transfer and communicate information between other devices we are around all the time. This includes our TV sets, computers, bluetooth car stereos, or even the cars themselves. But one thing that has not been exploding with popularity is digital wallets. Why is this?

Well money is a very important aspect of your data. Online banking and purchases need to be the most secure as possible in order for people to trust them. The fact is, is that data can be spoofed with false information. And if false informatin is given, someone else could make purchases in your name. This is very bad.

But when you think about the point of using a phone to manage payments, the point is to reduce time fumbling through giving information to the checkout and to keep less plastic with you that can get lost or stolen. So is there a solution?

Well one item that you probably need to always have on you is your Identification. This photo ID can be used to legally operate a vehicle, verify a credit card purchase, buy items with age requirements, or even go outside of the country. Yes, that information should be secure because the information is used by security at border control to review your private information, but maybe it could have a second source of information that is not as secure that can easily identify you as you?

Think about it: If you want to buy something from a shop. You may or may not need your ID. But if you could use your ID to create a “profile/account” with this store, it would be convenient to have the information digitally accessible right?

What if this process exists:

1) Touch card to the card reading sensor.

2) The teller will see your picture on-file to verify that it is, in fact, you. But they can also take a look at the card to make sure your picture matches as a secondary verification.

3) Put in a PIN number to verify large purchases. A PIN number would not be stored on the ID itself, so it cannot be spoofed.

4) Choose which bank you would like to use for your purchase.

Viola! You can now easily make an account with a store, and easily buy things from there in the future! Maybe combine this with facial recorgnition software, and you have the ultimate checkout system!

It solves the biometric problem of being inconsistent with fingerprints. It solves the problem of being able to spoof data. And it prevents a fat wallet full of cash and credit cards that you carry everywhere. Nice.

Now there just needs to be a centralized system for keeping your receipts… I throw most of them away anyway.

Screen-shot-2011-07-18-at-3.52.06-PM-657x245

Mark’s Observations on an Appealing Pricing Model

ImageWarning, this may be a biased post baised on my own shopping and preferences, but I seem to judge prices differently baised simply on what number order the prices are in.

I’d like to give a more formal hypothesis to this assumption with a theory that I have developed to make pricing more appealing to customers whether or not the price is too expensive for what you are selling. (No, it may not influence themto buy something way too overpriced, but may make it seem more reasonable.

My theory works like this:

For anything that is under $10, I prefer to go with odd numbers. For example, $1, $3, $5, $7, and $9 seems cheaper to me than say, an even number alternative. Now, to raise the price a dollar, I would go with adding $.99 to these numbers. So instead of $2, maybe $1.99 seems like a bit less money. Ironically, to me, it seems like I would rather buy something from say, the Apple store, if the cost was $1, but $.99 seems like it could be pricy. (I don’t like buying a single track for $1, so thank God there are much beter alternatives even in the apple store today.) So this is an observation I’ve made when trying to price things myself, for what you think it may be worth but still seem appealing when people may not think it is reasonable. Hopefully it works; it seems plausible to me.

Then I thought, now what about more expensive items then… does it still hold true?

Well interestingly enough, I figured out that when raising the price into the double digits, I noticed something intresting. After a little thought about what I would think would be more reasonable, I actually would prefer the last number to be even. $12, $14, $16. But another level up? $21, $23, $25. So I noticed this pattern: the even-odd pattern approach to pricing. Starting with odd numbers, making a price with an even-odd pattern in the digits seemed more appealing and slightly less expensive than other prices. I wonder if it is just my preference, or how the brain is calulating the numbers in terms of cost and expenses. Maybe the even-odd combination influences the mind to think the numbers are somehow not as even and thus maybe not as large since they do not fit as well together? Either way, I’ve noticed this when coming up with prices for my own products, and I like to use it as a guide to try to make “friendlier pricing”. Further study would be needed to confirm this observation…

Random thoughts… but hey, interesting how the mind works.

Simple observation, I know, but maybe it is just me that prefers these patterns?

Google + comes off as an Advertising Board rather than a “Social Network”

What is a social network anyway?

Ok, so you can make an argument about what a social network really is, but what you can’t deny is that the Google Plus network has no Hangouts-325x325features that resemble a place on the web to be advertised to. But after all, it makes sense, considering that what Google does boils down to advertising in the end.

So what makes it seem like an advertising avenue than a legitimate place to socialize with people you know? And the answer to that is this: it is the way they collect and show your information.

This sounds familiar to a techie that knows about the behind the scenes of Facebook (which by the way looks weird to me having a capital letter), and of course everyone else who doesn’t know how Facebook really works, just that it does. A quick explanation, is that Facebook analyzes each person’s feed to give you an ordered list of what they may want to see the most. There is too much information being posted by everyone you know to show you everything in one page, so Facebook tries to order this of importance to show you the most important updates first. Pretty ingenious really.

So how is Google + different, and why don’t people use it the same way?

Both Facebook and Google put together a list of pages that you follow, pages that you like (or +1… I guess), or groups or “communities” that you are a part of, and give you updates on all of them at once. But what is each one focusing on? Google is focused on bringing you content from what you follow, where Facebook is focused on bring you content from your friends and family, and of course random pages that you like. Right when you join, you are encouraged to join “communities” in Google+. On Facebook, you are encouraged to find people you know, and just “like pages”, rather than join them.

So a main point that this comes down to, is would you rather see information from random people in communities or posts from friends and ocassional updates from pages? The answer is: occasionally both, but more likely from friends. And who may want random posts? Maybe smaller, less general communities will offer some value, right? Probably could.

But what do these communities offer? Well it is a fantastic place to get your word out to a good number of people. And you see where I am going with this… this is what advertising is. People who post about what people may most want to read will be the ones in the spotlight with Google+.

Don’t get me wrong though with this, Google communities are honestly a great concept and I have made my own myself. But Google + as a whole network falls short in being a platform that people want to get updates from.

Why won’t people post their photos and statuses on Google+?

Honestly, I’ve used Google+ for a little while now, mostly to create Community pages and meet and talk with people there. But when it comes to photos, I can see it becoming a really confusing interface to use. Google automatically can sync photos from your phone to your Google profile. This is truely a fantastic service that has a ton of potential! But when you land on your profile, it shows your photo highlights, as if Google somehow knows what your photo highlights are for you? So you click to see all your photos, but they are not a part of your page just yet. In fact, you can’t even add the photos to an album easily unless you click the small checkmark and share it with arbituary circles, or go into the individial photo and click a “more” drop-down. How anti-intuitive! Why can we not easily organize our photos, when we can so easily organize our ‘circles’? This is a question I would ask the designers of the interface of “Plus”.

Is Google+ a good place to put my business information?

Google + is actually a great place to put a page of information about a business, especially after they integrated services with google maps and youtube. However, take the service with a grain of salt. While it is a great place to have some short ‘n sweet info for a person passing by, that is what most of the traffic will be: a quick glance at the company profile. I would spend just a little time putting in the information that people can quickly reference, but that is all the effort you would really need.

Should I get involved with Google+?

That is the thing. People are not using Google + very much, and probably because of some of the reasons I mentioned above. When you create a social network, you need to explain to people how they recommend you use their service. People like to be on the same page with each other, and not “be going in blind”, so-to-speak. People want to understand the concept of what is being presented, so that they are not guessing at what they should be using it. If it is too complicated, redesign the interface. If the concept is not understood, explain it. People like a standard to follow, but the freedom to explore past it if they would like, and that standard really hasn’t been established that well with this platform. Remember the campaign with the “YOU” circled with a bunch of arrows around it? Its great that Plus is about ME, but what can I really use it for, and how can I use it? It is probably too complicated to explain to many people, and if it is not complicated, then maybe too disorganized.

 

TL;DR version: Google focuses on generic communities, does not focus on a simple photo organization service, and does not focus on connecting friends and family as much as Facebook does.

When should I use Visual Studio Lightswitch?

I have had my hands on Visual Studio Lightswitch since its fairly earlier stages. Back a few years ago, there were bugs with the designer that had to be addressed periodically. Now the project is much more robust and can be used with major implementations. But not all implementations would go well with how Lightswitch is designed.

Lightswitch now has two methods of rendering a project: the Silverlight client and an HTML5 export. Silverlight is used for a few major applications like Netflix and some smaller applications for web input. Of course, Netflix has announced that they would like to move away from Silverlight to HTML5 when it is feasible, as Microsoft had announced the end of support for silverlight by 2021.

So why is Silverlight being phased out?

Although Microsoft Silverlight is a proprietary software it is meant to be compatible with most devices. Silverlight is in a way similar to Adobe’s flash player plugin, only that it can focus on data driven applications much better than flash, where flash is more of an artistic solution. But what is the same is that both forms of media require a browser plugin in order to use the software. Some see this as a security issue, others see it as an inconvenience. Microsoft probably realizes that a

Difficulties with Silverlight

I have been working on Silverlight software for the past couple of years with Lightswitch to organize everything into one location that is easily manageable. Along with this, every person using the software now had to make sure that they had the Silverlight plugin installed and activated, which was not always the case. This is extra maintenance that goes into ensuring that the users are properly set up to use the application.

Now with the Lightswitch Silverlight client, it is very easy to publish to the web server once you have the server properly configured. However, the client itself is a full-page application that by default will take up the entire page, and only provides tools to edit the Silverlight application. Furthermore, when loading all of the controls into the browser, the Silverlight client can take up a considerable amount of RAM and CPU processing power while using it, compared with an HTML form.

The Lightswitch Application

The Lightswitch clients both output complete pages of the application you are trying to make. It uses your data model and provides a framework to reference your data within your application, as you will most likely need to code some attributes under the drag-and-drop layout design at some point. With the complete page, each client has a built-in navigation that is displayed on top.

You are also limited to what you can use inside of the application. For example, Lightswitch does not come with any complex forms of reporting. There are plugins available, for example from ComponentOne, but it does cost more than a professional-level license of visual studio. You can also include web pages into the screen with some coding, but is not a good strategy considering that you now have web content inside of Silverlight content.

Okay on to the main point: Reasons to Use Lightswitch

Lightswitch makes it easy to publish an app to the server and make any changes it needs to. When installing the Lightswitch deployment package for the server, it attempts to configure the server to what is needed to have the application work properly. Although, this often does need to be fine tuned to fit your needs. It also will work with Windows Authentication quite nicely, avoiding the need to even sign in manually or messing with group permissions between a file server and the app itself. This alone is a great reason to use this kind of software: compatibility.

Lightswitch is meant to make it easy for developers to quickly create an app to display and edit information from their database. The advantages of this method is being able to generate the underlying information needed for the controls and data that is used. Of course, you must be sure to follow the procedure of working from the data-level, updating the data-source in Lightswitch, making any necessary changes, then publishing the changes. You must also be sure that the clients refresh and have the latest version of the application that has not been pulled from their cache.

Recently, they have also improved their HTML5 client, that is able to be run on any device. This can be especially useful when using tablets or other smaller and simpler devices to quickly get and modify information. Being able to export in both formats while using the same data back-end is especially impressive and can save a lot of time.

It provides a way to quickly generate forms and grids that allow you to edit and display the content of your database. It provides good controls to quickly show a pop-up window, or choose a date or select from a drop-down list to filter results. Each screen can be created from a list of organization groups and controls, and can even be modified while testing. But that could be the extent of it before it gets complicated.

Problems with Lightswitch

Microsoft advertises Lightswitch as an easy solution that developers do not need to have much knowledge of programming in order to build applications. Depending on the applications you want to build, this may not be the case at all. While it is great to quickly create forms to edit outside of the database-level, it can get just as complex when working with more advanced applications. When the demands are getting to the point where information is being moved around, default values are needed based on how data is added, or disabling or highlighting grid rows, programming will be needed.

Using the Silverlight will require the user to have the plugin installed and need it to be activated. This is an extra step needed that is better than having files of MS access laying around for example, but is not as expected from an internal website. Luckily, Microsoft has introduced the HTML5 client that can work on any device to help solve this problem, but it is not quite the same as using traditional web forms, as this export is intended to be used with mobile and touch-screen devices.

Overall Conclusions: Should I make an App using Lightswitch?

Lightswitch is a software that has the ability to be used on any device, is easy and quick to develop with from a variety of data sources, can publish easily and quickly to a windows server, and can use windows authentication. I’d like to stress two things here: easy, quick, and windows authentication. I’d also emphasize the fact that it is also quite limited, so your requirements for the application should be fairly minimal and straightforward before coding becomes fairly involved.

I see Lightswitch as a good tool for creating quick ways for users to access certain information without having to use other ways to do it (ex: MS Access, Excel, requesting SQL Management Studio access, etc.). In which case I highly recommend giving it a try.

But, when creating more involved software, you may want to consider using web forms, actual applications, or other solutions that specialize in this area. Lightswitch controls are not always customizable to what users would hope for, but that is at the expense of using an organized and built-in control interface.

It is also important to understand how they want to be using the software. Lightswitch does not have any built-in reporting, unlike Silverlight applications that can use these controls with less problems, built right into a webpage. There are other forms of reporting software out there that might be easier to use than Lightswitch, like in Microsoft Excel, or other web solutions.

PROS: Rapid development, Simple and organized interface with built-in controls, easy data-source import, easy publishing to Microsoft server, Windows Authentication

CONS: No included reporting abilities (costs extra), often requires programming when working on anything more complex, uses Silverlight client for desktop approach, can be restrictive in design needs and capabilities.