Solved

Order by clause

Posted on 2016-10-18
24
36 Views
Last Modified: 2016-10-19
I've inherited the code below:
//Get manufacturers
$query = "select * from manufactures order by manufacturer";
$manufacturers = sqlsrv_query($objConnection, $query);
if($manufacturers === false) {
    die( print_r( sqlsrv_errors(), true) );
}

Open in new window

What if you really wanted the "order by manufacturer" portion to work.  Is it possible?
0
Comment
Question by:classnet
  • 9
  • 5
  • 4
  • +3
24 Comments
 
LVL 9

Expert Comment

by:Brian Tao
ID: 41848733
What do you mean?  Doesn't it work?  It looks fine.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 41848768
What are the diagnostics or error messages you get?
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 41848777
Looks fine to me too.  "order by manufacturer" implies that there is a column called 'manufacturer' in the results.  "order by" is used to 'order' the results.
0
 

Author Comment

by:classnet
ID: 41848796
It doesn't error out... but also doesn't change the sort order.  Adding ASC or DESC does nothing.

Changing manufacturer to manufacturerxxx kills the query (as expected).

Is there some kind of a $Params syntax?
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 41848811
I'm sorry but 'order by' has always worked that way and I have it working that way on a dozen servers here, both MS SQL and MySQL.  Something else is going on.  Are you getting any error messages?
0
 
LVL 9

Expert Comment

by:Brian Tao
ID: 41848830
Since the SQL statement itself looks good, I guess you're having sorting issues when displaying the result.  Can you show us other part of the PHP script?
0
 

Author Comment

by:classnet
ID: 41848840
This is PHP working on a MS SQL server.

This query:
//Get manufacturers
$query = "select * from manufactures order by manufacturer";
$manufacturers = sqlsrv_query($objConnection, $query);
if($manufacturers === false) {
    die( print_r( sqlsrv_errors(), true) );
}

Open in new window


is used in:
<div class="col-md-3">
	<select id="manufacturer" name="manufacturer" class="selectpicker" style="width: 174px">
		<option>Please Select</option>
		<?php
			while( $row = sqlsrv_fetch_array( $manufacturers, SQLSRV_FETCH_ASSOC) ) {
				echo "<option>" . $row['manufacturer']. "</option>";
			}

			sqlsrv_free_stmt( $manufacturers);
		?>
	</select>
</div>

Open in new window


No errors are thrown... simply just doesn't adjust the order.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 41848862
Time to show us your data from the "View Source" in your browser.  The only reason I can think of that would make it not work is leading spaces in the data or something odd like that.
1
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 41848872
I notice that your 'option' tags do not have a 'value' in them.  http://www.w3schools.com/tags/tag_select.asp
1
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 41848944
Agree with Dave.  Either create the SSCCE so we can test it ourselves, or at least show us the view source  so we can see what is present in the generated HTML. thanks.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 41849189
I just ran a test on one of my simple 'sqlsrv' PHP files with this query:
"SELECT TOP 10 ent_num, DisplayName, Sortname, WebSite, Descript, Cat, Approved FROM  dbo.websitelist WHERE ent_num < 11 ORDER BY ent_num"

Open in new window

I can put any of the fields as the ORDER BY and it sorts properly every time.
1
 
LVL 22

Expert Comment

by:Kim Walker
ID: 41849374
Show us a sample of the values in your manufacturer column. Does this column contain names or id numbers?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:classnet
ID: 41849787
Kim... just names
0
 

Author Comment

by:classnet
ID: 41849863
Here is the view source:
<div class="row">
			<div class="col-md-2 col-md-offset-4">
				<span>Manufacturer </span>
			</div>
			<div class="col-md-3">
				<select id="manufacturer" name="manufacturer" class="selectpicker" style="width: 174px">
					<option>Please Select</option>
					<option>Toshiba</option><option>Micro</option><option>Hitachi</option><option>Texas Instruments</option><option>Xilinx</option><option>Cypress</option><option>VLSI</option><option>Phillips</option><option>NXP</option><option>Hyundai</option><option>Samsung</option><option>MOT</option><option>DALE/VISHAY</option><option>YAGEO</option><option>BC COMPONENTS</option><option>KEMET</option><option>KOA</option><option>AVX</option><option>GRYPHON</option><option>FREESCALE SEMICONDUTOR</option><option>AMPHENOL</option><option>JDS UNIPHASE</option><option>MEAN WELL</option><option>FCI</option><option>MOLEX</option><option>AMP</option><option>LINEAR TECHNOLOGY</option><option>PERICOM</option><option>SCHAFFNER</option><option>DALLAS</option><option>MAXIAM</option><option>GTRAN</option><option>EXCELSYS</option><option>FINISAR</option><option>ATI</option><option>ATI</option><option>DATATRONIC</option><option>ARTESYN</option><option>GTRAN</option><option>ASTEC</option><option>WUHAN TELECOMUNCATIONS DEVICES</option><option>SIEMENS</option><option>CONEXANT</option><option>TYCO</option><option>ERICSSON</option><option>AIRPAX</option><option>OPNEXT</option><option>TELLLABS</option><option>EXCELSYS</option><option>LSILOGIC</option><option>BROADCOM</option><option>EPCOS</option><option>PILKOR ELECTRONICS</option><option>COEV</option><option>PANJIT</option><option>TYCO</option><option>RAYCHEM</option><option>MURATA</option><option>SPRAGUE</option><option>PULSE</option><option>LITTLE FUSE</option><option>TDK</option><option>CYPRESS</option><option>NXP</option><option>PHILLIPS</option><option>MICRONAS</option><option>CHIPS</option><option>ALTERA</option><option>ACTEL</option><option>ACTEL</option><option>LSI</option><option>PANASONIC</option><option>TRIAD</option><option>FOXCONN</option><option>GRAYHILL</option><option>ACCELINK TECH</option><option>SOURCE PHOTONICS</option>				</select>
				<!--input id="manufacturer" name="manufacturer"-->
			</div>
		</div>

Open in new window

0
 

Author Comment

by:classnet
ID: 41849867
Here is sample data (ID and manufacturer):
77      ACCELINK TECH
70      ACTEL
71      ACTEL
46      AIRPAX
69      ALTERA
26      AMP
21      AMPHENOL
38      ARTESYN
40      ASTEC
35      ATI
36      ATI
18      AVX
15      BC COMPONENTS
52      BROADCOM
68      CHIPS
55      COEV
43      CONEXANT
64      CYPRESS


and design:

Table design
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 41849944
What appears in the HTTP request when you submit the selection?  You can use PHP var_dump($_REQUEST) to find out.  Please get that and post it here, thanks.
0
 

Author Comment

by:classnet
ID: 41849955
Ray... this issue is on a page that contains a form.  The code is in the form trying to order a list of manufacturers.  

I put the dump code at the top of that page to get:

array(0) { }
0
 
LVL 43

Accepted Solution

by:
Chris Stanyon earned 250 total points
ID: 41850013
I would suggest here that the $manufacturers from your original code is not what's being passed into your PHP loop. Make sure you're not getting a different $manufacturers from somewhere else. (change the variable name in your query code to test this)
1
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 250 total points
ID: 41850042
I agree with Chris, if the ORDER BY clause is not ordering the data correctly, it's likely that the ORDER BY clause is not getting used at all.  Here's another place where var_dump() can be your friend.  Try var_dump($manufacturers) and var_dump($row) immediately  after running the query to retrieve the list.
0
 

Author Comment

by:classnet
ID: 41850049
I agree.... I changed $manufacturers to $NEWmanufacturers and it doesn't load anything now.  Working on it.
0
 
LVL 9

Expert Comment

by:Brian Tao
ID: 41850112
Reading the code from your "View Source".  It's ordering by the ID column.
That's weird, is it possible that you attach your PHP script here and not just paste part of it?  Maybe all you need is just another pair of eyes.
1
 

Author Closing Comment

by:classnet
ID: 41850861
:-/  Found an "include" statement...  Thanks for the assist!
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 41850868
Yep - that'll catch you out :)

Happy you're sorted.
0
 

Author Comment

by:classnet
ID: 41850873
Great catch Chris
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now