|
Welcome to the Shroomery Message Board! Please  login or  register to post messages and view our members-only content. You'll gain access to additional forums, encrypted messages, file attachments, board customizations, and much more!
|
 elbisivni
Registered: 10/01/06
Posts: 2,018
|
 PHP/MySQL question
#8397669 - 05/13/08 05:09 PM |
|
|
Having a little trouble with the logic behind something here, hopefully someone can lend me a hand (Seuss or Ythan?  )
I'm building a kind of financing program in which users can create a series of columns (in one table) and then put $$$ values under those columns (held in another table), each row representing one day.
Right now the script cycles through the columns inside each day row, and finds $$$ records that have position values appended to them that tell the program where to put them.
I want the user to be able to reorder their rows in one table, and have all the position values in the $$$ table to sync up.
What I originally did was a mysql_query that would update the position value of a column in both tables to an obscure, unique number - then update the records with the number the prior will be changed to to what the prior was - and finally update the 1st set to the proper numbers.
But that was kind of a sloppy way of going about it obviously..
--
I tried to be as simple and straight-forward with my explanation, so hopefully someone will get the gist of what I'm trying to do here.
--
What I think I need to do is attach some sort of id on the column table. There is already a common link between the column table and the $$$ table, the position value. But I should be able to change only the position in the column table to subsequently change the display order of records from the $$$ table.
I'm expecting I'll have to rewrite a major portion of my script to accommodate this function. Not exactly sure where to start now, just fishing for some ideas.
--------------------
superstition
1: a belief or practice resulting from ignorance, fear of the unknown, trust in magic or chance, or a false conception of causation
2: a notion maintained despite evidence to the contrary
|
 Ythan
0011101111111110
 
Registered: 08/08/97
Posts: 10,401
Loc: Upstate NY
Last seen: 1 hour, 38 minutes
|
|
Okay let me make sure I understand this right. You want users to be able to set up something like this, with arbitrary user-defined columns:
Code:
+-------------+-------------+-------------+-------------+
| | NY Location | CA Location | AZ Location |
+-------------+-------------+-------------+-------------+
| 4/20/2008 | $6,000 | $4,000 | $1,000 |
+-------------+-------------+-------------+-------------+
| 4/21/2008 | $3,000 | $8,000 | $5,000 |
+-------------+-------------+-------------+-------------+
| 4/22/2008 | $1,000 | $9,000 | $7,000 |
+-------------+-------------+-------------+-------------+
and you want the capability to reorder the rows, eg. by date:
Code:
+-------------+-------------+-------------+-------------+
| | NY Location | CA Location | AZ Location |
+-------------+-------------+-------------+-------------+
| 4/22/2008 | $1,000 | $9,000 | $7,000 |
+-------------+-------------+-------------+-------------+
| 4/21/2008 | $3,000 | $8,000 | $5,000 |
+-------------+-------------+-------------+-------------+
| 4/20/2008 | $6,000 | $4,000 | $1,000 |
+-------------+-------------+-------------+-------------+
Is sounds like you just need a join, let me know if I understand the problem correctly and I'll elaborate.
--------------------
 Visit the Growery!
|
elbisivni
Registered: 10/01/06
Posts: 2,018
|
|
I want users to be able to change the order of the columns, say from
Code:
+-------------+-------------+-------------+-------------+
| | NY Location | CA Location | AZ Location |
+-------------+-------------+-------------+-------------+
| 4/20/2008 | $6,000 | $4,000 | $1,000 |
+-------------+-------------+-------------+-------------+
| 4/21/2008 | $3,000 | $8,000 | $5,000 |
+-------------+-------------+-------------+-------------+
| 4/22/2008 | $1,000 | $9,000 | $7,000 |
+-------------+-------------+-------------+-------------+
to
Code:
+-------------+-------------+-------------+-------------+
| | CA Location | NY Location | AZ Location |
+-------------+-------------+-------------+-------------+
| 4/20/2008 | $4,000 | $6,000 | $1,000 |
+-------------+-------------+-------------+-------------+
| 4/21/2008 | $8,000 | $3,000 | $5,000 |
+-------------+-------------+-------------+-------------+
| 4/22/2008 | $9,000 | $1,000 | $7,000 |
+-------------+-------------+-------------+-------------+
and have all the $ value records realign correctly. Right now they align based on a position key as demonstrated in the tables below.
----------
Here's what my two tables look like..
userColumns
Code:
+-------------+-------------+-------------+-------------+
| userid | columnName | position | sign |
+-------------+-------------+-------------+-------------+
| 1 | Income | 1 | + |
+-------------+-------------+-------------+-------------+
| 1 | Taxes | 2 | - |
+-------------+-------------+-------------+-------------+
| 1 | Grcoery | 3 | - |
+-------------+-------------+-------------+-------------+
etc..
userRecords
Code:
+------+--------+-------------+-------------+-------------+
| id | userid | date | value | position |
+------+--------+-------------+-------------+-------------+
| 25 | 1 | 2008-05-05 | 1500.00 | 1 |
+------+--------+-------------+-------------+-------------+
| 26 | 1 | 2008-05-05 | 400.00 | 2 |
+------+--------+-------------+-------------+-------------+
| 27 | 1 | 2008-05-06 | 150.00 | 3 |
+------+--------+-------------+-------------+-------------+
etc..
sorry I couldn't format these samples better..
--------------------
superstition
1: a belief or practice resulting from ignorance, fear of the unknown, trust in magic or chance, or a false conception of causation
2: a notion maintained despite evidence to the contrary
|
Ythan
0011101111111110
Registered: 08/08/97
Posts: 10,401
Loc: Upstate NY
Last seen: 1 hour, 38 minutes
|
|
Okay you probably need to do something like this:
Code:
# Custom column definitions
CREATE TABLE `UserColumns` (
`UC_KeyID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`UC_UserID` INT UNSIGNED NOT NULL,
`UC_Order` INT UNSIGNED NOT NULL,
`UC_Title` VARCHAR(255) NOT NULL,
INDEX (`UC_UserID`),
UNIQUE (`UC_KeyID`)
);
# Individual entries
CREATE TABLE `UserEntries` (
`UE_KeyID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`UE_Column` INT UNSIGNED NOT NULL,
`UE_Date` INT UNSIGNED NOT NULL,
`UE_Value` INT NOT NULL,
INDEX (`UE_Column`),
UNIQUE (`UE_KeyID`)
);
# Create 3 columns each for 2 different user ids
INSERT INTO UserColumns (UC_UserID, UC_Order, UC_Title)
VALUES (1, 1, 'Test Column 1'), (1, 2, 'Test Column 2'), (1, 3, 'Test Column 3'), (2, 1, 'Happy Meals'), (2, 2, 'Sex Toys'), (2, 3, 'Yoga Lessons');
# Populate 3 days of entries for each column
INSERT INTO UserEntries (UE_Column, UE_Date, UE_Value)
VALUES (1, 1208707200, 4000), (1, 1208793600, 3000), (1, 1208880000, 5000),
(2, 1208707200, 3000), (2, 1208793600, 2000), (2, 1208880000, 9000),
(3, 1208707200, 7000), (3, 1208793600, 5000), (3, 1208880000, 4000),
(4, 1208707200, 8000), (4, 1208793600, 8000), (4, 1208880000, 2000),
(5, 1208707200, 1000), (5, 1208793600, 7000), (5, 1208880000, 6000),
(6, 1208707200, 9000), (6, 1208793600, 1000), (6, 1208880000, 8000);
# Display user 1's data
SELECT YEAR(FROM_UNIXTIME(UE_Date)) AS UE_Year, MONTH(FROM_UNIXTIME(UE_Date)) AS UE_Month, DAY(FROM_UNIXTIME(UE_Date)) AS UE_Day, UC_Title, UE_Value
FROM UserEntries, UserColumns WHERE UC_UserID = 1 AND UE_Column = UC_KeyID
GROUP BY UC_KeyID, UE_Year, UE_Month, UE_Day
ORDER BY UC_Order, UE_Year ASC, UE_Month ASC, UE_Day ASC, UC_Title ASC;
# Change the row order
UPDATE UserColumns SET UC_Order = CASE UC_KeyID
WHEN 1 THEN 2
WHEN 2 THEN 3
WHEN 3 THEN 1
ELSE UC_Order END;
# Display user 1's data again
SELECT YEAR(FROM_UNIXTIME(UE_Date)) AS UE_Year, MONTH(FROM_UNIXTIME(UE_Date)) AS UE_Month, DAY(FROM_UNIXTIME(UE_Date)) AS UE_Day, UC_Title, UE_Value
FROM UserEntries, UserColumns WHERE UC_UserID = 1 AND UE_Column = UC_KeyID
GROUP BY UC_KeyID, UE_Year, UE_Month, UE_Day
ORDER BY UC_Order, UE_Year ASC, UE_Month ASC, UE_Day ASC, UC_Title ASC;
# Display user 2's data, newest first
SELECT YEAR(FROM_UNIXTIME(UE_Date)) AS UE_Year, MONTH(FROM_UNIXTIME(UE_Date)) AS UE_Month, DAY(FROM_UNIXTIME(UE_Date)) AS UE_Day, UC_Title, UE_Value
FROM UserEntries, UserColumns WHERE UC_UserID = 2 AND UE_Column = UC_KeyID
GROUP BY UC_KeyID, UE_Year, UE_Month, UE_Day
ORDER BY UC_Order, UE_Year DESC, UE_Month DESC, UE_Day DESC, UC_Title DESC;
Displaying the results in a tabular format is left as an exercise for the reader. 
--------------------
Visit the Growery!
|
elbisivni
Registered: 10/01/06
Posts: 2,018
|
|
So it looks like what I want is this CASE-WHEN-THEN bit that will reorder columns as I tell it? I'll have to look into how to use it, looks simple enough.
--------------------
superstition
1: a belief or practice resulting from ignorance, fear of the unknown, trust in magic or chance, or a false conception of causation
2: a notion maintained despite evidence to the contrary
| |
|
|
You cannot start new topics / You cannot reply to topics
HTML is disabled / UBBCode is enabled
Moderator: Lana, Diploid, automan
139 topic views. 1 registered and 1 anonymous users are browsing this forum.
[ Toggle Favorite | Print Topic ]
| | |
|
|
Previous
Index
Next
Threaded
Edit 
Reply 
Quote 
