Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

how to return values aphebetically without messing up sub values MySQL

Posted on 2011-04-30
8
Medium Priority
?
274 Views
Last Modified: 2012-05-11
I have a database that I will order alphabetically except for a subset of results.  Here is what I mean:
I want my data to look like the first table but it is stored in my database as the second table.  How do I structure my query so that it returns the data exactly as shown as the first table?

StateName

Alabama
Alaska
     -Juneau
    -Fairbanks
California
    - Los Angeles
    -San Fransisco
Colorado
Arizona

StateName

Currently it looks like this:
California
    - Los Angeles
    -San Fransisco
Alaska
     -Juneau
    -Fairbanks
Colorado
Arizona
Alabama


If I enter this data into my database and order it alphebetically, I will lose the subset of cities displayed for the states.
0
Comment
Question by:NewWebDesigner
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 3

Expert Comment

by:Andy Green
ID: 35496329
How are you getting the sub set of results?

Andy
0
 

Author Comment

by:NewWebDesigner
ID: 35496559
Andy,

I don't know what you mean.  By subset I mean the cities that I listed for a particular state.  I want the query to return the states in alphabetical order and the cities that belong to the states to display where they are supposed to-- that is under the states in which they are located.  If i just run a MySQl query to alphabetize my table,  i lose the formatting and end up with this:

Alabama
Alaska
Arizona
California
Colorado
Juneau
Fairbanks
Los Angeles
San Fransisco

As you can see, the results are alphabetized but I lost the formatting where the city is grouped with the appropriate state.  I guess what I am asking is how should I design my table so that I can display queries alphabetically and that also have formatting displayed in the first table in my first post?
0
 
LVL 3

Expert Comment

by:Andy Green
ID: 35496565
Look at correlated sub queries with an order by on the main query and no order on the sub select. But this will depend on some parent / child relationship between state and city.

In SQL Server there is the 'with hierarchy' function but not aware of similar in mySQL

Posting your current query may help.

Andy
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 34

Accepted Solution

by:
Beverley Portlock earned 2000 total points
ID: 35496863
Use a composite key of (I'm guessing here) state and city will help. I'm also guessing that you have columns called 'state' and 'city'.

You could do this with just a GROUP BY / ORDER BY but if your table is large then the performance will poor.  If you already have an index of 'state' then you can disregard the following index

ALTER TABLE yourTableName ADD INDEX ( `state` , `city` )

Then add an ORDER BY to your query like do

SELECT * FROM yourTableName
ORDER BY state, city

0
 

Author Comment

by:NewWebDesigner
ID: 35499223
bportlock:  to use your statement SELECT * FROM yourTableName
ORDER BY state, city
, is this what my table structure should look like?

tablename: stateAndCity

ID                   State                     City
5                  California              Los Angeles
6                 California              San Fransisco
3                  Alaska                  Juneau
2                  Alaska                  Fairbanks
7                 Colorado                Null
4                 Arizona                  Null  
1                  Alabama                Null
0
 

Author Comment

by:NewWebDesigner
ID: 35499294
My previous comment was not only directed to bportlock, if anyone else wants to chime in, feel free.
0
 
LVL 13

Expert Comment

by:Cedric Obinna A.
ID: 35501614
Yes it will look like that if you have california for instance, repeated in your database.

What that query would do basically is to sort your result by state (first) and if there are more than one occurrence of any state, the result will be sorted by city (for that state).

0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35511275
The table structure you mentioned above is one way


tablename: stateAndCity

ID                   State                     City
5                  California              Los Angeles
6                 California              San Fransisco
3                  Alaska                  Juneau
2                  Alaska                  Fairbanks
7                 Colorado                Null
4                 Arizona                  Null  
1                  Alabama                Null

A purist would have a state ID code and a table with State Id, stateName and then your table would have ID, StateId, City but the way you have it will work OK.

In either case, if you have that table structure then you can run a query like that I typed earlier. However if you wanted to report by state then you can run two queries like so

SELECT state FROM myTable
GROUP BY state
ORDER BY state

and that will give you a list of all the states. Within PHP you can then output by state and then use this query to feed another query

 (UNTESTED CODE)

$rs = mysql_query("SELECT state FROM myTable GROUP BY state ORDER BY state ");

while ( $rw = mysql_fetch_assoc( $rs ) {

      echo "State: " . $rw['state'];

      $cityRs = "SELECT city FROM myTable WHERE state = '{$rw['state']}' AND city IS NOT NULL ORDER BY city ");
      if ( $cityRs )
            while ($cityRw = mysql_fetch_assoc( $cityRs ) ) {
                 echo "&nsp; - " . $rwCity['city'];
            }
}
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

578 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