Selecting only those with active = 0

[vet911]

I am trying to control the items shown by selecting them by active = 0. I can get it to work. Any help would be appreciated.

<?php
		try
		{
		$sql = "SELECT company.co_name,
		company.active,
                co_info.customer_no,
                co_info.password,
		co_address,
                co_city,
                co_state,
                co_zip,
                host,
                ftp_name,
                ftp_password FROM company LEFT JOIN co_info ON company.id = co_info.id WHERE company.active = '0' ";
            
		$result = $dbh->query($sql);

[Barand]

What exactly is your problem if you can get it to work?

[vet911]

I must have missed the word can't is stead can. It doesn't work with the way it's shown above. Is there anything you see to be the problem. I want to eliminate the files that aren't active. Any help would be appreciated.

[ginerjm]

Add a test on the query result to be sure it is True and not False. Also - your query selects some fields without a table qualifier. Are those fieldnames unique? Also - why would you want to retrieve the password? If this is a general query and not a logon query, why do you want to expose the password?

[Barand]

 It doesn't work ...

 

Explain "doesn't work". What symptoms are you experiencing that leads you to that conclusion? Give us a clue.

[vet911]

I changed the code to add WHERE company.active ='0' and I still get a full listing of all the companys and the WHERE  company.active ='0' didn't make a difference. So I'm stuck! Any help would be appreciated.

[mac_gyver]

what is the data type of the active column? what are some of the values in it? and is there any chance that you imported that data into that column from a csv file or copy/pasted into a form field or you have an error in your code inserting the data and you could have some white-space characters (either a space or a tab) in the column in front of the data so that when the 'apparent/visible' value is converted to a number for the comparison, all the values end up being a zero, which results in a TRUE WHERE clause for every row?

[gizmola]

Please run this in command line mysql or phpMyAdmin and give use the result:

 

 

 

describe company;

 

 

describe co_info;

 

 

When you do this, what do you get?

SELECT * from company WHERE active = '0';

[gizmola]

The first thing to try, per mac's point is to specify this, assuming that active is an integer type:

 

 

WHERE active = 0

 

If you specify '0' you are asking mysql to cast the value from an integer to a string for string comparison, which could help explain your odd result.

 

With that said, I think we need some confidence that you aren't confused about the nature of your schema and data, which frequently is the reason people have the issues you seem to be having, due to assumptions they made about their own database which turn out to be erroneous.