Database Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Codewalkers Forums > Other Technologies > Database Help
Reload this Page

sortring and displaying sql results

Discuss sortring and displaying sql results in the Database Help forum on Codewalkers.
sortring and displaying sql results General SQL help. Help with MySQL, MSSQL, Oracle, mSQL, PostgreSQL, etc.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Codewalkers Forums Sponsor:
  #1  
Old October 25th, 2002, 01:54 PM
Anonymous Anonymous is offline
Registered User
Codewalkers God 35th Plane (22000 - 22499 posts)
  
Join Date: Apr 2007
Posts: 22,309 Anonymous User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 24
sortring and displaying sql results
Hello,

I am a newbie--

I am trying to print out a list of restaurant--sorted by the type of restaurant.

But I think I'm making this too complicated. When I use sort on sql--only the first row is printed. I want to pull up all of the restaurants for a particular state and then print out the results according to the type of restaurant--for instance--

Chinese Restaurants
My Huang
Choo Choo

Indian Restaurants
Indoo Inn
Chutney Central
Isle of India

Italian Restarants
Lil Italy
Spagetti Inn
Pizza and Stuff

This is the code I have so far--all it does it pull up the restaurants--I want it to sort by the cuisine

php Code:
Original - php Code 
  1.  
  2.  
  3. function showerror()
  4. {
  5. die("Error" . mysql_errno() . " : " .mysql_error());
  6. }
  7.  
  8. function stateName ($connection,        
  9.                     $statnum)
  10. {
  11. $query = "SELECT state 
  12.         from location 
  13.         where statnum = $statnum";
  14.         
  15.     //run the query 
  16.     if(!($result = @ mysql_query($query, $connection)))
  17.     showerror(); 
  18.     //retrieve state
  19.     while ($row = @ mysql_fetch_array($result))
  20.     //print state name 
  21.     echo " ". $row["state"]; 
  22. }
  23. //............
  24.                 
  25. //run query on dbms
  26.  
  27.  
  28. $query = "SELECT cuisine, 
  29.                 name,
  30.                 address,
  31.                 phone,
  32.                 url, 
  33.                 city 
  34.         FROM restaurants        
  35.         WHERE state = $statnum";    
  36. //open a database connection        
  37.         
  38.  
  39.  
  40.  
  41.  
  42. if(!$result = @ mysql_query ($query, $connection))
  43. showerror();
  44.  
  45. //find out how many rows are available 
  46. $rowsFound = @ mysql_num_rows($result);
  47.  
  48. //if query has not results
  49. if($rowsFound < 1)
  50. {
  51.     echo "<h3>  No Restaurants Found </h3>";
  52. }
  53. else
  54.  
  55. {
  56.     //start a table 
  57.     echo "<table width="90%"> <tr><td bgcolor="#FF9933" align="center" valign="bottom"><p>&nbsp;</p><h2>Restaurants in";
  58.     stateName ($connection, $statnum); 
  59.     echo "</h2></td> </tr>";
  60.     
  61.     while ($row = @ mysql_fetch_array($result))
  62.     {
  63.     //print rows 
  64.     
  65.     echo "<tr> <td bgcolor="#FFCC00">".
  66.             $row[name]. "   " .   
  67.             $row[address]. "  ".
  68.             $row[city]. "  ".
  69.             PH. " " .
  70.             $row[phone]. "  ";
  71.             
  72.     echo "</td></tr>";
  73.     }
  74.     
  75.     echo "</table>";
  76. }            
  77. if(!mysql_close($connection))
  78. showerror();
  79. ?>
  80.  






Reply With Quote
  #2  
Old October 25th, 2002, 02:11 PM
Kohaar Kohaar is offline
Codewalkers Newbie (0 - 499 posts)
  
Join Date: Apr 2007
Location: Denmark
Posts: 147 Kohaar User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 m 37 sec
Reputation Power: 2
Send a message via ICQ to Kohaar
RE: sortring and displaying sql results
You can use:

[CODE]
SELECT * FROM table
WHERE value = value ORDER BY cuisine
Reply With Quote
  #3  
Old October 25th, 2002, 03:54 PM
Anonymous Anonymous is offline
Registered User
Codewalkers God 35th Plane (22000 - 22499 posts)
  
Join Date: Apr 2007
Posts: 22,309 Anonymous User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 24
RE: sortring and displaying sql results
I don't understand what should go in the "value" field? Are you saying do three separate queries?
Reply With Quote
  #4  
Old October 25th, 2002, 06:20 PM
Kohaar Kohaar is offline
Codewalkers Newbie (0 - 499 posts)
  
Join Date: Apr 2007
Location: Denmark
Posts: 147 Kohaar User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 m 37 sec
Reputation Power: 2
Send a message via ICQ to Kohaar
RE: sortring and displaying sql results

Code:

$query = "SELECT cuisine, 
                name,
                address,
                phone,
                url, 
                city 
        FROM restaurants        
        WHERE state = $statnum"
        ORDER BY cousine;


This will display your output ordered by cuisine.

If you want i in reverse order you type ORDER BY cuisine DESC
Reply With Quote
  #5  
Old October 25th, 2002, 07:37 PM
Anonymous Anonymous is offline
Registered User
Codewalkers God 35th Plane (22000 - 22499 posts)
  
Join Date: Apr 2007
Posts: 22,309 Anonymous User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 24
RE: sortring and displaying sql results
ok thanks that worked so to seperate the types of cusines do I do an if statement
I'm thinking something like
php Code:
Original - php Code 
  1.  
  2.  
  3. if $row[cusine] ="1" 
  4. echo chinese restaurants    
  5.     echo "<tr> <td bgcolor="#FFCC00">".
  6.             $row[name]. "   " .   
  7.             $row[address]. "  ".
  8.             $row[city]. "  ".
  9.             PH. " " .
  10.             $row[phone]. "  ";
  11.             
  12.     echo "</td></tr>";
  13.     }
  14.     
  15.     echo "</table>";
  16. }            
  17. if(!mysql_close($connection))
  18. showerror();
  19. ?>
  20.  

does that look about right do one for each cuisine
Reply With Quote
  #6  
Old October 25th, 2002, 08:20 PM
Kohaar Kohaar is offline
Codewalkers Newbie (0 - 499 posts)
  
Join Date: Apr 2007
Location: Denmark
Posts: 147 Kohaar User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 m 37 sec
Reputation Power: 2
Send a message via ICQ to Kohaar
RE: sortring and displaying sql results
It looks allright with a few alterations. Haven't tested it though.

Code:
<?php
if ($row[cusine] == "1") { 
echo chinese restaurants    
    echo "<tr> <td bgcolor="#FFCC00">".
            $row[name]. "   " .            
            $row[address]. "  ".
            $row[city]. "  ".
            PH. " " .
            $row[phone]. "  ";
            
    echo "</td></tr>";
    }
    
    echo "</table>";
                
if(!mysql_close($connection)) {
showerror();
}
?>
Reply With Quote
  #7  
Old October 28th, 2002, 05:43 AM
Anonymous Anonymous is offline
Registered User
Codewalkers God 35th Plane (22000 - 22499 posts)
  
Join Date: Apr 2007
Posts: 22,309 Anonymous User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 24
RE: sorting and displaying sql results
I still can't get this--I've tried several ways this is the lastest it prints out all the rows--how do I check for one attribute and have it print based on that attribute???

php Code:
Original - php Code 
  1.  
  2. {
  3.     //start a table 
  4.     echo "<table width="90%"> <tr><td bgcolor="#FF9933" align="center" valign="bottom"><p>&nbsp;</p><h2>Restaurants in";
  5.     stateName ($connection, $statnum); 
  6.     echo "</h2></td> </tr>";
  7.     echo "<tr><td bodycolor="white">";
  8.     echo "<h3> American Restaurants</h3>";
  9.     echo "</td></tr>";
  10.     
  11.     while ($row = @ mysql_fetch_array($result))
  12.     {
  13.     //print rows 
  14.     if ($row[cuisine]="1")
  15.     echo "<tr> <td bgcolor="#FFCC00">".
  16.             $row[name]. "   " .   
  17.             $row[address]. "  ".
  18.             $row[city]. "  ".
  19.             PH. " " .
  20.             $row[phone]. "  ";
  21.             
  22.     echo "</td></tr>";
  23.     }
  24.     
  25.     echo "</table>";
  26. }


Reply With Quote
Reply

Viewing: Codewalkers Forums > Other Technologies > Database Help > sortring and displaying sql results

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump