Последния резултат

L

Lumix

Registered
Абе така и така сега се появи друг проблем - датата не вади на последния торент а вади тази на първия ето го и кода

[sql]
SELECT users.id AS userid, users.username AS username, users.uploaded AS upload, users.downloaded AS download, users.warned AS warned, users.class, users.subclass, torrents.id AS tid, UNIX_TIMESTAMP(torrents.added) AS added, torrents.owner AS owner FROM users JOIN torrents ON torrents.owner = users.id WHERE users.class = '2' OR users.class = '3' AND (users.subclass='1' or users.subclass='2') GROUP BY users.id ORDER BY torrents.added DESC;
[/sql]

Т.е. кода си е верен, само че в момента ми показва първия качен торент на потребителя, а на мен ми трябва да ми показва последния качен торент на потребителя.
 
[sql]SELECT users.id AS userid, users.username AS username, users.uploaded AS upload, users.downloaded AS download,
users.warned AS warned, users.class, users.subclass, torrents.id AS tid, UNIX_TIMESTAMP(torrents.added) AS added,
torrents.owner AS owner
FROM users JOIN torrents ON torrents.owner = users.id
WHERE users.class = '2' OR users.class = '3' AND (users.subclass='1' or users.subclass='2')
GROUP BY users.id ORDER BY UNIX_TIMESTAMP(torrents.added) DESC[/sql]
 
[sql]SELECT users.id AS userid, users.username AS username, users.uploaded AS upload, users.downloaded AS download,
users.warned AS warned, users.class, users.subclass, torrents.id AS tid, UNIX_TIMESTAMP(torrents.added) AS added,
torrents.owner AS owner
FROM users JOIN torrents ON torrents.owner = users.id
WHERE users.class = '2' OR users.class = '3' AND (users.subclass='1' or users.subclass='2') and
torrents.added=(SELECT MAX(torrents.added) FROM torrents WHERE torrents.owner=users.id)
GROUP BY users.id ORDER BY UNIX_TIMESTAMP(torrents.added) DESC[/sql]
Не съм сигурен дали ще проработи понеже нямам БД, с която да го тествам, но го пробвай все пак.
 
Малко стана голяма и объркваща, но трябва да сработи.

[sql]SELECT inner_s.*
(SELECT users.id AS userid, users.username AS username, users.uploaded AS upload, users.downloaded AS download,
users.warned AS warned, users.class, users.subclass, MAX(torrents.id) AS tid, MAX(torrents.added) AS added,
torrents.owner AS owner
FROM users JOIN torrents ON torrents.owner = users.id
WHERE users.class = '2' OR users.class = '3' AND (users.subclass='1' or users.subclass='2')
GROUP BY users.id DESC) as inner_s
JOIN torrents ON torrents.owner = users.id
ORDER BY inner_s.added[/sql]
 
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in ...

Unknown column 'users.id' in 'on clause'

wtf :blink:
 
Lumix каза:
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in ...

Unknown column 'users.id' in 'on clause'

wtf :blink:
Увеличете...
Сигурен ли си, че имаш id в таблицата users :idea:
 
10000%

# Column Type Collation Attributes Null Default Extra Action
1 id int(10) UNSIGNED No None AUTO_INCREMENT Change Change Drop Drop More Show more actions

Предполагам че това е в първия селект при инера - най-отзад ON където е....
 
[sql]SELECT inner_s.* FROM
(SELECT users.id AS userid, users.username AS username, users.uploaded AS upload, users.downloaded AS download,
users.warned AS warned, users.class, users.subclass, MAX(torrents.id) AS tid, MAX(torrents.added) AS added,
torrents.owner AS owner
FROM users JOIN torrents ON torrents.owner = users.id
WHERE users.class = '2' OR users.class = '3' AND (users.subclass='1' or users.subclass='2')
GROUP BY users.id DESC) as inner_s
JOIN torrents ON torrents.owner = users.id
ORDER BY inner_s.added[/sql]

Избери си и другите нужни полета в главната заявка.
 
За тва и аз се сетих да го добавя но продължава да ми изкарва проблем с users.id

И по-специално става въпрос за това:
ON torrents.owner = users.id
ORDER BY inner_s.added
 
Lumix каза:
За тва и аз се сетих да го добавя но продължава да ми изкарва проблем с users.id

И по-специално става въпрос за това:
ON torrents.owner = users.id
ORDER BY inner_s.added
Увеличете...

Ами, доста ми е трудно да ги удрям така на сляпо. Замени ги с нещата които селектираш от inner_s. Най-добре им сложи и по един alias - inner_s.user_id например да стане.
 
Добре де сега излизат правилно, обаче спря да ги сортира :D Е**си кода отивам да си тегля ножа :lol:
 
Lumix каза:
Добре де сега излизат правилно, обаче спря да ги сортира :D Е**си кода отивам да си тегля ножа :lol:
Увеличете...
SELECT inner_s.* FROM
(SELECT users.id AS userid, users.username AS username, users.uploaded AS upload, users.downloaded AS download,
users.warned AS warned, users.class, users.subclass, MAX(torrents.id) AS tid, MAX(torrents.added) AS added,
torrents.owner AS owner
FROM users JOIN torrents ON torrents.owner = users.id
WHERE users.class = '2' OR users.class = '3' AND (users.subclass='1' or users.subclass='2')
GROUP BY users.id DESC) as inner_s
JOIN torrents ON torrents.owner = users.id
ORDER BY inner_s.added DESC
 
Охх каква каша стана...

SELECT inner_s.* FROM
(SELECT users.id AS userid, users.username AS username, users.uploaded AS upload, users.downloaded AS download,
users.warned AS warned, users.class, users.subclass, MAX(torrents.id) AS tid, MAX(torrents.added) AS added,
torrents.owner AS owner
FROM users JOIN torrents ON torrents.owner = users.id
WHERE users.class = '2' OR users.class = '3' AND (users.subclass='1' or users.subclass='2')
GROUP BY userid ORDER BY torrents.added DESC) as inner_s
JOIN torrents ON torrents.owner = inner_s.userid
ORDER BY inner_s.added DESC
 
SELECT distinct(users.id) AS userid, users.username AS username, users.uploaded AS upload, users.downloaded AS download,
users.warned AS warned, users.class, users.subclass, torrents.id AS tid, UNIX_TIMESTAMP(torrents.added) AS added,
torrents.owner AS owner
FROM users JOIN torrents ON torrents.owner = users.id
WHERE users.class = '2' OR users.class = '3' AND (users.subclass='1' or users.subclass='2') and
torrents.added=(SELECT MAX(torrents.added) FROM torrents WHERE torrents.owner=users.id)
ORDER BY UNIX_TIMESTAMP(torrents.added) DESC, users.id ASC
 
ivo_pi4a каза:
SELECT distinct(users.id) AS userid, users.username AS username, users.uploaded AS upload, users.downloaded AS download,
users.warned AS warned, users.class, users.subclass, torrents.id AS tid, UNIX_TIMESTAMP(torrents.added) AS added,
torrents.owner AS owner
FROM users JOIN torrents ON torrents.owner = users.id
WHERE users.class = '2' OR users.class = '3' AND (users.subclass='1' or users.subclass='2') and
torrents.added=(SELECT MAX(torrents.added) FROM torrents WHERE torrents.owner=users.id)
ORDER BY UNIX_TIMESTAMP(torrents.added) DESC, users.id ASC
Увеличете...

Нещо такова само да не вадеше пак за всеки торент юзера :D Eбати шита
 
[sql]SELECT distinct(users.id) AS userid, users.username AS username, users.uploaded AS upload, users.downloaded AS download,
users.warned AS warned, users.class, users.subclass, torrents.id AS tid, UNIX_TIMESTAMP(torrents.added) AS added,
torrents.owner AS owner
FROM users JOIN torrents ON torrents.owner = users.id
WHERE users.class = '2' OR users.class = '3' AND (users.subclass='1' or users.subclass='2') and
torrents.added=(SELECT MAX(torrents.added) FROM torrents WHERE torrents.owner=users.id)
GROUP BY users.id
ORDER BY UNIX_TIMESTAMP(torrents.added) DESC, users.id ASC[/sql]
 
Трябва да си влезнал или регистриран, за да отговориш тук.

Back
Горе