Selecting from the database

How to get data out of the database

OK, that is great. You have a database running. You have managed to define variables. You can get data to and from forms, and into a database. But what good is data in a database if you cannot get it out of the database!? So here goes. A basics on data extraction. In other tutorials we will deal with the more advanced things you can do, like linking tables using the join command, retrieving random datasets, and subsetting your data using links. For now, let us look at the basic data-retrieval syntax. You want to select something, from somewhere, limit the dataset to some subset, and order the data in some way. In the insert data tutorial you have seen how to connect to your database. So we will skip that, and go straight to getting the data from the database.

1
2
3
4
5
6
7
8
9
10
11
12
<? 
$select_query = "Select * from my_first_table 
    where M_id > 0
    order by M_id ASC
    limit 0 5";
$result = mysql_query($select_query) 
  or die("It was not possible to get the links from the database");
if($result)
  {
  // Do something!
  }
?>
<? 
$select_query = "Select * from my_first_table 
    where M_id > 0
    order by M_id ASC
    limit 0 5";
$result = mysql_query($select_query) 
  or die("It was not possible to get the links from the database");
if($result)
  {
  // Do something!
  }
?>

So, what did we do; The query defined: We select all colums (*) from my_first_table where the colum M_id is larger then 0, we order the output by the colum M_id in ascending order, and we retrieve records 0-5 which fall within this select set. The next step is to actually get the values. The $result variable is actually just a pointer array. In other words: It is a list of records which were selected. It does not contain the values. So we need to go ahead, and retrieve the records:

1
2
3
4
5
6
<? 
if($results)
  {
  while($row=mysql_fetch_array($results))
    {
    echo "
<? 
if($results)
  {
  while($row=mysql_fetch_array($results))
    {
    echo "

$row['M_name'] $row['M_description']

1
2
3
4
"; 
    }
  }
?>
"; 
    }
  }
?>

Basically, what this does is: As long as you can retrieve a new set of data from the selected data (while($row=mysql_fetch_array($result))), echo out the values for M_url and M_name, in the syntax for a weblink, and add the M_description after the link. Basically, it creates a series of links, as can be seen in my weblinks section, which is created using this system. When taken all together, we get the following script:

1
2
3
4
5
6
7
8
9
10
11
12
13
<? 
$select_query = "
  Select * from my_first_table 
    where M_id > 0
    order by M_id ASC
    limit 0 5";
 
$result = mysql_query($select_query) or die("It was not possible to get the links from the database");
if($results)
  {
  while($row=mysql_fetch_array($results))
    {
    echo "
<? 
$select_query = "
  Select * from my_first_table 
    where M_id > 0
    order by M_id ASC
    limit 0 5";

$result = mysql_query($select_query) or die("It was not possible to get the links from the database");
if($results)
  {
  while($row=mysql_fetch_array($results))
    {
    echo "

$row['M_name'] $row['M_description']

1
2
3
4
"; 
    }
  }
?>
"; 
    }
  }
?>

Leave a Reply