[RESOLVED] suggestion on which way to go for doing a query

tbbd

What I have is some tables with a listing of books, rather than having one large table with (id, name, category, etc…), I have each genre of book split up into separate categories (there are many more, but this is just an example)
books_fantasy (id, name, etc…)
books_fiction (id, name, etc…)

Then there are user tables that track the user’s collection of these books
user_books_fantasy (username, fantasy1, fantasy2, etc…)
user_books_fiction (username, fiction1, fiction2, etc…)
The reason why the fields are that way is so I can use one $cat variable that will select the right table and then loop thru each field

What I want to do is query the user table, see if they own that book (field value of more than 0), then get the info from the books table and display it.

Currently the way I have I setup it works ok
It is in a loop that queries the user table, if they have the book, it then queries the books table to get the info.

function display_books($cat)
{
//set max
$result=db_query("select * from user_books_$cat");
$max=mysql_num_fields($result);

//loop thru users(books they own)
$cntr=1;
do
{
$place=$cat.$cntr;
//get data from the users, $place will be a number value of how many they own
$result=mysql_query("select $place from user_books_$cat");
$own_amount=mysql_result($result);
if ( $own_amount > 0 )
{
//they own the book, get book info
$result2 = mysql_query("select * from books_$cat where id='$cntr'");
$info=mysql_fetch_array($result2);
//display the book info
}
$cntr++;
} while ( $cntr < $max );
} //end function


//some code here that defines "$cat" and then calls the function

I'm at work right now, and just typing this out, so if you test it might have parse error or something, but this is the meat of what it should be
Like I said, this works fine and I have no problems with it, I am just wandering if there is another way I could go about doing it, like maybe doing a join statement to do just one query (which at this point I'm not very good at, lol)...I still don't see any other way to do the queries(or query)without using the loop though.
That's why I'm throwing this out here, I keep thinking of it and this is all that I come up with

Thanks

leatherback

Hi, not wanting to be rude, but going to be...

For starters.. Combine the different "book tables" into one book table with a categoryt colum. That will solve 99% of all headaches for the next major update / modifications / processes you want to run on the database. There really is no good argument for splitting these up, unless the book attrributes are seriously different, which I doubt. Instead of a category colum, you could add a category table, with a category_to_book table if a book is allowed to be assigned to multiple categories.

After you have done that, I think your problem is solved already. If not.. Please post again, and I am sure there is lots of people who will help you figure out the rest of the probs.

Good luck.

Jelle.

PS: You might want to use " select into " when combining the tables, assuming they hold data already.

tbbd

no, no, not rude at all, I was looking for an alternative way to do it, I originally had every book into one table with a category field, I just thought that after I had 40,000 books in there, it would take forever doing a query to get all the books listed for that category, would that not be the case then?

but even so, all that would mean is that for this, I just be doing a query on user_books, then books instead of user_books$cat, then books$cat.... all the rest of the code I have there would stay exactly the same and the mehtod for retrieving the data would stay the same, it would just be coming from 1 larger table.. so back to the original thing, what would be the best way for that?

laserlight

I just thought that after I had 40,000 books in there, it would take forever doing a query to get all the books listed for that category, would that not be the case then?

40000 rows in a table does not necessarily constitute a tremendous amount of data. With a well written query and a modern computer, the retrieval of data would be pretty fast.

You should examine the relationships between the entities.
There is a one-many relationship between genres and books. This means that the books table should have a foreign key from the genres table.
There is a many-many relationship between users and books. This means that you need an additional table to store the relations between users and books.

A possible schema would be:

Table: genres
genre_id
name
...

Table: books
book_id
genre_id
name
isbn
...

Table: users
user_id
name
...

Table: users_books
id
user_id
book_id
quantity

You may find it useful to read PHPBuilder's article on Database Normalization And Design Techniques.

all the rest of the code I have there would stay exactly the same and the mehtod for retrieving the data would stay the same, it would just be coming from 1 larger table

No, your queries would change since you would write JOINs to select from related tables.

so back to the original thing, what would be the best way for that?

Well, what exactly do you want to do? Do you want to display the list of books for a given user? Do you want to include the genres of the books in such a display? Do you want to display the books of each and every user? Or do you want to display a list of all the books belonging to the users, with no repeated entries?

tbbd

well, everything here has definetly given me a lot to think of, thanks a lot
I see I really need to redign my way of doing this, thanks again

tbbd

ok, I looked at several different ways of doing this, I put all the books into 1 table with a category field, what I don't understand is this part:

Table: users_books
id
user_id
book_id
quantity

So, for each book that a person owned, there would be a different "row" for that entry? Then if I wanted to get all teh books that they owned I could just select * from users_books where user_id-xxx, then put in a fetch_array?
Just making sure I got that right

laserlight

So, for each book that a person owned, there would be a different "row" for that entry?

Yes. The quantity here would specify how many copies of that book belongs to that user.

if I wanted to get all teh books that they owned

Let's say you already have the user_id of the user, and want to list the names of the books that this user owns.

If $user_id is the given user_id, your SQL statement might be:

SELECT books.name
	FROM books, users_books
	WHERE books.book_id=users_books.book_id
		AND users_books.user_id=$user_id;

leatherback

You would have:

"select * from books
left join users_books
on books.id = users_books.book_id
where user_id = $this_user";

"select * from books
left join users_books
on books.id = users_books.book_id
left join users
on users.user_id = users_books.user_id
where user = '$username';

leatherback

@laser: Do you have an automatic reply-writer? You are too quick for me

tbbd

ok, I did as suggested here, created a table with just the books info, then a table with the users name that had the qty ( so each row would be a book they owned), that way I only had to do 1 query

$ret = db_query( "select books.*, user_books.qty from books, user_books where user_books.qty>0 AND user_books.user_name='{$session["name"]}' AND books.id  = user_books.item_id" );
while ( $info = db_fetch_array($ret) )
{
do my stuff
}

It works perfect, no big long nasty loops, loads very quick too (with the few recors I have of course), thanks a lot everybody..
I still have one question though, in the end this won't be for books, but for a store that sells many small items (scrapbook store), so when there is about 5000+ users, each owning about 5000 items (paper adds up very fast), is this table setup still going to work ok?

leatherback

Hi,

In principle, yes.
It becomes harder when you want to do full-text searches, but as long as you are using a nice normalized database setup, it should work. (Normalized meaning that you do not store the same information more then once. Instead of having a username in eah table, you have a table with user details, and just a user ID. Is things become sluggish, consider specifying indices on specific field you use in searches. But I douct you will need it. For you 5000 records sounds like a lot. For a computer it is not. (I have programs which do 100.000 mathematical operations per run, regressions, correlations and stuff, which take a few minutes). So looking through a list of 5000 items really is not that much

tbbd

that really helps explain it much, and thanks again everybody... I played around with some, and this waw it is SSSSOOOO much faster doing the query, I am much happier with it.... the only performance issue that I have ran into is my admin script where I give myself $amount of every book there is, but hey, that's only gonna happen by me, for me, and only once in a while...but the user part where it lists the books is so much nicer, my database table loads like instantly without those 1000s of fields and the code is reduced to almost nothing...
man, I learned a great deal here