SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Mar 2014
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Need help with this simple SQL problem

    Im not great and SQL but i am learning, anyway i have a small issue i can't seem to fix.

    i need to add so it only selects from the table 'users' with the status 'active'

    here is the original code i had

    PHP Code:
    if(isset($_GET['page']))
        {
            
    $pageNum $_GET['page'];
        }

        
    $offset = ($pageNum 1) * $rowsPerPage;

        
    $query "SELECT `twitter_info`.*, users.usertype

                    FROM twitter_info

                    INNER JOIN users ON users.id = twitter_info.user_id

                    WHERE users.usertype = 'celebrity'

                  ORDER BY id LIMIT 
    $offset$rowsPerPage";

        
    $result mysql_query($query)  or die(mysql_error());



        
    $query "SELECT COUNT(twitter_info.id) AS numrows FROM twitter_info INNER JOIN users ON users.id = twitter_info.user_id WHERE users.usertype = 'celebrity'";

        
    $result2 mysql_query($query)  or die(mysql_error());

        
    $row mysql_fetch_assoc($result2);

        
    $numrows $row['numrows']; 
    and here is what i have tried to do

    PHP Code:
    $query "SELECT `twitter_info`.*, users.usertype, users.status

                    FROM twitter_info, users

                    INNER JOIN users ON users.id = twitter_info.user_id

                    WHERE users.usertype = 'celebrity'
                    
                    AND users.status = 'active'

                  ORDER BY id LIMIT 
    $offset$rowsPerPage";

        
    $result mysql_query($query)  or die(mysql_error());



        
    $query "SELECT COUNT(twitter_info.id) AS numrows FROM twitter_info INNER JOIN users ON users.id = twitter_info.user_id WHERE users.usertype = 'celebrity' AND users.status = 'active'";

        
    $result2 mysql_query($query)  or die(mysql_error());

        
    $row mysql_fetch_assoc($result2);

        
    $numrows $row['numrows']; 


    This doesn't work and i can't figure out why could somebody please discuss with me where i have gone wrong.

    Sorry if there isn't enough information the main problem i am having is purely adding the "AND status 'active' FROM table users" to the original coding on both sql queries.

  2. #2
    SitePoint Addict bronze trophy
    Join Date
    Sep 2005
    Posts
    318
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $query "SELECT `twitter_info`.*, users.usertype, users.status

                    FROM twitter_info

                    INNER JOIN users ON users.id = twitter_info.user_id

                    WHERE users.usertype = 'celebrity'
                    
                    AND users.status = 'active'

                  ORDER BY id LIMIT 
    $offset$rowsPerPage";

        
    $result mysql_query($query)  or die(mysql_error()); 
    Try it without the FROM twitter_info, users and just with the twitter_info. The query looks OK to me; have you checked your database for data with usertype celebrity and status active ? Does the query give any errors?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by roboconnor View Post
    This doesn't work
    you could start by running the query outside of php, i.e. directly in mysql

    that will tell you the actual error message which caused the "doesn't work" result

    my guess: ambiguous id in the ORDER BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Mar 2014
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you could start by running the query outside of php, i.e. directly in mysql

    that will tell you the actual error message which caused the "doesn't work" result

    my guess: ambiguous id in the ORDER BY clause
    Thanks r937!

    i never thought of this before, i tried it and debug it from there.

    the problem was the first query


    PHP Code:
    $query "SELECT `twitter_info`.*, users.usertype
        
                   FROM twitter_info
                   
                   INNER JOIN users ON users.id = twitter_info.user_id 
                   
                   WHERE users.usertype = 'celebrity' 
                   
                   AND users.status = 'active'

                  ORDER BY id LIMIT 
    $offset$rowsPerPage"
    All i had to do was add "AND users.status = 'active'" to the original code, which i thought i did in the first place but it didn't work so i added more to the query to figure it out (users.status and FROM users), which then just made me dig myself a deeper hole, i guess the first time i added it i must have had a type.

    Anyway thanks again for that useful tip, i will remember that one next time i have a silly sql problem

  5. #5
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,034
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Please be aware that the mysql_* extension is now deprecated as of the current version of PHP and will very likely be removed from the next 5.x version and will likely not be in PHP 6.x (when it eventually is released). You should migrate over to either the mysqli_* extension or to PDO. PDO is a better choice as it doesn't tie you down so much to a particular database server software.

    Once you have migrated you should use Prepared Statements to prevent SQL Injection attacks. Have a read of this article from the PHP manual, it shows how to use prepared statements with PDO and also explains the principle.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator


Tags for this Thread

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
  •