PHP/MySQL: Improper data displaying in drop-down menu...

Discussion in 'OT Technology' started by curtis_rak, Feb 21, 2004.

  1. curtis_rak

    curtis_rak Guest

    This is probably pretty easy, but I can't get my head around it because it involves pulling data from a joined MySQL Select statement, and its causing problems.

    It is a basic Add/Edit/Delete system in PHP/MySQL. I have a table
    called 'ARENAS'. The table consistes of 2 fields. One is an
    auto-incrementing ID field, and the other is a text field.

    The main table is called 'RENTALS' and it contains 5 fields. An
    auto-inc. ID field, DATE field, START (time) field, END time field,
    and an RINK (foreign key; ARENAS.ID) field.

    When I go to edit an entry from the 'RENTALS' table, the values for
    ID, DATE, START, and END will carry over and display properly with no
    problem, however, the RINK field will pull the top value from the
    'ARENAS' table, not the actual value in the row. Now, if I have it
    set to display just the ARENA.ID, it is fine, but, I would like to
    see the actual arena name, not the numerical value (which would be ARENAS.ID). I am currently using a drop dowm menu to display this information. All of the other fields, that are just text boxes, not drop-down menus, display perfectly fine.

    Any help would be appreciated.
  2. GmH

    GmH Guest

    SELECT,, r.start, r.end,,
    FROM rentals r, arenas a
    WHERE r.rink =
    AND = <id of rental row you want to edit>;

    Or (alternative syntax):

    SELECT,, r.start, r.end,,
    FROM rentals r LEFT JOIN arenas a ON r.rink =
    WHERE = <id of rental row you want to edit>;
  3. curtis_rak

    curtis_rak Guest

    My SELECT statement seems to be fine.

    I just tested displaying the Drop-Down data as a Text Box, and the proper name showed up. But as soon as I switched it back to the Drop-Down, whatever value thats at the top of the table will show first in the list no matter what.

    Here is my code for fetching the contents of the drop down:

    <th align=left><font size='1'>Arena:</font></th>
    <td><select name="rinkid">

    $result = mysql_query($SQL);
    while ($myrow=mysql_fetch_array($result)) {
    $rinkid2 = $myrow["id"];
    $name = $myrow["name"];
    <option value="<?php echo $rinkid2?>"><?php echo $name; ?></option>

    }//end while



    Like I noted above, the SQL statement is probably my issue. But I dont know how to propogate the names from the entire table, with the proper value being first in the drop down list.

    I hope this is understandable. If not, yell at me.
  4. GmH

    GmH Guest

    Ahh ok, I see what you mean now. You need to mark the appropriate option tag as "selected". Assuming that $rinkid holds the ID of the correct row, then something like this should work:

    <select name="rinkid">
    $SQL = "SELECT * FROM arenas";
    $result = mysql_query($SQL);
    while($myrow=mysql_fetch_array($result)) {
    $rinkid2 = $myrow["id"];
    $name = $myrow["name"];
    $selected = ($rinkid2 == $rinkid) ? " selected" : "";
    <option value="<?=$rinkid2?>"<?=$selected?>><?=$name?></option>
    } // end while
  5. curtis_rak

    curtis_rak Guest

    The data in the row should be as follows.

    --> rentalID, date, start, end, rinkID (primary key=rentalID, foreign key=rinkID)

    My Rink table would be like this:
    ID Name
    1 Fleet Center
    2 The Igloo

    If the rinkID of the row I want to edit is '2', the drop down box will still show '1' or 'Fleet Center' as the first value.

    I tried the above code and couldnt get it to go.
  6. curtis_rak

    curtis_rak Guest

    I got that code to work, but how to bring it to the top of the drop-down menu? Any ideas?
  7. GmH

    GmH Guest

    So you want the current value to be the first item in the drop-down, not just to be the currently selected item? It's a pretty pointless thing to do IMHO, but if you must, then you'll need to do something like re-order the set of data you are pulling out of the database. For example:

    1. Query the database
    2. Fetch all the result rows and store them in an array
    3. Search through the array until you find the row you want as the first option
    4. Move it to the front of the array
    5. Loop through the array printing out the option tag for each row.
  8. curtis_rak

    curtis_rak Guest

    I see now. Thank you.

    Appreciate the help!

Share This Page