1

This is driving me insane. I have an Items table (PK item_id). Each item belongs to a category and so one of the fields of the table is category_id. Here is the simplified SQL I am using to debug this:

SELECT * FROM Items WHERE category_id = '?'

I am replacing ? with the category ID's manually for debugging purposes, for now. There are 5 items in the table with id's 1, 2, 3, 4, 5. Now here is where it gets weird.

  • Item 1's category is 17. In phpMyAdmin the above query using 17 as the parameter returns Item 1. PHP does not.

  • PHP returns all of the other items fine when using their categories: 15, 13, 10, and 14.

  • If I alter Item 1 in the database to have a category ID of either 15, 13, 10, or 14 it still does not show up in the results within PHP.

  • If I alter Item 1 in the database to have a different category ID, for example 4, it still does not show up in the results.

  • If I alter any other item to have a category ID of 17, that item will no longer show up (both that item and Item 1 do not, i.e., there are no results).

So what is going on here? Item 1, and Item 1 only, will never show up no matter what, and other categories will not show up if given a category of 17 but show up otherwise? And it works in phpMyAdmin, but not PHP?

Here is the PHP code to test this:

$query = mysql_query("SELECT * FROM `Items` WHERE `category_id` = '17'");

while ($r = mysql_fetch_assoc($query))
{
    echo $r['item_id'];
}

Here is SHOW CREATE TABLE Items:

 CREATE TABLE `Items` (
 `item_id` int(11) NOT NULL default '0',
 `description` varchar(250) default NULL,
 `name` varchar(25) default NULL,
 `category_id` int(11) default NULL,
 PRIMARY KEY  (`item_id`),
 UNIQUE KEY `item_id` (`item_id`),
 KEY `FK_Items_Categories` (`category_id`),
 CONSTRAINT `FK_Items_Categories` FOREIGN KEY (`category_id`) REFERENCES `Categories` (`category_id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1

And Categories:

 CREATE TABLE `Categories` (
 `category_id` int(11) NOT NULL auto_increment,
 `parent_id` int(11) default NULL,
 `name` varchar(25) default NULL,
 PRIMARY KEY  (`category_id`),
 KEY `FK_Categories_Categories` (`parent_id`),
 CONSTRAINT `FK_Categories_Categories` FOREIGN KEY (`parent_id`) REFERENCES `Categories` (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1

Note: I did not create the database, so let me know if there is a problem with this.

Improve this question
32
  • Did you try query without quotations around category_id value? `category_id` = 17 Commented Apr 1, 2012 at 20:33
  • Yes, I have tried that. Same result. Commented Apr 1, 2012 at 20:33
  • Can you post your database structure in more details please? Commented Apr 1, 2012 at 20:33
  • 1
    What happens with no WHERE clause? Please also give the result of SHOW CREATE TABLE Items. Commented Apr 1, 2012 at 20:34
  • With no WHERE clause, it echos every item "12345". Commented Apr 1, 2012 at 20:35

2 Answers 2

Reset to default
1

Smells like your script and phpmyadmin are not using the same database. Double check if you have production/development databases.

And double check you're running the script you think you're running, put die('im here'); before the query line.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

They are using the same database!
Do the die test before. End the script execution right after the while block with die also, to eliminate looking the wrong results to begin with. Use var_dump($r) rather than echo. It really seems like a silly problem like that.
It is more than that, sorry :/
1

Try

$query = mysql_query("SELECT * FROM Items i, Categories c WHERE i.category_id = '17' AND i.category_id = c.category_id");
Improve this answer

6 Comments

Good thinking, but this still does not work... PHP is still believing that Items.category_id is referencing the parent_id of the Categories table. Is it something to do with the KEY? When creating the table, FOREIGN KEY (category_id) REFERENCES Categories (category_id) was used...
Do a SHOW CREATE TABLE Categories
Done. When defining FOREIGN KEY in MySQL, does it reference the KEY of the referenced table? That seems to be what is going on...
Do a use INFORMATION_SCHEMA; select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME = 'Categories';
MyISAM does not support foreign key constraints, so I won't bet on this. Maybe phpMyAdmin tries something that it shouldnt.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.