Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2012
    Posts
    19

    Unanswered: Count(*) AS count?

    I'm writing a script that uses php and mysql to see if the user name entered into a text entry field is unique within the table queried.

    Part of this script is a mysql query that comes from a tutorial. The writer of this tutorial says this mysql query asks the table if the user name entered exists in the table, and if it does, returns the number of times it does.

    This query uses some keywords that I don't understand, so I don't know how this query "asks" the table that question. I don't want to just use this query without understanding what these key words mean:
    Code:
    Count(*) AS Count

    Here is the full query:
    Code:
    SELECT COUNT(*) AS count FROM $table WHERE $field='$value';
    Thank you.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    COUNT(*) is a function, and it returns the number of rows found

    AS count assigns a column alias called "count" to the column

    by the way, running a query to count how many times a user name already exists in the table is not the best way to go about the task of ensuring uniqueness -- but perhaps that's more of an intermediate rather than a beginner topic
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2012
    Posts
    19
    Quote Originally Posted by r937 View Post
    by the way, running a query to count how many times a user name already exists in the table is not the best way to go about the task of ensuring uniqueness -- but perhaps that's more of an intermediate rather than a beginner topic
    Thanks for telling me that. I'm ready to learn if you're willing to explain it.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    define the user name as unique using a UNIQUE constraint

    then just simply issue the INSERT for the potential new user name without doing the SELECT COUNT(*) first

    if you get no error message, it went in as a new name

    if you get an error message, it was already there, and the insert was rejected

    this is on average approximately twice as efficient, as you're only running one sql statement, whereas with the SELECT COUNT(*) first, you're running two sql statements -- except for those times when it actually does find an existing name, so it's not 100% less efficient, maybe 98% less efficient
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2012
    Posts
    19
    Thank you very much

Posting Permissions

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