Baffle With A Simple Query

[WDjoe]

Ok, I have pounded my head long enough (literally hours working on this) and I just can't figure this one out. I am hoping someone can solve this issue for me.

Hopefully I can explain myself well enough to be understood.

 

I have a database table ("items") with a column named "item_name", inside this column I have "Invitations" as a category name. 

 

I am doing a simple query like this;

--------------------------------------------------------------------------------------------------------------------------

$category = "Invitations"

 

Query ="SELECT * FROM items WHERE username='$user' AND item_name='$category' ";

--------------------------------------------------------------------------------------------------------------------------

The problem is it will not return the rows where item_name = "Invitations".

 

I checked and rechecked making sure all letters are correct and that there are no spacing , etc.

 

If I edit that column and replace the text ("Invitations") with numbers, i.e."1234" and change $category ="1234"  it returns the rows exactly as I would expect. I tried changing the column type to VARCHAR, CHAR, TEXT.

 

So what's going on?? Any ideas?

 

 

 

[HartleySan]

You try running the query directly on the DB and seeing what happens?

[WDjoe]

Tried it and I get the following. But I still don't see what is wrong with it:

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$item_name="Invitations" ' at line 1

[HartleySan]

Please show us the exact query you're running on the DB.

[WDjoe]

$item_name = 'Invitations';

$q = "SELECT   *, DATE_FORMAT(payment_date, '%M %D, %Y') AS formatted_date  FROM item_payments WHERE  username='$user'  AND item_name='$item_name',";

[HartleySan]

That's not quite what I was asking. I meant, what is the actual query, minus all the variables, etc. For example:

SELECT *, 
DATE_FORMAT(payment_date) AS formatted_date 
FROM item_payments 
WHERE username = 'Bob' 
AND item_name = 'Invitations';

Also, you have a comma at the end of your query, not a semicolon. Is that the problem?

[WDjoe]

Hi, yes I did notice the comma and removed the comma at the end of the query (no difference).

$q = "SELECT   *, DATE_FORMAT(payment_date, '%M %D, %Y') AS formatted_date  FROM item_payments WHERE username = 'benm' AND item_name='Invitations'  " ;

And this is what the database looks like:

 

CREATE TABLE IF NOT EXISTS `item_payments` (
  `payment_id` int(11) NOT NULL AUTO_INCREMENT,
  `item_name` varchar(30) DEFAULT NULL,
  `budget_id` int(50) DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `payment_date` date DEFAULT NULL,
  `username` varchar(50) DEFAULT NULL,
  `notes` varchar(200) NOT NULL,
  `invoice_number` varchar(50) NOT NULL,
  PRIMARY KEY (`payment_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=227 ;

--
-- Dumping data for table `item_payments`
--

INSERT INTO `item_payments` (`payment_id`, `item_name`, `budget_id`, `amount`, `payment_date`, `username`, `notes`, `invoice_number`) VALUES
(226, 'Invitations', 728, '32.00', '2015-01-01', 'benm', '', 'abc123'),
(225, 'Invitations', 728, '36.77', '2015-01-08', 'benm', ' test notes in invitations', 'test invoice number varchar ABCD12345');

[WDjoe]

It's working now.

I had to go line by line by line and follow every step of the code in extreme detail and analyze after comma and semi-colon and so forth.

 

I had an "else" statement in my code where if any records were found to display the table and if no records were found to display a message. Unfortunately for me the message was nothing ( I had not yet written the message I wanted to display) and I had the "if" statement reversed, so when I ran the query it found records but because the "if" statement was reversed nothing was shown on the webpage. 

 

No problem, only spend 3 days trying to get it to work, lots of coffee and aspirin and a huge learning experience for me! ...lol

 

Thank you for your help HartleySan for helping me out.

[HartleySan]

Believe me, I've been in the same situation myself many times. Unfortunately, debugging is one of those things that no one enjoys doing, and you really only get better at it by doing it a lot.

Anyway, I guess the lesson here is to always output some sort of temporary message for all parts of a conditional so that you can more easily catch these things.

Good find!