Home | Community | Message Board


This site includes paid links. Please support our sponsors.


Welcome to the Shroomery Message Board! You are experiencing a small sample of what the site has to offer. Please login or register to post messages and view our exclusive members-only content. You'll gain access to additional forums, file attachments, board customizations, encrypted private messages, and much more!

Shop: Kraken Kratom Red Vein Kratom   Unfolding Nature Unfolding Nature: Being in the Implicate Order   PhytoExtractum Kratom Powder for Sale

Jump to first unread post Pages: 1
Invisibleelbisivni
Registered: 10/01/06
Posts: 2,839
PHP/MySQL question
    #8397669 - 05/13/08 05:09 PM (16 years, 9 days ago)

Having a little trouble with the logic behind something here, hopefully someone can lend me a hand (Seuss or Ythan? :eyebrow:)

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.


--------------------
From dust you are made and to dust you shall return.

Extras: Filter Print Post Top
OnlineYthanA
ᕕ( ᐛ )ᕗ
Male User Gallery


Registered: 08/08/97
Posts: 18,840
Loc: NY/MA/VT Borderlands Flag
Last seen: 1 second
Re: PHP/MySQL question [Re: elbisivni]
    #8405089 - 05/15/08 12:10 PM (16 years, 7 days ago)

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.

Extras: Filter Print Post Top
Invisibleelbisivni
Registered: 10/01/06
Posts: 2,839
Re: PHP/MySQL question [Re: Ythan]
    #8405185 - 05/15/08 12:40 PM (16 years, 7 days ago)

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..


--------------------
From dust you are made and to dust you shall return.

Extras: Filter Print Post Top
OnlineYthanA
ᕕ( ᐛ )ᕗ
Male User Gallery


Registered: 08/08/97
Posts: 18,840
Loc: NY/MA/VT Borderlands Flag
Last seen: 1 second
Re: PHP/MySQL question [Re: elbisivni]
    #8405280 - 05/15/08 01:06 PM (16 years, 7 days ago)

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. :wink:

Extras: Filter Print Post Top
Invisibleelbisivni
Registered: 10/01/06
Posts: 2,839
Re: PHP/MySQL question [Re: Ythan]
    #8405358 - 05/15/08 01:25 PM (16 years, 7 days ago)

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.


--------------------
From dust you are made and to dust you shall return.

Extras: Filter Print Post Top
Jump to top Pages: 1

Shop: Kraken Kratom Red Vein Kratom   Unfolding Nature Unfolding Nature: Being in the Implicate Order   PhytoExtractum Kratom Powder for Sale


Similar ThreadsPosterViewsRepliesLast post
* webmasters HELP ( php ) ChromeCrow 714 3 09/08/03 07:47 AM
by Seuss
* hooking openoffice to mysql automanM 782 2 07/22/05 02:54 PM
by automan
* PHP vs. Cold Fusion Evolving 1,126 7 03/15/03 06:08 AM
by Lana
* mysql hint please sherm 850 10 07/01/05 07:25 PM
by automan
* PHP? Crazy! TinMan 1,110 7 09/11/03 10:44 AM
by 3DSHROOM
* Good php scripts? Murex 572 1 01/14/04 06:19 PM
by nife
* Post deleted by Administrator Anonymous 526 1 02/08/03 06:08 PM
by darkfly
* Boards and cpus question
( 1 2 all )
Demiurge 3,305 22 08/23/03 10:59 AM
by fIsh in my head

Extra information
You cannot start new topics / You cannot reply to topics
HTML is disabled / BBCode is enabled
Moderator: trendal, automan, Northerner
765 topic views. 0 members, 0 guests and 2 web crawlers are browsing this forum.
[ Show Images Only | Sort by Score | Print Topic ]
Search this thread:

Copyright 1997-2024 Mind Media. Some rights reserved.

Generated in 0.024 seconds spending 0.007 seconds on 14 queries.