Need to modify or alter the structure of a MySQL table that you created a while ago? Before you carry out the changes you must know that if you haphazardly change the structure of your MySQL table you might lose your data. So make sure you know what you are doing.

You may lose your data while making the following changes in your MySQL table:

• By altering the type — from integer to text or some other type
• By deleting the column

Now coming back to altering the structure.

Suppose a while ago you created a MySQL table called “tourists” with the following structure.

name varchar(255)
destination varchar(255)
id integer not null auto_increment

Then you realized the name should in fact be in two separate fields, viz. first_name and last_name. Now whatever data is there in the current name field you can have it in the new first_name field and in the new last_name field you can manually enter the new values. But let us see how you can change the name to first_name and last_name:

ALTER TABLE ‘tourists’ CHANGE ‘name’ ‘first_name’ VARCHAR(255);
ALTER TABLE ‘tourists’ ADD COLUMN ‘last_name’ VARCHAR(255);

We have in fact executed here two commands simultaneously to change the structure of our MySQL table.

This is highly improbable but you can also change the type of a field. Let’s say you want name to be an integer. You change it like this:

ALTER TABLE ‘tourists’ CHANGE ‘name’ ‘name’ INTEGER NOT NULL

You can also use MODIFY to change a column definition:

ALTER TABLE ‘tourists’ MODIFY ‘name’ INTEGER NOT NULL

You can either store your web or online form data into a database using PHP — MySQL or some other — or you can get it mailed to you. Read how to connect to MySQL database with PHP. It’s preferable to mail the data to you each time someone fills up your web form but for later analysis it’s advised that you also store it somewhere, especially when you are gathering information like age, gender and the reason of visiting your website.

Although there is a multitude of databases and languages available to you, most web hosts provide PHP and MySQL so we’ll base our example on this prevalent database server. We’ll first create an online form that accept the following information:

• Name
• Age
• Gender
• Country

To store this information into your MySQL database either should already be having a table that can accommodate this data, or you should create it. In your hosting account (or local host if you have installed the MySQL database on your PC) you can easily go to phpmyadmin and create a table “mydata” (you can have any name):

CREATE TABLE mydata (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(255),
age integer,
gender VHARCHAR(20),
country VARCHAR(255),
PRIMARY KEY (id));

And now your table is all ready to store your web form data.

The code for the form goes like this:

<form name=”user_details” id=”user_details” method=”post” action=”save-in-database.php”>
        <p><label>Enter Name:</label> <input type=”text” name=”name” id=”name” /></p>
        <p><label>Enter Age:</label> <input type=”text” name=”age” id=”age” /></p>
        <p><label>Enter Gender:</label> <input type=”text” name=”gender” id=”gender” /></p>
        <p><label>Enter Country:</label> <input type=”text” name=”country” id=”country” /></p>
        <p><input type=”submit” name=”s1″ value=”Submit” /> <input type=”reset” name=”r1″ value=”Reset” /></p>
</form>

We could have jazzed it up a bit more with formatting the date field and using a dropdown for countries, but right now our main purpose is to know how to store your online form data into a MySQL database. As you can see above, the action attribute refers to the file “save-in-database.php” and this is the file that receives the information from the form and saves it.

First, let is store the form details in local PHP variables:

<?php
$name = $_POST['name'];
$age = $_POST['age'];
$gender = $_POST['age'];
$country = $_POST['country'];
?>

We use POST because in the method attribute of the form we specified “post”. You can use either “post” or “get”. Once you have stored the form data in the variables you need to connect to the MySQL database server and we’re going to borrow the code from the blog post link mentioned above:

<?php
$host=”mysql.yourdomain.com”;
$user_name=”peter”;
$pwd=”F$5tBGtr”;
$database_name=”our_clients”;
$db=mysql_connect($host, $user_name, $pwd);
if (mysql_error() > “”) print mysql_error() . “<br>”;
mysql_select_db($database_name, $db);
if (mysql_error() > “”) print mysql_error() . “<br>”;
?>

Once we have successfully established the MySQL database connection, we’ll use PHP’s inbuilt MySQL functions to store the values into the database table “mydata”:

<?php
$query = “insert into mydata (name, age, gender, country) values (‘” . $name . “‘, ” . $age . “, ‘” . $gender . “‘, ‘” . $country . “‘)”;

$qresult = mysql_query($query);
?>

You create a simple MySQL command, store it in a string and then execute it by using the PHP function mysql_query(). There are many validations you can carry out to improve and maintain data integrity, but this is a simple example of storing your online form data in MySQL database using PHP.

You can create really cool database applications usind PHP and MySQL. In fact if you are using WordPress you are already using PHP and MySQL. You need to first connect to your MySQL database server before you can manipulate tables created in a particular database. In order to connect, you need the following information:

• MySQL server host link
• User name
• User password
• Database name

If you are hosting your website through a web hosting company, they may have you use “localhost” as your MySQL server host link. Mostly, you use “localhost” when you have installed the server on your own machine. In many cases, you may also have something like “mysql.yourdomain.com”. The rest of the things are easily understandable.

Different people may have different ways of connecting to MySQL database with PHP, but here is a straightforward way:

<?php
$host=”mysql.yourdomain.com”;
$user_name=”peter”;
$pwd=”F$5tBGtr”;
$database_name=”our_clients”;
$db=mysql_connect($host, $user_name, $pwd);
if (mysql_error() > “”) print mysql_error() . “<br>”;
mysql_select_db($database_name, $db);
if (mysql_error() > “”) print mysql_error() . “<br>”;
?>

This way you can easily connect to your MySQL database with PHP. You can easily have this code in a function and then call that function:

<?php
function connect2database()
{
        $host=”mysql.yourdomain.com”;
        $user_name=”peter”;
        $pwd=”F$5tBGtr”;
        $database_name=”our_clients”;
        $db=mysql_connect($host, $user_name, $pwd);
        if (mysql_error() > “”) print mysql_error() . “<br>”;
        mysql_select_db($database_name, $db);
        if (mysql_error() > “”) print mysql_error() . “<br>”;
}
?>

Once this function is called on a particular page, you don’t need to call it again and again every time you run a MySQL function in PHP, unless due to some reason you had to close the database.

You can update a MySQL table, either with PHP or through its command prompt, by adding (inserting) new records to it or modifying the existing records. But in this tutorial we’re going to explore how you can update a MySQL table with PHP. You may have to do this when creating an online application in PHP that involves saving new information or modifying existing information.

In order to modify a MySQL table you first need to establish a database connection. In PHP, you can establish a MySQL database connection using the following lines of code:

<?php
$host=”localhost”;
$user_name=”username”;
$pwd=”password”;
$database_name=”your_database”;
$db=mysql_connect($host, $user_name, $pwd);
if (mysql_error() > “”) print mysql_error() . “<br>”;
mysql_select_db($database_name, $db);
if (mysql_error() > “”) print mysql_error() . “<br>”;
?>

This basically first connects to your MySQL server (in most of the cases it is localhost but if it is different you’ll get this information from your host), and then selects the database that contains the table.

Just to make it a bit convenient let’s assume the table name is students having the following structure:

• first_name
• last_name
• roll_number
• attendance

And we’ll also suppose that a form is being submitted through POST and the following variables contain the information to be saved:

<?php
$fname=”Some first name”;
$lname=”Some last name”;
$rollnumber=”Some roll number”;
$attendance=150;
?>

If this is a new record being saved in the MySQL table you’ll use the following PHP code:

<?php
$r = mysql_query(“insert into students (first_name, last_name, roll_number, attendance) values (‘” . $fname . “‘, ‘” . $lname . “‘, ‘” . $rollnumber . “‘, ” . $attendance . “)”;
?>

The three single quotes are actually single quotes within double quotes because when you are saving alphanumeric values you have to enclose them within quotes. The quotes here may appear “fancy” due to the selected font of the blog, otherwise they have to be simple quotes.

Similarly, if you want to update existing values in a MySQL table you use the following PHP code:

<?php
$r = mysql_query(“update students set first_name=’” . $fname . “‘, last_name=’” . $lname . “‘, roll_number=’” . $rollnumber . “‘, attendance=” . $attendance;
?>

But the problem here is, if you simply use the update command like this, you may end up updating a record where the pointer currently is, whereas you’d like to update a selected record. Suppose you want to update the record of a student with roll number “120956″. You do it like this:

<?
$r = mysql_query(“update students set first_name=’” . $fname . “‘, last_name=’” . $lname . “‘, roll_number=’” . $rollnumber . “‘, attendance=” . $attendance . ” where roll_number=’120956′”;
?>

This is how you can update a MySQL table with PHP.

So you have finally got MySQL running in Ubuntu. Now how to manipulate the database? You can open the Terminal window and use the MySQL command prompt but it can become really laborious after a while, especially if you’ve been using some sort of GUI in Windows to work with MySQL. Although there are several options, in this blog post we’re installing mysql-admin. Conventionally people use the command prompt to install software, but this should be present in the repository.

Go to System -> Administration -> Synaptic Package Manager

It gives you this window (it asks for your user password before the window):

Search for mysql-admin. Mark it, and then click Apply. After it has been installed, you’ll need to run MySQL Query Browser in order to view databases. It’s not as elaborate as the Windows versions of many GUIs, but it does the job better than the command prompt.

Changing data in a MySQL table using command line command is not as complicated as it sounds, and the basic problem is the way commands are written in various tutorials and manuals. If you want to update data in a MySQL table, alter a column or a low, you can modify your MySQL command accordingly.

Suppose you want to change data in a single MySQL table cell. In order to achieve this you need to know the name of the column that contains the cell, the row number, or some trait that can uniquely identify that cell. Normally, a complete row in a MySQL table is a complete record and this record is distinguished by a unique record ID, let is say, “rec_id”. Suppose the cell you want to change belongs to rec_id 678 and the name of the cell is “first_name”. Let the MySQL table be “students”. This is how you change the first_name:

update students set first_name=’Suba’ where rec_id=678;

Isn’t it a straightforward command? No matter what value first_name originally contains, it now has Suba in it. Notice that you don’t write “update table students”, you simply write “update students”.

You must backup your WordPress blog posts since you work so hard at protecting them and then publishing them. Taking regular backups of your WordPress blog posts can be very simple and it can also be a bit complicated.  It depends upon the kind of backup you would like to take.  There are some WordPress backup plug-ins that can automatically take daily backups and e-mail files to a designated e-mail account.

First, let us explore the easiest way of taking the WordPress backups.

1 Log in to your WordPress admin are by going to http://yourdomain.com/blogpath/wp-admin.

2. Under the Tools menu, click Export.

3. Click the “Download Export File” button. This allows you to save an XML file on to your local machine.

Whenever you want, you can import the same file and restore your blog posts.

In MySQL sometimes you have to create a copy of an existing table. For instance, you would like to save some data in a temporary table before finally transferring it to the main table. You basically copy the structure of one table to another. You can create a blank table, and you can even transfer the existing data into the newly created table. In this post, we’ll simply copy the structure of one MySQL table to another. This is how you do it:

A very simple command indeed. It creates a new table, “table2” by copying the structure of “table1”.

MySQL is a freesource relational database application that is prevalently used these days to manage database-centric applications. The best examples are WordPress and PHPNuke.

You can also install MySQL on your local machine whether it is running on Windows XP or a Linux distribution such as Ubuntu, and run it as a server. But managing databases and tables using command prompts can really be a big pain. You need GUI software that enables you to control and manipulate your databases and tables in MySQL.

Some of the applications that can help you manage MySQL easily on your PC are:

• MySQL-Front (one of the oldest, and my favorite)
• HeidiSQL (Nice, I’m currently using this)
• PHPMyAdmin (this comes with every hosting package, and you can also install it on your local machine)
• SQLyog MySQL GUI (wasn’t very happy with it)

These, and some more applications are discussed in this post on SmashingMagazine.

In php sometimes when you execute MySQL commands the queries don’t execute well and you don’t even know unless you define some mechanism to trap the resulting MySQL error. For instance if you’re in setting a new record into MySQL table and if there is some column mismatched you won’t be able to know it unless you specifically checked the database to see whether a record has been inserted or not. There is a way you can trap whether an error occurred and then take the appropriate steps. This is now you can see if an error has occurred (please remember we are talking about php here so we will be using the php MySQL commands to manipulate MySQL tables):

<?php
if(mysql_errno()>0)
{
    // take care of the error.
}
?>

The error descriptions of MySQL are not very helpful so along with seeing the error message you should also see the query in case it is being dynamically generated for instance an online form. In the example below we will use a simple query:

<?php
    $fname=$_POST['fname'];
    $lname=$_POST['lname'];
    $query=mysql_query("insert into mytable (first_name, last_name) values (‘" . $fname . "’, ‘" . $lname . "’");
    if(mysql_error()>0)
    {
        print mysql_error() . "<br><br>";
        print $query;
    }
?>

In the above example in case there is an error in the dynamically generated MySQL query it will not only print the error it will also print the dynamically generated query.