Results 1 to 5 of 5

Thread: 2 tables problem

  1. #1

    Thread Starter
    Hyperactive Member AvisSoft's Avatar
    Join Date
    Sep 2002
    Location
    Chandigarh
    Posts
    459

    2 tables problem

    Hi!

    I have 2 tables... named acc_categroies and wnd_categories both have 1 field named categories...

    i need to get all data from both tables then sort them and display how can i do this in 1 go ?

    currenntly i use 2 sql queries to get the data from 2 tables..! and order it like this:

    "select category from acc_categroies order by category" then another query

    "select category from wnd_categories order by category"

    now the problem is the data is dsiplayed liek:

    A-Z for first tables then A-Z for second table.

    Instead it should be A_Z for both tables!
    Tapan Bhanot,
    CEO, Avis Software.
    Website: www.avissoftware.com

  2. #2

    Thread Starter
    Hyperactive Member AvisSoft's Avatar
    Join Date
    Sep 2002
    Location
    Chandigarh
    Posts
    459
    Currently i am using this code:

    PHP Code:
    <select name="position" id="position">
    <?php
    $connection 
    mysql_connect ("xxxx.xxx""xxx_table""xxxx") or die ("Unable to connect!");
    $db mysql_select_db ("xxxx"$connection) or die ("Unable to select database");
    $sql "select category from acc_categories ORDER BY category";
    $sql_result mysql_query($sql$connection) or die ("Unable to execute query");
    while (
    $row mysql_fetch_array($sql_result))
    {
            
    $cat $row["category"];
            echo 
    "<option>" $cat "</option>\n";
    }

    $sql "select category from wnd_categories ORDER BY category";
    $sql_result mysql_query($sql$connection) or die ("Unable to execute query");
    while (
    $row mysql_fetch_array($sql_result))
    {
            
    $cat $row["category"];
            echo 
    "<option>" $cat "</option>\n";
    }
    mysql_free_result($sql_result);
    mysql_close($connection);
    ?>
    </select>
    Tapan Bhanot,
    CEO, Avis Software.
    Website: www.avissoftware.com

  3. #3
    Frenzied Member ober0330's Avatar
    Join Date
    Dec 2001
    Location
    OH, USA
    Posts
    1,945
    Well... I'm sure there is a way to combine it into one SQL statement... you'd have to use a join, but I'm not sure on the sorting part. That would be your fastest and easiest method, but my brain isn't working that fast this morning.

    Your other option would be to still use 1 query with a join and dump everything to an array and then sort the array. Then you can dump that all out in your options.
    format your code!! - [vbcode] [/vbcode]

    ANSWERS CAN BE FOUND HERE!!

    my personal company

  4. #4
    Kitten CornedBee's Avatar
    Join Date
    Aug 2001
    Location
    In a microchip!
    Posts
    11,594
    Starting with MySQL 4.0.0 you can use UNION:
    Code:
      (SELECT category FROM acc_categories)
    UNION ALL
      (SELECT category FROM wnd_categories)
    ORDER BY category;
    All the buzzt
    CornedBee

    "Writing specifications is like writing a novel. Writing code is like writing poetry."
    - Anonymous, published by Raymond Chen

    Don't PM me with your problems, I scan most of the forums daily. If you do PM me, I will not answer your question.

  5. #5

    Thread Starter
    Hyperactive Member AvisSoft's Avatar
    Join Date
    Sep 2002
    Location
    Chandigarh
    Posts
    459
    Originally posted by CornedBee
    Starting with MySQL 4.0.0 you can use UNION:
    Code:
      (SELECT category FROM acc_categories)
    UNION ALL
      (SELECT category FROM wnd_categories)
    ORDER BY category;
    Thanks corned bee it helped! ;-)
    Tapan Bhanot,
    CEO, Avis Software.
    Website: www.avissoftware.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width