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?

Álvaro G. Vicario · Accepted Answer · 2011-11-25 18:14:16Z

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

Thanks! This did the trick. Excellent job explaining it! – Mark Rummel Nov 25 '11 at 18:17

Gabriel · Answer 2 · 2011-11-25 17:55:43Z

up vote 5 down vote

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']);
 }

answered Nov 25 '11 at 17:55

Gabriel
42228

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

Aditya Naidu · Answer 3 · 2011-11-25 18:06:13Z

up vote 1 down vote

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.

edited Nov 25 '11 at 18:06

answered Nov 25 '11 at 17:58

Aditya Naidu
1,197511

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

asked	2 years ago
viewed	2582 times
active	2 years ago

Explore our sites

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

3 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged php mysql select where fetch or ask your own question.

Hot Network Questions

Explore our sites

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

3 Answers

Your Answer

Sign up or login

Post as a guest

Not the answer you're looking for? Browse other questions tagged php mysql select where fetch or ask your own question.

Related

Hot Network Questions