PHP: How to Select Multiple Database Tables

Selecting a MySQL database table with a PHP document is a quick way to get certain output onto a web page with very little coding or effort. You may want nothing more than a simple printout of the database table or something more complex like a full web application.

Regardless of the scenario, selecting multiple database tables in the same query is a little more tricky. Here is a syntax that worked for me. First connect to your database the way you normally would, then enter your query like this:

$result = mysql_query("SELECT * FROM table1, table2 ");

Replace “table1″ and “table2″ with the actual names of your tables. Next, you can show rows from both tables, but if any of the rows have the same names in both tables, you will have to specify them specifically in the query.

Finally, display the results however you like:

while($row = mysql_fetch_array($result))
{
echo "<div id='corn'> ";
echo $row['corn'];
echo "</div> <div id='wheat'> ";
echo $row['wheat'];
echo "</div> ";

For more information on MySQL Select, see w3schools.com
Photo: Wikimedia Commons

How to create a table using phpMyAdmin

After you create a MySQL database, the next thing you will most likely want to do is to fill it with data. To do that, you need to setup tables. If you are installing a content management system or other type of script with an installer, it will probably create the tables for you. If that is not the case, you must create the tables yourself. While creating them from the command line is not terribly difficult, using the phpMyAdmin web interface is probably the easiest method.

To create a new table, follow these steps:

1. Login to your phpMyAdmin installation either through your control panel or directly.

2. On the left, click the database you want to manipulate.

3. At the bottom, underneath the current tables, find “Create new table on database” and enter the name and number of fields each row will have.

4. Enter the field names (each row will have a name assigned to it)

5. Select the data type. The default INT is for integer (i.e. a number). You can see a complete lists of data types on the mysql website

6. Select a maximum length or leave it blank

7. Set the type of collation, essentially the encoding, such as UTF-8.

8. When you have all of the information inputed, scroll to the bottom and click “Save”.

With that, you are all finished with your first table.

How to create backups with cPanel

Many VPS and dedicated server users have cPanel installed as their control panel. Therefore, it is very useful to know how to create backups of your website or entire server using cPanel. Follow these instructions to make your backups.

1. Login to cPanel on your website (usually something like http://yourdomain.com/cpanel)

2. Under the “Files” section, click “Backups”

There are several backups to choose, all of which will download the files to your computer. If you want automated scheduled backups and/or remote backups, choose another option.

3. To generate a full backup of your website, click the first button.

Optionally, you can choose to backup only a portion of your site, such as home directory, MySQL database, email forwards, or email filters.

4. Click the button of the component you want to download. Your browser’s save dialog should appear offering you a file in tar.gz format, which is a commonly used Linux form of file compression.

Backups are very important, and if you are unable to schedule routine automated backups, this convenient manual backup is a good option.

How to create a database in phpMyAdmin

In a previous post, we learned how to create a MySQL database from the Linux command line. Today, we will learn how to accomplish the same task with a graphical interface using the free web-based MySQL administration tool: phpMyAdmin. Just follow these steps.

1. Login to phpMyAdmin either through your server’s control panel or directly.

Note: Some panels, such as cPanel require you to create databases through their interface. If so, follow their instructions

2. In the main Home section, look for “MySQL localhost“, and under “Create new database”, enter the name you want to use

3. Click “Create”

That is all it takes! You now have a new database. To assign users to the database, click the “Privileges” tab while you are still on that database’s page. Once you have the permissions you want, you can add, delete, and configure databases with relative ease.

Data migration tips

Anytime you move to a new web host or upgrade to a new server, you will need to migrate your data. In the old days, data was often stored in flat files or simply directly within the HTML files. While the management eventually proved to be inefficient, it was much easier to move files. With databases, it can be trickier. Here are a few things to consider:

1. If you are moving to an identical setup (i.e. same version of the same content management system), a basic SQL export will work just fine with minimal to no data loss.

2. If you are changing versions of a CMS, you should read its documentation to see if migration is even possible and what steps you need to take to make it happen. If at all possible, upgrade to the identical version before migration.

3. When moving to a completely different CMS or database system, you will need to prepare your data. This can involve manipulating large chunks of a MySQL database or even exporting everything to XML. Some software like WordPress will import RSS posts from an XML file without any extra configuration, making a migration rather painless.

4. Backup everything as often as you can. If you mess something up, it is better to have to start over with your backup copy than to have to fix the only copy you have.

Photo Source: Flickr

Database management with MySQL Workbench

MySQL is one of the most popular databases for web servers. The MySQL website offers a free download of MySQL Workbench, a GUI database management program. I decided to download it and give it a try. It is available for Microsoft Windows, Mac OS X, and several versions of Linux. I tried out the Ubuntu version.

When you first start the application, you are greeted with your Workspace and three categories: SQL Development, Data Modeling, and Server Administration. I wanted to see how easy it was to connect to a database, so I immediately clicked “New Connection”. By default, it brings up your local databases, which is what I wanted to edit, so I did not have to configure anything, only enter my password. The workbench instantly presented me with a new tab containing all of my SQL databases. To edit any one, you just click on it and begin.

I honestly do not even know what data modeling is, so I left that section alone. I went straight to server administration and clicked “New server instance”. Again there were several configuration options, but it was already set to connect to my local server. Within seconds I had live graphs, server status, configuration options, and logs for my MySQL server.
Read More >>

New server setup

Question: I want to setup my own server. What software and tools will I need?

Answer: The first major decision to make after you have purchased server hardware is what operating system to use. Many servers come with one already, but if yours does not, you will need to decide on one. Linux or other Unix variants, such as BSD or Solaris are by far the most popular, but there are also many Windows servers out there. If you are on a limited budget, go with a free Linux distribution, such as CentOS.

The next thing to choose is the web server. Choices include Apache, Nginx, and Lighttpd. After that, you will probably one a database server such as MySQL or PostgreSQL Finally, you will want a web-based control panel. Depending on the one you choose, it might also install its own versions of Apache, PHP, and MySQL.

There are many control panel solutions on the market, and most of them are commercial. Popular control panels includ cPanel and Plesk. There are also free solutions available like Virtualmin. Decide what you need for whatever type of websites you plan to have and then choose the software that meets those needs.

Photo: Flickr

Twitter the latest among big players to leave MySQL for Cassandra

With a name that even sounds like the beautiful love interest of a secret affair, Cassandra appears to be positioning itself to steal the hearts of those once in love with MySQL. Twitter is now set to replace its MySQLdatabase system with the open source home wrecker.

Cassandra was originally developed by Facebook and was released to the public via Google’s code repository in 2008. In 2009, the Apache Foundation added it to its project, and in February of 2010, Apache made it a top-level project. It is designed to work with large amounts of data by spreading the data across multiple servers. Its notable feature is that it has no single point of failure.

Twitter joined a growing list of major companies that have adopted Cassandra. Among them are Digg, Cisco, IBM. Cassandra is free and open source software released under the Apache License 2 and is available for download from Apache.org.

Photo: Flickr

How to check and optimize MySQL databases automatically

MySQL databases require regular maintenance, but usually the databases just need to be re-optimized and checked for errors. In such cases, having to go through each database on your server can be time consuming. Following these steps, you can setup a cron job that will automatically check and repair your databases.

1. Login to the server via SSH
2. Edit the crontab file found in /etc/crontab

# crontab -e

3. Enter the following line in the crontab file:

0 1 * * * mysqlcheck -Aao –auto-repair -u root -p[password] > /dev/null

This will check all databases on the server. If you do not have root access and only want to check your own website, you can edit your cron in your control panel or replace the “root” user with your MySQL username. Save the file, and cron will now start mysqlcheck at 1 am everyday to optimize and check all databases.

Source: My Digital Life
Photo: Flickr

Micro-blogging on your own domain with StatusNet

Twitter has become very popular in a short amount of time. With it, users can post short updates about what they are currently doing, working on, thinking, or viewing/accessing. For businesses, it is a good way to communicate with others who might not be in the same office or even the same city or country. The downside to this is that Twitter is used by so many others for other purposes, and users are often inundated with requests.

Running a micro-blogging server of your own might be very appealing, particularly if you only need it for a company’s business and do not want outsiders to view it. While hosted solutions like Twitter offer privacy options, you would still be trusting your security to a third party. StatusNet is a free micro messaging platform that powers the also popular Identi.ca.

You can host it on your own domain, share files, create groups, add plug-ins and applications, and access it via both desktop computers and mobile devices. It is a PHP application that stores data in MySQL databases. StatusNet is free software released under the GNU AGPL and is available for download directly from the project’s website.