Need advice on SQL statement.

Paul_help_

Hi guys.

I am in the middle of building a rating application for a customer. It is just an application that allows his customers to leave a review on the services he provides.

Obviously to do this I am going to have to work with databases to store the reviews and display them on his website, for others to see. I have already done this.

But now I am ready to build him an admin area where he can 'log in' and delete testimonials that are no good (if he wishes to).

I already have a database table which contains the testimonials/reviews (sent from his reviews page) and i have named it 'testimonial_data'.

And I created a constant called: TBL_TESTIMONIALS_DATA

..... this is so i can refer to it in SQL statements.

So now I have created another table within the same database called 'admin_accounts' (which contains the usernames and passwords of any existing administrators).

And I created a constant called: TBL_ADMIN_ACCOUNTS

Now this is the point where I am struggling.

I can not figure out how to write an SQL statement that will allow me to gain access to all the rows in the 'testimonial_data' table - based on whether a username and password are matched in the 'admin_accounts' database.

I kind of understand how the first part of the statement would go:

SELECT * FROM " . TBL_TESTIMONIALS_DATA . " ......

........ but I don't know how to finish this statement off so I can include the other table with all the usernames and passwords (admin_accounts).

I want to be able to select all of the rows from 'testimonial_data' if a username and password pair match.

Paul.

laserlight

You should tell us what columns there are in each table, and how they are related.

Paul_help_

Hi.

I think I already explained how they are related (or how I want them to be related).

'admin_accounts' contains 3 columns: "adminID" "username", "password"

..... and 'testimonial_data' contains 6 columns: "id" "rating", "firstname", "lastname", "postcode", "comment"

'testimonial_data' holds the values posted in a form, from the reviews page.

pbismad

You wouldn't do this in the sql query that's retrieving the testimonial data. You would have a user login system (to let users authenticate who they are) and a user permission system (to determine what the logged in user can see and do.)

An "admin area" is a web page that requires a logged in user, that has 'admin' permissions, in order to see and perform actions coded on the page. If the current visitor isn't logged in and isn't an admin, the admin area page would never get to the point of running an sql query to retrieve any of the testimonial data.

NogDog

I would agree that the better approach would be to do the user access via the application code versus the SQL (though SQL would be involved when logging in the user to set a session flag indicating their admin privileges, or however else you want to control it).

if($_SESSION['user_type'] == 'admin') {
    // Admin code/html here
}
else {
    // maybe an access denied, or header redirect elsewhere?
}

dalecosp

Paul help!;11061739 wrote:
Obviously to do this I am going to have to work with databases to store the reviews and display them on his website, for others to see. I have already done this.

In your database work, did you become familiar with JOINs and sub-queries?

And please do listen to the advice of others about the design of your app as it relates to user permissions, login system, etc.

Paul_help_

Hi. Thanks guys.

Yes, I am familiar with JOINS but, you typically would only use joins with 2 different fields of two different tables that share corresponding values wouldn't you?

pbismad

Yes, a JOIN query is used when you have related data in one (you can join a table to itself) or more tables.

dalecosp

Paul help! wrote:
I can not figure out how to write an SQL statement that will allow me to gain access to all the rows in the 'testimonial_data' table - based on whether a username and password are matched in the 'admin_accounts' database.

Two tables. Probably not a case for JOIN, but you might see where I was coming from. As the rest of you correctly surmised, Paul actually needs a login system.