PHP/mySQL: mysql_query with SELECT, FROM, and WHERE

Question

I am trying to set $nextPageID and $nextPageTitle with the following code. Unfortunately they are not getting set.

$pageCategoryID = 1;
$nextPageOrder = 2;

$fetchedNextPageData = mysql_query("
  SELECT pageID, pageCategoryID, 'order', title 
  FROM pages 
  WHERE pageCategoryID='$pageCategoryID' AND 'order'='$nextPageOrder'
") or die(mysql_error());

            while ($nextPageArray = mysql_fetch_array($fetchedNextPageData)) {
                $nextPageID = $nextPageArray['pageID'];
                $nextPageTitle = $nextPageArray['title'];
            }

My table pages contains a row with pageID = 2, pageCategoryID = 1, order = 2, title = Next Page, plus 4 other columns with data I don't need for this query.

This code has been simplified for testing purposes. It will be sanitized after I get it working.

Any thoughts on what I can do to get this bit of code working?

Answer 1

you are overwriting the variable for each record, maybe create an array like

 $data = array();
 while ($nextPageArray = mysql_fetch_assoc($fetchedNextPageData)) {
   $data[] = array('id' => $nextPageArray['pageID'], 
            'title' => $nextPageArray['title']);
 }

Answer 2

Forget about PHP right now. This is your SQL query:

SELECT pageID, pageCategoryID, 'order', title 
FROM pages 
WHERE pageCategoryID='1' AND 'order'='2'

In SQL, as in many other languages, you use quotes to type literal strings. Since the 'order' string will never equal the '1' string, your query will always return zero rows, no matter the other values.

If order is a column name, you cannot single-quote it.

Now, given that ORDER is a reserved word, you'll have to use backticks around it. You can also type integers as integers (there's no need to quote them):

SELECT pageID, pageCategoryID, `order`, title 
FROM pages 
WHERE pageCategoryID=1 AND `order`=2