SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    334
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Selecting one or more records by primary index

    I'm trying to create a query that will extract one or more records from a database table. I've tried:

    Code:
    SELECT * FROM 'accommodations' WHERE 'accn_id' = 2007 OR 'accn_id' = 2024
    which gives an empty result -- that's to say there's no error message, but no rows are returned, even though there ARE records with those accn_id values.

    I've also tried:
    Code:
    SELECT * FROM accommodations WHERE 'accn_id' = 2007
    and that does give an error message, so presumably I've not got the syntax quite right ?

    I can't yet see what I've done wrong, but I hope I'm close.
    Tim Dawson
    Isle of Mull, Scotland

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you're comparing a fixed string ('accn_id') to a number

    you probably meant to use a column value (accn_id)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    334
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thank you for this insight. I had begun to wonder, but 'accn_id' (the primary index) is of type int(4), so I'd assumed it was a number. The values in the 'where' expression are derived from the 'accn_id' values of selected records, so I thought these would be numbers too.

    However, you've given me some more to work with !
    Tim Dawson
    Isle of Mull, Scotland

  4. #4
    SitePoint Zealot eeight's Avatar
    Join Date
    Oct 2006
    Location
    New York City
    Posts
    106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ramasaig - If I'm not mistaken, I think r937's point was that you should get rid of the quotation marks around the column name accn_id. Strings go in quotation marks, column names don't.

  5. #5
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    334
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thank you Eeight. I'd missed that point. I've changed my code now and it seems to work OK.
    Strange thing is that phpMyAdmin has single quotes round column names when using the Browse feature, e.g.: UPDATE `holidaymull`.`accommodations` SET `date` = NOW( ) WHERE `accommodations`.`accn_id` =2002 LIMIT 1. The single quotes remain when one clicks 'Create PHP code'.
    I was trying to folow that sytax. However, it's now going to work without.

    Thanks again.
    Tim Dawson
    Isle of Mull, Scotland

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by ramasaig View Post
    Strange thing is that phpMyAdmin has single quotes round column names...
    those aren't single quotes, those are backticks

    big difference
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    334
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thank you. Now you mention it they do look rather emaciated. But why is it such hard work finding out this stuff ? That just the kind of gotcha that should feature in large print in books and manuals dealing with phpMyAdmin.

    I bought your Simply SQL as a PDF a couple of weeks ago. Clearly I need to read it, urgently, although the title implies you won't deal with phpMyAdmin. I see from the contents that you cover many other aspects of SQL that seem to be missing or glossed over in the other books I've got.

    Anyway, thanks again, I seem to be on the straight and narrow now.
    Tim Dawson
    Isle of Mull, Scotland

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by ramasaig View Post
    But why is it such hard work finding out this stuff ?
    i know what you mean, but it's all in the manual, provided you know what to look for

    the backticks are mysql's (proprietary) method for escaping problematic identifiers (table and column names)

    e.g. `last name` (contains a space, so must be escaped)

    in ms access and sql server, it's square brackets

    e.g. [last name]

    the (ANSI) standard SQL method is doublequotes

    e.g. "last name"

    note that mysql has an ANSI_QUOTES option which will use the doublequote delimiter

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



    "eveyting i know, i learnt from da manual"

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    334
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thank you for that lucid explanation.

    As you say, it's often the knowing where to look and what to look for that pose the problem. I'm a great believer in reading the manual, which is why I've got so many books. I also make use of the MySQL on line documentation, but that's so comprehensive that sometimes you don't see the wood for the trees.
    Tim Dawson
    Isle of Mull, Scotland


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
  •