Viewing Thread:
"Add a Database to my Website - How?"

Hi All,
I have designed a database (4 tables) in Access 2007 which I am happy with. I would like to be able to upload this to my FTP and allow people to view and edit the database (password protected directory) as you would do on your PC. I use Frontpage 2003 for designing the website (Only supports asa but freeola doesnt support asa). I am currently subscribed to VIP+ and MySQL (Not got a clue how to use the MySQL though)

1 - Is it possible (I think it is)
2 - Any comments would be very appreciated

Many thanks JTD, I'll give it a go as I get delving:-)

Dragonlance, adapt,

I’ve lashed together a quick sample table, a mysql connection example and a php page with a simple table. This should get you going along the right track. Obviously bear in mind once you have extracted the row details you can use them in an authorisation script - with that in mind you might also want to read this thread and combine with the sample below.

First create sampletable and insert data.
Copy and paste this script directly into a sql window in phpmyadmin.


CREATE TABLE IF NOT EXISTS `sampletable` (
`recID` smallint(6) NOT NULL,
`name` varchar(30) NOT NULL,
`age` smallint(6) NOT NULL,
PRIMARY KEY (`recID`)
);

INSERT INTO `sampletable` (`recID`, `name`, `age`) VALUES
(1, 'Jimmy', 23),
(2, 'Billy', 33),
(3, 'Andrew', 21),
(4, 'Johnny', 24);



Now create a page called sampleconnection.php and save it.
Change the connection details such as password and database name to the details provided by freeola.


<?php
$hostname_sampleconnection = "mysql.freeola.net";
$database_sampleconnection = "sr0123456";
$username_sampleconnection = "sr0123456";
$password_sampleconnection = "yourpassword";
$sampleconnection = mysql_pconnect($hostname_sampleconnection, $username_sampleconnection, $password_sampleconnection) or trigger_error(mysql_error(),E_USER_ERROR);
?>


Now create this page testpage4.php


<?php
require_once('sampleconnection.php');

mysql_select_db($database_sampleconnection, $sampleconnection);
$query_sample = "SELECT sampletable.recID, sampletable.name, sampletable.age FROM sampletable";
$sample = mysql_query($query_sample, $sampleconnection) or die(mysql_error());
$row_sample = mysql_fetch_assoc($sample);
$totalRows_sample = mysql_num_rows($sample);
?>

<table width="348" border="1" cellpadding="0" cellspacing="0">
<tr>
<td>recID</td>
<td>name</td>
<td>age</td>
</tr>
<?php do { ?>
<tr>
<td><?php echo $row_sample['recID']; ?></td>
<td><?php echo $row_sample['name']; ?></td>
<td><?php echo $row_sample['age']; ?></td>
</tr>
<?php } while ($row_sample = mysql_fetch_assoc($sample)); ?>
</table>
</body>
</html>
<?php
mysql_free_result($sample);
?>


You can see all of the above in action here.

If you need help in extracting data from submitted forms (e.g. login authorisation form) then let me know.


I hope this helps.

JTD

Many thanks Tappet and yes I can understand your explanation - it brought make distant memories of using PHP and MySQL to create a database of pc game genre that I used as like a training exercise - you know, learn to use it as you dabble, get you hands dirty instead of just reading the book! Your post also got me to dig out a PHP publication that I was using at the time - I found it really helpful:

PHP fast & easy web development by Julie C Meloni.

It came with a CD which included MySQL, Apache and PHP4 and if I remember correctly, the installation was a dream and fully documented. However I ran it on a machine with W95 and when that went to the great computer rest home in the sky, so did my PHP etc etc. So when I finally replaced the machine with one running XP, I didn't bother re-installing. Looking through the book now and reading my notes, I am beginning to get the urge to delve again.

Many thanks for your offer of assistance - I may just come calling:-)

DL

hiya, ive just started using MySQL Database, at 1st i found it a bit confusing but after a weekend of practise it became self explanatory.

To help you understand how it works and how the website links to the database you are best off downloading an example php script from 1 of the many PHP MySQL website.

I downloaded a zip file that contained example web pages to setup a members signup and login feature to a website, these pages are simply unloaded to your webserver. Also included in the zip file was a text file that had to be cut and pasted into the database, this txt created the tables in the database. Once this is done you can open the PHP pages in any txt editor, i use dreamweaver but i guess frontpage does the same, one of the pages will be for linking the website to the database and you will have to edit a few lines of code with your database name, username and password.

The webpages that are uploaded to the webserver contain PHP code and must have the file extension .PHP (dot PHP) at the end, for example signuppage.php. The great thing about PHP pages is they can contain both PHP and HTML language. These pages are used to collect data from the user, the user will type information into text areas or select info from drop down menus then click submit, the info entered is then stored in variables, for example if a user enters their name it can then be stored in the variable $name, users_name=$name. Then a PHP MySQL query can be run that adds the users name to the database, for example INSERT INTO members_table (user_name) VALUES ('$name')
this query will insert the value of $name into the field user_name contained in the members_table.
At the same time, other info may be collected and inserted into the members_table such as 'users_name', 'user_id', 'email address', 'age', 'place of birth'.
So users may submit something like this...
'John Smith', '[email protected]', '35', 'london'
'Jimmy Jones, '[email protected]', '37', 'london'

You could then use the SELECT command to retieve info from the database and display it on a webpage by using the ECHO command. For example if you want to see all your members who live in London you would use this query...
$live=mysql_query (SELECT users_name FROM members table WHERE place of birth = london); ECHO $live;
this would display John Smith and Jimmy Jones as they both live in London,
If you wanted to see your members aged over 35 you would use something like this....
$age=mysql_query (SELECT user_name FROM members_table WHERE age > 35); ECHO $age;
this would display Jimmy Jones because he is 37 yrs old, John Smith is not displayed because he is not over 35yrs old.

Other commands include UPDATE, DELETE, CREATE and many more,
PHP also has many commands such as IF, PRINT, ELSE and loads more, so once a user has submitted info you can then use PHP to do almost anything, such as.....
IF $name = yourname
ECHO "your name is the same as mine"
ELSE
ECHO "your name is not the same as mine"

These examples are not exactly as they should be, some commas & brackets etc are missing. They are simple examples to give you some idea how it works.

I hope this makes some sense to you, like i said im only a beginner at a early stage myself.

have a look at this PHP MySQL website for help with the many commands.

If you would like a working example PHP and MySQL posted here which you can just cut and paste then please let me know.

Hope the above makes some sence to you and gives you the insight your looking for. Im sorry if it hasn`t, but im not the best at typing explanations, but if you sat here with me i could show you how to do the above in 2 mins flat, it realy is that simple to get started.
Please dont hesitate to contact me with any question or help you may need, Im only a beginner but im more than happy to help and if i dont know the answer im sure between us we will find the answer somewhere on the net.

Have fun with PHP and MySQL cos once you have a basic understanding your realise how powerful it truely is!

I could post you some links to many helpful websites with tutorials, examples and coding help but they also include adverts for ISP's & hosting etc and i think posting them here would be unfair on FREEOLA.

Keep Safe m8

There you go, adapt - JTD has taken up the challenge and provided a possible solution - it's a shame that I find it so hard to find the right 'keys' these days, or else I might have posted a solution myself...this is getting so hard to do:-)

Hi adapt,

For what it's worth here is the benefit of my experiences with Access, MySQL and PHP.

I have a web site which is extensively driven by data in a mysql db. However, I manage the data in the MySQL tables via an MS Access 'Control Centre' on my laptop.

Your first step will be to re-create your Access tables on your MySQL db - which I think you said you'de managed but just in case you're struggling I've included some instructions to get them created right first time.

This can easitly be done by exporting the table structures from Access into MySQL via an ODBC. You can get the MySQL ODBC drivers from here (I have V5.x installed).

When you have installed the driver then open your control panel, administartive tools, and open ODBC. Create a new System DSN. Work throgh the process and choose your MySQL driver from the ODBC driver list and complete the connection details as per your freeola account.

Once you have successfully complete the ODBC connection you can export your Access tables (right click on the tables tab, in Access, choose export, and when asked where you want to export your table then choose odbc and then the connection you created above. Be careful - use phpmyadmin to make sure the tables you have just created all have a primary key otherwise your Access mdb will grumble when you try to enter data.

Once you have the tables created on your MySQL (by whatever method you use) you can then delete (or rename) your local MS Access tables and link your web MySQL tables to your Access mdb.

At this point you can enter data into your tables via your Access MDB.

Any forms will be designed using the linked tables/quesries - but be warned you can only run the Access db when you are linked to the internet (and unless you have freeola's freedon package, it must be a freeola connection). I get round this problem by workig on a local copy of my tables (e.g. I have a table called 'Members' and a linked table calles 'Members (web)') and then updating/inserting/deleting the data via code or query.


Now - the Web Site bit...

The ability to show that data in a website relies on you understanding PHP.

I could carry on and give a step by step guide to creating various methods of retrieving and showing data but my best advice is to pop into your nearest Waterstones and buy 'PHP & MYSQL for Dummies'. It was the best thing I ever did when starting off in PHP/MySQL.

With regard to using FrontPage - personally I'd rather write my web sites in notepad than use FP. It has a habit of leading you down the path of using fanciful FP extentisions which many ISP's don't support. If you're serious about web design, and PHP in particular, then I'd recommend something like Dreamweaver - although I realise it's not cheap.

I know this is a lot to take in in one go but I hope it points you in the right direction.

JTD

Try PHP and MySQL - I dabbled once, many years ago and Freeola do advocate and use PHP - I'm no expert, but I did find it's 'workability' pretty straightforward. Once again, the staffies may advise you:-)

Ok, I have figured out how to create and use the database in MySQL with freeola but how do I get this showing in my website?

In the meantime, I am wondering if Freeola hosting will recognise my access database once I convert it to MySQL?

Again, any comments would be grateful!

adapt wrote:
> 2 - Any comments would be very appreciated


I don't use MySQL (must take a proper look one day!) but as I understand things you can't directly use your MS Access database, you'll have to convert it to MySQL.

I would start reading Freeola's notes here and then go to the many MySQL web based resources.
Also a Google on 'converting access to mysql' gives lots of reading!

Hope that helps until someone gives you a proper answer :¬)


Search Freeola Chat

Hi All,
I have designed a database (4 tables) in Access 2007 which I am happy with. I would like to be able to upload this to my FTP and allow people to view and edit the database (password protected directory) as you would do on your PC. I use Frontpage 2003 for designing the website (Only supports asa but freeola doesnt support asa). I am currently subscribed to VIP+ and MySQL (Not got a clue how to use the MySQL though)

1 - Is it possible (I think it is)
2 - Any comments would be very appreciated