Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2012
    Location
    London and Bucarest
    Posts
    25

    Unanswered: Help with my mySQL query

    Hi everyone

    This is just a quick question. i have incorrectly drafted my SQL query and would really appreciate advice on why i missed the mark.

    Below is a query to determine how many times a user has logged into a member login section. the query was incorrectly drafted because it returns zero results.
    However, just below the incorrect mySQL query is the same query that was tested in MYSQL: this query returns the correct results. So, i obviously ran into problems when i tried to integrate the query into a search function.

    THE QUERY
    PHP Code:

    SELECT 
                     u
    .user_id,
                       
    lognum_loggins,
              
    log.time_loggin,
              
    log.seconds_loggin
                
                          
                      
           FROM 
                              users u  


     LEFT OUTER  JOIN 
                    
    SELECT log.user_id,
                     
    COUNT(*)num_loggins,
               
    FROM_UNIXTIMEMAX(log.logindate), '%D %b %Y' ) AS time_loggin,
              
    FROM_UNIXTIMEMAX(log.logindate), '%r' ) AS seconds_loggin
               FROM  logindate log
                    
    ) AS log
                    ON log
    .user_id u.user_id 
    The MYQL query that works


    PHP Code:

         SELECT log
    .user_id,
                  
    COUNT(*)num_loggins,
                  
    FROM_UNIXTIMEMAX(log.logindate), '%D %b %Y' ) AS time_loggin,
                 
    FROM_UNIXTIMEMAX(log.logindate), '%r' ) AS seconds_loggin
                  FROM  logindate log
                where log
    .user_id 115 


    THE TABLE
    PHP Code:

    CREATE TABLE users
    (
      
    user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
      
    membership_type CHAR(3NOT NULL

    PRIMARY KEY (user_id)




    CREATE TABLE logindate(
    user_id MEDIUMINT UNSIGNED NOT NULL
    logindate VARCHAR(30NOT NULL,
    url VARCHAR(30NOT NULL,
    location VARCHAR(30NOT NULL,
    INDEX login (user_idlogindate


    ); 

    Thank you very much for your kind assistance

    warmest regards

    Andreea

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you forgot the GROUP BY clause in the subquery
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2012
    Location
    London and Bucarest
    Posts
    25
    Hello everyone

    once again, special thanks to R937 ( Good Call!!).

    i have corrected the mistake and it indeed works perfectly.

    thank again for all the help

    warm regards

    Andreea

  4. #4
    Join Date
    Mar 2004
    Posts
    14
    So many codes.

Posting Permissions

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