Selecting all except some columns in MySQL

The MySQL SELECT is a ubiquitous statement. You can select rows using the ‘*’ operator or by listing the individual column names. But many times you may require using all the columns from a table except a couple of them. For example you may have a table containing twelve columns from which you require only eleven columns. Most of us will use the ‘*’ operator in the SELECT statement rather than explicitly list all the eleven column names. But what if the column we do not require contains a large amount of BLOB data. It is inefficient to include the BLOB column in the query as it will unnecessarily increase the query load. What we want is to select all the columns except the BLOB column. We can construct the required statement using the ‘SHOW COLUMNS’ statement.
(more…)

Read More

Getting MySQL table size with PHP

Below is a small script that lets you read the table sizes of a MySQL database.

< ?php
 
$link = mysql_connect('host', 'username', 'password');
 
$db_name = "your database name here";
$tables = array();
 
mysql_select_db($db_name, $link);
$result = mysql_query("SHOW TABLE STATUS");
 
while($row = mysql_fetch_array($result)) {
    /* We return the size in Kilobytes */
    $total_size = ($row[ "Data_length" ] + 
                   $row[ "Index_length" ]) / 1024;
    $tables[$row['Name']] = sprintf("%.2f", $total_size);
}
 
print_r($tables);
 
?>

(more…)

Read More

Sidu – a Free MySQL client

MySQL Sidu is a FREE MySQL client which works via a web browser. Written in php (on surprise there!), MySQL Sidu is a simple and easy to use database tool. Sidu stands for Select Insert Delete Update. Of course Sidu can do more jobs than these. Many of you may already be using other MySQL clients like phpMyAdmin, MySQL Workbench, but still you can give Sidu a try. Being small in size (less then 200k), its easy to install. You may even think of integrating it in one of your own php applications.
(more…)

Read More

converting MySQL queries to xml

There frequently arises a need to return mySQL query results in xml. Maybe you need to send the xml data to the browser or you want to use it as a xml request to a web service; whatever the application, the following function will return the result of a sql query in xml format.

The function accepts 3 parameters. The first is the mysql query result resource, the second is the name of the root element and the third the name of the first child of the root. All the fields from the mySQL table will be sub elements of this child.

The function is shown below:
(more…)

Read More

6 books to master PHP

PHP is a wonderful dynamic language, and the addition of Unicode support, namespaces in the upcoming version 6 will make it even better.
If you are new to PHP or are thinking of moving to PHP form other language then the following list of books will provide you with the required knowledge to become a seasoned PHP programmer. Of course, the list is subjective and you may have a different list of books in mind. (more…)

Read More