Is it possible to sort by...

zzz

I have table with list of users. They can list either their real name or their fake names.

If they provide fake names I must use those, if not -- use real.

Fields are like this:

firstName_real
lastName_real
firstName_fake
lastName_fake

Is it possible to sort them alphabetically?
I wrote a logic where if fake name used it is used in the name string:

if ($fakename != '')
{
$fname = $fakename;
} else {
$fname = $realname;
}

// ect...

$fullName = $fname.' '.$lname;

So I output $fullName.

I'm getting a bit confused.

😕

fean0r


if ($fakename) {
$fname = $fakename;
} else {
$fname = $realname;
}

That should do the trick

Piranha

I would insert the real name as fake name for those that haven't submitted a fake one. Then you can do all the sorting and stuff in the database, and you don't have problems with different columns.

fean0r

Piranha wrote:
I would insert the real name as fake name for those that haven't submitted a fake one. Then you can do all the sorting and stuff in the database, and you don't have problems with different columns.

Yea that would make your query's much easier.

zzz

I know, unfortunatelly I've oversimplified even this example. I actually get values from 4 different tables for the output. The more i think about it, the more I realize that it's probably not possible. I get real name from one tbl and fake from another, then I make a substitution in a function... It probably would make sence to sort the funtion output, but then I guess I'dd need to put all in an array and sort afterwards, which is very unlikely with a large number of users.

I guess I'd just have to live with it...

Piranha

Maybe you can use a UNION in the query? Hard to know without knowing how it looks like.

zzz

UNION, hah?.. I think I've used it sometime in the past.

I have something like this:

SELECT
t1.real_name_f,
t1.real_name_l,
t2.fake_name_f,
t2.fake_name_l
FROM t1
LEFT JOIN t2 ON (t2.ID = t1.id)
ORDER BY 
t1.real_name_l,
t1.real_name_f
DESC
LIMIT 50

Piranha

Something like this would probably work:

((SELECT t1.rnf, t1.rnl, t2.rnf, t2.rnl
   FROM t1
   INNER JOIN t2 ON t2.id = t1.id
   WHERE t2.fnf IS NOT NULL
   OR t2.fnl IS NOT NULL)
UNION
(SELECT t1.rnf, t1.rnl, t1.rnf, t1.rnl
   FROM t1
   LEFT JOIN t2 ON t2.id = t1.id
   WHERE t2.fnf IS NULL
   AND t2.fnl IS NULL))
Order by...
Limit...

By the way, I noticed that you ask a lot of questions on these forums and not answering any. I think that you should start to answer a few questions as well, giving something back to others that need help. I personally will probably not answer your questions in the future if you don't. Don't get me wrong, you are always nice and I defenetley don't have anything against you.

zzz

Wow, I'll give it a whirl...

I do answer when I know for sure the answer is correct. I just don't want to polute with incorrect theories. There's plenty of places online where a blind leads a blind. Fortunatelly this forum is not one of them. But I'll try harder, thanks for pointing this out.

Piranha

Maybe I should explain the query as well. The first select joins the tables as normal and returns everyone that have a fake name. The second select joins the tables, return the real names two times where there is no fake name. The results from these 2 questions are merged, then the ordering and limit are used as normal.

But why can't you insert the real names in t2 where the names are not present? Should be something like this (untested and first time I use INSERT this way):

INSERT INTO t2 (id, fname, lname)
SELECT t1.id, t1.fname, t1.lname
FROM t1
WHERE t1.id NOT IN (SELECT id FROM t2)

Note that this is probably possible to do without aubqueries. I currently uses subqueries for lots of stuff for some strange reason 😉