Once you've got some records in your database, you might want to get, or select, them!
So how do we do that?
Select a record(s) from a MySQL Database:
<?PHP
As with all actions relating to a MySQL Database, we have to use the mysql_query() function.
include_once('DBConnect.php');
$query=mysql_query("select name, email, address from table_name where name='$name' limit 1");
?>
Anything between the quote marks is what we want to do.
select
This tells the database that we're selecting, or getting, a record(s)
email, address, phone
This tells the database which fields/columns we want data from.
table_name
This tells the database which table we'll find the record in.
where name='$name'
The where clause tells MySQL which record(s) inside table_name to select data from. Without this, Mysql would select data from all the records it finds!!!
In this case, we're selecting the email, address & phone fields for any record where the name field matches whatever value is stored in $name.
limit 1
This tells MySQL to select only one record.
In our example, there should only be one record for each name, so the limit clause isn't really needed. However, if your database has more than one John Smith, and you only want to select the first one MySQL finds, then limit 1 would do that. However, if you had more than one John Smith and you need to slect a particular one, then you might need more than one condition in the where clause, like so: where name='$name' and address='$address'.
Another advantage of the limit clause... is that it's quicker! Imagine you have a database with 1000 records, and you tell it to select the record belonging to John Smith, and let's assume there is only one. Without the limit clause, MySQL would scan the database for a record with the name of John Smith and grabs it's data when it finds it. MySQL would then carry on looking to see if there are any other records for John Smith... and that takes time! Using limit 1 tells MySQL that as soon as it's found & selected the data for 1 record... to stop looking and finish!
If you want to select more than one record from the same table at the same name, simply remove the limit clause, or increase the limit:
<?PHP
The first select would return every record it finds where the name field match $name.
include_once('DBConnect.php');
$query=mysql_query("select name, email, address from table_name where name='$name'");
$query=mysql_query("select name, email, address from table_name where name='$name' limit 10");
?>
The second select would return upto 10 records it finds where the name field matches $name. If there are only 5 records that match, you will only get 5 records. if there are 12 records, you'll get 10. The limit clause is an upper limit!!!
If you want to select only a certain number of records, but would like to know if there are any more... so you could provide a link to Page 2, for example, then you need to do 2 queries:
<?PHP
Let's assume we've requested this webpage with this URl: "http://www.mywebsite.com/select.php;
include_once('DBConnect.php');
$max=10;
if($_GET['pg']==''){ $pg=1; } else { $pg=$_GET['pg']; }
$from=($pg*$max)-$max;
$query=mysql_query("select name, email, address from table_name where name='$name' limit $from, $max");
$get_total=mysql_query("select count(*) table_name where name='$name'");
$total=mysql_result($get_total,0);
if($pg<($total/$max)){ $next_page='<a href="http://www.mywebsite.com/select.php?pg='.($pg+1).'">Page 2</a>'; }
?>
Inside the select.php page, we assign a variable $max which is the maximum number records we want MySQL to select.
We then check to see if a page number has been requested in the URL (does it include pg=). In this case it doesn't, so we assume this is page 1 and so set $pg to 1. We calculate where MySQL should start looking by setting $from to ($pg*$max)-$max.
MySQL then performs 2 queries. The first query starts looking at record number 0 ($from=0), and grabs the next 10 records ($max=10) that matches our where clause from the table called table_name (so MySQL selects records 1 to 10). The second query counts all records in table_name that matches our where clause.
We then divide the total number of records by how many records we show per page ($total/$max). If there are 60 records founds (so $total=60), and we only want to show 10 records at a time ($max=10), then the result of this division would equal 6... so we know we'd have to display 6 pages to show all 60 records.
We check to see if the current page we're on now ($pg=1) is less than this number ($total=6), and if so we know there are more pages to display. In our simple example above, we're only generating a link to the next page, but you might want to including a link to all 6 pages!
If we were to follow the link generated by this first query, we'd be accessing the same webpage, but this time with a pg= appended to the URL: "http://www.mywebsite.com/select.php?pg=2.
Working our way through the same script, $pg would now equal 2 & $from would now equal 10. So MySQL would now select records 11 to 20.
In the above examples, we've stored the records selected inside a PHP variable called $query. See the MySQL Fetch Array chapter for how to get at this data.