PHP Help. Selecting items from a database. MYSQL

Discussion in 'OT Technology' started by Viperjts10, Oct 18, 2007.

  1. Viperjts10

    Viperjts10 New Member

    Joined:
    Aug 15, 2007
    Messages:
    33
    Likes Received:
    0
    If the code below selects ALL from the database

    Code:
    $result = mysql_query("SELECT * FROM example")
    How do I select only the first row of a database. Then, if an item is added to the database, I want the new item to be displayed.
     
  2. Pork Chop Sandwiches

    Pork Chop Sandwiches Extraordinary Member

    Joined:
    Feb 25, 2005
    Messages:
    7,217
    Likes Received:
    0
    Location:
    TGP
    This will pull in the last record inserted:
    If you do this right after an INSERT, the best way to do it would be to set a variable to the value of the @@IDENTITY global variable. Then select the row based on the value of that variable. I don't know the syntax in MySQL/PHP, but this is how you would do it in SQL:
     
  3. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon

    Very simple:

    The "limit" clause will tell it the maximum number of records to return. If you only want 1, then do LIMIT 1;

    Code:
    $result = mysql_query("SELECT * FROM example LIMIT 1;")
     
  4. ez4me2c3d

    ez4me2c3d Cold Member

    Joined:
    Sep 19, 2003
    Messages:
    1,836
    Likes Received:
    0
    Location:
    Minneapolis, MN
    Why would you order by, when it's returning 1 record? :hsugh:
     
  5. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    davo is an idiot, ignore him.
     
  6. TheDarkHorizon

    TheDarkHorizon \xC0\xFF\xEE

    Joined:
    Sep 26, 2002
    Messages:
    2,396
    Likes Received:
    0
    Location:
    San Francisco, CA
    You would presumably ORDER BY the auto_increment field and LIMIT 1 to get last row inserted. And it's an ORDER BY on the whole table, not the result set.
     
  7. Pork Chop Sandwiches

    Pork Chop Sandwiches Extraordinary Member

    Joined:
    Feb 25, 2005
    Messages:
    7,217
    Likes Received:
    0
    Location:
    TGP
    You want it to descend order the entire result set (before it gets to selecting) by the identity field so that you get the last record inserted when it selects 'top' 1. Selecting top 1 anything can theoretically return any record in the table, but I'm sure all the RDB experts here knew that already. :ugh:
     
  8. Pork Chop Sandwiches

    Pork Chop Sandwiches Extraordinary Member

    Joined:
    Feb 25, 2005
    Messages:
    7,217
    Likes Received:
    0
    Location:
    TGP
    Your code is perfect, except for the fact that it doesn't do what the threadstarter was asking. :rofl:
     

Share This Page