Creating a website in Dreamweaver using MySQL and PHP

[Hobgoblin11]

Heya,

 

 

 

 

 

 

 

I'm hoping someone can help me here. I'm trying to build a web site at the moment, but up until a few weeks ago, I had never tried anything like this before. Basically, I know very little about coding/web servers etc., so I'm learning everything I know from online tutorials.

 

 

 

 

 

 

 

I'm currently using Dreamweaver, I have MX 2004 and Version 8. I have downloaded and successfully installed Apache web server on my own PC to act as a localhost for testing, and have installed MySQL and PHP onto it.

 

 

 

 

 

 

 

I'm not too keen to divulge the exact content of the site at the moment, but to construct the website, I need to construct a user-registration page, and allow users to upload a single image file which will then be resized to a thumbnail and displayed on a page with many other images. Presumably this involves creating a database and writing to it the location of the image on the server, then creating a table in Dreamweaver that is linked to the database (please tell me if I'm wrong btw). However, i can't seem to find any tutorials that tell me how to do this using MySQL and PHP, the only ones I have found so far use ASP coding, and use Access as the database with IIS or PWS - not helpful to me as I have XP Home and can't install IIS.

 

 

 

 

 

 

 

If anyone could provide me with a good source of information on constructing dynamic sites with SQL/PHP, that would be really, really helpful.

 

 

 

 

 

 

 

Thanks a lot.

[ClareJonsson]

ok, creating user registration in php and MySQL is pretty easy, I have already written some myself.

 

 

 

 

 

 

 

The first thing you need to do is to create the database. Now you can do this by hand, or you can use a tool to help you along. I suggest PHPMyAdmin. Download and install that, this will give you a range of tools from creating, deleteing, editing and viewing databases and the tables there in.

 

 

 

 

 

 

 

Then you need to write the code to get php to connect to the database. You can do this with a basic text editor if you like, but dreamweaver is php aware and highlights commands, and it also displays line numbers making debugging easier. Here are some basic lines of code in php showing how to connect, add , edit, display and delete records:

 

 

 

 

 

 

 

Using php to connect to MySQL:

 

 

 

$db = mysql_connect("localhost", "userid","password");



mysql_select_db("DatabaseName",$db);



?>

 

 

 

 

 

 

 

Using PHP to enter a new record:

 

 

 

<?php



$sql = "INSERT INTO DatabaseName (UserName, UserPassword) VALUES 'Fred', 'FredsPassword');"; //This creates the SQL Query



$result = mysql_query($sql,$db); //This performs the query



?>

 

 

 

 

 

 

 

Using PHP to edit an existing record, in this instance we are going to update a single record, changing Fred to Freddy:

 

 

 

<?php



$Query = "UPDATE DatabaseName SET UserName='Freddy' where UserName = 'Fred';"; //Create the Query



$result = mysql_query($Query,$db); //perform the query



?>

 

 

 

 

 

 

 

 

 

 

 

Using php to display records in database:

 

 

 

<?php



$Query = "SELECT * from DatabaseName;";



$result = mysql_query($Query,$db);



if ($myrow = @mysql_fetch_array($result)) {



do {



	echo $myrow['UserID'].'

';



} while ($myrow = @mysql_fetch_array($result));



}else{



echo "No Records Found";	



}



?>

 

 

 

 

 

 

 

To delete a record using php:

 

 

 

<?php



$Query = "DELETE FROM DatabaseName WHERE UserID = 'Fred';";



$Result = mysql_query($Query,$db);



?>

 

 

 

 

 

 

 

And always at the end of the script, close the connection to your database using:

 

 

 

<?php 



mysql_close($db);



?>

 

 

 

 

 

 

 

If you need any help in writing the pages, let me know and I can give you a hand when I have free time.

 

 

 

 

 

 

 

Bye for now,

 

 

 

Clare.

[Hobgoblin11]

Thanks very much clare, I'm sure those scripts will come in useful!

 

 

 

 

 

 

 

I've downloaded PHPMyAdmin, but I can't figure out where to extract the files. On the Official Wiki, it says to extract it to the web host root document root, but I'm not sure what this is. My Web host is Apache, but which folder within that do i need to extract it to? The configuration section also confuses me...

[JoeDaStudd]

Ok, first some good tutorial sites

 

 

 

W3Schools, some great php tuts, including a list and explanation of a lot of functions and some MySQL too.

 

 

 

Tizag, I good amount of php tutiorals and MySQL.

 

 

 

 

 

 

 

As for the server your set up, why don't you try using a WAMP, basically a all in one for Apache, MySQL Php/Python (FYI the W is for windows).

 

 

 

 

 

 

 

This are the two I have/do use;

 

 

 

:arrow: WAMP5

 

 

 

:arrow: XAMPP

 

 

 

 

 

 

 

Good luck with the learning.

[Cruiser]

I would definitely suggest practicing on much simpler PHP pages first before you even think about touching database powered programs. Trying to dive right in before you have a handle on the language itself will only lead to errors and exploitable holes in your code that will cause headaches later. Letting hacked together code loose into the world where random people can try to exploit it isn't a good idea. :P

 

 

 

 

 

 

 

The links posted by JoeDaStudd along with the official documentation at php.net are great starting places. You'll find a lot of example code in the docs for various functions with proper ways to use them and validate input along with user comments on other methods.

 

 

 

 

 

 

 

Getting a strong handle on PHP itself before you bother try anything database related will make development so much easier. If you don't have a strong handle on HTML, you'll need to start there before moving on to PHP too.

[ClareJonsson]

Thanks very much clare, I'm sure those scripts will come in useful!

 

 

 

 

 

 

 

I've downloaded PHPMyAdmin, but I can't figure out where to extract the files. On the Official Wiki, it says to extract it to the web host root document root, but I'm not sure what this is. My Web host is Apache, but which folder within that do i need to extract it to? The configuration section also confuses me...

 

 

 

 

 

 

 

Hello again,

 

 

 

 

 

 

 

The default folder for Apache is usually c:\apache\htdocs. This is the root of your website, remember this!

 

 

 

You should create a folder within that and extract the files there. Something like c:\apache\htdocs\phpmyadmin .

 

 

 

 

 

 

 

One thing you may find useful is a basic command in php to check your php settings, this will also tell you if php is actually working. Create a file called phpinfo.php in the root of your website, and cut and paste the following code into it:

 

 

 

<?php



phpinfo();



?>

 

 

 

 

 

 

 

The quickest way to create this file is to create a new text file, and then rename it.

 

 

 

Oh one thing about XP, it has show file extensions turned off by default, so if you don't see the .txt at the end of the text file, you will need to show extensions for all known file types. To do this, open My Computer, click Tools, and select Folder Options.... Click the View tab and then untick Hide extensions for known file types.

 

 

 

 

 

 

 

Okay to run the script, open a browser, type in the address http://localhost/phpinfo.php and you should get a full screen of information about your setup. In the list should also be info about MySQL, if all is fine, this proves that your web server is running, the php parser is being executed, and that MySQL is installed.

 

 

 

 

 

 

 

Addition:

 

 

 

About the comments above that you should start small and build up to databases on PHP, I would in principal agree with this, but may I add that you should give it a go anyway as it doesn't do any harm, and you will always learn something. However, don't start with the project you mentioned earlier, instead, create a very basic database to begin with as this will give you an idea of the relationship between MySQL, PHP and XHTML. Oh and try using CSS to format your projects. You will thank yourself afterwards believe me.

 

 

 

 

 

 

 

May I ask, do you have any previous programming experience?

[Hobgoblin11]

Thanks everyone:

 

 

 

 

 

 

 

Joedastud, those sites are great, ta muchly! I am not using WAMP, as on several of the forums I went on, it was not recommended to use an all in one package. I have Apache/MySQL/PHP set-up and working now anyway, so there seems little point in changing to WAMP, but thanks anyway.

 

 

 

 

 

 

 

Cruiser, I haven't just dived straight in and gone for the big time. I've pretty much exhausted the supply of Static HTML ttorials I could find, so I've moved onto dynamic. So far I've been learning the basics on how databases and HTML interact and connect, but now that I want to move onto actually producing something, not necessarily the main website, but at least something productive rather tha just looking at code all the time, I can't find any tutorials that are written for my server type. Also, as I found with HTML, trying to learn it by itself was a bit of a fruitless exercise, I found that if I did the tutorials and produced something, it helped put what I was learning into a useful context. If I use something like PHPMyAdmin, is it strictly necessary to learn the PHP language, would you say?

 

 

 

 

 

 

 

ClareJonsson, thanks again! Yes, I thought that it might be that as when I was playing around connecting the database to Dreamweaver, I had to create the site root there. I've created the file there that you suggest, and everything seems to be working fine, I get the long list of info.

 

 

 

 

 

 

 

No, I don't have any previous programming experience.

[ClareJonsson]

 

 

 

If I use something like PHPMyAdmin, is it strictly necessary to learn the PHP language, would you say?

 

 

 

 

 

 

 

 

 

 

Yes, you will definitely need to learn PHP. PHPMyAdmin is a basic tool for creating, editing and managing your databases, that's all it does. You can't use it to construct a website. Also, dreamweaver will not write php code, you need to write all the database connection and queries by hand. Dreamweaver is aware of PHP commands, but as far as PHP is really concerned, it's little more than a fancy text editor. I doubt there's any wizards that does it all for you.

 

 

 

 

 

 

 

 

 

 

ClareJonsson, thanks again! Yes, I thought that it might be that as when I was playing around connecting the database to Dreamweaver, I had to create the site root there. I've created the file there that you suggest, and everything seems to be working fine, I get the long list of info.

 

 

 

 

 

 

 

No, I don't have any previous programming experience.

 

 

 

Well it looks like you have crossed the first hurdle, now you will need to start coding in PHP, follow the online tutorials until you get the hang of it and then delve into SQL.

 

 

 

 

 

 

 

By the way, Are you aware of how PHP works? Actually more than that, how a webserver works and how PHP relates to the webserver and the end user? If not, shall I give you a quick tutorial here?

[Hobgoblin11]

That would be awesome, yes please! I've been away for the last week,hence, lack of posts, but a quick intro would be ace. What I understand of it so far is that it is like a 'plug-in' for the web server, allowing the server to read and use PHP code, but I'm not clear on why PHP should be used.

[JoeDaStudd]

What I understand of it so far is that it is like a 'plug-in' for the web server, allowing the server to read and use PHP code, but I'm not clear on why PHP should be used.

 

The main advantage of a side server language like php is in making dynamic sites, like forums to e-carts.

 

It lets you sends, receives, create and manipulates data (the big advantage is that you use databases).

 

With this information you can do a lot of things from a welcome message to whole forum, graphs and statistics (theres tons more).

 

Why php?

 

Its free (unlike asp.net and some others).

 

Its easy to set up and learn.

 

Cross platform

[Hobgoblin11]

Right, so it gathers data from the database on the server side, and compiles it onto the page so that it is simpler for the browser to read, essentially making the page that the browser sees a basic HTML page?

[____]

yep - but to be more general, it allows true dynamic content, not just dynamic html which is where Javascript and other 'client-side' scripting languages end.... gather data from a database is just one of the many possibilities :)

[ClareJonsson]

okay, here is a very quick rundown of how a web server running PHP works:

 

 

 

The server itself is running what is called a service that is constantly listening on a TCP/IP port for web page requests, this port is usually port 80. When it receives a request it sends the relevant page. So when you go to a website, your browser has sent a page request on port 80 to the server, the server then sends the requested page to your browser, quite simple really. Oh and one thing, the server then forgets about you.

 

 

 

So how does it deal with PHP? OK what happens is this. When the server is asked for a file with the .php extension, it doesn't simply send you the page, the server parses the page through a php interpreter, and what that does is the following:

 

 

 

The parser checks the page line by line, all HTML is sent straight to you, but as soon as it comes across <?php, the parser knows that the following is PHP code and it will run the code, anything echoed is sent to you, nothing else. The code is never sent.

 

When the Parser encounters ?> it then goes back to html mode and sends everything to you again.

 

 

 

Take a look at this simple code:

 

     Hello there 



     <?php //This starts the php code



        $Name="Fred"; //Sets a variable called $Name to "Fred".

        echo $Name; //Echoes the variable called $Name to the browser.



     ?> //Ends the PHP section and reverts back to normal HTML




How are you today?

 

 

 

What will be sent to the browser is:

 

     Hello there 

     Fred


How are you today?

 

 

 

And this is how it looks in the browser:

 

Hello there Fred

 

How are you today?

 

 

 

Remember I said earlier that the server forgets about you after it has sent you a page? So how about sites such as this forum that does remember you, how can variables be passed on from one page to another without using forms etc?

 

Well there are special variables called Session variables. When PHP opens a session it sends you a cookie with a unique identifier, your browser rememebers this and from then on it sends that cookie with all subsiquest page requests to the server. The server has variables associated with your session, and picks them up each time you request a page.

 

Sessions are killed if you close the browser, and they also time out after a period defined in the PHP.ini file.

 

This all sounds rather complex but when you get your head around it it's very simple!

 

 

 

So to recap:

 

Webservers send HTML pages directly to your browser and then forget about you.

 

 

 

Webservers parse PHP pages, all HTML is sent to you, but only the output from PHP scripts is sent.

 

 

 

PHP uses sessions to track your way around a site, without sessions, sites like this would be almost impossible and security wouldn't exist.

 

 

 

If you have any questions then please feel free to ask.

 

 

 

TTFN,

 

Clare.

[Rcty]

Pixel2Life has tons of PHP and MySQL tutorials that are helpful with the kind of things you need.

 

 

 

~Rcty

[Hobgoblin11]

Now that explains a LOT clare, thanks!!

 

 

 

I'm finding with a lot of these things that secuirty seems to be a really big issue. Especially on the sign-up front, half the code is to submit the information to the right place on the server and the rest of it is spent stripping it down of spaces and tags, and making sure people can't inject SQL.

 

 

 

That site is really good Rcty, some of it is way over my head atm and I don't seem to be able to make anything other than the set examples they give work (e.g., if the give an example of a sign-up process involving id, username and password, I'm struggling to add in an email field), but I'll get there in the end!

 

 

 

Is it worth buying a Dreamweaver book/getting one out of the library? If so, any tips on which to get?

[____]

No, there's more than enough tutorials for Dreamweaver on the net, and personally I suggest not using a WYSIWYG editor anyways. Stick to one which has just the syntax highlighting... Editplus, notpad++, etc. If you have a WAMP setup on your machine anyways, it's just as effective (if not more) to just have the active document open in FireFox, IE and Opera at the same time you're editing it, then just refresh in real browsers to see the changes you make.

 

 

 

And if you're competent with HTML, this shouldn't be a problem anyways. The only plus Dreamweaver really has, is in regards to dynamic html - but that's not exactly hard to do anyways if you are decent at javascript or even see the need to use it at all.

[ClareJonsson]

I use dreamweaver for all my web editing, but never in WYSIWYG view, always in code view for the very reason stated above.

 

Dreamweaver is also quite good at taking away mundane things such as basic CSS, but I still end up writing more complex styles by hand.

 

The package is a very useful tool, if used correctly. i.e I use it is a hyped up text editor that's PHP, Javascript and XHTML aware :). Oh and line numbers are a definite plus for debugging!

[ClareJonsson]

Now that explains a LOT clare, thanks!!

 

 

 

I'm finding with a lot of these things that security seems to be a really big issue. Especially on the sign-up front, half the code is to submit the information to the right place on the server and the rest of it is spent stripping it down of spaces and tags, and making sure people can't inject SQL.

 

 

You're welcome and yes you're correct, security is a big issue and something you should always be aware of. But saying that, PHP has built in functions to help in this, such as strip_tags, this removes all tags from a variable. Consider someone trying to add a bit of java script that pops up an alert box:

 

<?php

$Var = '';

echo $Var;

?>

 

This would pop up an alert box with Hello in it. strip_tags could do the following:

 

<?php

$Var = '';

echo strip_tags($Var);

?>

 

This would change the variable to: alert("Hello"); stripping out the script tags.

 

 

 

One thing to watch out for is pre written scripts and software, such as this forum. Because it is freely available you have the source code, and if you find a security hole you could use such an exploit on someone else's site running phpbb. Code written by yourself is generally harder to crack as nobody knows exactly what you have done, and they never get to see the source!

 

 

 

The other thing worth noting is that PHP can generate code for other languages, such as the javascript alert above, a good example of this was a site I wrote as a proof of concept. I wanted to create a sort of folder structure which is dynamically created from a database. Take a look at the system selection bit on the left: Checklist to see what I mean.

 

Basically what is happening is that PHP connects to a database, retrieves all the data and then generates the javascript that controls the menu!

[Mercifull]

Actually code written by yourself as an amateur is MORE likely to have bugs and holes in it than open source software such as phpBB due to common errors plus you are less likely to have the skills to fix it.

[ClareJonsson]

Actually code written by yourself as an amateur is MORE likely to have bugs and holes in it than open source software such as phpBB due to common errors plus you are less likely to have the skills to fix it.

 

Initially yes, but as you become more proficient your code will become less hackable, and you will be better at debugging your own code. I have had to fix some problems with free software such as a guestbook a friend ran on his site because robots were posting masses of rubbish. Oh and not forgetting the big Hoo Haa PHPBB had a few years back with the mega security problem.

 

 

 

But I think you may have missed the point I was making, the point was that being open source, everyone has access to the code, and that means if some dirty little swine digs deep enough to find a security hole, they could possibly exploit that on a lot of peoples sites. Where as the code for in house software is not published and usually harder to mess with. If the coder has done their job that is. Oh and another thing, coding is fun, when you get your own scripts running nicely it's really kewl :)

 

 

 

On a thumbs up for open source, we have the world using and updating the software so in theory problems get sorted! Doesn't always work like that though.

[____]

You'll have to forgive me for laughing at the last comment, Clare.

 

 

 

 

 

 

 

It doesn't matter how much experience you have, more often than not you are going to miss something. And to be prefectly blunt, the most common errors a person new to PHP makes are:

 

 

 

- Using $_GET variables directly

 

- Using field variables directly in SQL statements

 

- Not parsing < and > to &lt and &gt before sending to browser/database

 

- Allowing stuff like ?file=../../../pwd

 

 

 

And so on, obvious junk which is just the start of the iceberg, look at PHP - years and years of commercial experience and they still miss things, and ironically - closed source code is no diferent. Any hacker with a mindset to find an exploit will find out eventually. Just because you can't see the code doesn't mean you can't break it.

[ClareJonsson]

I still think it's more fun writing something yourself. There's no way I can find applications out there that does everything I want. Take that compatibility list for instance, and I'm in the middle of completely redesigning that site, as now I know I can do a much better job and I have an updated list of requirements, all that for fun :)

 

 

 

Lets face it, some of us love to get our hands dirty. I have loved to code ever since a boy at school showed me his ZX81 (No jokes please).

 

 

 

One thing I could see from your last post was, no matter what it is it can be broken if someone was really intent on it. Regardless of it being open source or home grown. Nothing is perfect.

 

 

 

Anyway, lets get back on topic shall we and give the guy any help he asks for.

[Hobgoblin11]

Lol, clearly there is some uncertainty over which is best for beginners. I think I'm going to try and learn both - learning the principles behind PHP, but learning how to do it on Dreamweaver too, 'just in case'. Dreamweaver seems to write PHP a lot differently to the way most of the tutorials are showing me, is that to be expected?

 

 

 

I've got the hang of creating tables where users can enter in their details to register on the site, and sending that data to my 'users' table in the database. I can also create a login page (but only on Dreamweaver, I've not managed to do that in PHP yet). I can't figure out what to do with it next - I want to create a kind of 'user profile' page, where the user can enter their own personal details into a form, but how do I link the info they enter to that specific user?

[ClareJonsson]

Okay I think we need here to get yourself into focus about how dreamweaver html and php relate to each other.

 

 

 

HTML is the markup that makes a web page, it's basically text, styles and tags that define the fonts, colours, images etc.

 

 

 

Dreamweaver is just a tool for creating these pages. You don't use PHP to create pages. The forms you have created in Dreamweaver should be just fine.

 

 

 

I think now you need to learn php, forget for the moment designing the pages, when you grasp the relationship between php and markup, things will become much more clear.

 

 

 

OK take a look at this tutorial, and work your way through it, you could probably skip the first bit as you already have php and mysql installed.

 

 

 

One thing to note, when editing php with Dreamweaver, make sure you are in code view and not design view.

 

 

 

Also, don't just read through the tutorial, try the examples! It's the only way you will really get the feel of it.

[Hobgoblin11]

I think I might have misled you on my last post - I realise that you don't create pages using PHP as a scripting language, PHP is a script which can be run inside a HTML page to change the data displayed on the HTML page that is shown to the browser, correct?

 

 

 

Ta, I worked through something like that this morning, covering the basics, arrays, loops, and variables. Some of it went over my head, but that one explained some parts clearer.

 

 

 

I'm curious as to whether a 'POST' can be given by two values. For example, if you had two fields in a table and wanted to combine them to create a single output. I know the usual POST script would be:

 

 

 

$quantity = $_POST['quantity'];

 

 

 

but could you do this?

 

 

 

$quantity = $_POST['quantity'.'item'];