problem in solve query??

[Aamit]

category table
id name date duration
1 aaa 12/12/07 1
2 bbb 10/10/08 1
.........

user_table

id name date
1 aaa 12/12/07
2 bbb 10/10/08
.......

rating table
id user_id name rating date

1 7 aaa 4 12/12/07
2 8 bbb 3 10/10/08

write now i am displaying these user in order of rating ...i.e high rating first display...
if rating match then with date i.e minimum date....

so it display like...

aaa 4 12/12/07
bbb 3 10/10/08
ccc 3 11/10/08
ddd 2 11/09/08
.....

i want show after 1 week

ddd 2 11/09/08
aaa 4 12/12/07
bbb 3 10/10/08
ccc 3 11/10/08 .....

again after 1 week

ccc 3 11/10/08
ddd 2 11/09/08
aaa 4 12/12/07
bbb 3 10/10/08

How to do this???

[stephen84s]

Honestly I have no clue what you trying to say, can you please be divulge more details and especially the relevant code / SQL query you are dealing with.
Also on what basis are you reorganizing your records every week, you have just shown us how it should appear not why or the Logic behind it.
Also please keep in mind to read essays like these , they will be very helpful in teaching you how to construct your questions so that more people will actually pay attention to you.

[stultuske]

Honestly I have no clue what you trying to say, can you please be divulge more details and especially the relevant code / SQL query you are dealing with.

I kind of think he's trying to make us write those.

[Aamit]

Table-> cat_users

id cat_id user_id
81 7 12
160 7 22
193 7 26
213 7 29
223 7 30

Table-> categories

id parent_id cat
7 NULL Stive
11 0 Akshay
15 NULL Shooting
24 NULL Fighting
52 NULL Puzzle

Table-> Users
id valid name date_added
1 1 Holly NULL
3 1 Tom 2007-11-01
7 1 Stive 2007-11-02
10 1 Elite 2007-11-02
11 1 Akshay 2007-11-02

Table-> ratings

id user_id rating date_added
1 7 4 2008-07-18
2 7 3 2008-07-18
3 3 4 2008-07-18
4 5 3 2008-07-18
5 10 2 2008-07-18
6 6 4 2008-07-18
7 1922 5 2008-07-18
15 1322 3 2008-08-02
16 2055 3 2008-08-07
81 74 4 2008-08-25
160 169 5 2008-08-25

$sql1 = "select users.*, round(avg(rating)) as rating
, date_format(date_added,'%Y-%m-%d') as `date`
from users
left join ratings on (ratings.user_id = users.id)
inner join cat_users on cat_users.user_id = users.id
where
valid = 1
and users.id in (
select user_id
from cat_users
where cat_id = $cat_id
)
and users.date_added <= now()
group by users.id
order by date_added desc, rating desc, name
";
$max = GetSQL("select count(*) from ($sql1) counter");

I am not giving all database it's very large
so got output like ....

id user_id rating date_created
123 1 4 2008-12-09
211 1 3 2008-12-08
33 1 4 2008-11-30
45 1 3 2008-10-31
57 1 2 2008-10-31
666 1 4 2008-08-25
723 1 5 2008-08-25
15 1 3 2008-08-25
16 1 3 2008-08-25
81 1 4 2008-08-25
160 1 5 2008-08-25

after that i want to display...after 1 week
like...

id user_id rating date_created
160 1 5 2008-08-25 .....change
123 1 4 2008-12-09 last data take 1st position
211 1 3 2008-12-08
33 1 4 2008-11-30
45 1 3 2008-10-31
57 1 2 2008-10-31
666 1 4 2008-08-25
723 1 5 2008-08-25
15 1 3 2008-08-25
16 1 3 2008-08-25
81 1 4 2008-08-25

again after 1 week ...
id user_id rating date_created
81 1 4 2008-08-25 .....change
160 1 5 2008-08-25 last data take 1st position
123 1 4 2008-12-09
211 1 3 2008-12-08
33 1 4 2008-11-30
45 1 3 2008-10-31
57 1 2 2008-10-31
666 1 4 2008-08-25
723 1 5 2008-08-25
15 1 3 2008-08-25
16 1 3 2008-08-25

How to do that??
can i add column like postion_id so that change position of data??
Reason behind change position is old user viewed there profile by another users.

[stultuske]

if you want to sort your output based on the data you already have, there is no need to add another column.
read it into an array of objects and sort those before printing

[masijade]

An "order by" clause ?

Your question has nothing to do with any programming language, what-so-ever. Find an SQL forum.

[Aamit]

I edit my table add one column

id user_id rating date_created change_id
123 1 4 2008-12-09
211 1 3 2008-12-08
33 1 4 2008-11-30
45 1 3 2008-10-31
57 1 2 2008-10-31
666 1 4 2008-08-25

which query to write....so that i got ..

id user_id rating date_created change_id
123 1 4 2008-12-09 1
211 1 3 2008-12-08 2
33 1 4 2008-11-30 3
45 1 3 2008-10-31 4
57 1 2 2008-10-31 5
666 1 4 2008-08-25 6

add data in change_id column with increment value...
how to put data for change_id with increment value in column??

[stultuske]

you might want to read this answer first.
obviously, masjiade did more effort into reading your code than I did

An "order by" clause ?

Your question has nothing to do with any programming language, what-so-ever. Find an SQL forum.

[Aamit]

i am edit table.....add column change_id
And i want to write data...means update table.....

original table
id user_id rating date_created
123 1 4 2008-12-09
211 1 3 2008-12-08
33 1 4 2008-11-30
45 1 3 2008-10-31
57 1 2 2008-10-31
666 1 4 2008-08-25

after update
id user_id rating date_created change_id
123 1 4 2008-12-09 1
211 1 3 2008-12-08 2
33 1 4 2008-11-30 3
45 1 3 2008-10-31 4
57 1 2 2008-10-31 5
666 1 4 2008-08-25 6

plz look into this table..just add number in this column in increment form

[masijade]

What about it? This is not an SQL forum, and your's is an SQL question.

As I have already said
Find an SQL Forum.

[peter_budo]

What about it? This is not an SQL forum, and your's is an SQL question.

As I have already said Find an SQL Forum.

Well, our "friend" Aamit took your advice literally and posted in 4 out 5 options of Database section, on top of that he also posted in PHP! (these been of course all deleted beside one in MySQL section, FORUM FLOODING Aamit)

I bet fiver on that he will come back with question like "Why auto increment column doesn't work properly after I delete some rows from my table?"

[Aamit]

http://forums.oracle.com/forums/message.jspa?messageID=3167957#3167957

Here i idea but how to change in mysql syntax

[stultuske]

http://forums.oracle.com/forums/message.jspa?messageID=3167957#3167957

Here i idea but how to change in mysql syntax

by STOP BUGGING US and do a little search on MySQL syntaxis.
and if you're too lazy for that, ask your questions on an SQL FORUM, so they can make the choice to either help you, or ignore you, since you're obviously not planning on doing much effort yourself

[masijade]

http://forums.oracle.com/forums/message.jspa?messageID=3167957#3167957

Here i idea but how to change in mysql syntax

Http://www.mysql.org/ ? Maybe? There are forums there, too, you know?