Jump to content

[SOLVED] sorting a querying by

benjaminbeazy

By benjaminbeazy
in MySQL Help

 Share

Recommended Posts

benjaminbeazy Member

benjaminbeazy

Posted
Posted

okay here's my issue

 

i'm trying to sort a user list alphabetically by their specialty for a search result page.

 

each user row has the specialty id in the field specialty_1 but when i write the query to sort by specialty, it sorts them by id obviously.

 

the specialty table has 90 some odd specialties but they are not alphabetically listed in relation to their id

 

i need the query to sort by specialties alphabetically and not sure what to do? i think this is the solution is a complex query

 

here is the complete search page script....

 

(please forgive the lack of proper formatting)

 



<?
session_start();

  // find out the domain:
  $domain = $_SERVER['HTTP_HOST'];
  // find out the path to the current file:
  $path = $_SERVER['SCRIPT_NAME'];
  // find out the QueryString:
  $queryString = $_SERVER['QUERY_STRING'];
  $queryString2 = array();
  $queryString2 = explode("&searchpage", $queryString);
  // put it all together:
  $url = "http://" . $domain . $path . "?" . $queryString2[0];

if(!$_GET[searchpage]){
$searchpage = 0;
}else{
$searchpage = $_GET[searchpage];
}
$limit = 40;

if($_POST ){
$search = array("last_name" => "$_POST[last_name_search]",
"first_name" => "$_POST[first_name_search]",
"city" => "$_POST[city_search]",
"specialty" => "$_POST[specialty_search]");
}else{
$search = array("last_name" => "$_GET[last_name_search]",
"first_name" => "$_GET[first_name_search]",
"city" => "$_GET[city_search]",
"specialty" => "$_GET[specialty_search]");
}


if($search[last_name] !== ""){
$last_name_string = 'AND `last_name` LIKE \''.$search[last_name].'%\'';
}else{
$last_name_string = "";
}

if($search[first_name] !== ""){
$first_name_string = 'AND `first_name` LIKE \''.$search[first_name].'%\'';
}else{
$first_name_string = "";
}

if($search[city] !== ""){
$city_string = 'AND `clinic_city`=\''.$search[city].'\'';
}else{
$city_string = "";
}

if($search[specialty] !== ""){
$specialty_string = 'AND `specialty_1`=\''.$search[specialty].'\'';
}else{
$specialty_string = "";
}

$sort1 = $_GET["sort1"];
$orient1 = $_GET["orient1"];
if(!$sort1 || $sort1 == ""){
$sort1 = 'last_name';
$orient1 = 'ASC';
}

$sort2 = $_GET["sort2"];
$orient2 = $_GET["orient2"];
if(!$sort2 || $sort2 == ""){
$sort2 = 'first_name';
$orient2 = 'ASC';
}

$sort3 = $_GET["sort3"];
$orient3 = $_GET["orient3"];
if(!$sort3 || $sort3 == ""){
$sort3 = 'first_name';
$orient3 = 'ASC';
}




$basic_num_result = mysql_query("SELECT * from `doctors` WHERE `list`='0' ".$last_name_string." ".$first_name_string." ".$city_string." ".$specialty_string." AND `listing_type` LIKE 'basic' ORDER BY `".$sort1."` ".$orient1.", `".$sort2."` ".$orient2.", `".$sort3."` ".$orient3."");

$search_enhanced_result = mysql_query("SELECT * from `doctors` WHERE `list`='0' ".$last_name_string." ".$first_name_string." ".$city_string." ".$specialty_string." AND `listing_type` LIKE 'enhanced' ORDER BY `last_name` ASC, `first_name` ASC");

$search_result = mysql_query("SELECT * from `doctors` WHERE `list`='0' ".$last_name_string." ".$first_name_string." ".$city_string." ".$specialty_string." AND `listing_type` LIKE 'basic' ORDER BY `".$sort1."` ".$orient1.", `".$sort2."` ".$orient2.", `".$sort3."` ".$orient3." LIMIT $searchpage, $limit");

$num_rows = mysql_num_rows($basic_num_result);
$num_pages = ceil($num_rows/$limit);


?>

<h1>Manage Doctors</h1>
This page allows you to browse or search the doctors database and perform various editing functions of the doctors and their respective modules.
<div style="margin-top:20px">
<h2>Filter Results</h2>Filter for:
<form method="post" action="?page=doctor">
<table class="c" width="250px" bgcolor="#e5e8f6" cellspacing=0 style="border-style:solid;border-width:1px;border-color:#29166e;margin-left:20px">
<tr><td align="right" width="70" class="c"><b>Last Name:</td><td align="left" valign="top" class="c"><input type="text" name="last_name_search"></td></tr>
<tr><td align="right" class="c"><b>First Name:</td><td align="left" valign="top" class="c"><input type="text" name="first_name_search"></td></tr>
<tr><td align="right" class="c"><b>City:</td><td align="left" valign="top" class="c"><select name="city_search">
<option value="" selected>All Cities</option>
<?
$city_query = mysql_query("SELECT * from `cities` WHERE `state_id`='27' ORDER by `city` ASC");
while($city_row = mysql_fetch_array($city_query)){
$city_id = $city_row["id"];
$city_name = $city_row["city"];
?>
<option value="<?=$city_row["id"];?>"><?=$city_row["city"];?></option>
<? } ?>
</select></td></tr>
<tr><td align="right" class="c"><b>Specialty:</td><td align="left" valign="top" class="c"><select name="specialty_search">
<option value="" selected>All Specialties</option>
<?
$specialty_query = mysql_query("SELECT * from `specialty` ORDER by `specialty` ASC");
while($specialty_row = mysql_fetch_array($specialty_query)){
?>
<option value="<?=$specialty_row["id"];?>"><?=$specialty_row["specialty"];?></option>
<? } ?>
</select></td></tr>

<tr><td colspan="2" align="right" class="c"><input type="submit" value="Filter Results"></td></tr>
</table>
</form>
</div>
<div style="margin-top:20px">
<h2>Search Results</h2>
Filters- 
<?
foreach($search as $var => $val){
if($val == ""){
	$$var = "All";
}else{
	$$var = $val;
}
}
echo "Last Name: <b>$last_name</b>, First Name: <b>$first_name</b>, Clinic Name: <b>$clinic_name</b>, City: <b>$city</b>, Specialty: <b>$specialty</b>";
?>
<br>
Displaying <b><?=$searchpage + 1;?> - <?=$searchpage + $limit;?></b> of <?=$num_rows;?>.<br>
<?
if($searchpage !== 0){
$limit3 = $searchpage - $limit;
echo "<a href=\"$url&searchpage=$limit3\"><< prev</a> ";
}

if($searchpage == 0){
echo "1 ";
}else{
echo "";
}

$i = 1;
$limit2 = $limit;
do{
  $i = $i + 1;

  if(!($i > $num_pages)){

    if($searchpage == $limit2){
      echo "$i ";
    }else{
      echo "";
    }

  }

  $limit2 = $limit2 + $limit;
} while($i < $num_pages);


$new_num = $searchpage / $limit;
$new_num = ceil($new_num) + 1;
$limit4 = $searchpage + $limit;
if($new_num != $num_pages){
echo "<a href=\"$url&searchpage=$limit4\">next >></a>";
}
?>

<table class="c" width="580px" bgcolor="#e5e8f6" cellspacing=0 style="border-style:solid;border-width:1px;border-color:#29166e;margin-left:20px">
<tr><td valign="top" align="left">

<!--Search Results--!>
<table border="0" width="570" cellspacing=0 cellborder=0>
<tr>
<td class="ct" width="130">Last Name<br><a href="<? 
echo "?page=doctor&last_name_search=$search[last_name]&first_name_search=$search[first_name]&specialty_search=$search[specialty]&city_search=$search[city]&sort1=last_name&orient1=ASC&searchpage=$searchpage";
?>">A-Z</a> | <a href="<?
echo "?page=doctor&last_name_search=$search[last_name]&first_name_search=$search[first_name]&specialty_search=$search[specialty]&city_search=$search[city]&sort1=last_name&orient1=DESC&searchpage=$searchpage"; 
?>">Z-A</a></td>

<td class="ct" width="100">First Name<br><a href="<?
echo "?page=doctor&last_name_search=$search[last_name]&first_name_search=$search[first_name]&specialty_search=$search[specialty]&city_search=$search[city]&sort1=first_name&orient1=ASC&sort2=last_name&searchpage=$searchpage"; 
?>">A-Z</a> | <a href="<?
echo "?page=doctor&last_name_search=$search[last_name]&first_name_search=$search[first_name]&specialty_search=$search[specialty]&city_search=$search[city]&sort1=first_name&orient1=DESC&sort2=last_name&searchpage=$searchpage"; 
?>">Z-A</a></td>

<td class="ct" width="100">Specialty<br><a href="<?
echo "?page=doctor&last_name_search=$search[last_name]&first_name_search=$search[first_name]&specialty_search=$search[specialty]&city_search=$search[city]&sort1=specialty_1&orient1=ASC&sort2=last_name&searchpage=$searchpage"; 
?>">A-Z</a> | <a href="<?
echo "?page=doctor&last_name_search=$search[last_name]&first_name_search=$search[first_name]&specialty_search=$search[specialty]&city_search=$search[city]&sort1=specialty_1&orient1=DESC&sort2=last_name&searchpage=$searchpage"; 
?>">Z-A</a></td>

<td class="ct" width="150">City<br><a href="<?
echo "?page=doctor&last_name_search=$search[last_name]&first_name_search=$search[first_name]&specialty_search=$search[specialty]&city_search=$search[city]&sort1=clinic_city&orient1=ASC&sort2=last_name&searchpage=$searchpage"; 
?>">A-Z</a> | <a href="<?
echo "?page=doctor&last_name_search=$search[last_name]&first_name_search=$search[first_name]&specialty_search=$search[specialty]&city_search=$search[city]&sort1=clinic_city&orient1=DESC&sort2=last_name&searchpage=$searchpage"; 
?>">Z-A</a></td>

<td class="ct" width="90">Options</td>

</tr>

<?
while($doctor_row = mysql_fetch_object($search_result)){

//all this code just to get the name of city and specialty
$city_id = $doctor_row->clinic_city;
$specialty_id = $doctor_row->specialty_1;
$city_query = mysql_query("SELECT * from `cities` WHERE `id`='$city_id'");
$specialty_query = mysql_query("SELECT * from `specialty` WHERE `id`='$specialty_id'");
$city_row = mysql_fetch_object($city_query);
$specialty_row = mysql_fetch_object($specialty_query);
$city = $city_row->city;
$specialty = $specialty_row->specialty;
//end ridiculously long block of code for 2 simple vars
?>

<tr>
<td class="d"><?=$doctor_row->last_name;?></td>
<td class="d"><?=$doctor_row->first_name;?></td>
<td class="d"><?=$specialty;?></td>
<td class="d"><?=$city;?></td>
<td class="d"><a href="?page=user_options/manage&user_id=<?=$user_row->id;?>"><img src="templates/images/edit.gif" border="0"></a>  <a href="?page=user_options/expunge&user_id=<?=$user_row->id;?>"><img src="templates/images/remove.gif" border="0"></a></td>
</tr>
<? } ?>
</table>
<!--End Search Results--!>
</td></tr>
</table>
</div>

<?
if($searchpage !== 0){
$limit3 = $searchpage - $limit;
echo "<a href=\"$url&searchpage=$limit3\"><< prev</a> ";
}

if($searchpage == 0){
echo "1 ";
}else{
echo "";
}

$i = 1;
$limit2 = $limit;
do{
  $i = $i + 1;

  if(!($i > $num_pages)){

    if($searchpage == $limit2){
      echo "$i ";
    }else{
      echo "";
    }

  }

  $limit2 = $limit2 + $limit;
} while($i < $num_pages);


$new_num = $searchpage / $limit;
$new_num = ceil($new_num) + 1;
$limit4 = $searchpage + $limit;
if($new_num != $num_pages){
echo "<a href=\"$url&searchpage=$limit4\">next >></a>";
}
?>

 

Link to comment
Share on other sites
benjaminbeazy Member

benjaminbeazy

Posted
  • Author
Posted

here's part of the specialty table, as you can see they aren't alphabetical

how do i order my user query by the specialty alphabetically when all i have in the user table is the specialty id

 

1  GENERAL PRACTICE

2 GENERAL SURGERY

3 ALLERGY/IMMUNOLOGY

4 OTOLARYNGOLOGY

5 ANESTHESIOLOGY

6 CARDIOLOGY

7 DERMATOLOGY

8 FAMILY PRACTICE

9 PAIN MANAGEMENT

10 GASTROENTEROLOGY

Link to comment
Share on other sites
btherl Member

btherl

Posted
Posted

Try

 

SELECT * FROM doctors JOIN specialty ON (doctors.specialty = specialty.id) ORDER BY specialty.specialty

 

That assumes that the specialty id is called "specialty" in the doctors table, and "id" in the specialty table.  You may have a problem with naming columns, which you may have to solve by listing the columns you want after 'select'.

Link to comment
Share on other sites
benjaminbeazy Member

benjaminbeazy

Posted
  • Author
Posted

thanks for your help but alas trouble continues

 

here is my search string

 

$search_result = mysql_query("SELECT * from `doctors` WHERE `list`='0' ".$last_name_string." ".$first_name_string." ".$city_string." ".$specialty_string." AND `listing_type` LIKE 'basic' ORDER BY `".$sort1."` ".$orient1.", `".$sort2."` ".$orient2.", `".$sort3."` ".$orient3." LIMIT $searchpage, $limit");

 

the $XX_string vars are search variables for matching certain critera

the $sort vars are the different sorting methods that can be passed

 

i tried..

 

$query = mysql_query("SELECT * FROM `doctors` JOIN `specialty` ON (doctors.specialty_1 = specialty.id) WHERE `list`='0' ".$last_name_string." ".$first_name_string." ".$city_string." ".$specialty_string." AND `listing_type` LIKE 'basic' ORDER BY `".$sort1."` ".$orient1.", `".$sort2."` ".$orient2.", `".$sort3."` ".$orient3." LIMIT $searchpage, $limit");

 

where all $XX_strings are blank

$sort1 = specialty.specialty

$orient1 = ASC

 

and everything else is blank.

 

this fails as not a valid resource, if i have to name columns at select, do i have to designate them like doctors.first_name etc..?

Link to comment
Share on other sites
btherl Member

btherl

Posted
Posted

Change that to

 

$sql = "SELECT * FROM `doctors` JOIN `specialty` ON (doctors.specialty_1 = specialty.id) WHERE `list`='0' ".$last_name_string." ".$first_name_string." ".$city_string." ".$specialty_string." AND `listing_type` LIKE 'basic' ORDER BY `".$sort1."` ".$orient1.", `".$sort2."` ".$orient2.", `".$sort3."` ".$orient3." LIMIT $searchpage, $limit";
$query = mysql_query($sql);
if (!$query) die("Query failed: $sql\nReason: " . mysql_error());

 

That will give you an error telling you what's wrong with the query.  Post the output here and we can fix it up :)

 

As a side note, you shouldn't need to use LIKE 'basic' .. `listing_type` = 'basic' should work just as well, and may be faster.

 

To answer your question about naming select columns, you only need to give the table name if the column has the same name in both tables.  So if both tables have id, you need to say doctors.id or specialty.id.  But if only one table has id, you can just say id.

Link to comment
Share on other sites
benjaminbeazy Member

benjaminbeazy

Posted
  • Author
Posted

Query failed: SELECT * FROM `doctors` JOIN `specialty` ON (doctors.specialty_1 = specialty.id) WHERE `list`='0' AND `listing_type` LIKE 'basic' ORDER BY `specialty.specialty` ASC, `last_name` , `first_name` ASC LIMIT 0, 40 Reason: Unknown column 'specialty.specialty' in 'order clause'

 

good eye on the listing_type LIKE, knew that but brain was on vacation

 

have both id column in specialty and doctors, does this change stuff?

Link to comment
Share on other sites
btherl Member

btherl

Posted
Posted

Hmm.. I think the problem may be the quoting.  Try `specialty`.`specialty` or just specialty.specialty.  I don't see any typos.

 

If you do have problems with the duplicate id columns, I think the only solution is to specify every column you want seperately, like

 

SELECT first_name, last_name, specialty, doctors.id, ... FROM ...

 

But if it works, it works :)

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.