Server-side scripting is used for a variety of reasons - mainly where access to a database is required, but also where there is a requirement to customise pages for different users. Different technologies and products work in slightly different ways, but, essentially, server-side scripting forms a program that generates an HTML page as its output, and it's the HTML page that is sent to the user's browser.
By using server-side scripting - i.e. a client-server architecture - you are minimising the amount of traffic to and from the web-server, because you are only sending the result of any database query; there is no need to send the whole of the database for the client to search. This has the obvious benefit of making the page load more quickly, but it's also more secure in that the user only receives data that they're entitled to access.
As less processing is done by the client, the pages tend to be smaller and therefore place less of a load on the client's browser. This can be important when using a low-powered device such as a phone or tablet, and reduces the likelihood of any compatability issues.
PHP and "Classic" ASP
PHP and "classic" ASP work in pretty-much the same way - although the languages used are different. ASP generally uses VBScript, whereas PHP is a programming language in its own right.
Generally, the programmer inserts small snippets of code into the page where the processing needs to take place. The web-server then interprets this code and inserts the output of the program into the same place in the page. From the client's perspective, there is no difference between the content generated by the script and any static content - they just receive a standard HTML page.
For example, if I wanted to include today's date on my page, using ASP, the relevant section of the source code for my page would look like this (the <% and %> characters separate the script from the rest of the page):
... <p>Today's date is <%=Date()%>.</p> ...
When the user requests the page, the web-server runs the code and replaces the section between <% and %> with it's output, so what the user receives is plain text - e.g. (assuming that today is 19th February 2012)
<p>Today's date is 19/02/2012.</p>
As server-side scripting is just text, you can write your scripts in web-design applications, such as Dreamweaver or Expression Web, or a even a text editor like Notepad.
For an example of a PHP client-side script access a database, have a look at my daughter's Food and Farming Top Trumps project. It effectively works like a mail-merge - there is a database of facts about the various crops, and a PHP script containing the HTML and CSS for a single card. The PHP script fetches the records from the database and loops through them, inserting the details into the card for each one.
ASP.NET is a newer offering from Microsoft, and works in a different way. Pages are usually created in the Visual Studio development environment - or it's free-to-download Express version. Pages are created much like Visual Basic applications or Access forms - controls are dragged from the toolbox onto the page, and then code can be added to certain events.
This means that it's much quicker to create pages - especially ones that access databases - and there is a greater number of controls that can be used on your web-page. You can also do things such as "requerying" combo boxes when other changes are made to a page - very much like on an Access form.
The main downside of this newer technology, for me, is that the page sent to the client - while still compatible with various browsers - is very much more complicated than one generated using "classic" ASP or PHP. If you were to view the source code of an ASP.NET page in your browser, you wouldn't be in any doubt that it wasn't hand-coded by the developer. ASP and PHP pages, on the other hand, can be impossible to distinguish from hand-coded pages. Another downside is that pages can be created by people with no understanding of web-pages or HTML - or, looking at it the other way round, developing ASP.NET pages won't necessarily give you an understanding of HTML or CSS.