Michael Lake wrote:
Trevor Tregoweth wrote:
HI All
i was wondering if someone could help me, this is probably a simple task, but for someone thats not done any before it seems impossible
I have a mysql database, with data in it, and i would like to be able to produce that data on a web page, so would appreciate some examples, i don't mind if its simple, just want to be able to view it via a web page
This is an example of a perl script that connects to a database of Journals on my laptop. The MySQL database is called 'Journal'
Note this is not meant to be secure its just a test I had around.
----- perl script --------------
#!/usr/bin/perl
use strict; use warnings; use DBI;
my $user = 'root'; my $password = 'your_mysql_root_password'; my @row;
my $date = `date '+%a %b %d, %Y'`; chomp $date;
# Connect to the Database my $dbh = DBI->connect('dbi:mysql:Journal', $user, $password, { RaiseError => 1, AutoCommit => 0 });
my $query = $dbh->prepare("SELECT * FROM journal"); $query->execute;
# Print the HTML header - note the two blank lines \r\n\r\n print qq{Content-type: text/html\r\n\r\n <html> <head><title>ASF KID Test</title></head> <body><h1>Test Database Access</h1> };
# Print the data preformatted. print "Todays date: $date <p>"; print"<pre>"; while ( @row = $query->fetchrow_array ) { print "@row<br>"; } print"</pre>";
# Print the HTML end. print qq{ </body> </html> };
$query->finish; $dbh->disconnect;
---------- end of Perl script --------------
Here is the output as a HTML page Its the Journal_id, volume, part, the issue_month, issue_year.
Test Database Connectivity
Todays date: Thu Aug 05, 2004
2 133 3 4 December 2000 3 134 1 2 June 2001 4 134 3 4 December 2001 5 135 1 2 July 2002 6 135 3 4 April 2003 7 136 1 4 December 2003
------- end HTML ------
Here is the Journal database table that was queried: Sorry it wrapped badly so I cut bits out but you get the idea.
+-------------+-----------------+
| Field | Type | Null | Key | Default | Extra
+-------------+---------------------+
| journal_id | int(10) unsigned
| volume
| part
| issue_month | enum('January','February','March','April','May','June','July','August','September','October','November','December')
| issue_year | year(4) +-------------+----------------+
Hope this helps.
Mike
I thought I'd throw in my own beginners version of a PHP/mySQL prog.
I slapped this together quickly as I was after a small web database solution in a hurry.
---------cut->paste-------
<?php
print "<HTML><HEAD><TITLE>House Database</TITLE></HEAD><BODY><H1 align=center>Listing of houses</H1>\n";
print "<HR>\n";
print "<TABLE align=center cellpadding=6 border=1>\n";
print "<CAPTION>Results of search: Please note that date is in year-month-day format.</CAPTION>\n";
//One day I'll do something about the date format
print "<TR><TH>ID</TH><TH>House No.</TH><TH>Street Name</TH><TH>Suburb</TH><TH>No. Bedrooms</TH><TH>Price</TH><TH>Date</TH><TH>Source</TH><TH>Description</TH></TR>\n";
$user = "username_with_access_to_the_DB/Table";
$pass = "users_password";
$db = "database_name";
$table = "name_of_table";
$query = "SELECT * FROM $table WHERE stnumber LIKE '%$stnumber%' AND stname LIKE '%$stname%' AND suburb LIKE '%$suburb%'";
//Connect to the database:
$link = mysql_connect("localhost", $user, $pass);
if(! $link)
die("Could not connect to database");//Select the database to work with:
mysql_select_db( $db, $link)
or die ("Couldn't open $db: ".mysql_error() );$result = mysql_query($query , $link);
if (! $result)
die("No matches found.");
$num_rows = mysql_num_rows($result);//While there continues to be data in the returned set, we loop and print this out in a table.
while ( $a_row = mysql_fetch_row( $result ) )
{
print "<TR>\n";
foreach ( $a_row as $field )
print "\t<TD>$field</TD>\n";
print "</TR>\n";
}
mysql_close($link);
print "</TABLE>";
print "<H4 align=center>Number of records found: $num_rows</H4>";
print "<P><H4 align=\"center\"><a href=\"search.html\">Search again</a></H4></BODY></HTML>";
print "<P><H4 align=\"center\"><a href=\"index.html\">Return to Home Page</a></H4></BODY></HTML>";
?>
--------- end paste -------
Obviously the variables such as "stnumber", "stname" etc.. are from the posted html form and used as part of the SQL search query.
I found PHP works really nicely with mySQL as it has some great support for it. I'm sure there's many expert PHP/SQL sluggers out there who would agree.
And if you're already familiar with languages such as C/C++, you will find picking up PHP even easier.
Hope this helps you out.
Rocci. -- SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/ Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html
