|
Your description isn't clear enough, so here's a design that could be used for some interpretations of what you wrote.
0) There are several sites.
1) A check list has several check list items.
2) Each site has a check list with a subset of all available check list items.
3) Each check list item may be on several sites' check lists.
This is a classic many-to-many relationship and the typical implementation is to have (at least) three tables:
Site: ID, Name, etc.
Item: ID, Name, etc.
SiteList: SiteID, ItemID (usually no other data here, but maybe in some cases)
Another schema would allow for defining lists and assigning them to sites:
Site: ID, Name, etc.
Item: ID, Name, etc.
List: ID, Name, etc.
ListItem: ListID, ItemID (usually no other data here, but maybe in some cases)
SiteList: SiteID, ListID (usually no other data here, but maybe in some cases)
This second way may be approriate for your needs, you can define a common list and custom lists and assign multiple lists to the sites. Getting a complete list of all items for a site is simple as well.
|
|
|
|
|
I've some data, there are
RealData Balance Sort by Desc
A 684 345 A 684
B 60 - E 308
C - - J 302
D - - I 273
E 308 - O 178
F - - H 140
G 11 - B 60
H 140 - N 27
I 273 - Q 22
J 302 - R 20
K - - P 14
L - - G 11
M - - C -
N 27 - D -
O 178 38 F -
P 14 - K -
Q 22 - L -
R 20 4 M -
Description
Proc = Process
Res = Result
Sort by Desc
Get 8 row data
Proc 1 Res 1 Proc 2 Res 2 Proc 3 Res 3 Proc 4 Res 4 Finish for 8 row data
A 684 657 A 657 635 A 635 621 A 621 610 A 610
E 308 281 E 281 259 E 259 245 E 245 234 E 234
J 302 275 J 275 253 J 253 239 J 239 228 J 228
I 273 246 I 246 224 I 224 210 I 210 199 I 199
O 178 151 O 151 129 O 129 115 O 115 104 O 104
H 140 113 H 113 91 H 91 77 H 77 66 H 66
B 60 33 B 33 11 R 20 6 B 11 - R 6
N 27 - Q 22 - P 14 - G 11 - B -
Q 22 22 R 20 20 B 11 11 R 6 6 G -
R 20 20 P 14 14 G 11 11 P - - P -
P 14 14 G 11 11 Q - - Q - - Q -
G 11 11 N - - N - - N - - N -
C - - C - - C - - C - - C -
D - - D - - D - - D - - D -
F - - F - - F - - F - - F -
K - - K - - K - - K - - K -
L - - L - - L - - L - - L -
M - - M - - M - - M - - M -
Result 1 27
Result 2 22
Result 3 14
Result 4 11
Total 1 74 (For 8 row data)
Get 4 row data
Proc 1 Res 1 Proc 2 Res 2 Proc 3 Res 3 Finish for 4 row data
A 610 411 A 411 376 A 376 347 A 347 345
E 234 35 O 104 69 O 69 40 O 40 38
J 228 29 H 66 31 H 31 2 R 6 4
I 199 - E 35 - J 29 - H 2 -
O 104 104 J 29 29 R 6 6 J - -
H 66 66 R 6 6 E - - E - -
R 6 6 I - - I - - I - -
B - - B - - B - - B - -
G - - G - - G - - G - -
P - - P - - P - - P - -
Q - - Q - - Q - - Q - -
N - - N - - N - - N - -
C - - C - - C - - C - -
D - - D - - D - - D - -
F - - F - - F - - F - -
K - - K - - K - - K - -
L - - L - - L - - L - -
M - - M - - M - - M - -
Result 1 199
Result 2 35
Result 3 29
Result 4 2
Total 2 265 (For 4 row data)
How to get this data using Query or Function? And how to using in PHP?
Please help!
Thank you.
Jimmy
modified 5-Dec-12 2:24am.
|
|
|
|
|
Create a connection to the database (unknown as you did not name it)
Create a query to extract the data from the unknown database
Put the results of the query in your collection (PHP and is nothing to do with the database)
So the only relevant information you have given us is PHP in a DATABASE forum! Find a tutorial about PHP and your unknown database and work through the tutorial. Come back and ask SPECIFIC questions when you have trouble!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi, Mycroft Holmes
Thank for reply.
You means the data can not using Query (MySQL) but use PHP.
Okay I move this forum to Web development. But how to move this topic to Web Development?
Thanks
|
|
|
|
|
I have a simple SQL table as follows:
CREATE TABLE [dbo].[DaysMonth](
[SNo] [int] NOT NULL,
[Month] [varchar](20) NOT NULL,
[Year] [varchar](4) NOT NULL,
[NoOfDays] [int] NOT NULL,
[Days] [nchar](10) NULL,
[Holiday] [nchar](1) NULL,
[ToDate] [date] NULL,
[Period] [nchar](30) NULL,
Following is the query:
Select SNo, NoOfDays, [Month], [Year], [Days], [Holiday] From DaysMonth Where [Period]='November,2012' order by ToDate
Now, i'm using this query in web application using ASP.Net environment and C# language within a loop. using SQL Server 2008. basic purpose here is to get all days within a period, say January,2012 in a sorted manner.
Out of 100 times running this query, 97-98 times, it's producing correct result, however, 1-2 times, sort is not working, so instead of 1st to 31st, output coming like 10th to 31st then 1st to 10th. Getting puzzled, as it's a huge application running on multi-user environment, getting very difficult to take care of error which is absolutely unpredictable and illogical. Please suggest where is the problem, thanks to all in advance.
|
|
|
|
|
Did you try:
Select SNo, NoOfDays, [Month], [Year], [Days], [Holiday] From DaysMonth Where [Period]='November,2012' order by ToDate ASC
Perhaps the direct order of sorting with "ASC" could help.
AnirbanM 2 wrote: Now, i'm using this query in web application using ASP.Net environment and C# language within a loop
Can't you let the SQL-Statement do the work of the loop? It would be more effective!
------------------------------
Author of Primary ROleplaying SysTem
How do I take my coffee? Black as midnight on a moonless night.
War doesn't determine who's right. War determines who's left.
|
|
|
|
|
Thanks, I will try but as sort is set by default as ASC in SQL server, I never put asc separately.
|
|
|
|
|
AnirbanM 2 wrote: Out of 100 times running this query, 97-98 times, it's producing correct result, however, 1-2 times, sort is not working, so instead of 1st to 31st, output coming like 10th to 31st then 1st to 10th.
From that, I'd say you have different collations (or regional settings on the client). But AFAIK, that would only apply to string-types, like VARCHAR and NTEXT.
Try converting the datetime to a fixed-format string, and sort on that.
|
|
|
|
|
Thanks for your reply. In SQL server, regional setting is set as default. Moreover, I need to do sorting based on Date only otherwise, it will defeat my purpose. However, as you are saying, i will try to do so.
|
|
|
|
|
I got a gut-feeling saying it's converting to DATE to a VARCHAR, that's the only way to have 10 precede the number 2.
Contrary to the previous advice, what happens when you cast it to a DOUBLE? Internally, a date is a floating-point number.
|
|
|
|
|
I notice that your table has Month, Year, Days all as character. If the sort uses these columns it might produce your problem.
|
|
|
|
|
Thanks for varchar columns are not included in sorting.
|
|
|
|
|
Always always always store date/time related values as DATE, DATETIME, etc.; never as string.
|
|
|
|
|
His ToDate is stored as a Date type, first thing I looked for!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
And yet his OrderBy is Todate
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Which is an all-you-can-eat sushi place -- very fishy indeed... 
|
|
|
|
|
Thanks for your reply, however, i'm not getting your point, if i'm sorting based on date and other fields are characters, what's the problem? If anything there, i will surely change it.
|
|
|
|
|
Thanks, but sort is based on Date column only. As you rightly said, I always keep date as date column only. Month, Year, NoofDays etc, these columns are meant to be varchar, these are not included for sorting.
|
|
|
|
|
There are a bunch of uglies in this table design
1. Storing numerics as character
2. Mixing varchar and nchar for no obvious reason
3. Using nchar instead of char (I would use varchar)
4. Using nchar instead of a bit (holiday)
Personally I think you should have only 3 fields in the table SNo,ToDate and Holiday. All the others should be calculated in a view!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Thanks for your valuable feedback. I will change all nchar to varchar and holiday to bit. Moreover, I can safely remove Month and Date. But as you are suggesting, rest of the fields are calculative, then I need to make query then construct data table, make calculation and then put it into grid, otherwise, i'm simply getting all values from sql and putting straightaway into grid. So I made a trade off here, though it may not be the best way to do so.
|
|
|
|
|
What I can't understand and one of the puzzle is why SQL Server is not consistent and giving different results based on same query? Why out of 100 times, 97-98 times giving correct results and 1-2 times giving wrong.
|
|
|
|
|
Please note I did not offer ANY help on that subject - it does not make any sense to me so I concentrated on the other areas hoping they may have an impact on your results.
To me a query cannot return inconsistent results!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Yes, that's my core question. even if my table design is not good, even if SQL may internally converting date into float or even if i need to put 'ASC' after Order By, still question remains same. Why on earth SQL query is not consistent? Why not it's producing same results 100 out of 100 times? Is that means any internal bug in the SQL Server? Thanks.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
