Topics
This week we start to explore how SQL databases can integrate with webpages. This seems like arcane technology and in fact is pretty straightforward in terms of technique, however it is important to consider the power of this technology. The largest and fastest growing companies in the world today - Google, Facebook, Amazon – are basically just web databases.
Some of the PHP and programming concepts may be very new to some of you, or may be review for others, but I find it useful to review both of these chapters as these authors offer very clear explanations of many of the techniques involved in using web database servers. The HTML may be new to some of you, but the code samples in the book should be sufficient to get started. You can review the parts of chapter 14 about command line PHP and PHPMYADMIN quickly as they are beyond the scope of this class.
Your PHP files need to be uploaded to your server account on student.santarosa.edu using any FTP software – they will not run on your local system. The database server is running on the webserver itself, so you can refer to it as localhost. Here is a version of the example PHP webpage on page 406-407 using my account on the SRJC student server.
Assignments – Due May 12
- Reading and exercises in Chapters 13 & 14
- Project 4 – A Database report on a webpage
Create a PHP page that lists the following information from your MySQL database created in Project 3:
BRANCH SALESPERSON TOTAL SALES ordered by TOTAL SALES
Create a second PHP page that lists each sales transaction to date for a salesperson. CUSTOMER VARIETY VINTAGE TOTAL SALE ordered by VINTAGE. Make the salesperson’s name value on the first page a link to the second PHP webpage listing all sales for that salesperson. - Quiz 4
Ooooh how exciting
Hi Jay,
I read Chapter 13 and have downloaded the FileZilla ftp tool as suggested at the end of Chapter 13. I”m a bit confused as to where I am supposed to write the html/PHP script that is in the book and above. Is this going in Workbench or FileZilla? Also, how do I save this, or does that not matter?
Francesca – excellent question. The PHP file is basically a text file with the code and a .php file name extension. When you upload the file to the server, you can view it through a browser and the PHP gets the data from the database and displays it on the page. The code on page 406 & 407 is a good start and I have shown you in this post how to set the variables for accessing your database. I will post a short video tutorial on uploading the file with FileZilla in the next day or so.
In the Project 4 description it says “Create a PHP page that lists the following information from your MySQL database created in Project 3:” but doesnt list what information you want displayed.
@ Francesca – You write the html/PHP in a texteditor like, Notepad or Notepad++, or an IDE like eclipse or netbeans. Just make sure you save the file with a .php extension. Then use FileZilla to upload that file to the server. I dont think its possible to write the PHP in Workbench but I could be wrong.
Thank you Paul. Don’t know what happened there. I updated the assignment, let me know if you have any questions. There are several new techniques to incorporate in this assignment.
No it is not possible to write the PHP in MySQL Workbench, but you could use it to write and test your SQL statements before incorporating them into PHP.
“Create a second PHP page that lists each sales transaction to date for a salesperson. DATE CUSTOMER VARIETY VINTAGE TOTAL SALE ordered by DATE. ”
Confused with “DATE”, did you mean vintage date? Or was there a date field some where that I’m forgetting about?
Hi Jay,
Where is the DATE information coming from for the second script? Unless I messed up my initial table creation, I don’t have any date information aside from the Vintage year for each grape variety.
Thanks.
@Brian, Noah, SQL Class – Yes, sorry for the confusion, I intended to say order by vintage. I updated the assignment.
Hi Jay,
Could you open Quiz 4 please? Thank you!
Sorry, Quiz 4 is now open
Hi Jay,
I have managed to figure out how to connect to the SRJC webserver using FileZilla and have uploaded my first php file for Project 4. Now I need to know where to find the webpage to check the results. Can you tell me where it is please?
Also, for the second php page in Project 4 how do we link the salesperson’s name from the first page to this page? Is this supposed to be in the SQL query or written in php? Where can I look in the textbook for help in figuring this out?
There’s not much communication going on on this page about Project 4. Is everyone else sailing through it, or am I alone in wondering how I’m supposed to figure this php stuff out? I really wanted to learn SQL!!!!!!
@Francesca – Your page is at http://student.santarosa.edu/~frohr/Week6_Query1.php – in other words, the public_html folder in the root folder when you login from FTP is mapped to a subfolder with your username (prepended with a tilde) on the webserver.
Unfortunately your page doesn’t display so there is a PHP syntax error. You can send me a zipped copy of the .php page and I will help see what the issue is.
Project 4 is based on a technique that is somewhat explained in the textbook on pages 452-455. In the first PHP page, each salesperson listed should be wrapped in a hyperlink with a query string that is generated by the PHP – such as http://xxx/sales_by_salesperson.php?salesperson_name=Smith. So your PHP in the first page would return the salesperson_name two times, once to display on the page and once within an a tag wrapped around the name displayed on the page to make it a link.
The SQL query in the second page would be based on a query that dynamically gets that value and uses it in the SQL query, ie : “SELECT customer, variety, vintage, tons * price as order-total FROM xxx WHERE … AND salesperson_name = “{$_GET["salesperson_name"]}”". The $_GET[] variable is passed from the link in the first page to the second page as a query string, it is part of the URL and the value can be found in the $_GET[xxx]
I uploaded my files to the student server but it looks like the php code is not happy. Is there a way to debug it? I added one like at a time and the page was showing blank when I added the following code:
A blank PHP page means that there is a syntax error in the PHP. It is difficult to debug PHP using a text editor, and it is impossible to debug just by looking at the page.
Dreamweaver and other tools offer code editing tools that highlight syntax errors. If you send me your page as a zipped attachment I can take a look at it.
Here is my path: http://student.santarosa.edu/~pgong/sqlclass/sales.php
what day and time will quiz 4 be closed?
Quiz 4 closes at midnight on May 12