Showing posts with label MYSQL Tutorials. Show all posts

Monday, 17 September 2012

Local UTF-8 Pages Coming Out as Plain Text

09:20 MYSQL, MYSQL Tutorials, PHP with MYSQL No comments

In this case the Content-Type header is relevant and you should look for a header that looks like:

Content-Type: text/html; charset=UTF-8

This:

locally, is getting the file opened as plain text. Page info gives "text/plain" as Type.

When I add either:

and in fact just

it looks fine locally. FF Page Info then gives "text/html" as Type.

I altered the default character encoding in FF and it made no difference unfortunately.

Lowercase Words To Uppercase Words

09:04 MYSQL, MYSQL Tutorials, PHP with MYSQL No comments

Hello Friends in this tutorials how to lowercase words will be replaced with upper/lowercase words to the use of DataBase.you to replace words in string with a tag where url is from database.

$nodecontent="this is test NIke CaLLaway page redirect blah blah"

$query = "SELECT * FROM wordlist";
$rst = mysql_query($query, $con);
while($row = mysql_fetch_assoc($rst)) {
$nodecontent= str_ireplace(" ".$row['link']." ", " <a href='". $row['url'] ."'>". $row['link'] ."</a> ");
}

This *would* (in your example) output:


this is test <a href="www.nike.com">nike</a> <a href="www.callaway.com">callaway</a> page redirect blah blah"

instead of


this is test <a href="www.nike.com">NIke</a> <a href="www.callaway.com">CaLLaway</a> page redirect blah blah"

Note the lowercase words will be replaced with the upper/lowercase version that is in the DB.

MYSQL ( Lesson No. XVI )

21:48 MYSQL, MYSQL Tutorials No comments

COLLATION

Collation in MySQL Database is a set of rules used in comparisons. Because many people use MySQL with data to be stored in languages other than English, they need to select the rules of comparisons which in turn depends on the character set used for storing that data.

In MySQL, data is stored using a specific character set, which can be defind at different levels; i.e., the sever, the database, the table, and the column levels. Each character set has a default collation; for instance, the Latine1 character set uses the latin1_swedish_ci collation which is the Swedish case insensitive order.

Usually, when someone develops an application that involves localization (using the local language; e.g., Arabic) or Internationalization (i.e., using multiple languages), they resort to Unicode (utf-8) which has several collations. In general, it is a good idea to set the character set to utf-8 and select the relevant collation or just accept the default utf8-general-ci.

MYSQL ( Lesson No. XV )

21:01 MYSQL, MYSQL Tutorials No comments

MYSQL Useful Functions and Clauses

Here is the list of all important MySQL functions. Each function has been explained along with suitable example.

mysql/mysql-group-by-clause.MySQL Group By Clause - The MySQL GROUP BY statement is used along with the SQL aggregate functions like SUM to provide means of grouping the result dataset by certain database table column(s).
mysql/mysql-in-clause.MySQL IN Clause - This is a clause which can be used alongwith any MySQL query to specify a condition.
mysql/mysql-between-clauseMySQL BETWEEN Clause - This is a clause which can be used alongwith any MySQL query to specify a condition.
mysql/mysql-union-keywordMySQL UNION Keyword - Use a UNION operation to combine multiple result sets into one.
mysql/mysql-count-functionMySQL COUNT Function - The MySQL COUNT aggregate function is used to count the number of rows in a database table.
mysql/mysql-max-functionMySQL MAX Function- The MySQL MAX aggregate function allows us to select the highest (maximum) value for a certain column.
mysql/mysql-min-functionMySQL MIN Function- The MySQL MIN aggregate function allows us to select the lowest (minimum) value for a certain column.
mysql/mysql-avg-functionMySQL AVG Function - The MySQL AVG aggregate function selects the average value for certain table column.
mysql/mysql-sum-functionMySQL SUM Function - The MySQL SUM aggregate function allows selecting the total for a numeric column.
mysql/mysql-sqrt-functionMySQL SQRT Functions - This is used to generate a square root of a given number.
mysql/mysql-rand-functionMySQL RAND Function- This is used to generate a random number using MySQL command.
mysql/mysql-concat-functionMySQL CONCAT Function- This is used to concatenate any string inside any MySQL command.
mysql/mysql-date-time-functionsMySQL DATE and Time Functions - Complete list of MySQL Date and Time related functions.
mysql/mysql-numeric-functionsMySQL Numeric Functions- Complete list of MySQL functions required to manipulate numbers in MySQL.

MYSQL ( Lesson No. XIV )

20:51 MYSQL, MYSQL Tutorials No comments

Using MYSQL Joins

Thus far we have only been getting data from one table at a time. This is fine for simple takes, but in most real world MySQL usage you will often need to get data from multiple tables in a single query.
You can use multiple tables in your single SQL query. The act of joining in MySQL refers to smashing two or more tables into a single table.

You can use JOINS in SELECT, UPDATE and DELETE statements to join MySQL tables. We will see an example of LEFT JOIN also which is different from simple MySQL JOIN.

Using Join at Command Prompt

Suppose we have two tables tcount_tbl and tutorials_tbl in TUTORIALS. A complete listing is given below:

Example:

Try out following examples:

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * FROM tcount_tbl;
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
| mahran          |             20 |
| mahnaz          |           NULL |
| Jen             |           NULL |
| Gill            |             20 |
| John Poul       |              1 |
| Sanjay          |              1 |
+-----------------+----------------+
6 rows in set (0.01 sec)


mysql> SELECT * from tutorials_tbl;
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|           1 | Learn PHP      | John Poul       | 2007-05-24      |
|           2 | Learn MySQL    | Abdul S         | 2007-05-24      |
|           3 | JAVA Tutorial  | Sanjay          | 2007-05-06      |
+-------------+----------------+-----------------+-----------------+
3 rows in set (0.00 sec)
mysql>

Now we can write a SQL query to join these two tables. This query will select all the authors from table tutorials_tbl and will pickup corresponding number of tutorials from tcount_tbl.

mysql> SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
    -> FROM tutorials_tbl a, tcount_tbl b
    -> WHERE a.tutorial_author = b.tutorial_author;
+-------------+-----------------+----------------+
| tutorial_id | tutorial_author | tutorial_count |
+-------------+-----------------+----------------+
|           1 | John Poul       |              1 |
|           3 | Sanjay          |              1 |
+-------------+-----------------+----------------+
2 rows in set (0.01 sec)
mysql>

Using Join in PHP Script

You can use any of the above mentioned SQL query in PHP script. You only need to pass SQL query into PHP function mysql_query() and then you will fetch results in usual way.

Example:

Try out following example:

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
        FROM tutorials_tbl a, tcount_tbl b
        WHERE a.tutorial_author = b.tutorial_author';

mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    echo "Author:{$row['tutorial_author']}  <br> ".
         "Count: {$row['tutorial_count']} <br> ".
         "Tutorial ID: {$row['tutorial_id']} <br> ".
         "--------------------------------<br>";
} 
echo "Fetched data successfully\n";
mysql_close($conn);
?>

MYSQL Left Join

A MySQL left join is different from a simple join. A MySQL LEFT JOIN gives extra consideration to the table that is on the left.
If I do a LEFT JOIN, I get all records that match in the same way and IN ADDITION I get an extra record for each unmatched record in the left table of the join - thus ensuring (in my example) that every AUTHOR gets a mention:

Example:

Try out following example to understand LEFT JOIN:

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
    -> FROM tutorials_tbl a LEFT JOIN tcount_tbl b
    -> ON a.tutorial_author = b.tutorial_author;
+-------------+-----------------+----------------+
| tutorial_id | tutorial_author | tutorial_count |
+-------------+-----------------+----------------+
|           1 | John Poul       |              1 |
|           2 | Abdul S         |           NULL |
|           3 | Sanjay          |              1 |
+-------------+-----------------+----------------+
3 rows in set (0.02 sec)

You would need to do more practice to become familiar with JOINS. This is a but complex concept in MySQL/SQL and will become more clear while doing real examples.

MYSQL ( Lesson No. XIII )

20:44 MYSQL, MYSQL Tutorials No comments

MYSQL Delete Query

If you want to delete a record from any MySQL table then you can use SQL command DELETE FROM. You can use this command at mysql> prompt as well as in any script like PHP.

Syntax:

Here is generic SQL syntax of DELETE command to delete data from a MySQL table:

DELETE FROM table_name [WHERE Clause]

If WHERE clause is not specified then all the records will be deleted from the given MySQL table.
You can specify any condition using WHERE clause.
You can delete records in a single table at a time.

The WHERE clause is very useful when you want to delete selected rows in a table.

Deleting Data From Command Prompt

This will use SQL DELETE command with WHERE clause to delete selected data into MySQL table tutorials_tbl

Example:

Following example will delete a record into tutorial_tbl whose tutorial_id is 3.

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> DELETE FROM tutorials_tbl WHERE tutorial_id=3;
Query OK, 1 row affected (0.23 sec)

mysql>

Deleting Data Using PHP Script

You can use SQL DELETE command with or without WHERE CLAUSE into PHP function mysql_query(). This function will execute SQL command in similar way it is executed at mysql> prompt.

Example:

Try out following example to delete a record into tutorial_tbl whose tutorial_id is 3.

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'DELETE FROM tutorials_tbl
        WHERE tutorial_id=3';

mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not delete data: ' . mysql_error());
}
echo "Deleted data successfully\n";
mysql_close($conn);
?>

MYSQL ( Lesson No. XII )

20:38 MYSQL, MYSQL Tutorials No comments

MYSQL Update Query

There may be a requirement where existing data in a MySQL table need to be modified. You can do so by using SQL UPDATE command. This will modify any field value of any MySQL table.

Syntax:

Here is generic SQL syntax of UPDATE command to modify data into MySQL table:

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

You can update one or more field all together.
You can specify any condition using WHERE clause.
You can update values in a single table at a time.

The WHERE clause is very useful when you want to update selected rows in a table.

Updating Data From Command Prompt

This will use SQL UPDATE command with WHERE clause to update selected data into MySQL table tutorials_tbl

Example:

Following example will update tutorial_title field for a record having tutorial_id as 3.

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> UPDATE tutorials_tbl 
    -> SET tutorial_title='Learning JAVA' 
    -> WHERE tutorial_id=3;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

Updating Data Using PHP Script

You can use SQL UPDATE command with or without WHERE CLAUSE into PHP function mysql_query(). This function will execute SQL command in similar way it is executed at mysql> prompt.

Example:

Try out following example to update tutorial_title field for a record having tutorial_id as 3.

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'UPDATE tutorials_tbl
        SET tutorial_title="Learning JAVA"
        WHERE tutorial_id=3';

mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not update data: ' . mysql_error());
}
echo "Updated data successfully\n";
mysql_close($conn);
?>

MYSQL ( Lesson No. XI )

09:13 MYSQL, MYSQL Tutorials No comments

MYSQL Where Clause

Syntax

Here is generic SQL syntax of SELECT command with WHERE clause to fetch data from MySQL table:

SELECT field1, field2,...fieldN table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

You can use one or more tables separated by comma to include various condition using a WHERE clause. But WHERE clause is an optional part of SELECT command.
You can specify any condition using WHERE clause.
You can specify more than one conditions using AND or OR operators.
A WHERE clause can be used alongwith DELETE or UPDATE SQL command also to specify a condition.

The WHERE clause works like a if condition in any programming language. This clause is used to compare given value with the field value available in MySQl table. If given value from outside is equal to the available field value in MySQL table then it returns that row.
Here is the list of operators which can be used with WHERE clause.
Assume field A holds 10 and field B holds 20 then:

Operator	Description	Example
=	Checks if the value of two operands is equal or not, if yes then condition becomes true.	(A = B) is not true.
!=	Checks if the value of two operands is equal or not, if values are not equal then condition becomes true.	(A != B) is true.
>	Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true.	(A > B) is not true.
<	Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true.	(A < B) is true.
>=	Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true.	(A >= B) is not true.
<=	Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true.	(A <= B) is true.

The WHERE clause is very useful when you want to fetch selected rows from a table, Specially when you use MySQL Join. Joins are discussed in another chapter.
It is a common practice to search records using Primary Key to make search fast.
If given condition does not match any record in the table then query would not return any row.

Fetching Data From Command Prompt

This will use SQL SELECT command with WHERE clause to fetch selected data from MySQL table tutorials_tbl

Example

Following example will return all the records from tutorials_tbl table for which author name is Sanjay:

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl WHERE tutorial_author='Sanjay';
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|           3 | JAVA Tutorial  | Sanjay          | 2007-05-21      |
+-------------+----------------+-----------------+-----------------+
1 rows in set (0.01 sec)

mysql>

All the Unless performing a LIKE comparison on a string, the comparison is not case sensitive. You can make your search case sensitive using BINARY keyword as follows.

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl \
          WHERE BINARY tutorial_author='sanjay';
Empty set (0.02 sec)

mysql>

Fetching Data Using PHP Script

You can use same SQL SELECT command with WHERE CLAUSE into PHP function mysql_query(). This function is used to execute SQL command and later another PHP function mysql_fetch_array() can be used to fetch all the selected data. This function returns row as an associative array, a numeric array, or both. This function returns FALSE if there are no more rows.

Example

Following example will return all the records from tutorials_tbl table for which author name is Sanjay:

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT tutorial_id, tutorial_title, 
               tutorial_author, submission_date
        FROM tutorials_tbl
        WHERE tutorial_author="Sanjay"';

mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    echo "Tutorial ID :{$row['tutorial_id']}  <br> ".
         "Title: {$row['tutorial_title']} <br> ".
         "Author: {$row['tutorial_author']} <br> ".
         "Submission Date : {$row['submission_date']} <br> ".
         "--------------------------------<br>";
} 
echo "Fetched data successfully\n";
mysql_close($conn);
?>

MYSQL ( Lesson No. X )

09:01 MYSQL, MYSQL Tutorials No comments

MySQL Insert Query

To insert data into MySQL table you would need to use SQL INSERT INTO command. You can insert data into MySQL table by using mysql> prompt or by using any script like PHP.

Syntax:

Here is generic SQL syntax of INSERT INTO command to insert data into MySQL table:

INSERT INTO table_name ( field1, field2,...fieldN )

VALUES

( value1, value2,...valueN );

To insert string data types it is required to keep all the values into double or single quote, for example:- "value".

Inserting Data From Command Prompt

This will use SQL INSERT INTO command to insert data into MySQL table tutorials_tbl

Example:

Following example will create 3 records into tutorials_tbl table:

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> INSERT INTO tutorials_tbl 
     ->(tutorial_title, tutorial_author, submission_date)
     ->VALUES
     ->("Learn PHP", "John Poul", NOW());
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO tutorials_tbl
     ->(tutorial_title, tutorial_author, submission_date)
     ->VALUES
     ->("Learn MySQL", "Abdul S", NOW());
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO tutorials_tbl
     ->(tutorial_title, tutorial_author, submission_date)
     ->VALUES
     ->("JAVA Tutorial", "Sanjay", '2007-05-06');
Query OK, 1 row affected (0.01 sec)
mysql>

In the above example we have not provided tutorial_id because at the time of table create we had given AUTO_INCREMENT option for this field. So MySQL takes care of inserting these IDs automatically. Here NOW() is a MySQL function which returns current date and time.

Inserting Data Using PHP Script:

You can use same SQL INSERT INTO command into PHP function mysql_query() to insert data into a MySQL table.

Example:

This example will take three parameters from user and will insert them into MySQL table:

<html>
<head>
<title>Add New Record in MySQL Database</title>
</head>
<body>
<?php
if(isset($_POST['add']))
{
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}

if(! get_magic_quotes_gpc() )
{
   $tutorial_title = addslashes ($_POST['tutorial_title']);
   $tutorial_author = addslashes ($_POST['tutorial_author']);
}
else
{
   $tutorial_title = $_POST['tutorial_title'];
   $tutorial_author = $_POST['tutorial_author'];
}
$submission_date = $_POST['submission_date'];

$sql = "INSERT INTO tutorials_tbl ".
       "(tutorial_title,tutorial_author, submission_date) ".
       "VALUES ".
       "('$tutorial_title','$tutorial_author','$submission_date')";
mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not enter data: ' . mysql_error());
}
echo "Entered data successfully\n";
mysql_close($conn);
}
else
{
?>
<form method="post" action="<?php $_PHP_SELF ?>">
<table width="600" border="0" cellspacing="1" cellpadding="2">
<tr>
<td width="250">Tutorial Title</td>
<td>
<input name="tutorial_title" type="text" id="tutorial_title">
</td>
</tr>
<tr>
<td width="250">Tutorial Author</td>
<td>
<input name="tutorial_author" type="text" id="tutorial_author">
</td>
</tr>
<tr>
<td width="250">Submission Date [ yyyy-mm-dd ]</td>
<td>
<input name="submission_date" type="text" id="submission_date">
</td>
</tr>
<tr>
<td width="250"> </td>
<td> </td>
</tr>
<tr>
<td width="250"> </td>
<td>
<input name="add" type="submit" id="add" value="Add Tutorial">
</td>
</tr>
</table>
</form>
<?php
}
?>
</body>
</html>

MYSQL ( Lesson No. IX )

08:40 MYSQL, MYSQL Tutorials No comments

Drop MYSQL Tables

Syntax:

Here is generic SQL syntax to drop a MySQL table:

DROP TABLE table_name ;

Dropping Table From Command Prompt

This needs just to execute DROP TABLE SQL command at mysql> prompt.

Example:

Here is an example which deletes tutorials_tbl:

root@host# mysql -u root -p
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> DROP TABLE tutorials_tbl
Query OK, 0 rows affected (0.8 sec)
mysql>

Dropping Table Using PHP Script

To drop an existing table in any database you would need to use PHP function mysql_query(). You will pass its second argument with proper SQL command to drop a table.

Example:

<html>
<head>
<title>Creating MySQL Tables</title>
</head>
<body>
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully<br />';
$sql = "DROP TABLE tutorials_tbl";
mysql_select_db( 'TUTORIALS' );
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not delete table: ' . mysql_error());
}
echo "Table deleted successfully\n";
mysql_close($conn);
?>
</body>
</html>

MYSQL ( Lesson No. VIII )

08:34 MYSQL, MYSQL Tutorials No comments

Create MySQL Tables

Syntax:

Here is generic SQL syntax to create a MySQL table:

CREATE TABLE table_name (column_name column_type);

Now we will create following table in TUTORIALS database.

tutorials_tbl(
   tutorial_id INT NOT NULL AUTO_INCREMENT,
   tutorial_title VARCHAR(100) NOT NULL,
   tutorial_author VARCHAR(40) NOT NULL,
   submission_date DATE,
   PRIMARY KEY ( tutorial_id )
);

Here few items need explanation:

Field Attribute NOT NULL is being used because we do not want this field to be NULL. SO if user will try to create a record with NULL value then MySQL will raise an error.
Field Attribute AUTO_INCREMENT tells to MySQL to go ahead and add the next available number to the id field.
Keyword PRIMARY KEY is used to define a column as primary key. You can use multiple columns separated by comma to define a primary key

Creating Table From Command Prompt

This is easy to create a MySQL table from mysql> prompt. You will use SQL command CREATE TABLE to create a table.

Example:

Here is an example which creates tutorials_tbl:

root@host# mysql -u root -p
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> CREATE TABLE tutorials_tbl(
   -> tutorial_id INT NOT NULL AUTO_INCREMENT,
   -> tutorial_title VARCHAR(100) NOT NULL,
   -> tutorial_author VARCHAR(40) NOT NULL,
   -> submission_date DATE,
   -> PRIMARY KEY ( tutorial_id )
   -> );
Query OK, 0 rows affected (0.16 sec)
mysql>

Creating Tables Using PHP Script:

To create new table in any existing database you would need to use PHP function mysql_query(). You will pass its second argument with proper SQL command to create a table.

Example:

Here is an example to create a table using PHP script:

<html>
<head>
<title>Creating MySQL Tables</title>
</head>
<body>
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully<br />';
$sql = "CREATE TABLE tutorials_tbl( ".
       "tutorial_id INT NOT NULL AUTO_INCREMENT, ".
       "tutorial_title VARCHAR(100) NOT NULL, ".
       "tutorial_author VARCHAR(40) NOT NULL, ".
       "submission_date DATE, ".
       "PRIMARY KEY ( tutorial_id )); ";
mysql_select_db( 'TUTORIALS' );
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not create table: ' . mysql_error());
}
echo "Table created successfully\n";
mysql_close($conn);
?>
</body>
</html>

MYSQL ( Lesson No. VII )

08:27 MYSQL, MYSQL Tutorials No comments

How to Create Backup & Restore Data in MYSQL

This is a simplify Coding of Backup & Restore Databases in MYSQL.If you want to make any project and use the databases of another PC same databases then to use this coding and Restore the database.And if your PC in a networking then to use this coding and give the IP addresses then you were use the same databases in another PC's.

1.) //dumping database
c:\programe files>mysqldump -u root -p database name>c:\filename.txt
type password

2.) //dumping table
c:\programe files>mysqldump -u root -p database_name table_name>c:\filename.txt
type password

3.) //importing database
c:\program files>mysql -u root -p database_name<c:\file_name
type password

4.) //dumping data of a table
mysql>select * into outfile 'c:\\file_name' from table;

5.) //loading data from 1 file
mysql>load data infile 'c:\\ss.txt' into table table_name;

MYSQL ( Lesson No. VI )

06:00 MYSQL, MYSQL Tutorials No comments

Selecting MySQL Database

Once you get connection with MySQL server,it is required to select a particular database to work with. This is because there may be more than one database available with MySQL Server.

Selecting MySQL Database from Command Prompt:

This is very simple to select a particular database from mysql> prompt. You can use SQL command use to select a particular database.

Example:

Here is an example to select database called TUTORIALS:

[root@host]# mysql -u root -p
Enter password:******
mysql> use TUTORIALS;
Database changed
mysql>

Now you have selected TUTORIALS database and all the subsequent operations will be performed on TUTORIALS database.

Selecting MySQL Database Using PHP Script:

PHP provides function mysql_select_db to select a database.It returns TRUE on success or FALSE on failure.

Syntax:

bool mysql_select_db( db_name, connection );

Parameter	Description
db_name	Required - MySQL Database name to be selected
connection	Optional - if not specified then last opened connection by mysql_connect will be used.

Example:

Here is the example showing you how to select a database.

<html>
<head>
<title>Selecting MySQL Database</title>
</head>
<body>
<?php
$dbhost = 'localhost:3036';
$dbuser = 'guest';
$dbpass = 'guest123';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_select_db( 'TUTORIALS' );
mysql_close($conn);
?>
</body>
</html>

MYSQL ( Lesson No. V )

05:52 MYSQL, MYSQL Tutorials No comments

Drop Database using mysqladmin:

You would need special privilege to create or to delete a MySQL database. So assuming you have access to root user, you can create any database using mysql mysqladmin binary.
Be careful while deleting any database because it will lose your all the data available in your database.
Here is an example to delete a database created in previous chapter:

[root@host]# mysqladmin -u root -p drop TUTORIALS
Enter password:******

This will give you a warning and it will confirm if you really want to delete this database or not.

Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'TUTORIALS' database [y/N] y
Database "TUTORIALS" dropped

Drop Database using PHP Script:

PHP uses mysql_query function to create or delete a MySQL database. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax:

bool mysql_query( sql, connection );

Parameter	Description
sql	Required - SQL query to create or delete a MySQL database
connection	Optional - if not specified then last opened connection by mysql_connect will be used.

Example:

Try out following example to delete a database:

<html>
<head>
<title>Deleting MySQL Database</title>
</head>
<body>
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully<br />';
$sql = 'DROP DATABASE TUTORIALS';
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not delete database: ' . mysql_error());
}
echo "Database TUTORIALS deleted successfully\n";
mysql_close($conn);
?>
</body>
</html>

MYSQL ( Lesson No. IV )

05:49 MYSQL, MYSQL Tutorials No comments

Create MySQL Database

CREATE DATABASE USING MYSQLADMIN:

You would need special privilege to create or to delete a MySQL database. So assuming you have access to root user, you can create any database using mysqladmin binary.

Example:

Here is a simple example to create database called TUTORIALS:

[root@host]# mysqladmin -u root -p create TUTORIALS
Enter password:******

This will create a MySQL database TUTORIALS.

CREATE DATABASE USING PHP SCRIPT:

PHP uses mysql_query function to create or delete a MySQL database. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax:

bool mysql_query( sql, connection );

Parameter	Description
sql	Required - SQL query to create or delete a MySQL database
connection	Optional - if not specified then last opened connection by mysql_connect will be used.

Example:

Try out following example to create a database:

<html>
<head>
<title>Creating MySQL Database</title>
</head>
<body>
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully<br />';
$sql = 'CREATE DATABASE TUTORIALS';
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not create database: ' . mysql_error());
}
echo "Database TUTORIALS created successfully\n";
mysql_close($conn);
?>
</body>
</html>

MYSQL ( Lesson No. III )

05:44 MYSQL, MYSQL Tutorials No comments

The SQL SELECT command is used to fetch data from MySQL database. You can use this command at mysql> prompt as well as in any script like PHP.

Syntax:

Here is generic SQL syntax of SELECT command to fetch data from MySQL table:

SELECT field1, field2,...fieldN table_name1, table_name2...
[WHERE Clause]
[OFFSET M ][LIMIT N]

You can use one or more tables separated by comma to include various condition using a WHERE clause. But WHERE clause is an optional part of SELECT command.
You can fetch one or more fields in a single SELECT command.
You can specify star (*) in place of fields. In this case SELECT will return all the fields
You can specify any condition using WHERE clause.
You can specify an offset using OFFSET from where SELECT will start returning records. By default offset is zero
You can limit the number of returned using LIMIT attribute.

FETCHING DATA FROM COMMAND PROMPT:

This will use SQL SELECT command to fetch data from MySQL table tutorials_tbl

Example:

Following example will return all the records from tutorials_tbl table:

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl 
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|           1 | Learn PHP      | John Poul       | 2007-05-21      |
|           2 | Learn MySQL    | Abdul S         | 2007-05-21      |
|           3 | JAVA Tutorial  | Sanjay          | 2007-05-21      |
+-------------+----------------+-----------------+-----------------+
3 rows in set (0.01 sec)

mysql>

FETCHING DATA USING PHP SCRIPT:

You can use same SQL SELECT command into PHP function mysql_query(). This function is used to execute SQL command and later another PHP functionmysql_fetch_array() can be used to fetch all the selected data. This function returns row as an associative array, a numeric array, or both. This function returns FALSE if there are no more rows.
Below is a simple example to fetch records from tutorials_tbl table.

EXAMPLE:

Try out following example to display all the records from tutorials_tbl table.

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT tutorial_id, tutorial_title, 
               tutorial_author, submission_date
        FROM tutorials_tbl';

mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    echo "Tutorial ID :{$row['tutorial_id']}  <br> ".
         "Title: {$row['tutorial_title']} <br> ".
         "Author: {$row['tutorial_author']} <br> ".
         "Submission Date : {$row['submission_date']} <br> ".
         "--------------------------------<br>";
} 
echo "Fetched data successfully\n";
mysql_close($conn);
?>

In above example the constant MYSQL_ASSOC is used as the second argument to PHP function mysql_fetch_array(), so that it returns the row as an associative array. With an associative array you can access the field by using their name instead of using the index.
PHP provides another function called mysql_fetch_assoc() which also returns the row as an associative array.

EXAMPLE:

Try out following example to display all the records from tutorial_tbl table using mysql_fetch_assoc() function.

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT tutorial_id, tutorial_title, 
               tutorial_author, submission_date
        FROM tutorials_tbl';

mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_assoc($retval))
{
    echo "Tutorial ID :{$row['tutorial_id']}  <br> ".
         "Title: {$row['tutorial_title']} <br> ".
         "Author: {$row['tutorial_author']} <br> ".
         "Submission Date : {$row['submission_date']} <br> ".
         "--------------------------------<br>";
} 
echo "Fetched data successfully\n";
mysql_close($conn);
?>

You can also use the constant MYSQL_NUM, as the second argument to PHP function mysql_fetch_array(). This will cause the function to return an array with numeric index.

EXAMPLE:

Try out following example to display all the records from tutorials_tbl table using MYSQL_NUM argument.

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT tutorial_id, tutorial_title, 
               tutorial_author, submission_date
        FROM tutorials_tbl';

mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_NUM))
{
    echo "Tutorial ID :{$row[0]}  <br> ".
         "Title: {$row[1]} <br> ".
         "Author: {$row[2]} <br> ".
         "Submission Date : {$row[3]} <br> ".
         "--------------------------------<br>";
}
echo "Fetched data successfully\n";
mysql_close($conn);
?>

All the above three examples will produce same result.

RELEASING MEMORY:

Its a good practice to release cursor memory at the end of each SELECT statement. This can be done by using PHP function mysql_free_result(). Below is the example to show how it has to be used.

EXAMPLE:

Try out following example

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT tutorial_id, tutorial_title, 
               tutorial_author, submission_date
        FROM tutorials_tbl';

mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_NUM))
{
    echo "Tutorial ID :{$row[0]}  <br> ".
         "Title: {$row[1]} <br> ".
         "Author: {$row[2]} <br> ".
         "Submission Date : {$row[3]} <br> ".
         "--------------------------------<br>";
}
mysql_free_result($retval);
echo "Fetched data successfully\n";
mysql_close($conn);
?>

MYSQL ( Lesson No. II )

05:29 MYSQL, MYSQL Tutorials No comments

MySQL PHP Syntax

MySQL works very well in combination of various programming languages like PERL, C, C++, JAVA and PHP. Out of these languages, PHP is the most popular one because of its web application development capabilities.
This tutorial focuses heavily on using MySQL in a PHP environment. If you are interested in MySQL with PERL then you can look into.
PHP provides various functions to access MySQL database and to manipulate data records inside MySQL database. You would require to call PHP functions in the same way you call any other PHP function.
The PHP functions for use with MySQL have the following general format:

The second part of the function name is specific to the function, usually a word that describes what the function does. The following are two of the functions which we will use in our tutorial

mysqli_connect($connect);
mysqli_query($connect,"SQL statement");

Following example shows a generic sysntax of PHP to call any MySQL function.

<html>
<head>
<title>PHP with MySQL</title>
</head>
<body>
<?php
   $retval = mysql_function(value, [value,...]);
   if( !$retval )
   {
       die ( "Error: a related error message" );
   }
   // Otherwise MySQL  or PHP Statements
?>
</body>
</html>

Monday, 17 September 2012

Wednesday, 12 September 2012

Syntax:

Example:

Example:

Syntax:

Example:

Example:

Syntax:

Example:

Drop Database using PHP Script:

Syntax:

Example:

CREATE DATABASE USING MYSQLADMIN:

CREATE DATABASE USING PHP SCRIPT:

Example:

Syntax:

FETCHING DATA FROM COMMAND PROMPT:

Example:

FETCHING DATA USING PHP SCRIPT:

EXAMPLE:

EXAMPLE:

EXAMPLE:

RELEASING MEMORY:

EXAMPLE:

MySQL PHP Syntax

Search

Translate

Categories

Archive

SlideShow

Popular Posts

Followers