9 Replies Latest reply on Nov 30, 2008 4:04 PM by brywilson88

    MYSQL Tables

    brywilson88
      I have noticed that when data is inserted into a table, either by my insert form/query or if I go directly through myphpadmin, the data get place in the table randomly. I even have an auto_increment row for the dataID. But, new rows get put in the table in a random order. Is there a way to insert data in order or in an order I want it or is there a way to move the data in the table to a specific order?

      I want to pull data in a certain order - It seems the query goes top to bottom - if the data is out of order, it grabs the results out of order. Maybe there is a way to set this up in the query that will sort the data I want, in the order I want before displaying the results?

      Can anyone point me in the right direction?

      Thanks
        • 1. Re: MYSQL Tables
          Level 7
          brywilson88 wrote:
          > I have noticed that when data is inserted into a table, either by my insert
          > form/query or if I go directly through myphpadmin, the data get place in the
          > table randomly.

          It's not random. If you click the Browse tab in phpMyAdmin, your data
          will normally be displayed with the record IDs in ascending order.

          You have got hold of the wrong end of the stick. You don't insert
          records in a specific order; you use the ORDER BY clause in your SQL
          query to sort the results of a SELECT statement however you want them to
          be. That's the beauty of a database: you can retrieve results in
          countless different ways. You can also manipulate the data in a
          database, e.g., by formatting the date in whatever way you want, or
          using a date calculation, say, to retrieve records that are less than
          two weeks old.

          --
          David Powers, Adobe Community Expert
          Author, "The Essential Guide to Dreamweaver CS4",
          "PHP Solutions" & "PHP Object-Oriented Solutions"
          http://foundationphp.com/
          • 2. Re: MYSQL Tables
            brywilson88 Level 1
            Thank you. I was not sure. When I hit the browse tab in myphpadmin it has my data in a random order. I then have to sort the table in myphpadmin to get thenm in order. Maybe my ntables are set wrong. However, the sort function does not stay. If I leave myphpadmin, it goes back to the random order. Anyway, I will try to find some information regarding how to use the ORDER BY. That sounds like it is exactly what I need.

            Another question: Is there a way or method to put multipule criteria in a table cell? Here is what I mean. I have a product vendor that covers three states (say, FL, TX, CA). I have in my query and forms to allow the user to select their state. Now, how I have my table set up, I would put in one time this vendor - then put in three products in my products table - one product for each state (everything else would be the same).

            Is there a way to put all three states in a cell - like FL,TX,CA - then be able to have that searched by the query? Thus, if the user chooses the state TX, it will match to this product?

            Thanks
            • 3. Re: MYSQL Tables
              St@tto Level 1
              quote:

              Originally posted by: brywilson88
              Is there a way to put all three states in a cell - like FL,TX,CA - then be able to have that searched by the query? Thus, if the user chooses the state TX, it will match to this product?



              I use checkboxes on the input form, then before inserting to the database loop through all of the options and create a string such as "FL,TX,CA" and insert that into the db. Then you can use something like ...WHERE state like '% TX,%'... in your query. If you need to display all of the states again afterwards use the php strpos on the string to establish whether or not your checkboxes need to be checked.
              • 4. Re: MYSQL Tables
                Level 7
                brywilson88 wrote:
                > I will try to find some information regarding how to use the ORDER BY.
                > That sounds like it is exactly what I need.

                ORDER BY is one of the most fundamental parts of a SELECT query:

                http://dev.mysql.com/doc/refman/5.0/en/select.html

                Dreamweaver will do a lot of the coding for you, but you need to learn
                the basics of SQL if you're going to do anything with a database.

                ORDER BY is very simple. All you do is create a comma-separated list of
                the columns you want your recordset to be in.

                SELECT product_id, product, style, color
                FROM products
                ORDER BY style, color

                That orders the results according to style, followed by color.

                > Is there a way to put all three states in a cell - like FL,TX,CA - then be
                > able to have that searched by the query? Thus, if the user chooses the state
                > TX, it will match to this product?

                Yes, you could do it by defining a SET column, which supports a
                predefined list of up to 64 items.

                If you use a SET column, the way to search for Florida looks like this:

                SELECT product, style, color
                FROM products
                WHERE FIND_IN_SET('FL', state)

                Using SET columns in MySQL moves beyond the beginner level, but it's
                explained in Chapter 17 of my book, "The Essential Guide to Dreamweaver
                CS4 with CSS, Ajax, and PHP".

                --
                David Powers, Adobe Community Expert
                Author, "The Essential Guide to Dreamweaver CS4",
                "PHP Solutions" & "PHP Object-Oriented Solutions"
                http://foundationphp.com/
                • 5. Re: MYSQL Tables
                  brywilson88 Level 1
                  Thanks to all. I will utilize the Order By - great stuff this Dreamweaver - already thought out and fixed all my problems.

                  I like the set column feature and will find out more about it.

                  Thanks to all - this is such a great place to get information when stuck
                  • 6. Re: MYSQL Tables
                    bregent Most Valuable Participant
                    >Thanks to all. I will utilize the Order By - great stuff this Dreamweaver

                    Again, as David stated, order by is not something magic in Dreamweaver, it is basic SQL.

                    >I like the set column feature and will find out more about it.

                    I would thing hard before using a set column. It might seem to solve your immediate problem but it violates proper database normalization rules. You have a many-to-many relationship so you should really create a new table to link the vendor table to a states table. This will make complex queries much easier down the road.
                    • 7. Re: MYSQL Tables
                      brywilson88 Level 1
                      I am just looking for a way to reduce the size of my database. Let's say I have a vendor that has three products and covers three states. The way I have the db set up is that I have to put in 9 table rows for this one vendor. i.e. product 1 - state 1, product 1 - state 2, product 1 - state 3, product 2 - state 1, product 2 - state 2, etc, etc, ....

                      I am just looking for a more effecient way - a way that I could put one vendor in my vendor table and one product in the product table - but can seach the one product table row for muiltiple criteria.

                      Not sure what to do. I liked the idea of setting the column with muiltiple data, then just searching inside that. Once the linking target is identified, then it would go to my vendors table and pull the results I wanted.

                      Is there a better way - as I really like the set table idea.

                      Thanks
                      • 8. Re: MYSQL Tables
                        Level 7
                        brywilson88 wrote:
                        > I am just looking for a way to reduce the size of my database. Let's say I
                        > have a vendor that has three products and covers three states. The way I have
                        > the db set up is that I have to put in 9 table rows for this one vendor. i.e.
                        > product 1 - state 1, product 1 - state 2, product 1 - state 3, product 2 -
                        > state 1, product 2 - state 2, etc, etc, ....

                        As bregent says, you need to learn about database normalization. Nine
                        records in one table for a single vendor breaks all the rules of
                        normalization. You should have three separate tables for the vendors,
                        products, and states. You then need to create lookup tables to link the
                        records. How you do it depends on what you want the database to do, but
                        the following is one way:

                        vendors

                        vendor_id name
                        1 VendorA
                        2 VendorB
                        3 VendorC

                        products

                        product_id name
                        1 ProductA
                        2 ProductB
                        3 ProductC

                        states

                        state_id state
                        AK Arkansas
                        AZ Arizona
                        FL Florida

                        vendor_lookup

                        vendor_id product_id state_id
                        1 2 FL
                        1 2 AK
                        2 1 AZ
                        3 3 FL

                        VendorA sells ProductB in Florida and Arkansas
                        VendorB sells ProductA in Arizona
                        VendorC sells ProductC in Florida

                        Dreamweaver provides the first stepping stone to learning about working
                        with PHP and MySQL, but it's a vast subject, and involves a lot of work
                        if you want to move beyond baby steps. You might want to take a look at
                        a book called "Database Design for Mere Mortals". I haven't read the
                        latest edition, but the first edition - although a bit wordy and
                        repetitive - is a good introduction to database design, written in a way
                        that doesn't require a degree in computer science to understand.

                        --
                        David Powers, Adobe Community Expert
                        Author, "The Essential Guide to Dreamweaver CS4",
                        "PHP Solutions" & "PHP Object-Oriented Solutions"
                        http://foundationphp.com/
                        • 9. Re: MYSQL Tables
                          brywilson88 Level 1
                          Mr. Powers, thanks for the information. I will look into the database design book and your book as well. I also utilized the set column and FIND_IN_SET () query. Works great just how I want it to.

                          I have two tables. The first is a vendor table that has vendor name, discription, and a text link to the vendors website.

                          The second is a product table that has several columns specific to the product including the state where the product is at and a vendor id that links to the vendor table. To me this is very simple.

                          I see what you mean about the three tables. However, with the set column and FIND_IN_SET()_ query, seems to work how I want it.

                          Again, thank you very much for your help and knowledge. Also, thank everyone else in this forum that helps us take baby steps.

                          I am learning everyday and hope to be able to walk with html, mysql, and php very soon.