Skip to main content

Posts

Showing posts with the label mysql

Retrieving Data,Selecting a Database,Querying MySQL

Retrieving Data The process of retrieving data once a connection has been made to the MySQL server is similar to the process of interacting with the data through the mysql client. The first thing that needs to be addressed is the database that holds the table (which in turn holds the data). Selecting a Database In the mysql client, a database is identified (or selected) by the USE <database> command. In PHP, this is accomplished with a PHP MySQL API function called mysql_select_db(). This function identifies the database that should be used for any subsequent requests to the data, which eliminates the need to qualify every table (or other MySQL resource) called with the database name. There are two ways to use this function: • mysql_select_db($db_name, $link_id) – Using the mysql_select_db function in this fashion ensures that the resource that initiated the connection to the MySQL server would be selected with the $link_id. The variable, that is identified here

Independent Connection Information

For many programmers, the idea of storing connection information (such as username and password) directly in the script itself is a little unnerving and rightfully so. Even though, with the correct privileges assigned to the actual file housing the php script, there would be little concern for the scripts themselves from being seen. As a best practice, it is wise to create a separate PHP script file that would contain variables that would contain the connection information and could be used in the PHP script that is actually connecting to the MySQL server. A typical file containing this connection information would look something like the example below: <?php // MySQL Server Connection Information $dbhost = 'localhost'; $dbuser = 'root'; $dbpass = 'training'; ?> In this example, the file would be saved as "connect_info.php" and stored in the same folder as our php script (most likely the htdocs folder of the apache server). Using t

Putting it all together

The following example demonstrates an example of PHP connecting to the MySQL server, completing a query against the data contained in the MySQL server and then closing out the connection to the MySQL server (the other details of this script will be discussed throughout the remainder of this chapter): <?php /* Connect to MySQL server */ $linkID1 = mysql_connect('localhost','root','training') or die("Could not connect to MySQL server"); /* Query the MySQL Server for Information */ $query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'world'"; $result = mysql_query($query, $linkID1); while ($row = mysql_fetch_array($result)) { print $row[0]."<br>"; } /* Close the connection to the MySQL server */ mysql_close($linkID1); ?> Even though it is not necessary to provide the link identifier in the mysql_close() function seeing there is only one open connect; however, it is be

INSERT with LAST_INSERT_ID

The MySQL-specific option LAST_INSERT_ID(expr) can be used after an INSERT to   after the insert takes place; mysql> INSERT INTO City (name, countrycode) -> VALUES ('Sarah City', 'USA'); Query OK, 1 row affected (#.## sec) mysql> SELECT LAST_INSERT_ID(); +---------------------------+ | LAST_INSERT_ID() | +----------------------------+ | 4080                            | +-----------------------------+ If a table contains an AUTO_INCREMENT column and INSERT ... UPDATE inserts a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. If the statement updates a row  instead, LAST_INSERT_ID() is not meaningful. However, LAST_INSERT_ID(expr) can be used  to worked around this.

Temporal Expressions

Temporal values include dates, times, and date/time values that have both a date and time. More  specialized temporal types are timestamp (commonly used for recording “current date and time”) and year  (for temporal values that require a resolution only to year units). Direct use of temporal values in expressions occurs primarily in comparison operations, or in arithmetic  operations that add an interval to or subtract an interval from a temporal value. Otherwise, most temporal  value operations are performed by using functions. Temporal data may be generated via any of the following means: ô€‚ƒ Copying data from an existing date, datetime, or time column ô€‚ƒ Executing a built-in function that returns a date, datetime, or time column ô€‚ƒ Building a string representation of the temporal data to be evaluated by the server Date Components: Type                       Default Format DATE                        YYYY-MM-DD TIME                          HH:MM:SS DATETIME      

Using LIKE for Pattern Matching

Operators such as = and != are useful for finding values that are equal to or not equal to a specific exact  comparison value. When it's necessary to find values based on similarity instead, a pattern match is useful.  To perform a pattern match, use value LIKE 'pattern', where value is the value to test and  'pattern' is a pattern string that describes the general form of values to match. Patterns used with the LIKE pattern-matching operator can contain two special characters (called  “metacharacters” or “wildcards”) that stand for something other than themselves: • Percent (%) Character - The percent character matches any sequence of zero or more characters. For example, the pattern 'a%' matches any string that begins with 'a', '%b' matches any string  that ends with 'b', and '%c%' matches any string that contains a 'c'. The pattern '%' matches  any string, including empty strings. • Underscore

Case Sensitivity in String Comparisons

String comparisons are somewhat more complex than numeric or temporal comparisons. A letter in  uppercase may compare as the same or different than the same letter in lowercase, and a letter with one  type of accent may be considered the same or different than that letter with another type of accent. • Character Sets and Collations - String expressions contain characters from a particular character  set, which is associated with one of the collations (sorting orders) available for the character set. Characters may consist of single or multiple bytes. A collation can be case insensitive (lettercase  is not significant) or case sensitive (lettercase is significant). The rules that govern string comparison apply in several ways. They determine the result of comparisons  performed explicitly with operators such as = and <, and comparisons performed implicitly by ORDER BY, GROUP BY, and DISTINCT operations. The default character set and collation for literal strings depend on the val

Altering Tables ( Drop Tables )

DROP TABLE removes one or more tables. All table data and the table definition are  removed, so  be careful with this statement!   DROP [TEMPORARY] TABLE [IF EXISTS] table1;  Use IF EXISTS to prevent an error from occurring for tables that do not exist. An error is generated for each non-existent table when using IF EXISTS. The TEMPORARY keyword has the following effects:               1.  The statement drops only TEMPORARY tables.               2.  The statement does not end an ongoing transaction Using TEMPORARY is a good way to ensure that non-TEMPORARY table is not deleted by accident. DROP TABLE examples:  DROP TABLE table1;  DROP TABLE IF EXISTS table1;  DROP TEMPORARY TABLE eu_countries_temp; 

Altering Tables ( Remove Columns )

To drop a column, use a DROP clause that names the column to be removed: ALTER TABLE eu_countries DROP id; The above ALTER TABLE/DROP statement changes the table structure as follows: +----------------+---------------+------+-----+---------+-------+ | Field                 | Type                | Null   | Key | Default    | Extra   | +----------------+---------------+------+-----+---------+-------+ | name                 | char(52)          | NO   |          | NULL    |           | | new_population | decimal(12,1)  | NO   |          | 0.0         |           | +----------------+---------------+-----+------+---------+-------+

MYSQL FOUNDATIONS

MySQL server   What is the MySQL Server? MySQL is a relational database that can be used to store dynamic Web page data for services such as online catalogs and stores, create accounting databases, and create address books. MySQL is an advanced feature for users that are familiar with databases and SQL (structured query language). For more information, go to  http://www.mysql.com . If the MySQL server is enabled, users on the internal network can access personal databases and the databases of any teams to which they belong. User and team databases are automatically created when user and team accounts are set up. Setting up Windows for MySQL Access You can use Microsoft (R)  Access to access and manage database tables. 1.        You first have to download the MySQL ODBC (Open Database Connectivity) connector. You can download this at  http://dev.mysql.com/downloads/connector/ .  2.        On the page that is displayed, click the link for the  Connector/ODBC . Ensure y

MySQL INTRODUCTON

   So after learning HTML lets go into the working part     lets first see what is MySQL and then go to php              MySQL is installed on every continent in the world (Yes, even Antarctica!) MySQL AB MySQL is a relational Database Management System (RDBMS). MySQL AB develops and markets a family of high performance, affordable database servers and tools. Contributing to building the mission-critical,high-volume systems and products worldwide is what makes MySQL the world’s most popular open source database, as well as its reliability, excellent performance and ease of use. MySQL is not only the world’s most popular open source database, it’s also the fastest growing database in the industry, with more than 11 million active installations and 50,000 downloads per day. The company was founded in Sweden by two Swedes and a Finn: David Axmark, Allan Larsson and Michael "Monty" Widenius who have worked together since the 80's. MySQL AB(Swedish for “Inc.”) is the sole

website introduction

Get started now – no experience required! How-to-build-websites.com is designed for total beginners … and for people who use programs like Dreamweaver, FrontPage, and other wysiwyg programs (wysiwyg stands for: “What You See Is What You Get”) … and now want to learn what’s going on ‘behind the scenes’.