Brian's Programming Code Examples
MySQL Examples
I recently decided to make the switch to using MySQL PDO Prepared Statements for my database queries. I wanted to add an additional layer of protection against SQL injection and PDO prepared statements are a perfect solution. When used properly PDO prepared statements are an excellent defense against SQL injections.
Continue reading “PHP MySQL PDO Prepared Statement Query Example”
In one of my posts, I explain How To Google Geocode an Address With PHP. Please refer to How To Google Geocode an Address With PHP for specifics on geocoding an address.
In this post, I’ll explain how to use those saved geocoordinates to search for properties within a distance from a specific zip code. This example assumes you have properties in a table named tbl_listings with columns for listing_zip, listing_geolatitude, and listing_geolongitude. Providing a search by zip code is very simple. Providing a search for a given distance from a specific zip code is also pretty simple if you have the geographic coordinates of the target zip code.
Continue reading “MySQL Query by Geolocation (Latitude and Longitude) Example”
Often I need to produce a query over a parent table and get a total of values from a child table. This can be accomplished with a single query that utilizes a subquery within the SELECT statement. In the following example, we’re selecting parent values from a table named tbl_loans and utilizing a subquery to select the total of the amount paid toward principal from a child table named tbl_loanpayments. For each row in the table tbl_loans, there could be multiple child rows in the table tbl_loanpayments. We want the amount paid to the principal for all loan payment rows that have an amount paid not equal zero.
The following example will work in SQL Server or MySQL.
Continue reading “SELECT Statement Subquery Example (MySQL/SQL Server)”
I recently needed to produce a query with detail and have it subtotal by employee, by date. Subtotals can be difficult if you have data that varies within the subtotalled group.
The work around is to union queries. One query with the detail and another query with the subtotal by the control break you need. We then use a case condition in the order by clause to cause our subtotal to appear where we want it in the result.
The following example will work in SQL Server or MySQL.
Continue reading “SQL Subtotal With Detail Example (SQL Server/MySQL)”