Ordered mysql Lists

[wrathican]

I have a little predicament. i have a section of code where the user selects an option to show a list. the script searches one table for the selcted options and then a query gets the name to display from another table.

the problem is i need the ouput to be listed alphabetically.

 

so, as it stand it does this:

user selects a list to view.

the script gets the entries from the db that match the users query.

each entry then gets the name to display from another table using the id.

the script outputs the data.

 

How can i get the names to be listed alphabetically. without editing the first table to contain the name.

 

here is the code:

$koslistaccess = $_SESSION['koslist'];
echo "<br /><br /><u><b>KOS List</b></u><br /><br />";

if ($koslistaccess != 'yes') {
echo "You are not authorized to view this page.";
}elseif($koslistaccess == 'yes'){
//show form to show citadel members, kos alliances, kos corps, kos pilots.
?>
Please select a list to view from the selection below:<br />
<form action="?c=koslist" method="post" name="koslist">
<select name="list">
		<option value="1">KOS</option>
		<?php 
		if($_SESSION['admin'] == 'yes')
		{
			echo '<option value="2">The Citadel</option>';
		}
		?>
</select>
<select name="type">
		<option value="alliance">Alliances</option>
		<option value="corp">Corporations</option>
		<option value="pilot">Pilots</option>
</select>
<input type="hidden" name="search" value="yes" />
<input type="submit" name="submit" value="Select" />
</form>
<?php
//get the full list.
//get alliances first
//then get corps
//then get people
if($_POST['search'] == 'yes'){
	$list = $_POST['list'];
	$type = $_POST['type'];
	if($type == 'alliance'){
		$typeidkos = "all";
		$typeidkb = "all";
	}elseif($type == 'corp'){
		$typeidkos = "corp";
		$typeidkb = "crp";
	}elseif($type == 'pilot'){
		$typeidkos = "pil";
		$typeidkb = "plt";
	}
$queryko = "SELECT * FROM kos_".$type." WHERE ".$typeidkos."_access='".$list."'";
$resultko = mysql_query($queryko);
$numrows = mysql_num_rows($resultko);
if ($numrows == 0){
	echo "<br />No matches found.";
}else{
	//start the table
	echo "<table border='1' width='250'>";
	while($row = mysql_fetch_array($resultko,MYSQL_NUM)){
		//get the table contents
		$allid = $row[0];
		//get the name from kb db
		echo $querykb = "SELECT * FROM kb3_".$type."s WHERE ".$typeidkb."_id='".$allid."'";
		echo $querykb;
		$resultkb = mysql_query($querykb);
		while($rowkb = mysql_fetch_array($resultkb,MYSQL_NUM)){
			$name = $rowkb[1];
			echo "<tr><td>".$name."</td></tr>";
		}
	}
echo "</table>";
}
}else{
	echo "<br />Please select an option.";
}
}

[clown[NOR]]

to sort alphabetically you use "SELECT * FROM `table_name` SORT BY `name` DESC/ASC" i can never remember if it's ASC or DESC that sorts from A-Z

[wrathican]

yes i know, but the first table the query runs on doesn't contain the name of the entry, so there is nothing to sort. i could sort by ID but this still wouldnt order the output alphabetically. once the script has an entry in the first table it THEN gets the name from a second table. because that section is this:

while($row = mysql_fetch_array($resultko,MYSQL_NUM)){
		//get the table contents
		$allid = $row[0];
		//get the name from kb db
		echo $querykb = "SELECT * FROM kb3_".$type."s WHERE ".$typeidkb."_id='".$allid."'";
		echo $querykb;
		$resultkb = mysql_query($querykb);
		while($rowkb = mysql_fetch_array($resultkb,MYSQL_NUM)){
			$name = $rowkb[1];
			echo "<tr><td>".$name."</td></tr>";
		}
	}

 

 

[clown[NOR]]

oh i see.. my bad.. misunderstood.. a little early for me well i must say that as the noob i am i would try making an array where i stored the outputs and somehow try sorting that array and then print the result... but that's just me

[wrathican]

yeah i thought of that. but thought it could be largly inefficient. (also i have no idea how to do it )

[clown[NOR]]

hehe well some genius will hopefully answer here soon... i'm curious on how to solve this myself

[beebum]

You need to do a join. That way you can sort by name.

[clown[NOR]]

^^----- And there was the genius -----^^

[wrathican]

right, i have tried a few tests with a JOIN and i keep getting this error: #1052 - Column 'all_id' in on clause is ambiguous

 

this is the query im running:

 

SELECT * FROM kos_alliance JOIN kb3_alliances ON all_id = all_id

 

both tables have a column called all_id

 

[beebum]

SELECT * FROM table_1 JOIN table_2 ON table_01.all_id = table_02.all_id;

[wrathican]

right, can i then add a where onto that to select certain cases? so like WHERE table_01.col_03=$somevariable?

[beebum]

of course