Advantages of Using PHP with MySQL

Advantages of Using PHP with MySQL

 

There are several factors that make using PHP and MySQL together a natural choice:

PHP and MySQL work well together

PHP and MySQL have been developed with each other in mind, so they are easy to use together. The programming interfaces between them are logically paired up. Working together wasn't an afterthought when the developers created the PHP and MySQL interfaces.

PHP and MySQL have open source power

As they are both open source projects, PHP and MySQL can both be used for free. MySQL client libraries are no longer bundled with PHP. Advanced users have the ability to make changes to the source code, and therefore, change the way the language and programs work.

PHP and MySQL have community support

There are active communities on the Web in which you can participate and they'll answer your questions. You can also purchase professional support for MySQL if you need it.

PHP and MySQL are fast

Their simplicity and efficient design enables faster processing.

PHP and MySQL don't bog you down with unnecessary details.

You don't need to know all of the low-level details of how the PHP language interfaces with the MySQL database, as there is a standard interface for calling MySQL procedures from PHP. Online APIs at http://www.php.net offer an unlimited resource.

As we mentioned above, both PHP and MySQL are open source projects, so there's no need to worry about user licenses for every computer in your office or home. In open source projects and technologies, programmers have access to the source code; this enables individual or group analysis to identify potentially problematic code, test, debug, and offer changes as well as additions to that code. For example, Unixthe forerunner in the open source software communitywas freely shared with university software researchers. Linux, the free alternative to Unix, is a direct result of their efforts and the open source licensing paradigm.

PHP is ubiquitous and compatible with all major operating systems. It is also easy to learn, making it an ideal tool for web-programming beginners. Additionally, you get to take advantage of a community's effort to make web development easier for everyone. The creators of PHP developed an infrastructure that allows experienced C programmers to extend PHP's abilities. As a result, PHP now integrates with advanced technologies like XML, XSL, and Microsoft's COM. At this juncture, PHP 5.0 is being used.

MySQL was developed in the 1990s to fill the ever-growing need for computers to manage information intelligently. The original core MySQL developers were trying to solve their needs for a database by using mSQL, a small and simple database. It become clear that mSQL couldn't solve all the problems they wanted it to, so they created a more robust database that turned into MySQL

MySQL supports several different database engines. The database engine determines how MySQL handles the actual storage and querying of the data. Because of that, each storage engine has its own set of abilities and strengths.

PHP Functions Php tutorial - imagemagick php.ini Basics PHP Sessions Cookies Versus Sessions PHP Web-Related Variables PHP ERRORS maximum size of a file uploaded Php Image upload php file_get_contents	MySQL Data on the Web What are GET and POST php and pdf $_ENV and $_SERVER PEAR with php SELECTING DATA PHP prevent hijacking with PHP LAMP PHP MySQL Functions PHP Zip File Functions	Substrings PHP PHP Variable names PHP magic methods How to get current session id Add variables into a session $_GET , $_POST,$_COOKIE different tables present in mysql PHP CURL php Sessions page PHP-sorting an array
PHP-count the elements of array Operators for If/Else Statements PHP file uploading code PHP global variables Testing working using phpinfo PHP Code for a Valid Number PHP-Associative Arrays PHP mvc tutorial PHP get_meta_tags-Extracts difference between print and echo	PHP best tutorial-PHP variables Reading DOC file in PHP PHP interview questions convert time PHP PHP implode array elements header function-PHP PHP-Renaming Files Directories PHP Classes in_array function in PHP keep your session secure PHP	Web Application with PHP What is SQL Injection PHP-extract part of a string PHP urlencode PHP- know browser properties PHP- Extracting Substrings Checking Variable Values /Types PHP-best 20 Open Source cms IP AddressPHP PHP-Scope Resolution Operator
how create new instance of object how eliminate an object PHP- ob_start XML file using the DOM API PHP- MVC PHP- CAPTCHA PHP- Position of a Value in an Array PHP-Mail Functions PHP-difference include vs require calculate the sum of values in an array	PHP-total number of rows Show unique records mysql MySQL Triggers MySQL data directory MySQL Subqueries PHP- Networking Functions PHP- Operators Restore database Conditional Functions mysql PHP-function overloading	Friend function mysql_connect /mysql_pconnect PHP-Error Control Operators what is IMAP Apache-Specific Functions Send Email from a PHP Script SQL inherently WAMP, MAMP, LAMP Php tutorial-SYMBOLS Table Types-MySQL
PHP-Encryption data management PHP Array Running MySQL on Windows Maximum Performance MySQL XML-RPC PHP-static variables Advanced Database Techniques FTP Codeigniter Apache Pool Size	Why NoSQL MySQL Server Performance Database software SQL Interview Answers PHP Redirect	PHP Interview Questions with Answers Advanced PHP

Read More

Create the months table With MySQL

Create the months table as follows:

CREATE TABLE months (
   month_id INT NOT NULL AUTO_INCREMENT,
   month VARCHAR (20),
   days INT,
   PRIMARY KEY (month_id)); 

To add the months to the new table, specify:

INSERT INTO months VALUES (NULL,'January',31);
INSERT INTO months VALUES (NULL,'February',28);
INSERT INTO months VALUES (NULL,'March',31);
INSERT INTO months VALUES (NULL,'April',30);
INSERT INTO months VALUES (NULL,'May',31);
 INSERT INTO months VALUES (NULL,'June',30);
INSERT INTO months VALUES (NULL,'July',31);
INSERT INTO months VALUES (NULL,'August',31);
INSERT INTO months VALUES (NULL,'September',30);
 INSERT INTO months VALUES (NULL,'October',31);
INSERT INTO months VALUES (NULL,'November',30);
INSERT INTO months VALUES (NULL,'December',31);

Read More

Basic MySQL

Basic MySQL

Create or Drop a Database

Starting with something simple, you can use the CREATE command to create a new database. The syntax is

CREATE DATABASE IF NOT EXISTS [yourDBName];

When you create a database with this command, you're really just creating a directory to hold the files that make up the tables in the database.

To delete an entire database from the system, use the DROP command:

DROP DATABASE IF EXISTS [yourDBName];

Be extremely careful when using the DROP command,
because once you drop the database,


You can also use the CREATE command to create a table within the current database. The syntax is

CREATE TABLE [yourTableName] ([fieldName1] (type], [fieldName2]
[type], ...) [options]

To delete a table from the current database, use the DROP command:

DROP TABLE [yourTableName];

Be extremely careful when using the DROP command, because once you drop the tables,

Altering a Table

To add a column to a table, use this:

ALTER TABLE [yourTableName] ADD [newColumn] [fieldDefinition];

To delete a column from a table, use this:

ALTER TABLE [yourTableName] DROP [columnName];

To change a column from one type to another, use this:

ALTER TABLE [yourTableName] CHANGE [columnName]
[newfieldDefinition];

To make a unique column in your table, use this:

ALTER TABLE [yourTableName] ADD UNIQUE [columnName]
([columnName]);

To index a column in your table, use this:

ALTER TABLE [yourTableName] ADD INDEX [columnName]
([columnName]);

Using the ALTER command alleviates the need to delete an entire
table and re-create it .

Insert, Update, or Replace tables record

The INSERT and REPLACE commands populate your tables one record at a time. The syntax of INSERT is

INSERT INTO [yourTableName] ([fieldName1], [fieldName2], ...)
VALUES ('[value of fieldName1]', '[value of fieldName2]'...);

When inserting records, be sure to separate your strings with single quotes or double quotes. If you use single quotes around your strings and the data you are adding contains apostrophes, avoid errors by escaping the apostrophe (\ ') within the INSERT statement. Similarly, if you use double quotes around your strings and you want to include double quotes as part of the data, escape them (\ ") within your INSERT statement.

The REPLACE command has the same syntax and requirements as the INSERT command. The only difference is that you use REPLACE to overwrite a record in a table, based on a unique value:

REPLACE INTO [yourTableName] ([fieldName1], [fieldName2], ...)
VALUES ('[value of fieldName1]', '[value of fieldName2]'...);

The UPDATE command modifies parts of a record without replacing the entire record. To update an entire column in a table with the same new value, use this:

UPDATE [yourTableName] SET [fieldName] = '[new value]';

If you want to update only specific rows, use a WHERE clause:

UPDATE [yourTableName] SET [fieldName] = '[new value]' WHERE [some
expression];

UPDATE can be a very powerful SQL command.

Deleting from a Table

Like the DROP command, using DELETE without paying attention to what you're doing can have horrible consequences in a production environment. Once you drop a table or delete a record, it's gone forever. Don't be afraid; just be careful. To delete all the contents of a table, use the following:

DELETE FROM [yourTableName];

If you want to delete only specific rows, use a WHERE clause:

DELETE FROM [yourTableName] WHERE [some expression];

If you're going to start deleting records, be sure you have a backup.



Selecting from a Table

When creating database-driven Web sites, the SELECT command will likely be the most often-used command in your arsenal. The SELECT command causes certain records in your table to be chosen, based on criteria that you define. Here is the basic syntax of SELECT:

SELECT [field names] FROM [table name]
WHERE [some expression]
ORDER BY [field names];

To select all the records in a table, use this:

SELECT * FROM [yourTableName];

To select just the entries in a given column of a table, use this:

SELECT [columnName] FROM [yourTableName];

To select all the records in a table and have them returned in a particular order, use an expression for ORDER BY. For example, if you have a date field for record entries and you want to see all the record entries ordered by newest to oldest, use this:

SELECT * FROM [yourTableName] ORDER BY [dateField] DESC;

DESC stands for "descending." To view from oldest to newest, use ASC for "ascending." ASC is the default order.

You can also perform mathematical and string functions within SQL statements,

PHP Functions Php tutorial - imagemagick php.ini Basics PHP Sessions Cookies Versus Sessions PHP Web-Related Variables PHP ERRORS maximum size of a file uploaded Php Image upload php file_get_contents	MySQL Data on the Web What are GET and POST php and pdf $_ENV and $_SERVER PEAR with php SELECTING DATA PHP prevent hijacking with PHP LAMP PHP MySQL Functions PHP Zip File Functions	Substrings PHP PHP Variable names PHP magic methods How to get current session id Add variables into a session $_GET , $_POST,$_COOKIE different tables present in mysql PHP CURL php Sessions page PHP-sorting an array
PHP-count the elements of array Operators for If/Else Statements PHP file uploading code PHP global variables Testing working using phpinfo PHP Code for a Valid Number PHP-Associative Arrays PHP mvc tutorial PHP get_meta_tags-Extracts difference between print and echo	PHP best tutorial-PHP variables Reading DOC file in PHP PHP interview questions convert time PHP PHP implode array elements header function-PHP PHP-Renaming Files Directories PHP Classes in_array function in PHP keep your session secure PHP	Web Application with PHP What is SQL Injection PHP-extract part of a string PHP urlencode PHP- know browser properties PHP- Extracting Substrings Checking Variable Values /Types PHP-best 20 Open Source cms IP AddressPHP PHP-Scope Resolution Operator
how create new instance of object how eliminate an object PHP- ob_start XML file using the DOM API PHP- MVC PHP- CAPTCHA PHP- Position of a Value in an Array PHP-Mail Functions PHP-difference include vs require calculate the sum of values in an array	PHP-total number of rows Show unique records mysql MySQL Triggers MySQL data directory MySQL Subqueries PHP- Networking Functions PHP- Operators Restore database Conditional Functions mysql PHP-function overloading	Friend function mysql_connect /mysql_pconnect PHP-Error Control Operators what is IMAP Apache-Specific Functions Send Email from a PHP Script SQL inherently WAMP, MAMP, LAMP Php tutorial-SYMBOLS Table Types-MySQL
PHP-Encryption data management PHP Array Running MySQL on Windows Maximum Performance MySQL XML-RPC PHP-static variables Advanced Database Techniques FTP Codeigniter Apache Pool Size	Why NoSQL MySQL Server Performance Database software SQL Interview Answers PHP Redirect	PHP Interview Questions with Answers Advanced PHP

Read More

Database Backups using mysqldump

The MySQL server, and mysql, the MySQL client, a MySQL installation comes with many useful utility programs. We have seen mysqladmin, which is responsible for the control and retrieval of information about an operational MySQL server, for example.

mysqldump is another such program. When run, it connects to a MySQL server (in much the same way as the mysql program or the PHP language does) and downloads the complete contents of the database you specify. It then outputs these as a series of SQL CREATE TABLE and INSERT commands that, if run in an empty MySQL database, would create a MySQL database with exactly the same contents as the original.

If you redirect the output of mysqldump to a file, you can store a “snapshot” of the database as a backup. The following command (typed all on one line) connects to the MySQL server running on myhost as user root with password mypass, and saves a backup of the database called dbname into the file dbname_backup.sql:

shell%mysqldump -h myhost -u root -pmypass dbname >
dbname_backup.sql 

To restore this database after a server crash, you would use these commands:

shell%mysqladmin -h myhost -u root -pmypass create dbname
shell%mysql -h myhost -u root -pmypass dbname < dbname_backup.sql 

The first command uses the mysqladmin program to create the database; alternatively, you can do this at the MySQL command line. The second connects to the MySQL server using the usual mysql program, and feeds in our backup file as the commands to be executed.

Facilities exist in MySQL to keep up-to-date backups that are not adversely affected by server activity at the time at which the backups are generated. Unfortunately, they require you to set up a backup scheme specifically for your MySQL data, completely apart from whatever backup measures you have established for the rest of your data. As with any good backup system, however, you'll appreciate it when the time comes to use it.

You can also edit update logs to undo mistakes that may have been made. For example, if a co-worker comes to you after having accidentally issued a DROP TABLE command, you can edit your update log to remove that command before you restore your database using your last backup and the log application. In this way, you can even keep changes to other tables that were made after the accident. And, as a precaution, you should probably also revoke your co-worker's DROP privileges. 

An update log is a record of all SQL queries that were received by the database, and which modified the contents of the database in some way. This includes INSERT, UPDATE, and CREATE TABLE statements among others, but doesn't include SELECT statements.

Read More

Advanced SQL

Many of the features shown in this chapter are specific to MySQL's
 version of SQL. For example, MySQL's functions are useful tools for
 working with strings, dates and times, and math. Also, we'll show some
 ways to tune MySQL in order to improve application performance.

It's important to know how to choose and design indexes for fast querying,
 and how to use MySQL's query cache for fast results.


Exploring your database, tables, indexes, and performance with SHOW

More on SELECT queries, including advanced join types, aliases, nested queries,
 user variables, and the limitations of MySQL.

More on manipulating data and databases, including finding out about tables
 and databases, creating tables with queries, altering tables, more on the
 UPDATE and DELETE statements, and bulk loading and exporting data.

Functions and operators in SQL and MySQL

Automatically running queries

MyISAM, InnoDB, and Heap table types

Backup and recovery, and transferring data between database servers

Managing database server users and privileges, and creating users for web
 database applications.

Basic tuning of MySQL, including index design, using the query cache,
 and, miscellaneous tips for speed.



The SHOW command is useful for exploring the details of databases, tables,
 indexes, and MySQL. It's a handy tool when you're writing new queries,
modifying database structure, creating reports, or understanding how your
MySQL server is performing. The SHOW command isn't part of the SQL
standard and is MySQL-specific. It can be used in several ways:




SHOW DATABASES

Lists the databases that are accessible by the MySQL server. You will only
 see those databases that you have access to, unless you have the
SHOW DATABASES privilege;
privileges and user rights are discussed later in this chapter.




SHOW TABLES

Shows the tables in the database, after a database has been
 selected with the use command.




SHOW TABLE STATUS

Provides information about all tables in the current database,
 including the table type, number of rows, how the rows are stored,
 average row length, size of the datafile, next auto_increment value if applicable,
 creation time, last modification time, and any extra options
 used with CREATE TABLE.




SHOW CREATE TABLE tablename

Shows the CREATE TABLE statement that was used to create the table tablename.
The output always includes any additional information automatically added or
changed by MySQL during the creation process, such as the table type and
character set used.




SHOW OPEN TABLES

Shows which tables the server currently has open and which tables are locked.




SHOW COLUMNS FROM tablename

Shows the attributes, types of attributes, key information, whether NULL
is permitted, defaults, and other information for a table tablename.
The alias DESCRIBE table produces the same output.




SHOW INDEX FROM tablename

Presents the details of all indexes on the table tablename, including the
 PRIMARY KEY. It shows amongst other information what the attributes are
that form each index, whether values in the index uniquely identify rows,
 how many different values there are in the index the cardinality,
 and the index data structure used usually a B-tree.




SHOW PRIVILEGES

Lists the access privileges that can be given or denied to users of the
 version of MySQL server that you've installed.




SHOW PROCESSLIST

Lists the current MySQL processes or threads that are running,
and what query they're carrying out on which database.




SHOW STATUS

Reports details of the MySQL server performance and statistics.
 Selected statistics .




SHOW TABLE TYPES

Lists the possible table types that are available in the version
 of the MySQL server that you have installed, and notes alongside
 each whether you have compiled-in support for that table type.





SHOW VARIABLES

Reports the values of most MySQL system variables.



SHOW WARNING and SHOW ERRORS
Reports warnings or errors from the last command or statement
 that was run on a table

Read More

PHP-Database-Basics-DB-Arrays

Adding MySQL to PHP and combining the applications for your
 dynamic web site is a great start. But, it helps tremendously
 to structure your database right. We'll give you a solid
understanding of both database design and the language that's
 used to communicate with the database, SQL. The first step in
 setting up your database is to design how you'll store your data.
 Then, you'll learn how to add, view, and change data.


Databases are a repository for information.
 They excel at managing and manipulating structured information.
 Structured information is a way to organize related pieces of
 information, which we discussed previously in our chapters on PHP.
 The basic types of structured information,
 which can also be called data structures, include:

Files

Lists

Arrays

Records

Trees

Tables



Each of these basic structures has many variations and allows
 for different operations to be performed on the data. An easy
way to understand this concept is to think of the phone book.
 It's the most widespread database, and it contains several
items of informationname, address, and phone number, as well
 as each phone subscriber in a particular area. Phone books
have evolved, and some people may have bolded names, but for
the most part, each entry in the phone book takes the same form.

If you think of the physical hardcopy phone book in similar
terms as a database, the phone book is a table, which
contains a record for each subscriber. Each subscriber record
 contains three fields also known as columns or

attributes: name, address, and phone number. These records
 are sorted alphabetically by the name field, which is
called the key field.


Adding MySQL to PHP and combining the applications for your
dynamic web site is a great start. But, it helps tremendously
 to structure your database right. We'll give you a solid
 understanding of both database design and the language that's
 used to communicate with the database, SQL. The first step in
 setting up your database is to design how you'll store your
data. Then, you'll learn how to add, view, and change data.

Databases are a repository for information. They excel at managing
 and manipulating structured information. Structured information
 is a way to organize related pieces of information, which we
discussed previously in our chapters on PHP. The basic types of
 structured information, which can also be called data structures
 include:

Files

Lists

Arrays

Records

Trees

Tables

Each of these basic structures has many variations and allows
 for different operations to be performed on the data.

An easy way to understand this concept is to think of the
 phone book. It's the most widespread database, and it
contains several items of informationname, address,
and phone number, as well as each phone subscriber
in a particular area. Phone books have evolved, and some
people may have bolded names, but for the most part,
each entry in the phone book takes the same form.

If you think of the physical hardcopy phone book in
similar terms as a database, the phone book is a table,
 which contains a record for each subscriber.
Each subscriber record contains three fields also known
 as columns or attributes: name, address, and phone number.
 These records are sorted alphabetically by the name field,
 which is called the key field.

Read More

mysql_query-executes query

mysql_query function  executes query on the default database, set using mysql_select_db() or by a previous query using mysql_db_query(), on the MySQL server connection referenced by connection . If no connection handle is specified in the connection argument, the last connection opened is used by default. If no connection is open, mysql_query() attempts to connect to a MySQL database by calling mysql_connect() without arguments.

The value returned depends on the query. SELECT, DESCRIBE, EXPLAIN, and SHOW queries return a MySQL result handle if successful and FALSE if they fail. Note that these types of queries are considered to have failed only if they're malformed. Other query types return TRUE on success and FALSE on failure.

To find the number of rows affected by a query:

mysql_num_rows() 

To set the default database:

mysql_select_db() 

To retrieve data from a query:

mysql_fetch_array() 
mysql_fetch_assoc() 
mysql_fetch_object() 
mysql_fetch_row() 
mysql_result() 

To query a specific database:

mysql_db_query() 

mysql_result() fetches a single field from a MySQL result set. The function accepts two or three arguments.

The first argument should be a MySQL result handle returned by mysql_db_query() or mysql_query().

The second argument should be the row from which to fetch the field, specified as an offset. Row offsets start at 0.

The optional last argument can contain a field offset or a field name. If the argument is not set, a field offset of 0 is assumed. Field offsets start at 0, while field names are based on an alias, a column name, or an expression.

Read More

Table Types-MySQL

MyISAM is the default table type in MySQL Version 3.23. It's based on the ISAM code and
has a lot of useful extensions.

The index is stored in a le with the .MYI (MYIndex) extension, and the data is stored
in a le with the .MYD (MYData) extension. You can check/repair MyISAM tables with the
myisamchk utility.

The following is new in MyISAM:
 If mysqld is started with --myisam-recover, MyISAM tables will automaticly be repaired on open if the table wasn't closed properly.

 You can INSERT new rows in a table without deleted rows, while other threads are
reading from the table.

 Support for big les 63-bit on lesystems/operating systems that support big les.
 All data is stored with the low byte rst. This makes the data machine and OS
independent. The only requirement is that the machine uses two's-complement signed
integers (as every machine for the last 20 years has) and IEEE

oating-point format
also totally dominant among mainstream machines. The only area of machines that
may not support binary compatibility are embedded systems because they sometimes
have peculiar processors.
There is no big speed penalty in storing data low byte rst; The bytes in a table row
is normally unaligned and it doesn't take that much more power to read an unaligned
byte in order than in reverse order. The actual fetch-column-value code is also not
time critical compared to other code.

 All number keys are stored with high byte rst to give better index compression.
 Internal handling of one AUTO_INCREMENT column. MyISAM will automatically update
this on INSERT/UPDATE. The AUTO_INCREMENT value can be reset with myisamchk.
This will make AUTO_INCREMENT columns faster (at least 10 %) and old numbers will
not be reused as with the old ISAM. Note that when an AUTO_INCREMENT is dened on
the end of a multi-part-key the old behavior is still present.
 When inserted in sorted order (as when you are using an AUTO_INCREMENT column) the
key tree will be split so that the high node only contains one key. This will improve
the space utilization in the key tree.
 BLOB and TEXT columns can be indexed.
 NULL values are allowed in indexed columns. This takes 0-1 bytes/key.
 Maximum key length is now 500 bytes by default. In cases of keys longer than 250
bytes, a bigger key block size than the default of 1024 bytes is used for this key.
 Maximum number of keys/table enlarged to 32 as default. This can be enlarged to 64
without having to recompile myisamchk.

 There is a ag in the MyISAM le that indicates whether or not the table was closed
correctly. This will soon be used for automatic repair in the MySQL server.
 myisamchk will mark tables as checked if one runs it with --update-state. myisamchk
--fast will only check those tables that don't have this mark.

 myisamchk -a stores statistics for key parts (and not only for whole keys as in ISAM).
 Dynamic size rows will now be much less fragmented when mixing deletes with updates
and inserts. This is done by automatically combining adjacent deleted blocks and by
extending blocks if the next block is deleted.

 myisampack can pack BLOB and VARCHAR columns.

Read More

Running MySQL on Windows

MySQL supports TCP/IP on all Windows platforms and named pipes on NT. The default
is to use named pipes for local connections on NT and TCP/IP for all other cases if the
client has TCP/IP installed. The host name species which protocol is used:
Host name Protocol
NULL (none) On NT, try named pipes rst; if that doesn't work, use
TCP/IP. On Win95/Win98, TCP/IP is used.
. Named pipes
localhost TCP/IP to current host
hostname TCP/IP
You can force a MySQL client to use named pipes by specifying the --pipe option or by
specifying . as the host name. Use the --socket option to specify the name of the pipe.

You can test whether or not MySQL is working by executing the following commands:

C:\mysql\bin\mysqlshow
C:\mysql\bin\mysqlshow -u root mysql
C:\mysql\bin\mysqladmin version status proc
C:\mysql\bin\mysql test
If mysqld is slow to answer to connections on Win95/Win98, there is probably a problem with your DNS. In this case, start mysqld with --skip-name-resolve and use only
localhost and IP numbers in the MySQL grant tables. You can also avoid DNS when
connecting to a mysqld-nt MySQL server running on NT by using the --pipe argument
to specify use of named pipes. This works for most MySQL clients.
There are two versions of the MySQL command-line tool:
mysql Compiled on native Windows, which oers very limited text editing capabilities.
mysqlc Compiled with the Cygnus GNU compiler and libraries, which
oers readline editing.
If you want to use mysqlc.exe, you must copy `C:\mysql\lib\cygwinb19.dll' to
`\windows\system' (or similar place).
The default privileges on Windows give all local users full privileges to all databases. To
make MySQL more secure, you should set a password for all users and remove the row in
the mysql.user table that has Host='localhost' and User=''.
You should also add a password for the root user. (The following example starts by
removing the anonymous user, that allows anyone to access the 'test' database.):
C:\mysql\bin\mysql mysql

mysql> DELETE FROM user WHERE Host='localhost' AND User='';
mysql> QUIT
C:\mysql\bin\mysqladmin reload
C:\mysql\bin\mysqladmin -u root password your_password

Read More

LOAD DATA INFILE statement MySQL

LOAD DATA INFILE provides an alternative to INSERT for adding new
records to a table. With INSERT, you specify data values directly in the INSERT statement.
 LOAD DATA INFILE reads the values from a separate datafile.

The simplest form of the LOAD DATA INFILE statement specifies only the name of the datafile
 and the table into which to load the file:


LOAD DATA INFILE 'file_name' INTO TABLE table_name;


The filename is given as a string and must be quoted. MySQL assumes, unless told otherwise,
 that the file is located on the server host, that it has the default file format
tab-delimited  and newline-terminated lines, and that each input line contains a value
 for each column in the table. However, LOAD DATA INFILE has clauses that give you control
 over each of those aspects of data-loading operations and more:

Which table to load

The name and location of the datafile

Which columns to load

The format of the datafile

How to handle duplicate records

Whether to ignore lines at the beginning of the datafile

The syntax for LOAD DATA INFILE is as follows, where optional parts of
 the statement are indicated by square brackets:


LOAD DATA [LOCAL] INFILE 'file_name'

    [IGNORE | REPLACE]

    INTO TABLE table_name

    format_specifiers

    [IGNORE n LINES]

    [(column_list)]




The following sections explain how the various parts of the statement work.

Specifying the Datafile Location
LOAD DATA INFILE can read datafiles that are located on the server host
 or on the client host:

By default, MySQL assumes that the file is located on the server host.
The MySQL server reads the file directly.

If the statement begins with LOAD DATA LOCAL INFILE rather than with LOAD DATA INFILE,
 the file is read from the client host on which the statement is issued. In other words
, LOCAL means local to the client host from which the statement is issued. In this case,
 the client program reads the datafile and sends its contents over the network to the server.

The rules for interpreting the filename are somewhat different for the server
host and the client host.

Specifying the Location of Files on the Server Host
Without LOCAL in the LOAD DATA INFILE statement, MySQL looks for the datafile
 located on the server host and interprets the pathname as follows:

If you refer to the file by its full pathname, the server looks for the file
 in that exact location.

If you specify a relative name with a single component, the server looks for
the file in the database directory for the default database. (This isn't necessarily
the database that contains the table into which you're loading the file.)

If you specify a relative pathname with more than one component, the server
 interprets the name relative to its data directory.

Suppose that the server's data directory is /var/mysql/data, the database directory
 for the test database is /var/mysql/data/test, and the file data.txt is located in
that database directory. Using the filename interpretation rules just given, it's
possible to refer to the data.txt file three different ways in a LOAD DATA INFILE statement:

You can refer to the file by its full pathname:


LOAD DATA INFILE '/var/mysql/data/test/data.txt' INTO TABLE t;


If test is the default database, you can refer to a file in the database directory
using just the final component of its pathname:



LOAD DATA INFILE 'data.txt' INTO TABLE t;




You can refer to any file in or under the server's data directory by its
 pathname relative to that directory:

LOAD DATA INFILE './test/data.txt' INTO TABLE t;


Specifying the Location of Files on the Client Host
If you use LOCAL to read a datafile located on the client host, pathname
interpretation is simpler:

If you refer to the file by its full pathname, the client program looks for
 the file in that exact location.

If you specify a relative pathname, the client program looks for the file relative
 to its current directory. Normally, this is the directory in which you invoked the program.

Suppose that there's a datafile named data.txt located in the /var/tmp directory
on the client host and you invoke the mysql program while located in that directory.
 You can load the file into a table t using either of these two statements:


LOAD DATA LOCAL INFILE '/var/tmp/data.txt' INTO TABLE t;

LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE t;


The first statement names the file using its full pathname. The second names the file relative
 to the current directory. If you invoke the mysql program in the /var directory instead, you
 can still load the file using the same full pathname. However, the relative pathname to the
 file is different than when running the program in the /var/tmp directory:


LOAD DATA LOCAL INFILE 'tmp/data.txt' INTO TABLE t;




Specifying Filenames on Windows
On Windows, the pathname separator character is \, but MySQL treats the backslash as
 the escape character in strings. To deal with this issue, write separators in Windows
 pathnames either as / or as \\. To load a file named C:\mydata\data.txt, specify the filename
 as shown in either of the following statements:






LOAD DATA INFILE 'C:/mydata/data.txt' INTO TABLE t;

LOAD DATA INFILE 'C:\\mydata\\data.txt' INTO TABLE t;

 Loading Specific Table Columns
By default, LOAD DATA INFILE assumes that data values in input lines are present
 in the same order as the columns in the table. If the datafile contains more columns
 than the table, MySQL ignores the excess data values. If the datafile contains too
few columns, each missing column is set to its default value in the table. This is the same
way MySQL handles columns that aren't named in an INSERT statement.


If input lines don't contain values for every table column, or the data values are not in
the same order as table columns, you can add a comma-separated list of column names within
 parentheses at the end of the LOAD DATA INFILE statement. This tells MySQL how columns in
 the table correspond to successive columns in the datafile.

Read More

Linking Affects the Speed of MySQL

Most of the following tests are done on Linux with the MySQL benchmarks, but they should
give some indication for other operating systems and workloads.
You get the fastest executable when you link with -static.
On Linux, you will get the fastest code when compiling with pgcc and -O6. To compile
`sql_yacc.cc' with these options, you need about 200M memory because gcc/pgcc needs
a lot of memory to make all functions inline. You should also set CXX=gcc when con guring
MySQL to avoid inclusion of the libstdc++ library (it is not needed). Note that with some
versions of pgcc, the resulting code will only run on true Pentium processors, even if you
use the compiler option that you want the resulting code to be working on all x586 type
processors (like AMD).

By just using a better compiler and/or better compiler options you can get a 10-30 % speed
increase in your application. This is particularly important if you compile the SQL server
yourself !
We have tested both the Cygnus CodeFusion and Fujitsu compilers, but when we tested
them, neither was su ciently bug free to allow MySQL to be compiled with optimizations
on.
When you compile MySQL you should only include support for the character sets that you
are going to use. (Option --with-charset=xxx).


1If you use pgcc and compile everything with -O6, the mysqld server is 1% faster than
with gcc 2.95.2.
2 If you link dynamically (without -static), the result is 13% slower on Linux. Note
that you still can use a dynamic linked MySQL library. It is only the server that is
critical for performance.
3 If you connect using TCP/IP rather than Unix sockets, the result is 7.5% slower on
the same computer. (If you are connection to localhost, MySQL will, by default, use
sockets).
4 If you compile with --with-debug=full, then you will loose 20 % for most queries,
but some queries may take substantially longer (The MySQL benchmarks ran 35 %
slower) If you use --with-debug, then you will only loose 15 %.
5 On a Sun SPARCstation 20, SunPro C++ 4.2 is 5 % faster than gcc 2.95.2.
6 Compiling with gcc 2.95.2 for ultrasparc with the option -mcpu=v8 -Wa,-xarch=v8plusa
gives 4 % more performance.
7 On Solaris 2.5.1, MIT-pthreads is 8-12% slower than Solaris native threads on a single
processor. With more load/CPUs the di erence should get bigger.
8 Running with --log-bin makes [MySQL 1 % slower.
9 Compiling without frame pointers -fomit-frame-pointer with gcc makes MySQL 1
% faster.
The MySQL-Linux distribution provided by MySQL AB used to be compiled with pgcc,
but we had to go back to regular gcc because of a bug in pgcc that would generate the
code that does not run on AMD.

Read More

SQL inherently

SQL, or Structured Query Language, is not really a language. It does not follow all the rules of the typical
 computer programming language. There are no constructs in SQL to allow decision branches.
You can't go to a specific piece of code or execute that code if a decision has been made.

On the other hand, SQL is ideal for getting data out of a database using complicated rules.
If you team SQL with other languages such as PHP or C/C++, the combination is very powerful.
 With this kind of combination, you can accomplish any programming task.

SQL is inherently simple. The most commonly used commands in SQL are CREATE, DROP, GRANT, INSERT, SELECT, and UPDATE. The modifiers for most of these commands are WHERE, AND, OR, LIKE, GROUP BY, or ORDER BY.
A few helper functions such as, but not limited to, COUNT(), AVG(), MIN(), MAX(), and SUM() also exist.

After you have mastered this list of commands, you are ready to use SQL for most tasks. In almost all cases,
 by using some thought, complex statements can be boiled down to simpler statements. Very rarely do you require some of the more complex constructs.

MySQL Implements a Subset of SQL
Not all ANSI SQL features are implemented in MySQL. As of the 3.22 version of MySQL, the following
features are left out:



In VARCHAR columns, trailing spaces are removed when stored in the database.

In some cases CHAR columns are changed to VARCHAR columns without notification.

Privileges stay after a table is deleted. They must be explicitly revoked using the REVOKE command.
 This is because privileges are stored in the mysql database tables.

NULL and FALSE both evaluate to NULL.

MySQL 3.22 doesn't support transactions. However, it does do its work using "atomic operations."
 The authors of MySQL believe this provides "equal or better integrity," with better performance.
 MySQL version 3.23 is having transactions added to it.


You can simulate COMMIT and ROLLBACK. A COMMIT on an operation is a way of saying all the conditions are
 good, store the data. To do this in a multiuser environment, you LOCK the tables to prevent any other user
 from changing them. You then make your changes and test all the conditions.


The MySQL server version 3.22 and below is under the MySQL Free Public license, which is applicable
 to non-Microsoft platforms, and is included at the end of this book. It is a very liberal license,
 requiring payment under limited circumstances.

Some parts of the MySQL tools are under the GNU public license. Some parts of the MySQL tools are
 in the public domain. The MySQL server itself is not under the GNU license or in the public domain.
 On Microsoft platforms, the license for version 3.22 and below is a different license and requires
payment after a trial period. Check their Web site for details concerning the Microsoft licensing details.

MySQL server version 3.23 is now released under the GPL license. Version 3.23 is still in alpha as of
this writing, but should be released soon. It is in your best interest to upgrade to version 3.23 as
soon as it is stable because the GPL license is less restrictive than the MySQL license.

Read More

Maximum Performance from MySQL

Optimization is a complicated task because it ultimately requires understanding of the whole
system. While it may be possible to do some local optimizations with small knowledge of
your system/application, the more optimal you want your system to become the more you
will have to know about it.
So this chapter will try to explain and give some examples of di erent ways to optimize
MySQL. But remember that there are always some (increasingly harder) additional ways
to make the system even faster.
 Optimization Overview
The most important part for getting a system fast is of course the basic design. You also
need to know what kinds of things your system will be doing, and what your bottlenecks
are.
The most common bottlenecks are:
Disk seeks. It takes time for the disk to nd a piece of data. With modern disks in
1999, the mean time for this is usually lower than 10ms, so we can in theory do about
1000 seeks a second. This time improves slowly with new disks and is very hard to
optimize for a single table. The way to optimize this is to spread the data on more
than one disk.
Disk reading/writing. When the disk is at the correct position we need to read the
data. With modern disks in 1999, one disk delivers something like 10-20Mb/s. This is
easier to optimize than seeks because you can read in parallel from multiple disks.
CPU cycles. When we have the data in main memory (or if it already were there) we
need to process it to get to our result. Having small tables compared to the memory
is the most common limiting factor. But then, with small tables speed is usually not
the problem.
Memory bandwidth. When the CPU needs more data than can t in the CPU cache
the main memory bandwidth becomes a bottleneck. This is an uncommon bottleneck
for most systems, but one should be aware of it.

Read More

Why NoSQL?

Application requirements have fundamentally changed, and traditional RDBMS technology has failed to keep pace. As a result, the use of NoSQL technology is rising rapidly. Massive change is underway, disrupting the database status quo. Download this white paper to learn the megatrends affecting the database industry, how the convergence of these trends is creating new requirements for data management, and why NoSQL databases are better equipped to meet these requirements.

Mobile apps are all the rage these days but it's easy to get caught up in all the bells and whistles which you can use and lose sight of the core design process. In this you'll learn about seven design mistakes to avoid when creating apps.

Read More

Web-Based MySQL Administration

At http://phpwizard.net/phpMyAdmin you will find a PHP Web application that can help you manage MySQL. It enables you to manage a MySQL database using a browser and makes administrative work much simpler.

BlueSQLFree web based Database management. 



After you have installed PHP and MySQL, you are ready to install phpMyAdmin. The work done while preparing for IMP has also prepared the system for phpMyAdmin. You do have to edit one of the . php3 files in phpMyAdmin to enter the MySQL database name and password.
With phpMyAdmin, you can create and drop databases and tables. You can also add and remove fields and perform other administrative chores.

Navicatanother option,
I have found phpMyAdmin to be very useful when trying to create or delete tables. After you have filled out a form that describes the table, it generates the SQL for you. You can use this information to create script files for later use.
For now, phpMyAdmin has some bugs. I have had it crash on me several times. However, its usefulness is good enough for me to ignore the occasional crash.
From a security point of view, phpMyAdmin is terrible. After it is installed, everyone who uses that Web page has unrestricted access to the MySQL database. I strongly recommend that you do not put your server on the Internet with phpMyAdmin active. If you do, you must configure your Web server to serve only the phpMyAdmin Web pages to your local network. You should also protect the Web pages with a password. Be careful!

Adminer

Open Source db manage tool.

HeidiSQL

Open Source 

HeidiSQL is a  Windows based interface for MySQL databases

Read More

MySQL Server Performance

To make things  easier for administrators.

• Minimal memory, relatively infrequent MySQL usage: my-small.cnf
• Minimal memory combined with reliance on MySQL, or larger memory for a multipurpose system: my-medium.cnf
• Large memory server dedicated primarily to MySQL: my-large.cnf
• Very large memory server completely dedicated to MySQL: my-huge.cnf

It's worth your time to review these files, if for no other reason than to gain some ideas on the optimal ratios between the various server settings.

• Connectivity
• Memory management
• Application control
• User resource control

Read More

Query Strings

Query string data is very easy for the user to alter, because it ’ s visible and editable within the browser ’ s
address bar. Therefore, query strings should be used only in situations where sending incorrect data
won ’ t compromise security.

You also need to make sure you don ’ t rely on query strings to authenticate users, because people often
send URLs to friends in emails or instant messaging applications. If your URL contains all the data
needed to authenticate a user, and that user sends the URL to a friend, then the friend can pretend to be
them! You ’ ll find that sessions — discussed later in the chapter — are a much better way of authenticating
users.
If you ’ ve worked your way through Chapter 9 , you ’ re already somewhat familiar with the concept of
query strings. You ’ ll remember that you can embed sent form data in a URL by setting the form ’ s
method attribute to get . When the form data is sent to the server.

http://localhost/iscript.php?firstname=Tred & lastname=Fish& ...

the browser adds a query ( ? ) character to the end of the URL, then follows it with each
of the form fields as “name=value” pairs, with each pair separated by an ampersand ( & ).

Read More

Conditional Functions mysql

The IF() Function

The IF() function provides a way to return a value based on a condition within a query.

The first argument is a condition that is evaluated for each row of the query. The value in the second is returned if the condition is trUE, and the third argument is returned if it is FALSE.

The following example is a very simple shipping rate calculator. If the product weight is less than 5 pounds, shipping costs $1.99; otherwise, it costs $2.99.

mysql> SELECT code, weight, IF(weight < 5, 1.99, 2.99)
    -> FROM products;
+------+--------+------------------------------+
| code | weight |    IF(weight <5, 1.99, 2.99) |
+------+--------+------------------------------+
| MINI |   1.50 |                         1.99 |
| MIDI |   4.50 |                         1.99 |
| MAXI |   8.00 |                         2.99 |
+------+--------+------------------------------+
3 rows in set (0.00 sec) 

The CASE Statement

The CASE statement is a multiple-valued conditional construct. Suppose you wanted to set three or more shipping rates based on weight. This would require a complex series of nested IF() functions.

The following example uses a CASE statement to determine the shipping rate based on three different weight bands:

mysql> SELECT code, weight,
    ->        CASE WHEN weight < 2 THEN 1.99
    ->             WHEN weight < 5 THEN 2.99
    ->             ELSE 4.99 END as shipping
    -> FROM products; 
+------+--------+----------+
| code | weight | shipping |
+------+--------+----------+
| MINI |   1.50 |     1.99 |
| MIDI |   4.50 |     2.99 |
| MAXI |   8.00 |     4.99 |
+------+--------+----------+
3 rows in set (0.00 sec)

Read More

Combined Queries-UNION

Mosy SQL queries contain just one SELECT statement that can return data from one table, or several tables using a join. The technique of combining two or more independent queries into a single data set is usually known as a union or a compound query.

You might want to use this technique to retrieve records from two tables that have a similar structure in a single query. For instance, suppose you have archived off some data so that you have a customers table that contains your current customers and another table called old_customers.

These tables would have the same structureor at least would share many common columns if new columns had been added to the customers table since the archive took place. Therefore, you could perform a query on this table that takes into account both current and archived customers. This would look something like the following:

SELECT name, telephone, email
FROM customers
UNION
SELECT name, telephone, email
FROM old_customers;

You can also use UNION to perform two different queries on the same table and combine the two results into a single data set.

mysql> SELECT first_name, last_name
    -> FROM customer_contacts
    -> WHERE customer_code = 'SCICORP'
    -> UNION
    -> SELECT first_name, last_name
    -> FROM customer_contacts
    -> WHERE customer_code = 'PRESINC';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Albert     | Einstein  |
| Charles    | Darwin    |
| Marie      | Curie     |
| Benjamin   | Franklin  |
| Abraham    | Lincoln   |
| Richard    | Nixon     |
| Franklin   | Roosevelt |
| Theodore   | Roosevelt |
+------------+-----------+
8 rows in set (0.01 sec)

Read More

Data Control Language (DCL) statements ?

• Data Control Language (DCL) statements  Allow you to change the permissions on database structures. There are two DCL statements:
  • GRANT  Allows you to give another user access to your database structures, such as tables.
  • REVOKE  Allows you to prevent another user from accessing to your database structures, such as tables.

Read More