Flat File Database Demo 3

20 February 2006 · Last updated: 11 December 2006

I was asked recently if there was a way to run my Flat File Database Demo so that it only ouputs just one of the lines from the database. I was able to say yes, because at work I use a technique that pulls a specific line from a database of records, each of which start with a unique ID. But I wondered if there wasn't an easier way, where you didn't have to use an ID. I thought it should be possible to make a loop that ran the same number of times as the line you wanted to show. So if you were after line 3 of the database, then it would loop 3 times. So here is that code. If there is a simpler method, let me know! Only I have tried to simplify it as much as I can.

For this demo I have constructed a simple database of 10 messages, each of which has a background colour and a text colour defined in the database. The demo has a simple form which enables you to choose the database line you wish to display. Try it!

Here is a breakdown of the PHP used, which I'll explain afterwards:

list ($text, $number) = split ('=', $_SERVER['QUERY_STRING']);
$query = round($number);
if (($query > 0) and ($query < 11)) {
$fp = fopen('flat-file-database-demo-3-database.txt','r');
if (!$fp) {exit('ERROR! Unable to open database.');}
$count = 0;
while ($count <> $query) {
$count++;
$line = fgets($fp, 1024);
$fp++;
}
fclose($fp);
list ($field1, $field2, $field3) = split ('\|', $line);
echo <<<HTML
<p id="line" style="background-color:$field2; color:$field3">$field1</p>
HTML;
}

The first line gets the query string from the end of the web page address. That is, everything after the "?". So if the address is "index.php?sauce=ketchup" then the query string will be "sauce=ketchup". Normally I would advise removing the first part and just using the word "ketchup", as this can be assigned to the variable $sauce in the code. (Multiple variables can be handled by separating them with a dot or a hyphen, and splitting the query string down later. This avoids lengthy URLs.) However, the demo uses a form that automatically adds the words "line=" to the end of the address. This is down to the name I gave to the input in the form. So I have stuck with this approach here, meaning the URL is likely to end in "?line=" then a number. In order to get the line number we need, I then split the query string down using the equals sign as the delimiter.
The second line rounds the query number up or down. The reason is that I found you could enter something like "2.4" and PHP would freeze. I'm not sure why, so this is a quick fix.
Line 3 is a gap to make the code easier to read.
But what if someone enters a word, or a number higher than the ones that we need? I check for this by making sure the query is in the range 1 to 10. (Because there are 10 lines in my database.)
The database is opened ready for reading.
If it can't be opened, I generate an error.
Line 7 is a gap to make the code easier to read.
Line 8 defines the variable $count. Can you guess what it's used for? That's right, to count the number of times the loop runs.
If the count has not yet reached the line number we need, the loop carries on.
Here, the count is incremented by 1.
A whole line of the database is extracted. PHP 4.2 defaults to 1024 characters per line, but PHP 4.3 carries on until the end of the line is reached. If most of your lines are over 8Kb, it's best to specify an exact length here. (See this page on the PHP Manual for more details.)
The file pointer is incremented to move it on to the next line.
Here's the end of the loop.
Line 14 is a gap to make the code easier to read.
Now I close the database file as it's no longer needed.
Line 16 is a gap to make the code easier to read.
Because the loop has reached the right line, I already have the line as a string $line. So now I can split it down to get the fields from the database.
Now it's time to echo the line using PHP's heredoc syntax.
The line is styled differently depending on the contents of the database.
Here I end the echo statement. Note this must not be indented in your code.
And that's it!

Although this is a very basic demo, hopefully it should offer some insight into what can be achieved with a simple loop. Of course it's possible to echo more than one line from a database. To do that, you could make the loop a function, then call it as often as you like. Instead of echoing the text, you could start defining a string, and just keep adding to it after each loop. When all the loops have run, then you can echo the finished string. I'll leave the exact code up to you!

The Demo

Comments (8)

Comments are locked on this topic. Thanks to everyone who posted a comment.

Kynace:
Wow! This is a very well written tutorial, Chris! I actually understand it more. You've made a very detailed explanation here, which is good for a slow learner like me . Thanks for taking your time putting this up. This page will be a great reference page .

Posted on 20 February 2006 at 9:07 pm ¶
Ro:
This is what the native file() function is for.

Posted on 27 February 2006 at 4:42 am ¶
Chris Hester:
You're right! PHP has so many ways of doing the same thing. I wonder which method is faster?

Posted on 27 February 2006 at 9:37 am ¶
curtisb:
do you have a download of your complete database 3 that I could install on my server for testing purposes.

Posted on 7 March 2006 at 4:04 pm ¶
Chris Hester:
Oops, I forgot to add a link to the database. I'll do that now.

Posted on 7 March 2006 at 11:02 pm ¶
Ian Chua:
Hi Christopher,

Would you be interested to create a similar PHP script for us which operate on flat files? The requirements are:

1) Admin page:
(a) To create text files with extension "tableX.tbl"
to contain properties of a table "tableX", where "tableX" is a name to be input from the HTML page.
(b) Add data for table "tableX" in text file "tableX.txt".
(c) Display data for table "tableX" with sort functions
on the headers (similar to your Demo2) - only for viewing
(d) Display data as in (c) but for editing (ie., delete a row or add a row)
(e) Display all tables (ie., "tableX1.tbl", tableX2.tbl", etc.)

If you could do this for us, please let me know your charges.

Thanks & Best Regards,
Ian Chua

Posted on 26 March 2006 at 6:38 am ¶
Ross Fobian:
Hi

I have had a look at your demos for reading delimited text files and they have helped loads, but I am trying to read a txt file that is not located on my server, but it is accessible from the web. I was wondering if it is possible to read from this file and if so how is it done?

Thanks

Posted on 15 April 2006 at 1:19 am ¶
Jay:
is there any way to make it write from a query 10 rows per page saving them to static file like .html,
example,
$rowsPerPage = 10;
$req = 'Anything';
$query = "SELECT * FROM `article` WHERE headline like '%$req%' . " LIMIT $offset, $rowsPerPage
$filename = "$req" . ".html";

if (is_writable($filename)) {

if (!$handle = fopen($filename, 'w')) {
echo "Cannot open file ($filename)";
exit;
}
if (fwrite($handle, $show) === FALSE) {
echo "Cannot write to file ($filename)";
exit;
}

echo "Success, wrote ($req) to file ($filename)";

fclose($handle);
} else {
echo "The file $filename is not writable";
}

of course my page is more than that and its working fine, but what i need exactly is to loop the writing to make it generate and save static pages on the server for example its looking for "anything" to make it save 10 results in each page, anything1.html anything2.html anything3.html .....
i really need help with this one if you can plz, thank you

Posted on 17 April 2006 at 5:16 pm ¶