Solved

Order by clause

Posted on 2016-10-18
24
32 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 82

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 82

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 82

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 82

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 82

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 21

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 42

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 42

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

Easy Project Management (No User Manual Required)

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
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…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

707 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

13 Experts available now in Live!

Get 1:1 Help Now