Myql/PHP Problem $20 for solution

Hi
I give $20 via paypal to the programmer who solves this problem, I wasn't able to do it, that's my code so far:

SELECT * FROM `sites` WHERE (`banned` = '0' AND `user`!='{$data->login}') AND  sites.user IN (select concat_ws(',', login) from users WHERE `coins`>=`cpc`) AND `id` NOT IN (SELECT `site_id` FROM `plused` WHERE `user_id`='{$data->id}') ORDER BY `cpc` DESC LIMIT 0, 10

Code (markup):

As you can see I order by sites.cpc DESC, now I try order by users.coins DESC as well, but users is another table. The two tables have sites.user = users.login

So sites.cpc should be the first order and users.coins the second

Why I need that, because a lot of sites have the same cpc and now I want the user with more coins comes before

 

Your ORDER BY clause has only cpc. Try

ORDER BY users.coins DESC, sites.cpc DESC

 

Already tried that, it's not that simple I work since yesterday on this problem still haven't found a solution

 

SELECT *
FROM   `sites` AS s
       LEFT JOIN `users` AS u
         ON s.`user` = u.`login`
WHERE  ( s.`banned` = '0'
         AND s.`user`!='{$data->login}' )
       AND s.`user` IN (SELECT Concat_ws(',', login)
                      FROM   users
                      WHERE  `coins`>=`cpc`)
       AND `id` NOT IN (SELECT `site_id`
                        FROM   `plused`
                        WHERE  `user_id` = '{$data->id}')
ORDER  BY s.`cpc` DESC,
          u.`coins` DESC
LIMIT  0, 10

Code (markup):

Double check for any syntax errors. If you require information that's on another table (other than the one you're affecting with the SELECT), you need to JOIN the tables in order to manipulate its values.

Give it a try and leave some feedback.

 

Yeah this looks good only id I had to make to s.id, but I still can't use the script. Can you tell me how I call the id object mysql_fetch_object because now I have two different ids. I call the id like $sit->id. I tried it like $sit->s.id, but this doesn't work

 

It's the SELECT * part. If you have a same column value in both tables, they shall become indistinguishable.

Ideally instead of SELECT * you would try something like this:

SELECT s.id AS siteId, s.cpc AS siteCPC, u.id AS userId, u.coins AS userCoins ...

Code (markup):

SELECT *, s.id AS siteId, s.cpc AS siteCPC, u.id AS userId, u.coins AS userCoins ...

Code (markup):

Do this for the columns that have the same name on both tables. Then to fetch them its simple:

<?php
$db 	= new PDO("mysql:host={$db['host']};dbname={$db['name']}", $db['username'], $db['passwd']);
$sql	= "SELECT s.id AS siteId, s.cpc AS siteCPC, u.id AS userId, u.coins AS userCoins ...";

foreach($db->query($sql) as $row) {
	$sideId 	= $row['siteId'];		// Contains s.id
	$cpc		= $row['siteCPC'];		// Contains s.cpc
	$userId		= $row['userId'];		// Contains u.id
	$coins		= $row['userCoins'];	// Contains u.coins
}

PHP:

 

hi,
your all syntax is wrong and I tell you please use joint function to solve this problem

 

your problem solved or still you need ?