visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Languages :: PHP :: Complex mySQL problem
By: dEcOr U.S.A.  Date: 11/07/2003 00:00:00  English French  Points: 125 Status: Answered
Quality : Excellent
It's kinda complicated to explain it but i will still try my best...
I have a table named 'items_info', with about 60 rows which also means that there are 60 items with about 10+ columns and an unique id was assigned to each items(each row). This table purpose is not for storing data but for storing information of each item only.
I then have another table named 'mem_accounts' for storing accounts information. Again each accounts has an unique id assigned to each account and about 20+ columns to store information about each account.
Now the problem comes...Each account has the ability to own items listed in the table 'items_info' and the purpose of my script is to store the number of an item the account has. Because the table 'items_info' has too much things(more than 60 rows), I decided to think of a way to not open 60 columns in the mem_accounts table.
I decided to make a new table named 'mem_items' with 61 columns with 60 columns numbered from 1-60 which is used to match the id in the item info table 'items_info' and 1 of the columns as the id of the account so that if both the id column in the mem_items table and mem_accounts table matches, it means the items in the mem_items table belongs to the particular account.
However, after creating everything, I discover that it's still not an easy solution to solve this problem as I do not know how to detect which column(the item id) in the mem_items table for a particular account id is not zero(which means the account does not has the item if the column for a item id is zero) and make sure it will return both the column name(the item id) of those which are not zero and the column name itself as I still need the id of the column name to again do a query to get the information for each of the items from the item_info table.

Is this solution a good one for my situation or are there any better solutions for this?
How do solve my problem now so that I could get to know each account having how many of each items?
Thanks a lot.

Did I manage to explain myself?
Please let me know if you don't understand any parts...Sorry
By: VGR Date: 12/07/2003 20:34:00 English  Type : Answer
no. You chose the worst solution ( a matrix becoming quadratic in n² as n users come in, and sparse = lost storage space)

you need a "jointure table" being :
create table join1 (id integer unique auto_increment, item_id integer not null,user_id integer not null);

and you need to only insert useful entries in this.

I think you got the picture.
By: basvg Date: 12/07/2003 21:14:00 English  Type : Comment
Right on the money, VGR. I thought one called 'em 'linkage tables', but what's in a name...
By: VGR Date: 12/07/2003 21:36:00 English  Type : Comment
why not. I heard also "relation tables"
By: dEcOr Date: 12/07/2003 23:25:00 English  Type : Comment
but a user name have many different items with a lot of amount of each kind...
i will need to get the info of what items a user have and how many of the item that the user have.

create table join1 (id integer unique auto_increment, item_id integer not null,user_id integer not null);

i am not sure but this is like not enuff...
By: basvg Date: 12/07/2003 23:40:00 English  Type : Assist
How about this then (changed order of user and item and added amount):

create table join1 (
id integer unique auto_increment,
user_id integer not null,
item_id integer not null,
amount integer not null
);

This table can hold info like so:
1, 1, 1, 10
2, 1, 2, 12
3, 1, 3, 20
etc...

Which means user with id=1 holds items numbers 1 (10 of these), 2 (12 of these), 3 (20 of these)
By: VGR Date: 12/07/2003 23:48:00 English  Type : Comment
if it's not enough, then it means your description wansn't clear enough :D
By: dEcOr Date: 13/07/2003 00:41:00 English  Type : Comment
basvg, good idea but when adding more items of an existing item, am i going to insert another row? if i am going to insert another row, it will be like...

1, 1, 1, 10
2, 1, 2, 12
3, 1, 3, 20
4, 1, 1, 20

then this will be a little difficult for me to alter them becuz of multiple same item ids....

VGR, actually, you almost got the whole idea that i was trying to explain....the table u suggested was alright, but just that i couldnt store the amount of each item.
By: VGR Date: 13/07/2003 05:04:00 English  Type : Comment
either maintain a "amount" column in this "join table" or - more classically - insert as many lines as there are items, and use this :
-to check if a user has an item and eventually have the count of them :
select count(*) as a from jointable where user_id=$userid and item_id=$itemid;
By: dEcOr Date: 13/07/2003 18:33:00 English  Type : Comment
if i were to maintain an 'amount' column in the join table, its a little bit hard for me to count how many a user has an item and its also hard to decrease the amount of certain of an item a user has...

as for the insert as many lines solution, its easier to count but when the table gradually become bigger, will it take up a lot of storage? this solution is easier to count but what if when i want to lets say decrease 50 of a certain item own by a user? i know i will delete 50 row but how should it be? also what about increasing? insert 50 new rows? but will it take a long time to load and do this process? what i am thinking is either a while loop or for loop?
By: VGR Date: 13/07/2003 18:51:00 English  Type : Comment
put an "amount" field in this "hasitems" relation table, and for decrementing/incrementing, do :

UPDATE hasitems SET amount=amount-50 WHERE user_id=$this and item_id=$that;
By: dEcOr Date: 13/07/2003 19:50:00 English  Type : Comment
ermm...sorry but i dun quite understand...is the 'hasitems' table the previous table we were talking or is it a new table?

if 'hasitems' table is the previous table, isnt it like what basvg had said? yes, i can easily increase and decrease with the query u stated but the user might not have that item yet and the script doesnt know and will not be able to increase to let the user has the item has the table does not have the item id for the user id yet...
By: VGR Date: 13/07/2003 20:09:00 English  Type : Comment
1) yes

2) no , because the UPDATE ... WHERE... will fail to find any matching line and thus perform no operation at all..
By: basvg Date: 13/07/2003 20:10:00 English  Type : Comment
I think that with the change I made to VGR's join-table (or linkage-table or relation-table) you should be able to do anything you want. It's just a matter of figuring out the correct SQL statements to do what you want with it. I wouldn't change it to let it synch with the items table: that will get messy and hard to update.

1. Has user #1 any items of item #2 and if so, how many?
select amount from jointable where user_id=1 and item_id=2

2. user #1 gets 10 more of item #2
already has items #2: update jointable set amount=amount+10 where user_id=1 and item_id=2
doesn't have item #2: insert into jointable set user_id=1, item_id=2, amount=10

3. user #1 loses 20 items of item #2
he can only do that if he has item #2, so: update jointable set amount=amount-20 where user_id=1 and item_id=2

I think you get the picture, right?
Make a function, or something, of the first query, 'cos you need to now the result of that before you can do other updates/insert on the jointable.

Something like this:

function getAmount($user_id, $item_id) {
$sql = 'select amount from jointable where user_id=1 and item_id=2';
$res = mysql_query($sql) or die('bad sql');
list($amount) = mysql_fetch_array($res);
return $amount;
}
By: VGR Date: 13/07/2003 20:21:00 English  Type : Comment
exactly. except that in case "2;" the logic ***could be*** "add 20 more items [implicitly : it must already have some]", thus a simple UPDATE [eventually failing] would be enough. It's up to the Asker to choose his work logic.
By: basvg Date: 13/07/2003 20:37:00 English  Type : Comment
Right, point number 2 should read:

2. user #1 gets 10 (more) of item #2

At the moment this question arrises (user #1 gets 10 items of item #2), you probably won't now if this user already has item #2 (indeed depending on dEcOr's work logic). So, first get the amount and then chose between an update or insert... Something like that. Combining this in one function seems like a smart thing to do.

Anyhow, you really need two queries to handle this point.
By: VGR Date: 13/07/2003 21:15:00 English  Type : Comment
except with a properly crafted layout and a single REPLACE INTO ;-)
By: dEcOr Date: 13/07/2003 23:54:00 English  Type : Comment
as for case 2, user #1 gets 10 more of item #2
but if user #1 does not even have any of item #2, i will have to insert a new row.
that means i have to detect if a user has the item or not before increment or decrement so that i can know to insert row or update row.
how should i detect to see if a user has the item or not before going on to a insert or update query? what i am thinking is using count function to see of the number return to indicate but i think my idea is a little stupid...
By: VGR Date: 14/07/2003 00:01:00 English  Type : Comment
no, it's the proper way.

quoting "Comment from VGR Date: 07/13/2003 12:04AM PDT "

-to check if a user has an item and eventually have the count of them :
select count(*) as a from jointable where user_id=$userid and item_id=$itemid;
By: dEcOr Date: 14/07/2003 22:26:00 English  Type : Comment
oh...sorry...i think i missed that part out...

anyway, to list the tables, i will use

$result = mysql_query("select * from jointable where user_id = $userid");
while($array = mysql_fetch_array($result)) {
print "You have the item $array[item_id] and you have $array[amount] of them";
}

is this correct and it will just list whatever the user has?
By: basvg Date: 14/07/2003 22:37:00 English  Type : Comment
Make it like this:

$result = mysql_query("select item_id, amount from jointable where user_id = $userid");
while ($array = mysql_fetch_array($result)) {
print "You have the item ".$array['item_id']." and you have ".$array['amount']." of them";
}

Works better this way - arrays and all.

You could do a left join on the items table, to get the correct name of the referred item, all in one query.
By: VGR Date: 14/07/2003 23:06:00 English  Type : Comment
hummm


print "You have the item {$array[item_id]} and you have {$array[amount]} of them";
By: VGR Date: 14/07/2003 23:06:00 English  Type : Comment
and please quote your string litterals :

print "You have the item {$array['item_id']} and you have {$array['amount']} of them";
By: basvg Date: 14/07/2003 23:19:00 English  Type : Comment
I've read something about the { and } - that they will be depricated one of these days, or something. I wouldn't count on the workings of those. Anyhow, imho it's a bit unreadable as the { and } are not rendered.

And yes, I've commented on it too: USE QUOTES!

Btw, how's the weather now in Belgium? ;-)
By: VGR Date: 14/07/2003 23:25:00 French  Type : Comment
34°C à crever
By: dEcOr Date: 15/07/2003 23:50:00 English  Type : Comment
thanks VGR and basvg! :D

btw, that's kinda hot...
By: basvg Date: 16/07/2003 00:01:00 English  Type : Comment
Thanks.
It's gonna be tropical here...
By: VGR Date: 16/07/2003 00:49:00 English  Type : Comment
37°C today (in the shadows, not in direct sun)

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 362.344030 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page