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.