Building dynamic database-driven websites... noob...

Dave!

Yo there,

I'm trying to forge a career in web design at the moment. I'm fairly proficient at front-end stuff like HTML, CSS and JavaScript, but obviously this limits me to crappy static pages, which are of limited use. It's fairly clear that I need to be able to incorporate databases into my sites.

Now, I'm quite good with SQL, because my last job involved alot of data analysis and stuff (I was just using SQL Management Studio to run the queries), but linking the database to a website is where I'm found lacking.

From what I can tell, the two main options for me would be ASP.Net, or PHP. I've got heaps of ebooks for both, and have dipped into them both a little bit at various points, but I don't know which to commit to. I studied computer science in uni, and am a decent programmer (Java primarily).

1. Which should I focus on?

2. The tutorials I've been doing for ASP.Net seem to be very heavy on using an IDE like Visual Web Developer to do most of the work, meaning you don't really get your hands dirty with the code. Obviously this is easier, but is this how it's done in a commercial environment? I don't think I'd learn in enough depth dragging and dropping things onto my pages.

Any other thoughts appreciated.

Cheers

Adam

personally i'd recommend php, but it's a toss up for the most part. you don't HAVE to use an ide for asp, and from the point of view of getting hands on writing the code yourself, i'd say php would be a nice place to start, it's fairly easy to read at the basic levels, so not too much time spent scratching your head at code you cant follow.

go download xampp, install that and then start copying out some basic php tutorials, you'll get in to the swing of things in no time with your background i'm sure.

henryporter

Yes PHP is quite easy to get going once you get the basics right - having the other parts of web development already makes it a doddle to focus on making sites dynamic (I had to try to learn html, css and PHP all at the same time, as well as java and VBA so my head was spinning a bit!)

Programs like Dreamweaver make it easy to use PHP too as they provide wizards to interface the site with your database by inserting PHP code, however I think you get a better understanding of PHP by manually inserting the code. A lot of code for putting and getting data is SQL anyway so you'll be up and running in no time.

fasty

I'd recommend PHP simply because your hosting options are far greater. I would also suggest using a framework like CakePHP to build your actual site after you've followed the usual tutorials that outline connecting to a DB and enumerating records.

I do prefer ASP.Net, or at least the MVC2 framework built upon it but having build my own site and a database driven catalogue style app for it I found getting decent hosting a massive pain.

komodosp

While I would prefer PHP, when I was looking for a job (this time last year) ASP.net - usually with C# - seemed to be more sought after, and since Dave! is "trying to forge a career" this is something to consider.

henryporter

komodosp wrote: »

While I would prefer PHP, when I was looking for a job (this time last year) ASP.net - usually with C# - seemed to be more sought after, and since Dave! is "trying to forge a career" this is something to consider.

Good advice there

Dave!

Cheers for the words of wisdom gents!

I've gone for PHP, and was messing around with it the last few days, and I'm really getting the hang of it! I've got a fairly simple page displaying my database and enabling me to add and delete things, and I even AJAXified it, so now it's proper cool

Will look through a few ebooks next to see what the next step is...

Cheers gents

Dave!

Bumparooney

Lads quick question... I'm putting together a fairly simple web app, and I'll need to store lots of different types of input. Meaning, I could be storing long passages of text, code, markup, or pretty much anything textual.

What would be the best way to do this? I'd need to preserve the integrity of the text stored, meaning spaces and indentation and stuff -- is that possible? Would it present a security risk to allow code to be entered into a database?

Would XML be suitable for this?

I don't have a clue about XML, but might as well learn...

Cheers gents

fasty

It's safe to put whatever you like into a large text field of a database as long as you escape the text as some point so that users can't embed javascript, sql commands, dodgy html tags etc.

I prefer to do this before adding it to the database but you can clean the text before you show it to the user if you really want to preserve things literally in the table.

This might be handy I suppose if you were going to export the text to some other application and not necessarily a webpage.

You don't need XML. In fact, no one needs XML! It's a ballache to deal with.

Dave!

Cheers man.

I tried briefly yesterday to store some php code in a varchar field, but obviously when it was retrieved from the database, the code was executed rather than displayed! How would I get around this? I want to be able to store any kind of textual data and retrieve it without it being changed or executed.

I was thinking of storing the text in seperate .txt files, and then having the file locations stored in the database. Is that a silly way to go about it?

Cheers!

stevenmu

Yes, scales poorly and makes things like searching and indexing awkward. It's much better to just encode your text and save it in the database, it'll scale well, be easily searchable/indexable etc. And as a bonus, encoding the text will also be teaching you about protecting against SQL injection.

jmcc

Dave! wrote: »

Now, I'm quite good with SQL, because my last job involved alot of data analysis and stuff (I was just using SQL Management Studio to run the queries), but linking the database to a website is where I'm found lacking.

Linking the databases with the sites is actually the easy part. You can learn how to do ith with PHP, ASP etc very quickly. You are lucky in that you may have a good grounding in database design and that is a very rare commodity when it comes to developing db backed/driven websites. It might be a good thing to learn more about indexing strategies and optimisation as they apply to db backed/driven websites.

Regards...jmcc

jmcc

Dave! wrote: »

Cheers man.

I tried briefly yesterday to store some php code in a varchar field, but obviously when it was retrieved from the database, the code was executed rather than displayed! How would I get around this? I want to be able to store any kind of textual data and retrieve it without it being changed or executed.

Just don't store the text with < ? php brackets. Raw text is easy enough to store in varchar fields. When you read the data from the db you may have had the < ? brackets at the start and end of the field. The webserver then assumed that it was some php code to be executed. Alternatively escape the < characters.

Regards...jmcc

amen

You don't need XML. In fact, no one needs XML! It's a ballache to deal with

maybe for storing in DB but its a great mechanism for sharing data between systems and for displaying data. Very easy to manipulate using xsl

fasty

amen wrote: »

maybe for storing in DB but its a great mechanism for sharing data between systems and for displaying data. Very easy to manipulate using xsl

For pure data transport, it's fine, provided the actual structure is sane, but I guess that applies to all data formats. I'm just sick of seeing piles of XML config files in lieu of code in enterprisey frameworks and having to write yet another XSLT to make sense of files from external sources!

Plus, as an alternative to XML, I'm absolutely loving JSON at the moment.

Dave!

Lads quick question. If I want to allow users to upload files and retrieve them later, how should I accomplish this? As I understand it, binary files can be stored directly in MySQL using the blob datatype.

Would this be the standard way to do it, or would I be better off uploading the file to a directory on the server, and then just store the URL in the database?

Does the method used differ depending on the filetype?

And lastly, if I'm allowing the user to upload multiple files, then I'll have to store multiple URLs in the database. How should I do this? Have a single varchar field, and maybe add some sort of delimiter between the URLs that I add to it?

Or what would be a better way to go about it?

Cheers

edit

Or should I maybe have a seperate table with just file paths/URLs and userIDs in it?

Webmonkey

Hi Dave,

No, the standard way would be to store the files in a directory on your server and have a reference to the filename stored in the database.

No, do not add a varchar with delimiter. This is the first sign of bad database design. You need to normalize it by creating a "files" table with each file row having the user id as a foreign key. But it might be better to associate the files with something else. I'm not sure what your site is or it's design. (I haven't read the thread sorry)

By the way, don't store URL's as well. Just store the name of the image. (You should put some randomisation into the filename as well) and then in your PHP script, reconstruct the URL.

Dave!

Cheers!

No site at the moment, just messing around and trying to learn stuff!

I suppose what I'm trying to create for experimental purposes is a system whereby a user can log into their members' area, and then upload files to the server, and retrieve them later (using a search form).

Any chance of a schema example?

users table:
-user_id -> primary key
-username
-password
etc.

files table:
-file_id
-file_path
...???

I'll have to learn about foreign keys, cos I'm not sure how that works What do you mean by "might be better to associate the files with something else"?

I understand the rest of your post, store the filename, use randomisation, etc.

Thanks for your help

Webmonkey

Sorry my posts can be confusing.


users table:
-user_id -> primary key
-username
-password
etc.

files table:
-file_id
-file_path
..user_id ->Foreign key that references a user's ID (it's primary key in user table)

It's just a reference really back. So each file is associated with a user. Ignore my thing about the reference other thing. For example if a user submitted a post, and it included files. Then it would be better that the file is associated with the post than the user ID and the post is associated with the user.

All you have to do then is select all files where the user id = 'userid'.