Practical Queries in MySQL



Share

Creating a Table


Let’s assume that you are working for a wildlife park and need to create a database to hold details about all the types of cats it houses. You are told that there are nine families of cats—Lion, Tiger, Jaguar, Leopard, Cougar, Cheetah, Lynx, Caracal, and Domestic—so you’ll need a column for that. Then each cat has been given a name, so that’s another column, and you also want to keep track of their ages, which is another. Of course, you will probably need more columns later, perhaps to hold dietary requirements, inoculations, and other details, but for now that’s enough to get going. A unique identifier is also needed for each animal, so you also decide to create a column for that, called id.


   <?php
   require_once 'login.php';
   $db_server = mysql_connect($db_hostname, $db_username, $db_password);
    if (!$db_server) die("Unable to connect to MySQL: " . mysql_error());
     mysql_select_db($db_database) 
     or die("Unable to select database: " . mysql_error());
 
       $query = "CREATE TABLE cats (  
            id SMALLINT NOT NULL AUTO_INCREMENT, 
           family VARCHAR(32) NOT NULL,     
           name VARCHAR(32) NOT NULL,
           age TINYINT NOT NULL,  
           PRIMARY KEY (id) 
          )";
      
       $result = mysql_query($query);
       if (!$result) die ("Database access failed: " . mysql_error()); 
     ?>
 

Describing a Table


When you aren’t logged in to the MySQL command line, there’s a handy piece of code that you can use to verify that a table has been created correctly from inside a browser. It simply issues the query DESCRIBE tablename and then outputs an HTML table with four headings: Column, Type, Null, and Key, underneath which all columns within the table are shown. To use the code in below Example with other tables, simply replace the name cats in the query with that of the new table.

     <?php
     require_once 'login.php'; 
     $db_server = mysql_connect($db_hostname, $db_username, $db_password);
     if (!$db_server) die("Unable to connect to MySQL: " . mysql_error());
     mysql_select_db($db_database)  
     or die("Unable to select database: " . mysql_error());
     
     $query = "DESCRIBE cats"; 
     
     $result = mysql_query($query);
     if (!$result) die ("Database access failed: " . mysql_error());
     $rows = mysql_num_rows($result);
     echo "<table>";
     for ($j = 0 ; $j < $rows ; ++$j)
     { 
     $row = mysql_fetch_row($result);  
     for ($k = 0 ; $k < 4 ; ++$k) echo "$row[$k]";  
      }
     echo "</table>"; 
    ?>



Dropping a Table


        <?php
        require_once 'login.php';
        $db_server = mysql_connect($db_hostname, $db_username, $db_password);
        if (!$db_server) die("Unable to connect to MySQL: " . mysql_error()); mysql_select_db($db_database)  
        or die("Unable to select database: " . mysql_error());
        
     $query = "DROP TABLE cats";
    
     $result = mysql_query($query); 
     if (!$result) die ("Database access failed: " . mysql_error()); 
         ?>
    



Adding Data

       <?php
       require_once 'login.php';
       $db_server = mysql_connect($db_hostname, $db_username, $db_password);
       if (!$db_server) die("Unable to connect to MySQL: " . mysql_error()); mysql_select_db($db_database) 
       or die("Unable to select database: " . mysql_error());
       
       $query = "INSERT INTO cats VALUES(NULL, 'Lion', 'Leo', 4)";

        $result = mysql_query($query);
       if (!$result) die ("Database access failed: " . mysql_error());
       ?>



Updating Data


        <?php
        require_once 'login.php';
        $db_server = mysql_connect($db_hostname, $db_username, $db_password); 
        if (!$db_server) die("Unable to connect to MySQL: " . mysql_error()); mysql_select_db($db_database) 
        or die("Unable to select database: " . mysql_error());
        
       $query = "UPDATE cats SET name='Charlie' WHERE name='Charly'";

          $result = mysql_query($query);
           if (!$result) die ("Database access failed: " . mysql_error()); 
        ?> 
    



Deleting Data


    <?php
    require_once 'login.php'; 
    $db_server = mysql_connect($db_hostname, $db_username, $db_password);
    if (!$db_server) die("Unable to connect to MySQL: " . mysql_error()); mysql_select_db($db_database) 
    or die("Unable to select database: " . mysql_error());
    
    $query = "DELETE FROM cats WHERE name='Growler'";

       $result = mysql_query($query);
      if (!$result) die ("Database access failed: " . mysql_error()); 
    ?>


Using AUTO_INCREMENT


    <?php
    require_once 'login.php';
    $db_server = mysql_connect($db_hostname, $db_username, $db_password);
    if (!$db_server) die("Unable to connect to MySQL: " . mysql_error()); mysql_select_db($db_database)  
    or die("Unable to select database: " . mysql_error());
    
     $query = "INSERT INTO cats VALUES(NULL, 'Lynx', 'Stumpy', 5)";

      $result = mysql_query($query);
        echo "The Insert ID was: " . mysql_insert_id();
       if (!$result) die ("Database access failed: " . mysql_error()); 
    ?>