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

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

$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.


$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?

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

$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 :slight_smile:

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.