ORDER BY lookalike

[Josso]

Hey!!

It seems that this forum is just for me. :D

Well,, I'm an experienced PHP and HTML user.
But a bit noob to MySQL. (Although I know some...)

char = some letters. Eg..: a A ã Ã
$find = my search: ã
lookalike for char=ã = ã;Ã;ä;â;á;à;A;Ä ;Â;Á;À;

$result = dbquery("
	SELECT ".$db_altk."id, ".$db_altk."char, ".$db_altk."alt, ".$db_altk."lookalike
	FROM ".DB_ALT_KEYCODES."
	WHERE ".$db_altk."char = '".$find."'
);

When I search ($find) for a (or any other: Ã) it shows up in this order:
A, a, ã, Ã
But it should be with the search string first, and then it should be after lookalike.

I have no clue how to solve it, but it should be possible.

Greetings,
- Johan aka Josso

[hsriat]

Hey!!

It seems that this forum is just for me. :D

Well,, I'm an experienced PHP and HTML user.
But a bit noob to MySQL. (Although I know some...)

char = some letters. Eg..: a A ã Ã
$find = my search: ã
lookalike for char=ã = ã;Ã;ä;â;á;à;A;Ä ;Â;Á;À;

$result = dbquery("
	SELECT ".$db_altk."id, ".$db_altk."char, ".$db_altk."alt, ".$db_altk."lookalike
	FROM ".DB_ALT_KEYCODES."
	WHERE ".$db_altk."char = '".$find."'
);

When I search ($find) for a (or any other: Ã) it shows up in this order:
A, a, ã, Ã
But it should be with the search string first, and then it should be after lookalike.

I have no clue how to solve it, but it should be possible.

Greetings,
- Johan aka Josso

Show the code, you may need to do some algorithmic changes.

[ronverdonk]

You should sort it by using an ORDER BY FIELD, such as
[code=mysql]
.... ORDER BY FIELD(field_nam e,'1st-to-sort', '2nd-to-sort', '3rd-to-sort', etc.)[/code]

This thread does not belong in the PHP forum, but in MySQL. I'll move it there.

Ronald

[Josso]

You should sort it by using an ORDER BY FIELD, such as
[code=mysql]
.... ORDER BY FIELD(field_nam e,'1st-to-sort', '2nd-to-sort', '3rd-to-sort', etc.)[/code]

This thread does not belong in the PHP forum, but in MySQL. I'll move it there.

Ronald

Forum: Ofc. ;)

Can you give a example if the tabel looks like this:

(`alt_keycodes_id`, `alt_keycodes_char`, `alt_keycodes_alt`, `alt_keycodes_lookalike`) VALUES 
(1, 'A', '65', 'A;a;Ä;Â;Á;À;Ã;ä;â;á;à;ã;'),
(2, 'a', '97', 'a;A;ä;â;á;à;ã;Ä;Â;Á;À;Ã;'),
(3, 'ã', '0227', 'ã;Ã;ä;â;á;à;A;Ä;Â;Á;À;'),
(4, 'Ã', '0195', 'Ã;ã;A;Ä;Â;Á;À;a;ä;â;á;à;');

[ronverdonk]

This sorts the result set according to the value in position 1 of the 'field_name'. When not in the list it will be sorted the normal way.[code=sql]...ORDER BY FIELD(LEFT(fiel d_name,1), 'ã','Ã','ä','â' ,'á','à','A','Ä ','Â','Á','À')[/code]
You can, of course, fill that FIELD list dynamically using PHP.

Ronald

[Josso]

This sorts the result set according to the value in position 1 of the 'field_name'. When not in the list it will be sorted the normal way.[code=sql]...ORDER BY FIELD(LEFT(fiel d_name,1), 'ã','Ã','ä','â' ,'á','à','A','Ä ','Â','Á','À')[/code]
You can, of course, fill that FIELD list dynamically using PHP.

Ronald

Sorry, but I can't get it to work. :(

I wanna make a small list with Alt-Keycodes.

And when I search for " ã " A and a, should not be listed first.

Therefore I made a field called "lookalike" , where I made a list, with what to come first, and last. ;)

Hope it was transfigured better. :)

- Johan aka Josso

[ronverdonk]

I am lost now. Let's narrow it down:

When I search ($find) for a (or any other: Ã) it shows up in this order: A, a, ã, Ã

Question: WHAT shows up in this order? Which field(s) in the result? Because that is the field you must sort using ORDER BY.

Ronald

[Josso]

This is the list: (Using while() )
like removed - moderator

[ronverdonk]

That link is not the answer to my question. It shows some screen image, which is HTML make up and has nothing to do with the SELECT you want help with. So my question was: What field(s) in your query must be ordered your way.

Due to Posting Guidelines this type of links is not allowed. Especially one that sends out 3 cookies and asks for allowance of Active objects. So the url is taken out of the post; it had nothing to do with my question anyway.

Ronald

[Josso]

Sorry...
I'm not sure I know what you mean...?

I just give you the code:
[PHP]require_once "inc.php";
echo "<table border='0' width='100%'>
<tr>
<td align='left'><b >".$locale['ak001']."</b></td>
<td align='right'>< a href='lang.php' >".$locale['ak002'].$lang."</a></td>
</tr>\n</table>
<br /><br />\n";
echo "<form action='".FUSIO N_SELF."?lang=" .$lang."' method='get'>
<table align='center'>
<tr align='center'>
<td>Find:</td>
<td><input type='text' name='find'".($ find ? " value='".$find. "'" : "")."/></td>
<td><input type='submit' value='".$local e['ak003']."' class='button' /></td>
</tr>
</table>
</form>\n";

echo "<table width='100%' cellspacing='0' cellpadding='0' >
<tr><td height='10'></td></tr>
</table>\n";

if ($find) {
echo "\n<table align='center' width='100%' cellpadding='3' cellspacing='1' class='tbl-border'>\n";
echo "<tr class='tbl1' align='center'>
<td width='45%'><b> ".$locale['ak004']."</b></td>
<td width='10%'><b> ".$locale['ak005']."</b></td>
<td width='45%'><b> ".$locale['ak006']."</b></td>
</tr>\n";

$result = dbquery("
SELECT ".$db_altk. "id, ".$db_altk."cha r, ".$db_altk."alt , ".$db_altk."loo kalike
FROM ".DB_ALT_KEYCOD ES."
WHERE ".$db_altk."cha r = '".$find."'
ORDER BY FIELD(LEFT(".$d b_altk."char,1) , 'ã','Ã','ä','â' ,'á','à','A','Ä ','Â','Á','À')"
);
if (dbrows($result )) {
while($data = dbarray($result )) {
echo "<tr class='tbl2' align='center'>
<td width='45%'>".$ data[$db_altk.'char']."</td>
<td width='10%'>".$ locale['ak005']."</td>
<td width='45%'>".$ data[$db_altk.'alt']."</td>
</tr>\n";
}
}
echo "</table>\n";
}

echo "<table width='100%' cellspacing='0' cellpadding='0' >
<tr><td height='10'></td></tr>
</table>\n";

echo "<table cellpadding='0' cellspacing='1' class='tbl-border center'>
<tr>
<td align='center' class='tbl1'>
<textarea name='test_alt_ keycodes' cols='70' rows='7' class='textbox' >".$locale['ak007']."</textarea><br />
</td>
</tr>
</table>";[/PHP]

inc.php:
[PHP]define("DB_ALT_ KEYCODES", DB_PREFIX."alt_ keycodes");
$db_alt_keycode s = DB_ALT_KEYCODES .".alt_keycodes _";
$db_altk = "alt_keycodes_" ;
$find = $_GET['find'];[/PHP]

MySQL:

CREATE TABLE `DB_PREFIX_alt_keycodes` (
  `alt_keycodes_id` mediumint(8) unsigned NOT NULL auto_increment,
  `alt_keycodes_char` varchar(100) collate latin1_danish_ci NOT NULL,
  `alt_keycodes_alt` varchar(500) collate latin1_danish_ci NOT NULL,
  `alt_keycodes_lookalike` varchar(500) collate latin1_danish_ci NOT NULL,
  PRIMARY KEY  (`alt_keycodes_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci AUTO_INCREMENT=5 ;

-- 
-- Data dump for tabellen `DB_PREFIX_alt_keycodes`
-- 

INSERT INTO `DB_PREFIX_alt_keycodes` (`alt_keycodes_id`, `alt_keycodes_char`, `alt_keycodes_alt`, `alt_keycodes_lookalike`) VALUES 
(1, 'A', '65', 'A;a;Ä;Â;Á;À;Ã;ä;â;á;à;ã;'),
(2, 'a', '97', 'a;A;ä;â;á;à;ã;Ä;Â;Á;À;Ã;'),
(3, 'ã', '0227', 'ã;Ã;ä;â;á;à;A;Ä;Â;Á;À;'),
(4, 'Ã', '0195', 'Ã;ã;A;Ä;Â;Á;À;a;ä;â;á;à;');

When I search for ã it shows A, in the first row (in the while() ), then a, and then ã.

What I want:
When I search it shall take the data out of the db (as now) but it shall list it after alt_keycodes_lo okalike.
That mean, that when I search for ã it shall show it in order by alt_keycodes_lo okalike (ã;Ã;ä;â;á;à;A; Ä;Â;Á;À;).

Hope it was better explained. :)
(Yeah I suck at English :P )

[ronverdonk]

The order in which you display is the order of field 'alt_keycodes_i d'. Command [code=sql] SELECT alt_keycodes_id , alt_keycodes_ch ar, alt_keycodes_al t, alt_keycodes_lo okalike
FROM db_prefix_alt_k eycodes
WHERE alt_keycodes_ch ar = 'ã'[/code]
yields 2 results they are [code=text]
alt_keycodes_id alt_keycodes_ch ar alt_keycodes_al t
1 A 65
2 a 97[/code]
and these last 2 columns are displayed in your output table.

When you say that you want them in order of 'search' (which is 'ã') and subsequently in order of lookalike, you'll see a problem. Because which lookalike do you want? The one from result row 1 (alt-keycode_id=1) or the one from alt_keycode_id= 2 ? What you are asking is to sort all rows according to the result field in 1 particular row. See??

Ronald

[Josso]

The order in which you display is the order of field 'alt_keycodes_i d'. Command [code=sql] SELECT alt_keycodes_id , alt_keycodes_ch ar, alt_keycodes_al t, alt_keycodes_lo okalike
FROM db_prefix_alt_k eycodes
WHERE alt_keycodes_ch ar = 'ã'[/code]
yields 2 results they are [code=text]
alt_keycodes_id alt_keycodes_ch ar alt_keycodes_al t
1 A 65
2 a 97[/code]
and these last 2 columns are displayed in your output table.

When you say that you want them in order of 'search' (which is 'ã') and subsequently in order of lookalike, you'll see a problem. Because which lookalike do you want? The one from result row 1 (alt-keycode_id=1) or the one from alt_keycode_id= 2 ? What you are asking is to sort all rows according to the result field in 1 particular row. See??

Ronald

You opened my eyes a little more. :)
Using this:

	SELECT ".$db_altk."id, ".$db_altk."char, ".$db_altk."alt, ".$db_altk."lookalike
	FROM ".DB_ALT_KEYCODES."
	WHERE ".$db_altk."char REGEXP '^".$find."$'

I get two rows. (With ã and Ã)

Then I'll try to get the others (lookalike) with a new SQL call.

Well,, I'll be back if I get problems. :)

[SOMEHOW SOLVED]

[ronverdonk]

Somehow indeed. But you will have to make a decision on what 'lookalike' you want it to sort on. When you have decided that (or leave it as it is) come back and we will see again. Until then.

Ronald