Servage Magazine

Information about YOUR hosting company – where we give you a clear picture of what we think and do!

Articles Tagged ‘mysql’

Using Database in Web Development-Part 2

Friday, May 2nd, 2014 by Servage
2 Now that you have a feel for how to organize the contents of your database I bet you'd like to create some real tables. What is a Data Type? Well, before we get started making a table there is one thing you need to understand first, Data Types. Data Types are pretty straight forward. They indicate the type of data that you are storing in a given table column. Amazing, huh? So, what are the different Data Types? Here is a list of some of the most common ones and what type of values they hold: CHAR This will hold between 0 and 255 characters. You have to specify the maximum amount of characters that ...

Using Database in Web Development-Part 1

Thursday, May 1st, 2014 by Servage
So, you've decided that you're interested in using a database in your web development, huh? Well, then the next step would be to learn about how the database works. How Does My Data Get Stored? How the data is stored in a database is probably much simpler than you might think. Databases use a series of Tables to store the data. A table simply refers to a two dimensional representation of your data using columns and rows. For example: John      Smith                  jsmith@huh.com Paul       McCartney          paul@beatles.com Bill        Murray                gopher@caddyshack.com John Smith jsmith@huh.com Paul McCartney paul@beatles.com Bill Murray gopher@caddyshack.com   So, then , how ...

Relationship types between models

Saturday, March 16th, 2013 by Servage
When you work with data models you will come along the question of relationships between your models, or data tables. Most frameworks support this out of the box, and come with advanced features for handling such issues. You can take a look at the documentation for relationships for some of the major PHP projects on the matter: Yii Framework CakePHP Propel What do the different types of relationships mean? There are four basic types of relationships between models, and they are all distinguished by the way they store the foreign keys, i.e. how the relation is stored in the database. Example 1: Author has many Books You have an Author table with a field Author.id. You have ...

Building data models with MySQL Workbench

Friday, February 8th, 2013 by Servage
Building and maintaing a proper data model is crucial to any web application. I believe that one of the most powerful and often overlooked free tools available is the MySQL Workbench application. It has been introduced here earlier, but it still appears to come as a surprise to many, that such a great tool is available - and even free: Workbench is a desktop application which helps you design and administrate your MySQL servers, databases, tables, users etc. Extremely useful for large and smal projects. I particularly like the data model editor, where you can get a visual overview over your tables and relations between them. Check out the listed ...

Simplify SQL “where” conditions with the “in” statement

Thursday, November 22nd, 2012 by Servage
When you select some thing with SQL and accept any record where a given parameter has one of multiple values, you often see long where conditions like the following: Simple example SELECT * FROM `table` WHERE `id` = "2" OR `id` = "4" OR `id` = "6"; This is based around the regular "where" conditions, where you can append multiple rules with AND/OR - plus even group the with parenthesis, thus creating very complex and long where conditions. This process can be simplified a bit by using the "in" statement. It is used to indicate that the statement is fulfilled if a field has any of a set of following values. Consider the example ...

Optimize your MySQL tables and queries

Thursday, November 15th, 2012 by Servage
When working with dynamic content, you know the problems that can arise from improperly designed tables and inefficient queries. In a worst case scenario, such problems can lead to long load times, user dissatisfaction and thus resulting in loss of revenue if running a commercial website. Therefore even database optimization is an important factor for you as a webmaster. On your Servvage web hosting account you get MySQL databases to use with your scripts. If you need more information or are just curious about more advanced MySQL knowledge, then take a look at the following book examples: Learning MySQL Whether you're running a business, keeping track of members and meetings ...

Secure password storage with PHP and MySQL

Thursday, August 2nd, 2012 by Servage
Every web developer knows what the user data is the most important data he administrates. The user data is the base for logins, authenticating sessions to be a specific person, thus authorizing them to perform certain actions. If the authenticated session isn't by the person you expect, then you risk authorizing wrong people to actions you'd never want them to do. You could argue that this is usually the users problem, but actually it is yours just as much. The inconvenience, problems, bad reviews, or whatever might be the result of compromised security, is something you really don't want. It could break your business. Therefore having a proper and secure ...

Foreign key constraints in MySQL Workbench

Wednesday, July 25th, 2012 by Servage
In this previous article you were introduced to MySQL Workbench, which essentially is a pretty great app to work with data modelling for MySQL databases. In MySQL you can work with different indexes, keys and contraints, which are tools that help you build and maintain logical relationships between entries and tune your database for performance. One of those is the foreign key, which is automatically added to rows when using the relations functionality in MySQL workbench. What are foreign keys If you are used to manually create your database on your hosting account, for example using phpMyAdmin or similar, you might not create foreign keys. Their purpose is to indicate how a ...

Working with spatial functions in MySQL

Tuesday, July 17th, 2012 by Servage
Geo-based services are increasingly popular, and databases are getting better, faster and more advanced querieng functionality to deliver geo-based results fast and accurate. PostgreSQL with the PostGIS extenstion has for a long time been a great solution, and continues to deliver great database products to the open source community. However, MySQL is the by-far most implemented database software with hosting companies. It can be argued for and against, but the fact remains, and therefore it is great to see how spatial functionality continues to improve in MySQL. While the most stable and delpoyed MySQL versions are the lower version 5 distributions, the newer version 5.6 and upwards finally integrates rather ...

Selecting data from tables with the Yii framework

Friday, July 6th, 2012 by Servage
Yii is one of the best PHP frameworks out there, and it really does a lot of stuff pretty awesome. One of the things that is built right into the core functionality of Yii is CDbCriteria class, which helps you manage your queries whenever you want to select data from a database. The criteria object is especially useful in combination with the various find methods of active records. This way you get powerful and customizable methods to get data rightout of Yii for free. CActiveRecord::find() CActiveRecord::findAll() CActiveRecord::findByPk() CActiveRecord::findAllByPk() CActiveRecord::findByAttributes() CActiveRecord::findAllByAttributes() CActiveRecord::count() Here is an example of how to use the criteria object to specifcy your query $criteria = new CDbCriteria; $criteria->condition='post_id=:post_id AND status=:status'; $criteria->params=array(':post_id'=>8, ':status'=>'active'); $post = Post::model()->find($criteria); Check out the ...