Developpement Informatique

mars 11, 2010

MYSQL Fonction/ Function

Filed under: MYSQL — izaam01 @ 9:46

1) Date du jour

 AND   DATE_FORMAT(DATE_RAPPEL, ‘%Y-%m-%d’) = ‘ ». date(‘Y-m-d’)

mars 4, 2010

MySQL – Database Connections

Filed under: MYSQL — izaam01 @ 7:02

MySQL – Database Connections

PHP has inbuilt support for MySql databases. Before installing PHP, you should install MySql first, so that when PHP installs you can point it to your MySql installation otherwise it will use it’s default settings.

DB Connection

To connect to a MySql db you’ll need to use the function mysql_connect and host, username and password as part of the argument eg

$connection = mysql_connect(« hostname », »username », »password »);

You can use the die function to offer an alternative option if the connection fails eg

$connection = mysql_connect(« hostname », »username », »password ») or die(« Couldn’t connect to MySql »);

With the addition of using the @ symbol to before the function call will suppress warnings which would be passed on the the user eg

$connection = @mysql_connect(« hostname », »username », »password ») or die(« Couldn’t connect to MySql »);

Selecting the Database

Once connected you can use your connection to open a database with the function mysql_select_db eg

<?PHP
  $connection = mysql_connect("hostname","username","password") 
  or die("Couldn't connect to MySql");
  $myDB= "users";
  $db = mysql_select_db($myDB,$connection) or die("Couldn't 
  select database ");
?>

Queries

To run a query use mysql_query which takes a a SQL statement and a connection as the argument eg

<?PHP
  $connection = mysql_connect("hostname","username","password") 
  or die("Couldn't connect to MySql");
  $myDB= "users"; 
  $db = mysql_select_db($myDB,$connection) or die("Couldn't 
  select database ");
  $sql = "SELECT * FROM users;";
  $result = mysql_query($sql,$connection) or die("Couldn't 
  execute query. ");
?>

The records returned are placed in $result which is an array or recordset.

Reading Returned Records

To read the records returned from a query use mysql_fetch_array which takes the returned array as the argument eg

<?php
  $connection = mysql_connect("hostname","username","password") 
  or die("Couldn't connect to MySql");
  $myDB= "users";
  $connection = mysql_select_db($myDB,$connection) or 
  die("Couldn't select database ");
  $sql = "SELECT * FROM users;";
  $result = mysql_query($sql,$connection) or die("Couldn't 
  execute query. ");
  while ($row = mysql_fetch_array($result)){
  echo $row["firstname"];
  echo $row["lastname"];
  echo $row["email"];
  }
?>

MySQL – Inserting

Inserting Data

To insert data you would also use mysql_query eg

<?PHP
  $connection = mysql_connect("hostname","username","paswword") 
  or die("Couldn't connect to MySql");
  $myDB= "users"; 
  $connection = mysql_select_db($myDB,$connection) or 
  die("Couldn't select database ");
  $sql = "INSERT INTO users(firstname,lastname,email)VALUES($first,$last,$email);";
  $result = mysql_query($sql,$connection) or die("Couldn't 
  execute query. ");
?>

Unfortunately as SQL is written using english you’ll need to escape the values being written if held in a variable otherwise the insert statement will insert the variable name instead of the value eg

<?PHP
   $connection = mysql_connect("hostname","username","paswword") 
  or die("Couldn't connect to MySql");
  $myDB= "users";
  $connection = mysql_select_db($myDB,$connection) or 
  die("Couldn't select database ");
  $sql = "INSERT INTO users(firstname,lastname,email)VALUES(\"$first\",\"$last\",\"$email\");";
  $result = mysql_query($sql,$connection) or die("Couldn't 
  execute query. ");
?>

next

MySQL – Database Functions

You’ll find brief descriptions of the following functions helpful when working with a MySql database.

mysql_list_tables

Using this function will return a pointer eg

$tables = mysql_list_tables($mydb);

mysql_list_tablename

Using this function will return a table’s name from a database. It must be used in conjunction with mysql_list_tables to be able to extract the table name eg

$tables = mysql_list_tables($mydb);
for ($i < $mysql_num_rows ($tables)){
$tablename = mysql_tablename($tables,$i);
echo $tablename;
$i++;
}

mysql_list_fields

Using this function will return a pointer to list a table’s fields eg

$fieldinfo = mysql_list_fields($mydb,$tablename);

mysql_num_rows

Using this function will return the number of rows from a query eg

$numrows = mysql_num_rows($result);

mysql_num_fields

Using this function will return the number of fields in a result set eg

$numfields = mysql_num_fields($result);

mysql_field_name

Using this function will return the name of a field based on a result and an index number eg

$fieldname = mysql_field_name($result,2);

février 23, 2010

Update Multiple Rows With Different Values and a Single SQL Query

Filed under: MYSQL — izaam01 @ 5:17
April 30th, 2009 12:22 pm / Posted in SQL by Karl

Sometimes you may need to update multiple rows of data in a table. This is no problem a lot of the time, as the fields to update may all need to be changed to the same value, in which case you might run a query like the following.

1
UPDATE mytable SET myfield = ‘value’ WHERE other_field = ‘other_value’;

But what about if you want to update multiple rows but you need the field set to a different value for each row? For example, maybe you have a table of categories with a field to store their display order. How would you update the display order field for each category when the order changes? Most often you will see people just run multiple queries. For example, if you are using PHP you might think to do something like this:

1
2
3
4
foreach ($display_order as $id => $ordinal) {
    $sql = « UPDATE categories SET display_order = $ordinal WHERE id = $id »;
    mysql_query($sql);
}

Of course this will work fine, but queries in loops make me start to sweat nervously. It’s all to easy to start hammering the database with a crazy number of queries. Fortunately there is a better way! The SQL is a little more complex, but it is possible to achieve the same result using only one query. The syntax is as follows.

1
2
3
4
5
6
7
UPDATE mytable
    SET myfield = CASE other_field
        WHEN 1 THEN ‘value’
        WHEN 2 THEN ‘value’
        WHEN 3 THEN ‘value’
    END
WHERE id IN (1,2,3)

Mapping this to the categories example, we get the following query.

1
2
3
4
5
6
7
UPDATE categories
    SET display_order = CASE id
        WHEN 1 THEN 3
        WHEN 2 THEN 4
        WHEN 3 THEN 5
    END
WHERE id IN (1,2,3)

This is fairly simple to understand. Rather than setting a field to a particular value, a CASE operator is used to determine which of a set of values is used based on a given condition, in this case if the value of the id field matches the specified id.

The WHERE clause is not needed as such in that it doesn’t affect the logic of the query, but it does improve performance by ensuring that the logic is only applied to the smallest number of rows possible. In this example only 3 rows are being updated, and the WHERE clause ensures that only those 3 rows are tested. Without it the every row in the table would be tested (unnecessarily, since they will never match).

What about when you need to update multiple fields? This is easily done just by adding another CASE block.

1
2
3
4
5
6
7
8
9
10
11
12
UPDATE categories
    SET display_order = CASE id
        WHEN 1 THEN 3
        WHEN 2 THEN 4
        WHEN 3 THEN 5
    END,
    title = CASE id
        WHEN 1 THEN ‘New Title 1’
        WHEN 2 THEN ‘New Title 2’
        WHEN 3 THEN ‘New Title 3’
    END
WHERE id IN (1,2,3)

Now this is all well and good, but the real beauty of this comes when the technique is combined with a scripting language such as PHP in order to build these queries dynamically. Let’s examine one technique for doing this, using the category ordering example.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
// An array containing the category ids as keys and the new positions as values
$display_order = array(
    1 => 4,
    2 => 1,
    3 => 2,
    4 => 3,
    5 => 9,
    6 => 5,
    7 => 8,
    8 => 9
);

$ids = implode(‘,’, array_keys($display_order));
$sql = « UPDATE categories SET display_order = CASE id « ;
foreach ($display_order as $id => $ordinal) {
    $sql .= sprintf(« WHEN %d THEN %d « , $id, $ordinal);
}
$sql .= « END WHERE id IN ($ids) »;
echo $sql;

Now in this particular example, only 8 rows are being updated, but 7 queries have been trimed, which is not insignificant. Apply this technique to a situation where hundreds or thousands of rows have to be updated and you can imagine the benefits it will have.

Créez un site Web ou un blog gratuitement sur WordPress.com.