Basic Functions of MySQL

Posted by kumarjit in Internet Trends, Software, Web Hosting Tips

1 Star2 Stars (No Ratings Yet)

Basic Functions of MySQL

Basic Functions of mySQL

SQL or Structured Query Language is a standardized query language, which is used to access information from a database. The request for information which is written in SQL is called a query. It was initially designed by the IBM in 1975s, and later introduced in the commercial market by Oracle Corporation. SQL utilizes distributed databases, which means that the databases are spread over several machines. That is why it is gradually gaining popularity among LAN network users. MySQL is a Relational Database Management System which is used to provide access to multiple databases for multiple users, as the situation requires.

Some basic functions of MySQL are,

Connect function

This is one of the most easiest and fundamental functions of my SQL. Here, the function is added, and the user data is stored in constants, so no variable needs to be specified while calling the function. An example code of the Connect function is provided below

function connectDb() {
// connect and set the working db
if (mysql_connect( DBHOST, DBUSER, DBPWD ) && mysql_select_db( DBNAME ))
return true;
else
return false;
}

Repair Table and Optimize Table

This command is used to scan all the tables which are present in a database, and then perform repair and optimization in each table. An example code is provided below for the Repair and Optimize Table function.

function fixTables($dbname) {
// search for all the tables of a db and run repair and optimize
// note: this can take a lot of time if you have big/many tables.
$result = mysql_list_tables($dbname) or die(mysql_error());
while ($row = mysql_fetch_row($result)) {
mysql_query(“REPAIR TABLE $row[0]”);
mysql_query(“OPTIMIZE TABLE $row[0]”);
}
}

Converting a Recordset to HTML Table

This code receives a recordset from a mysql_query output, and then prints it in the usual HTML format. Using css can improve the code functionality. A small example of this code is provided below.

function getHtmlTable($result)
{
// receive a record set and print it into an html table
$out = ‘<table>’;
for($i = 0; $i < mysql_num_fields($result); $i++){
$aux = mysql_field_name($result, $i);
$out .= “<th>”.$aux.”</th>”;
}
while ($linea = mysql_fetch_array($result, MYSQL_ASSOC)) {
$out .= “<tr>”;
foreach ($linea as $valor_col) $out .= ‘<td>’.$valor_col.'</td>’;
$out .= “</tr>”;
}
$out .= “</table>”;
return $out;
}

Returning the values of a row in a comma separated format

It helps the user to get all the values in the first row of the table, in a comma separated format. The code below is an example of the same:

function getCommaValues($sql) {
// execute a $sql query and return all the first value of the rows in a comma separated string
$out = “”;
$rs = mysql_query($sql) or die(mysql_error().$sql);
while($r=mysql_fetch_row($rs)) $out.=($out?”,”:””).$r[0];
return $out;
}

To get the first value after running a query

One of the most commonly used functions, this code, as explained by the topic header, helps to execute the code and then provides the first output of the executed code. We always need to extract a singular value or row, so this code often comes in handy.

function getScalar($sql,$def=””) {
// execute a $sql query and return the first value, or, if none, the $def value
$rs = mysql_query($sql) or die(mysql_error().$sql);
if (mysql_num_rows($rs)) {
$r = mysql_fetch_row($rs);
mysql_free_result($rs);
return $r[0];
}
return $def;
}

To get the first row after running the query

Once again, as the topic name explains, the ensuing code will help to get the first row after executing a code

function getRow($sql) {
// execute a $sql query and return the first row, or, if none, return an empty string
$rs = mysql_query($sql) or die(mysql_error().$sql);
if (mysql_num_rows($rs)) {
$r = mysql_fetch_array($rs);
mysql_free_result($rs);
return $r;
}
mysql_free_result($rs);
return “”;
}

To duplicate a specific row

This function is used to insert into any table, after duplicating any specified row, which is defined using the primaryID field and the values which have been specified in the parameters itself.

function duplicateRow($table,$primaryField,$primaryIDvalue) {
// duplicate one record in a table and return the id
$fields = getCommaFields($table,$primaryField);
$sql = “insert into $table ($fields) select $fields from $table where $primaryField='”.mysql_real_escape($primaryIDvalue).”‘ limit 0,1″;
mysql_query($sql) or die(mysql_error().$sql);
return mysql_insert_id();
}

These are some of the many fundamental functions provided by SQL for developing queries for databases.

Get the best of MySQL hostingĀ  | MySQL web hosting only on Bounceweb hosting!

Be Sociable, Share!

Leave a Reply

You must be logged in to post a comment.