background preloader


Facebook Twitter

MySQL and WordPress: Understanding How Databases Work. WordPress uses MySQL, an open source database management system, to store and retrieve all of your website’s information, from the content of your posts and pages to your comments, usernames and passwords.

MySQL and WordPress: Understanding How Databases Work

If you need to visualize it, think of your site’s database as a filing cabinet and MySQL as the company that made it. MySQL is a popular choice of database for web applications – Joomla! And Drupal also use it, and according to Wikipedia lots of high-profile companies like Google, Facebook, Twitter, Flickr and YouTube use it, too. So how exactly does MySQL work with WordPress? In this article, I’ll walk you through everything there is to know about MySQL and how it interacts with WordPress, including the database architecture, storage engines, optimizing techniques and best practices for optimization and database management. Qrytip. Given a clinic of physicians, patients and appointments, how to find an available appointment time for a given physician?


This is a variant of the [Not] Exists query pattern. Though we can write it with subqueries, performance will be crisper with a join. But finding data that is not there requires a join to data which is there. So in addition to tables for appointments, doctors and patients, we need a table of all possible appointment datetimes. Here's a schema illustrating the idea ... MySQL Stored Procedure Tutorial. Each tutorial is packed with the easy-to-understand examples with detailed explanations.

MySQL Stored Procedure Tutorial

If you go through all the tutorials, you can develop the simple to complex stored procedures in MySQL. Using php $GET string in MySQL query - wpDataTables. Microsoft Drivers for PHP for SQL Server - Home. Graphs. Most non-trivial data is hierarchical.


Customers have orders, which have line items, which refer to products, which have prices. Population samples have subjects, who take tests, which give results, which have sub-results and norms. Web sites have pages, which have links, which collect hits, which distribute across dates and times. With such data, we know the depth of the hierarchy before we sit down to write a query. The depth of the hierarchy of tables fixes the number of JOINs we need to write. But if our data describes a family tree, or a browsing history, or a bill of materials, hierarchical depth depends on the data. That model is the graph (Fig 1), which is a set of nodes (vertices) and the edges (lines or arcs) that connect them. Graph theory is a branch of topology. Let the set of nodes in Fig 1 be N, the set of edges be L, and the graph be G. If the edges are directed, the graph is a digraph or directed graph. Place holder and update of version - wpDataTables.

(2) MySQL at Facebook. (2) How does Facebook maintain a list of friends for each user? Does it maintain a separate table for each user? PHP: Calling MySQL Stored Procedure with Both INPUT AND OUTPUT Parameters (NOT "INOUT") PHP PDO Querying Data from MySQL Database. Summary: in this tutorial, you will learn how to query data from MySQL database by using PHP PDO.

PHP PDO Querying Data from MySQL Database

Using MySQL Stored Procedure to create sample data. PHP MySqli Basic usage (select, insert & update) MySQLi, what’s the difference between standard queries and prepared statements? You may have heard of prepared statements, but don’t quite understand the differences.

MySQLi, what’s the difference between standard queries and prepared statements?

In this post I’m going to try and help you with that. Well, with your standard query, you would get your information, escape it for safety, build your query and execute. MySQLi, what’s the difference between standard queries and prepared statements? Mysql - How to SELECT using prepared statements in PHP? Php - Create HTML table from sql table. Prepared Statements in PHP and MySQLi : Matt Bango's Journal.

This article is intended for readers who have experience using PHP and MySQL.

Prepared Statements in PHP and MySQLi : Matt Bango's Journal

You should also have a general understanding of databases and programming (both procedural and object-oriented) as well as how to use PHP to execute a simple query to MySQL. I will not cover how to install PHP or MySQL, however at the end of the article are some links to help you get started with the installation process and for some further reading on the subject. I will be covering the basics of prepared statements in PHP and MySQLi and why you should consider using them in your own code as well as some technical explanation as to why you should use them. Introduction If you are like me and most other people, you probably have not taken the time to learn about web security when you first started writing server-side code. Prepared statements are more secure.Prepared statements have better performance.Prepared statements are more convenient to write.

The Well-Known Way. A Visual Explanation of SQL Joins. I love the concept, though, so let's see if we can make it work.

A Visual Explanation of SQL Joins

Assume we have the following two tables. Table A is on the left, and Table B is on the right. We'll populate them with four records each. id name id name -- ---- -- ---- 1 Pirate 1 Rutabaga 2 Monkey 2 Pirate 3 Ninja 3 Darth Vader 4 Spaghetti 4 Ninja Let's join these tables by the name field in a few different ways and see if we can get a conceptual match to those nifty Venn diagrams. There's also a cartesian product or cross join, which as far as I can tell, can't be expressed as a Venn diagram: World's Best Software Experts. World's Best Software Experts. Gravity Forms - MySQL Connect. [MySQL 5.0.45-community-nt-log] Sort the “rollup” in group by. 12.16.2 GROUP BY Modifiers. 12.16.2 GROUP BY Modifiers. OLAP Paradise - WITH ROLLUP.

Though this is an age old concept, but after seeing the ignorance factor of many regarding this, I thought of writing a bit about it.


ROLLUP is a GROUP BY modifier that adds extra rows to the output which contain summary data. At each level of aggregation the aggregated column is filled with a NULL value. Let's see a small example. I know the values are too small for the data presented, but just to keep it readable. 1: mysql> CREATE TABLE `rollup_1` ( 2: `url` varchar(255) DEFAULT NULL, 3: `year` int(11) DEFAULT NULL, 4: `country` char(2) DEFAULT NULL, Common MySQL Queries. Common MySQL Queries Basic aggregation Last updated 05 Jan 2013 Aggregate across columns Last updated 09 Sep 2009.

Common MySQL Queries

Sql - MySQL pivot row into dynamic number of columns. Dynamic pivot tables (transform rows to columns) » Buy SQL Portal. MySQL pivot table with dynamic headers. Downloads/pivot_tables_mysql_5.pdf. MySQL Rollup Puzzle. Stored Procedures in MySQL and PHP. Put simply, a Stored Procedure ("SP") is a procedure (written in SQL and other control statements) stored in a database which can be called by the database engine and connected programming languages. In this tutorial, we will see how to create an SP in MySQL and execute it in MySQL server and in PHP. Note: We are not going to cover the full aspect of the SP here. The official MySQL document should always be the place for reference. Getting Started with MySQL Stored Procedures. Summary: in this tutorial, we will show you step by step how to develop the first MySQL stored procedure by using CREATE PROCEDURE statement.

In addition, we will show you how to call the stored procedures from SQL statements. Writing the first MySQL stored procedure We are going to develop a simple stored procedure named GetAllProducts() to help you get familiar with the syntax. The GetAllProducts() stored procedure selects all products from the products table. An Introduction to Stored Procedures in MySQL 5. MySQL 5 introduced a plethora of new features - stored procedures being one of the most significant. In this tutorial, we will focus on what they are, and how they can make your life easier. Introduction. PDO Tutorial for MySQL Developers - From Why use PDO? Mysql_* functions are getting old. For a long time now mysql_* has been at odds with other common SQL database programming interfaces. It doesn't support modern SQL database concepts such as prepared statements, stored procs, transactions etc... and it's method for escaping parameters with mysql_real_escape_string and concatenating into SQL strings is error prone and old fashioned.

The other issue with mysql_* is that it has had a lack of attention lately from developers, it is not being maintained... which could mean things like security vulnerabilities are not getting fixed, or it may stop working altogether with newer versions of MySQL. PDO has a much nicer interface, you will end up being more productive, and write safer and cleaner code. Stored Procedures in MySQL and PHP. PDO Tutorial for MySQL Developers - Choosing an API. Using MySQL Stored Procedures with PHP mysql/mysqli/pdo « Joey Rivera.

Introduction to Database Programming using PHP and MySQL. PHP MySQL Select. Streamlining MySQL Insert Queries. Implode. Boundless : Spatial Database Tips and Tricks : Union Geometries inside the Spatial Database. If you have installed the PostGIS database, the Medford data, and Tomcat, you should be able to view the functioning example here: The previous example was a fun bit of analysis, but it was lacking an important visual enhancement: what parcels were being used to compute the summary? This example builds on the previous one by pulling geometries directly out of the database, and letting them be rendered on the client side. The interface is just like the last example, but with one drop-down added, which will cause the database to union the geometry results before returning them to the client. So, what is going on here? The base map from Google and the WMS overlay of the zoning areas remain the same.

Chapter 17. Spatial Extensions. Chapter 17. Spatial Extensions MySQL supports spatial extensions to allow the generation, storage, and analysis of geographic features. These features are available for MyISAM, InnoDB, NDB, and ARCHIVE tables. (However, the ARCHIVE engine does not support indexing, so spatial columns in ARCHIVE columns cannot be indexed. Three easy ways to optimize your MySQL queries.

Database applications likes MySQL come with tools to aid in the optimization of queries. Three of the more common tools include using indexes, analyzing queries with EXPLAIN, and adjusting MySQL's internal configuration. Creating a Store Locator with PHP, MySQL & Google Maps - Google Maps API. Google Geo APIs TeamAugust 2009. Functions for Testing Spatial Relations Between Geometric Objects. Using the new spatial functions in MySQL 5.6 for geo-enabled applications.

Three easy ways to optimize your MySQL queries. 26.4 Append Excel Data into MySQL. Easily Import CSV File Data Into Mysql Using PHP - InfoTuts. PHP – Import CSV to MySQL. USODI/db-to-api. How to use MySQL Spatial Extensions. Class Polygon. People near you with MySQL. The most common problem on developing a social network is, that you have to reflect a real world into the computer or more specific into the database.

There are many nice things to work on, like finding the friend path to someone (to follow the theory, that everyone knows everyone on the earth over at most 6 edges), show friends of friends and the little more complex task of friend suggestion through a social graph. I will treat these social mappings in later articles. Map Scripting 101 Examples - Map Scripting. Php - Finding Points in a Rectangle or Circle with mysql.