March 11, 2008

MySQL basic query

Connecting to the Database
Before we can connect to the database, we need four things:
❑ Database name
❑ Host Server name
❑ Username
❑ Password

Connect to the database using the following command (in PHP):

$connection = mysql_connect(“servername”, “username”, “password”);

We then need to select the appropriate database by using the following command (in PHP):

$database = mysql_select_db(“databasename”, $connection)
or die(“couldn’t find the database”);
?>

Accessing the Database
MySQL commands are inserted within our PHP code to access the database in this way:

$query = mysql_query(“UPDATE field1 FROM tablename WHERE condition1”)
or die(“Couldn’t find the table”);
$result = mysql_fetch_array($query);
?>

Retrieving Data from the Database
We can access the data stored in our tables with the following statement (we can use * to retrieve all fields):

SELECT field1, field2 FROM tablename

Condition Clauses
Use the following conditions in conjunction with this statement:

SELECT * FROM tablename WHERE

Conditions (use % for wildcard):
field = value
field > value
field < value
field >= value
field <= value
field != value (field is not equal to value)
field <> value (field is not equal to value)
field BETWEEN value1 AND value2
field NOT BETWEEN value1 AND value2
field LIKE value
field NOT LIKE value
field IS NULL
field IS NOT NULL
field IN (value1, value2, value3, etc)
field NOT IN (value1, value2, value3, etc)

Selecting from Multiple Tables
We can retrieve information from two or more tables at once by using the following statements:

SELECT table1.field, table2.field FROM table1, table2 WHERE
table1.field = table2.field;
or
SELECT table1field, table2field FROM table1 LEFT JOIN table2 ON
table1.table1field=table2.table2field;

Sorting the Results
We can sort the results of the SELECT query by using the following clause at the end of the statement (and the optional ascending or descending qualifier):

SELECT * FROM tablename WHERE field1=value1 ORDER BY field2 ASC|DESC

Limiting the Results
If we would like to limit the results returned from our query, we can modify our SELECT statement like this:

SELECT * FROM tablename WHERE field1=value1
ORDER BY field2 ASC
LIMIT offset, number_of_rows_to_be_returned

Share

0 comments:

Blogger template 'PlainFish' by Ourblogtemplates.com 2008