[RESOLVED] MySql Query Where field is greater than

[snyderd]

I am having a problem with what I would have thought would have been a simple query. I have database where all field data is numbers. I only want the results to show those records where the number in the field is greater than 4

What I have been trying is:
$query = "SELECT * FROM table WHERE field1 > '4' ";

I have tried without the quotes
eg; $query = "SELECT * FROM table WHERE field1 > 4 ";

I get No results using this.

NOTE: I also do not want to show the results of empty fields (fields with no data) for testing to show only fields with data I tried:
$query = "SELECT * FROM table WHERE field1 != '' "; While this does show the results it also shows the empty fields (fields with no data).

So I am assuming maybe I need to use AND in my query so something like:

In Words: Select from Table where Feild1 is not empty AND greater then 4

Code: $query = "SELECT * FROM table WHERE field1 != '' AND > 4 ";

If someone could give me an example of the code I would use to do this It would Be greatly appreciated. Thank You all In advance for any help you can offer.

[bradgrafelman]

What column type is field1? The empty string ('') isn't a valid value for numeric data types, thus either you've got a zero value or you've got a NULL value.

[tomhath]

The original query you tried should work without anything else. Try running the query without the WHERE clause to see if there are any rows > 4

[snyderd]

tomhath and bradgrafelman, Thank you for the replies.

The Column type is varchar

tomhath - It does work when I run it with out the Where Clause. (see below)

Here is the full query code I am using:

<?php
require('config.php');
$query = "SELECT * FROM surnames WHERE code > 4 ORDER BY surname";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
$surname = $row[surname];
$code = $row[$code];

$i++;
echo "

As I mentioned previously it does work if I query all of it.. Example:
$query = "SELECT * FROM surnames ORDER BY surname";

Also Works If I do it as below, but it still shows the blank or empty fields:
$query = "SELECT * FROM surnames WHERE code != '' ORDER BY surname";

I Appreciate your help.

[Derokorian]

Are you sure they are empty strings (the blank fields) and not NULL. If they are NULL values you would have to do WHERE code IS NOT NULL.

[snyderd]

Hi Derokorian Yes they are empty. I have checked the table. I was questioning if there is something wrong with the table because I imported from excel spreadsheet (excel to csv, etc) But as I said it does work with no Where clause. It does seem like the empty fields are not being recognized as empty.

Thank you for your reply and help

[bradgrafelman]

Why are you trying to do numerical comparisons against non-numerical data types (e.g. VARCHAR)?

EDIT: Perhaps a better question might be: why are you storing numerical data using a non-numerical data type?

[tomhath]

It does seem like the empty fields are not being recognized as empty.

Then the fields are not empty.

It's a common error to think the code isn't doing what you expect when it's actually the data that isn't what you think.

[snyderd]

It seems there was something wrong with the table. I have recreated the table with the code column as INT. I have re-imported the data and I can now query without the empty fields, however the query for > 4 is still not working. I am using same as before
$query = "SELECT * FROM surnames WHERE code > 4 ORDER BY surname";

I get no results.

Anyone have any suggestions?

[laserlight]

snyderd wrote:

The Column type is varchar

This means that '4' > '1234'. You might need to do something like:

$query = "SELECT * FROM surnames WHERE CAST(code as INT) > 4 ORDER BY surname";

[snyderd]

RESOLVED! Thank you to everyone who posted help for me on this issue.. I have finally figured it out. There was something wrong with the way the data imported to the table "surnames" (specifically the code field) As I mentioned previously I uploaded the data from an excel spreadsheet (converted to csv, etc). Although I am not sure what the deal was.. I exported the data to a sql file downloaded to my pc, etc. I then deleted all records from the table and re-imported them using the sql file I just saved. I then ran repair table in phpmyadmin, and now I am able to use my original query:
$query = "SELECT * FROM surnames WHERE code > '4' ORDER BY surname"; and it all works

NOTE: not sure if running the repair table was necessary after I re-imported the Data as I did repair the table before exporting and it did not help, but the above is the steps I took.

My Lesson here is when something does not work that you know should work save "pulling your hair out" and changing you code 100 times and LOOK ELSEWHERE 🙂

Again thank you to all that posted.