Can MySQL Do This... And How?

Ham

I am taking an online PHP course and for my "project" I made a website to catalog all my DVD's and Video Games (there are close to 350 total). I have it working as such:

If you are not logged in (there is a user signup page and password validation, etc) all you can do is view results broken down by categories that contain information from my 'discs' table. If you are logged in however you can edit and delete entries so if a friend borrows a movie and can make an entry to that row in the database that says it is out and who has it.

There are two things that I want to do to the site but do not know if it is possible or perhaps to difficult for me to do:

1) Right now I am the only user. Is it possible though to let other users sign up (this is already possible) and give them their own "section" where they can catalog all their games and movies? Basically, making another user whos information doesn't get mixed in with my own. I am sure there is a much better way of explaining this.

2) On my main page I have it layed out where it reads "Newest XBOX Games", "Newest DVD Movies", etc... Is there a way that I could enter a variable or something for each category where if I add a new Xbox game to the table it would show up on my main page?

Thanks for the continued help guys!

[deleted]

1) Add a column in the table that stores dvds and video games that is an 'owner' column. Then when you want to pull an owners inventory, just use the where clause:

SELECT * FROM table WHERE owner = 'somebody'

2) add a date column to the table. Then you can pull and order by date in descending order:

SELECT * FROM table ORDER BY date DESC

that will give you the newest entries first. You can even limit the results to 5 (or whatever):

SELECT * FROM table ORDER BY date DESC LIMIT 5

Ham

But then won't I have to manually create the pages that query the tables so that it only pulls that uses information off? I want the page to be able to take the 'username' field from my 'users' table and use that match up to the 'discs' table automatically.

So it doesn't matter if I log in or my friend... displaying DVD's would always go to dvd.php. Damn, I just confused myself. 😃

[deleted]

I think a minimum of three tables to start.

Table_1 holds all the dvd names and descriptions.

Table_2 holds user_ids.

Table_3 holds user_id matched to dvd_id

To match up dvd to owner

SELECT dvd_name FROM dvd,owner WHERE owner.user_id=$id AND owner.dvd_id=dvd.dvd_id.

A possible problem with this scenario is that it doesn't address situations where someone has multiple copies of the same dvd.

Ham

Well I am going to attempt this later today but before I do, here is my current table layout.

table 'users'

'id' 'username' 'password' 'email' 'city' 'state'

table 'discs'

'discid' 'system' 'title' 'rating' 'discs' 'loaned'

So you are saying that I need another tabe to combine the 'users' and 'discs' data?

Ham

Lets make it more complicated!!

I am putting aside the multiple users thing and instead going to try to implement something which I am sure is even more difficult. Now, it's just me... just like the site is running right now. I hear all kinds of talk about adding images to your database but really just storing a link to the image which is really stored in a folder on the server.

Right now my data is spit out in a table as such:

Title ------------- + Rating + Discs + Loaned + Delete +

What is involved in making it so on my add_disk.php page where I insert data into the database I can also upload a small thumbnail of the box art. I mean, I know how to program an upload field in HTML but obviously I am here for the PHP. I read in a chapter of my PHP Book about you even being able to have the script resize the image for you! How cool is that 😃

As far as the final product, I guess it would be cool if my table would look like so:

Title --------- + Image ------ + Rating + Discs + Loaned + Delete +
| Disc name | Link 2 image | R | 2 | No | Delete |