Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

up vote 2 down vote accepted

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
share|improve this answer
 
Thanks! This did the trick. Excellent job explaining it! –  Mark Rummel Nov 25 '11 at 18:17
add comment

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']);
 }
share|improve this answer
 
Thank you for your solution. This did not fix the primary issue of needing to use backticks and removing some of the single quotes. Personally I find the way you wrote this to require a lot more code and not be as clear. However, I'm still new to PHP and mySQL and maybe in time I'll see it your way. –  Mark Rummel Nov 25 '11 at 18:22
add comment

In the WHERE clause of your query try removing the quotes around order and use back ticks instead like this:

`order`='$nextPageOrder'

And try to avoid using keywords for table/column names! Been there.

share|improve this answer
1  
I have to have the quotes around order or else I get an error. I think because it reads it as order as in ORDER BY. –  Mark Rummel Nov 25 '11 at 18:01
1  
@MarkRummel you then need backticks, not quotes –  Damien Pirsy Nov 25 '11 at 18:06
 
added the back ticks. –  Aditya Naidu Nov 25 '11 at 18:09
 
Thanks for your answer. The backticks are correct, but I had to remove the single quotes to get it to work. –  Mark Rummel Nov 25 '11 at 18:19
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.