[Resolved] Seeking help with query using BETWEEN statement

kilbey1

I am having problems using the sql BETWEEN statement to pull out information from a database. I keep getting no results.

I have tried datatypes decimal, varchar, and integer. I have tried mysql_fetch_row and mysql_fetch_array, all to no avail.

Any thoughts? Below is some of the various code I've tried.

				$sql = "
				SELECT id, start, end, whamount, percentage, cap 
				FROM federal-married 
				WHERE {$_POST['salary']} BETWEEN start AND end
				";
				echo $sql . "<p>";
				$result = mysql_query($sql);
				//$row=mysql_fetch_row($result);
				echo "<pre>";
				print_r($result);
				echo "</pre>";
				//echo $row['whamount'];
				//while (list($start, $end) = mysql_fetch_row($result)) {
				//   echo "$start $end<br>\n";
				//}

weekender

when you do a between, the syntax is

WHERE <fieldname> BETWEEN <x> AND <y>

ie that first var cannot be from php - it has to be a value already in mysql (or one from a mysql function).

if you think about it logically

WHERE {$_POST['salary']} BETWEEN start AND end

is equivalent to

WHERE start <= {$POST['salary']} AND end >= {$POST['salary']}

so put that in your sql and let me know if it works

adam

kilbey1

Well, that makes sense. I did try putting static data and running a SQL statement through phpmyadmin...but of course that wasn't a form variable. Input like so:

SELECT * FROM federal-married WHERE 5412 BETWEEN start AND end

...which gave me the result I needed.

phpmyadmin converted to php code like so:
$sql = 'SELECT * '
. ' FROM federal-married '
. ' WHERE 5412 '
. ' BETWEEN START AND '
. ' END LIMIT 0, 30';

The latest result is, however, still saying that mysql_fetch_row(): supplied argument is not a valid MySQL result resource.

Outputting SQL statement gives me:
SELECT * FROM 'federal-married' WHERE start <= 5412 AND end >= 5412

Code:

				$sql = "
				SELECT *
				FROM 'federal-married'
				WHERE start <= {$_POST['salary']} AND end >= {$_POST['salary']}
			";
				echo $sql;
				$result = mysql_query($sql);
				$row=mysql_fetch_row($result);

It still appears to be returning empty data this way.

kilbey1

This worked. It appears I needed to have the AND statement switched around; the other way, it was returning an empty result.

				$sql = 'SELECT * '
				. ' FROM `federal-married` '
				. ' WHERE START <= '
				. $_POST['salary']
				. ' AND END >= '
				. $_POST['salary']
				. ''; 
				echo $sql;
				$result = mysql_query($sql);
				$row=mysql_fetch_assoc($result);
				echo "<P>" . $row['whamount'];