Datanase (PHP/MYSQL or PERL)

finnpark

Hi Everyone,

I need to put together an online database. Basically I want visitors to my web site to be able to query a database to compare different sequences of stored results. I want to create a comparison table to compare the performance of different methods etc. All and all what we are looking at is a database that is quite flexible in that it can be queried in many different ways.

So what should I use?

I do have a reasonable strong programming software knowledge. I have never programmed in PHP or MYSQL. I am a good PERL programmer though and do know C among others.

Is PHP/MYSQL the way to go for what Im trying to do? Or should I use PERL to query a database (i think it is possible to do) since I already have a knowledge of PERL.

All advice is appreciated.

Dermot.

Cake Fiend

Perl would be fine. Then again, you could learn PHP in a day. Hell, you could even write a CGI in C to do it if you wanted. But under your particular circumstances, Perl would probably be easiest.

I'd recommend learning SQL and using either MySQL or PostGRE. SQL is easy to learn, and while it might be overkill for this application, it's handy to know. Then you can use APIs in any popular language to interface with the database. Someone who has more experience using Perl and PHP with SQL might be able to give tips on which would be more suited.

finnpark

Sico wrote:

Perl would be fine. Then again, you could learn PHP in a day. Hell, you could even write a CGI in C to do it if you wanted. But under your particular circumstances, Perl would probably be easiest.

I'd recommend learning SQL and using either MySQL or PostGRE. SQL is easy to learn, and while it might be overkill for this application, it's handy to know. Then you can use APIs in any popular language to interface with the database. Someone who has more experience using Perl and PHP with SQL might be able to give tips on which would be more suited.

Im tending towards PHP as this seems the standard one to use now. I could use C/CGI as you suggested but my C skills would not be developed enough to do much database stuff.

So if I were to decide to use PHP to query a database then which languages would I need along with PHP.?

Also are SQL, MYSQL and PostGRE 3 separate languages? If I use PHP do I need to know 2 other languages also in order to query data in a reasonably complex way.

Thanks again.

jayo99

1) Learn php
2) Learn mysql
3) Some javascript would be handy..

Other than that you're good to go.

cython

There are slight differences between the different SQL databases in some of the commands, but they're broadly the same. Learn one and the others follow suit closely enough that for many applications you'll hardly notice the difference. When it comes to querying the database in given languages, however, you may need to learn different libraries, as I think the functions used to connect are different.

If you use PHP, you need to know:
-HTML (obviously!)
-basic PHP
-some database library(ies)
-some kind of SQL language.

And that's pretty much it

finnpark

jayo99 wrote:

1) Learn php
2) Learn mysql
3) Some javascript would be handy..

Other than that you're good to go.

Cheers Jayo:) .

How many hours of work would be required to reach a high standard of the above 1 and 2? In other words how long should it take me to be able to put a complex database together so that large amounts of data can be queried in many different ways to compare methods with stored results. I want to become expertish in databasing.

Many thanks.

finnpark

cython wrote:

There are slight differences between the different SQL databases in some of the commands, but they're broadly the same. Learn one and the others follow suit closely enough that for many applications you'll hardly notice the difference. When it comes to querying the database in given languages, however, you may need to learn different libraries, as I think the functions used to connect are different.

If you use PHP, you need to know:
-HTML (obviously!)
-basic PHP
-some database library(ies)
-some kind of SQL language.

And that's pretty much it

Cheers. I think I will chance PHP along with MYSQL as there seems to be more info available on these on the web than others.

My HTML is very good, know PERL but not PHP so must learn PHP but that shouldn't be too bad from what Ive seen.

Thanks again and any more advice is welcome.

sean_or99

finnpark wrote:

Cheers Jayo:) .

How many hours of work would be required to reach a high standard of the above 1 and 2? In other words how long should it take me to be able to put a complex database together so that large amounts of data can be queried in many different ways to compare methods with stored results. I want to become expertish in databasing.

Many thanks.

You should be able to put something together very fast. Some advice on using PHP and mysql is:

• Leverage existing code in PEAR
• Use a db abstraction layer, like adodb
• The php manual is one of the best php resources
• Understand the relational db model before doing anything in mysql
• Normalize your data
• Pick a coding standard and stick with it
• Phpmyadmin is pretty much essential
• Using a templating system can be very advantageous, like smarty, to start off with.

To become very proficient in the LAMP setup I would say takes about 2 years.

finnpark

sean_or99 wrote:

You should be able to put something together very fast. Some advice on using PHP and mysql is:

• Leverage existing code in PEAR
• Use a db abstraction layer, like adodb
• The php manual is one of the best php resources
• Understand the relational db model before doing anything in mysql
• Normalize your data
• Pick a coding standard and stick with it
• Phpmyadmin is pretty much essential
• Using a templating system can be very advantageous, like smarty, to start off with.

To become very proficient in the LAMP setup I would say takes about 2 years.

That's brilliant. Great stuff thanks.

Can anyone give me a link to a site that allows me to download php, mysql and apache all in 1 go. Ive heard that some of these are reccommended and some are not, any links to this would be great. I want to install apache on my local PC along with MYSQL/PHP in order to test my scripts locally.

Thanks again.

sean_or99

finnpark wrote:

That's brilliant. Great stuff thanks.

Can anyone give me a link to a site that allows me to download php, mysql and apache all in 1 go. Ive heard that some of these are reccommended and some are not, any links to this would be great. I want to install apache on my local PC along with MYSQL/PHP in order to test my scripts locally.

Thanks again.

I assume you are using windows.

Then I have heard good reports of WAMP.

Apache2Triad will set you up with more software including extensions for running perl and python.

Good Luck

finnpark

Many thanks for all this, it has really been helpful and put me on the right road. Hopefully I will be able to manage this.

Thanks again to all for all the help:) .

Dermot.

ishnid

Unless you're intending on treating this as a learning exercise for getting to know PHP, I'm not sure why you wouldn't use Perl if you have skills in it already. The DBI module (which comes as standard with any Perl installation) will take care of the database interaction. You appear to be picking PHP for the wrong reasons (i.e. it's popular), rather than because you want to learn it. There are plenty of Perl resources around the web to help you out too, including forums (this being my most regular haunt, for example).

finnpark

ishnid wrote:

Unless you're intending on treating this as a learning exercise for getting to know PHP, I'm not sure why you wouldn't use Perl if you have skills in it already. The DBI module (which comes as standard with any Perl installation) will take care of the database interaction. You appear to be picking PHP for the wrong reasons (i.e. it's popular), rather than because you want to learn it. There are plenty of Perl resources around the web to help you out too, including forums (this being my most regular haunt, for example).

Thanks, now Im re-considering as I have not started yet.

So I would be able to do the same queries using PERL as I would using PHP/MYSQL? I would be able to query data in same exact ways?

Many thanks.

Zaltais

Yep, exactly the same queries, though the methods would be slightly different.

Although there is a significant similarity between perl's DBI module and PHP's PEAR DB module, the builtin PHP DB access methods are completely different.

Cake Fiend

PHP is popular because it's easy to learn. If you already have experience with Perl, no need to reinvent the wheel (although learning a new language is always fun (unless it's brainfuck))

finnpark

Many thanks to all

Just wondering is this DB Perl Module included in the latest version of Active PERL or do I have to download a special library or something? Apologies for my ignorance:o .

Thanks again to all.

Zaltais

I don't think DBI is included in ActiveState perl, however, installation is trivial

from a command-prompt run

ppm

this will launch the activestate package manager.

then type

install DBI

and ppm should do it's magic...

finnpark

Zaltais wrote:

I don't think DBI is included in ActiveState perl, however, installation is trivial

from a command-prompt run

ppm

this will launch the activestate package manager.

then type

install DBI

and ppm should do it's magic...

Thanks for the above.

Do I need to download anything else 1st before i do the above? I have apache server on a windows machine with ActiveState PERL installed also.

finnpark

Installed Successfully;) it says - many thanks.:)

What would be my next 2 steps to get me started?. Thanks for your patience.

I will learn PHP for interest after this also so thanks also for all the php/mysql advice.

Cheers:)

Zaltais

Well obviously you'll need a database to connect to - many people choose MySQL, though that's not really a good critera on it's own to choose a database by.

You'll then need the Database Driver for DBI - to use MySQL as an example open ppm as before and simply

install DBD::mysql

The DBD::mysql module provides some documentation as does the DBI module.

And as usual google is your friend...

finnpark

I was given 3 options this time, I installed option 1 which was DBD::MYSQL. Didn'y install the other 2...hope this is ok?

So am I now ready to start googling for tutorials? Is there anything else I need to download?

Again, as usual, your info has been extremely helpful and much appreciated...nearly there now.:)

Garfieldus

finnpark, you owe it to yourself to check out CodeCharge Studio. Download a 20 day fully working version and never look back, seriously.

PHP, ASP, JSP, Perl, it does it all.
MSQL, MySQL, Access, PostGre, it does it all.

http://www.codecharge.com

finnpark

Garfieldus wrote:

finnpark, you owe it to yourself to check out CodeCharge Studio. Download a 20 day fully working version and never look back, seriously.

PHP, ASP, JSP, Perl, it does it all.
MSQL, MySQL, Access, PostGre, it does it all.

http://www.codecharge.com

Thanks but this may be for someone else - it takes the fun out of learning.

Thanks anyway, may help others.

Zaltais

finnpark wrote:

I was given 3 options this time, I installed option 1 which was DBD::MYSQL. Didn'y install the other 2...hope this is ok?

So am I now ready to start googling for tutorials? Is there anything else I need to download?

Again, as usual, your info has been extremely helpful and much appreciated...nearly there now.:)

Assuming you have MySQL already installed then you're ready to go (DBD::mysql just allows Perl to connect to MySQL - you still need MySQL installed for it to work).

As for tutorials, I can't really make any recommendations - it's been too long since i learned, and I can't find any of those old tutorials any more.

But if you do find a tutorial you think is good enough to follow (you should try to find one that covers creating the database tables and querying the database) and are having any problems, you know where to ask...

finnpark

Zaltais wrote:

Assuming you have MySQL already installed then you're ready to go (DBD::mysql just allows Perl to connect to MySQL - you still need MySQL installed for it to work).

As for tutorials, I can't really make any recommendations - it's been too long since i learned, and I can't find any of those old tutorials any more.

But if you do find a tutorial you think is good enough to follow (you should try to find one that covers creating the database tables and querying the database) and are having any problems, you know where to ask...

Youve been more than helpful.

Yes, I need to download and install MYSQL.

I have some tutorials myself and will google for more. I may also get a book out from my local library on this to help me along - its getting started thats always the most difficult.

Many thanks again to all - no doubt you will be hearing from me soon:D

I will do my best to work this out for myself.

Cheers, as always everyone on here has been a fantastic help.;)

finnpark

I set a MYSQL database on a hosted server and seems to be going fine.

However, whenever I try to create a table it gives me an error. Here is the code:

#!/usr/bin/perl
use DBI;
use CGI::Carp qw(fatalsToBrowser);

my $database = "database_name"; 
my $data_source = "DBI:mysql:$database:localhost"; 
my $username = "database_name"; 
my $password = "pass1"; 

my $dbh = DBI->connect( $data_source, $username, $password) ;



USE database_name;

CREATE TABLE names (myname CHAR(10), yourname VARCHAR(10));

INSERT INTO names VALUES ('Monty ', 'Monty ');

SELECT myname = 'Monty  ', yourname = 'Monty  ' FROM names;


$dbh->disconnect; 

print "Content-type:text/html\n\n";
print "Hello World\n";

I get the following error:
syntax error at tables.pl line 20, near "'Monty ' FROM names"
Execution of tables.pl aborted due to compilation errors

Any ideas where Im going wrong.

Many thanks.

Please note that the database name is the same as my username - may this be wrong?

NeverSayDie

The problem here is that you're throwing raw SQL instructions into the middle of your Perl code, which isn't going to work. Basically, you need to use DBI methods to send your SQL statements to the database, and retrieve the results. It's pretty straightfoward, see here for a tutorial:
http://www.perl.com/pub/a/1999/10/DBI.html

I'm assuming you're comfortable with Perl btw, if you're not, this book is a good bet, nice balance of introduction and reference:
http://www.bookpool.com/sm/0596000278

finnpark

NeverSayDie wrote:

The problem here is that you're throwing raw SQL instructions into the middle of your Perl code, which isn't going to work. Basically, you need to use DBI methods to send your SQL statements to the database, and retrieve the results. It's pretty straightfoward, see here for a tutorial:
http://www.perl.com/pub/a/1999/10/DBI.html

I'm assuming you're comfortable with Perl btw, if you're not, this book is a good bet, nice balance of introduction and reference:
http://www.bookpool.com/sm/0596000278

Cheers. It seems so simple when you hear the answer but seems impossible when you don't know the answer.

Ive changed the code to:

#!/usr/bin/perl
use CGI::Carp qw(fatalsToBrowser);
use DBI;

my $database = "d_name"; 
my $data_source = "DBI:mysql:$database:localhost"; 
my $username = "user1"; 
my $password = "pass1"; 

my $dbh = DBI->connect( $data_source, $username, $password) ;

my $sth = $dbh->prepare('create table names (myname CHAR(10), yourname VARCHAR(10))');

my $sth = $dbh->prepare('CREATE table names (myname CHAR(10), yourname VARCHAR(10))');

my $sth = $dbh->prepare('INSERT INTO names VALUES (Monty,Monty2)');


my $sth = $dbh->prepare('SELECT myname = Monty, yourname = Monty2 FROM names;');


$dbh->disconnect; 


print "Content-type:text/html\n\n";
print "Hello World\n\n$sth\n";

Can you tell me what the SELECT does in SQL language? What is returned to perl etc.

Thanks again, finnpark.

NeverSayDie

You'd be best to brush up on your SQL, there's loads of tutorials on the web. SELECT statements are used to retrieve fields from records that match values that you specify. Eg, "SELECT firstname, secondname FROM customer WHERE customer.income = 50", or "SELECT name FROM pet WHERE age > 5 AND numChildren = 2", that kind of thing. Google for tutorials, or check the MySQL manual - there's loads of info in there.

Re getting results back to Perl, you need to take another read of that DBI tutorial I linked to, particularly the sample code. Once you've prepared a statement, you need to execute it, and in the case of a SELECT, call fetchrow_array() or an equivalent method if you want to get back the results.

finnpark

NeverSayDie wrote:

You'd be best to brush up on your SQL, there's loads of tutorials on the web. SELECT statements are used to retrieve fields from records that match values that you specify. Eg, "SELECT firstname, secondname FROM customer WHERE customer.income = 50", or "SELECT name FROM pet WHERE age > 5 AND numChildren = 2", that kind of thing. Google for tutorials, or check the MySQL manual - there's loads of info in there.

Re getting results back to Perl, you need to take another read of that DBI tutorial I linked to, particularly the sample code. Once you've prepared a statement, you need to execute it, and in the case of a SELECT, call fetchrow_array() or an equivalent method if you want to get back the results.

Yes I thought there was something else to get the data back;) . Thanks, the above was a rush job.

Great advice and I should be able to take it from here.

Its such a great method, not too difficult and really clever the way its done. Happy I chose PERL /SQL to do this.

Thanks again, very much appreciated.

finnpark

Hi all, me again .

I am starting to get the hang of sql etc so thanks for all your help.

Also, I was hoping to put together a forum. Now, instead of writing my own code for this I was hoping to download open source script to implement a forum. There are PERL scripts available for download but I was thinking of using PHP Bulletin Board.

Would I need a level of PHP to implement the PHP BB on a hosted web site or is the process of getting the BB up and running simple? In other words how much work is involved in getting the PHP BB working after you download the files?

Many thanks for all the help.