SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    Aug 2004
    Location
    Wiltshire SWE
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PHP MySQL Multiple Column Dynamic Dropdown

    Greetings all,

    I am currently in the process of setting up a e-commerce suite on a client
    website but as I am a PHP MySQL newbie, I have come accross a few problems. I have a product page which has static information on it. I have a form with
    a dropdown box. I want this dropdown box to show data from three different columns in a MySQL database table. These would be, Colour, Size and Price.
    I have the database setup and running with Apache, PHP and MySQL all
    installed, configured and fully operational.

    I am using Dreamweaver MX 2004 and have already setup the dropdown on the
    page using the DW insert List/Menu feature. I have been able to select what
    dynamic data I would like to show by use of a recordset I have already setup
    with all the relevant columns.

    The first problem I have is the DW insert list/menu feature only allows me to show one column of data. I want to show all three one after the other in
    the same dropdown box. I am sure this is possible because I have done this
    using SQL in MS Access. I am unsure how this is done in PHP MySQL.

    The second problem I have is that I would like the selected data from the
    dropdown to be transported to the next page which will be the Review Cart
    page. I have read on forums that the use of variables would do this for me
    but am unsure as to how I would implement them into dropdown on the product page.

    The third problem I have is I do not quite understand how the dropdown would only show the Colour, Size and Price options for the product of that product page. I have a dynamic dropdown tutorial which states that a parents and child lists would be needed, but seeing as I am only having one dropdown box, I think the child list would not be necessary.

    Would I need to put a parentID column in the database table and give an ID for each set of options? for example, Bracelet would have Silver, Gold and Silver Plate.

    Would I need to put an ID of 1 for each of these and then go on to put 2 for the next set of options for another product and 3 for the next and so on?

    Any help would be much appreciated.

    Thank-you all,


    Regards,



    Rue

  2. #2
    SitePoint Enthusiast
    Join Date
    Jun 2004
    Location
    UK
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Use the SELECT column1, column2, column3 FROM your_table and create a a recursive loop to display the results. The alternative is to use dynamic selection where the input of one selector alters the values available in another. This is possible with a full page refresh JavaScript and php. Tranfering the data is easy. Just post to the next page. Whilst these are fairly basic data handling methods I fail to see why you offered to undertake such a project.

  3. #3
    SitePoint Member
    Join Date
    Aug 2004
    Location
    Wiltshire SWE
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think i understand what you mean but how would i get the dropdown to only show options for the specific product on the product page?

    I took on the project because I am their system administrator and co web developer and wanted to learn php mysql anyway

  4. #4
    SitePoint Member
    Join Date
    Aug 2004
    Location
    Wiltshire SWE
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I understand what you say about SELECT but I am not sure how this would fit into a PHP dropdown.

    This is the code I have so far:

    Code:
    <select name="select">
                              <option value="0">Please Select One</option>
                              <?php
    do {  
    ?>
                              <option value="<?php echo $row_Recordset1['Title']?>"><?php echo $row_Recordset1['Title']?></option>
                              <?php
    } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));
      $rows = mysql_num_rows($Recordset1);
      if($rows > 0) {
          mysql_data_seek($Recordset1, 0);
    	  $row_Recordset1 = mysql_fetch_assoc($Recordset1);
      }
    ?>
                            </select>
    As you know, the Please Select One is the static value, the option value is from the recordset and only allows me to show one title. I am unsure how to show three columns.

  5. #5
    SitePoint Enthusiast
    Join Date
    Jun 2004
    Location
    UK
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Several ways of doing it. One would be to use a call to server and get menu items based upon the selection in another menu. String 3 dynamic dependant menus. Another would be to execute 3 seperate queries and put results into a JavaScript array. Use JavaScript to create dependant menus. This method would rely on JavaScript and may net be user friendly. Or third would be to extract the results from the three columns and create a recursive loop like display all results from column 1 loop and then sucessive loops until all results are displayed. From what you originally posted you seem to be modding a clothing store or similar.

    If the database structure was modded to hold stock names, sizes and colors available in each item then 3 dynamic dependant selectors would be a good method of filtering the options, and making the visitors shopping experience more friendly.

    If you wish to post some code examples and tables I will have a little free time tomorow afternoon and would help resolve the situation. PM if you wish for my email ad.

    If you fancy tackling this alone try google for jsrs or type chained selectors and click result 1 for a zend tutorial.

  6. #6
    SitePoint Enthusiast
    Join Date
    Jun 2004
    Location
    UK
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try also this asp tutorial for Ultradev. The php dialogue boxes do exactly the same so this will work great for php in DMX2004

    http://www.dmxzone.com/showDetail.as...=2&NewsId=3218

  7. #7
    SitePoint Member
    Join Date
    Aug 2004
    Location
    Wiltshire SWE
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the major problem i have come accross which has stopped me from considering three separate dropdowns is that we have a database with just over 1200 products in. From reading the DW help files and other forum posts, i have found that in order to have three dropdowns instead of one, I would have to put a parent id column, a child id column and a subchild column id so that the three could be linked up correctly. To do this would take many many man hours and so I have stuck with the idea of having just one dropdown that shows data from three columns by use of just a parent id.
    I am planning to put this dropdown into static product pages and so I need to show only the options for that specific product. I could use the WHERE = "$prod-ID" and put the prod_id in there so it links with the prod_id in the DB table.

    I would prefer not use use Javascript as it can complicate the successful Google search engine optimising I have throughout our site.

  8. #8
    SitePoint Guru toasti's Avatar
    Join Date
    Feb 2004
    Location
    Grahamstown
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not sure if i understand correctly, but if you are wanting just to display one dropdown menu with values from three columns in your table its pretty easy, using your code above:

    Code:
    select name="select">
                              <option value="0">Please Select One</option>
                              <?php
    do {  
    ?>
    <option value="<?php echo $row_Recordset1['Title']?>"><?php echo $row_Recordset1['Title']." ".$row_Recordset1['col2']." ".$row_Recordset1['col3'] ?></option>
                              <?php
    } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));
      $rows = mysql_num_rows($Recordset1);
      if($rows > 0) {
          mysql_data_seek($Recordset1, 0);
    	  $row_Recordset1 = mysql_fetch_assoc($Recordset1);
      }
    ?>
                            </select>
    So the different part would be:
    Code:
    <?php echo $row_Recordset1['Title']." ".$row_Recordset1['col2']." ".$row_Recordset1['col3'] ?>
    instead of
    Code:
    <?php echo $row_Recordset1['Title'] ?>
    inside your option tags.


Bookmarks

Posting Permissions

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