Data Retrieval in MySQL

Question

I want to retrieve some data from a table where the age is in a certain range.

EXAMPLE

Persons with age from 20 to 30 years. How can I do it? I tried with the following query, but I was unable to get it.

$age="SELECT * 
    From    rec_patient_reg
    WHERE   p_age  >='$afrom' <= '$to'";
    $result1=mysql_query($age) 
    or die(mysql_error());
    while($result = mysql_fetch_array($result1)) {
    echo "<b>Patient ID</b> = " .$result["Patient_id"] . " <br>";
    echo "<b>NAME</b> = ".$result["p_fname"]."<br><br>----------------<br><br>";

Please help me.

no error message but the retirived values are not as expected. — praveenrsmart, Mar 31 '13 at 07:51

score 1 · Accepted Answer · edited May 23 '17 at 12:21

1

I bet you are not getting any error message since you are using mysql. Take a look at this example below,

SELECT *
FROM   tableName
WHERE  Age  >= 20 <= 25

This is not allowed on any other RDBMS but MySQL allows it. What it does is it implictly allows boolean expression on the rightmost side of the filter, eg

SELECT *
FROM   tableName
WHERE  (Age  >= 20) <= 25

So in this case, the value is now parsed into (let's say Age = 5)

WHERE  0  >= 25

causing you to retrieve unexpected result.

You can use BETWEEN if you want to search for ranges,

SELECT * 
From    rec_patient_reg
WHERE   p_age  BETWEEN '$afrom' AND '$to'

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

edited May 23 '17 at 12:21

Community

1
1

answered Mar 31 '13 at 07:48

John Woo

258,903
69
498
492

Will you let us answer 1 SQL question? :p – Mr. Alien Mar 31 '13 at 07:50
@Mr.Alien you are still allowed to answer all of it :p – John Woo Mar 31 '13 at 08:00
i have lot of queries, i done with mysql_* functions? should i change everything for my site? no other way to give security? – praveenrsmart Mar 31 '13 at 08:08
@praveenrsmart it will soon be deprecated. If the site is already huge and curently up then leave it as it. or it's up to you `:)` – John Woo Mar 31 '13 at 08:10

score 0 · Answer 2 · answered Mar 31 '13 at 07:49

0

First thing, do not use mysql_* functions as they are deprecated.

Your query should be:

SELECT * 
FROM `rec_patient_reg`
WHERE `p_age` BETWEEN '$afrom' AND '$to'

answered Mar 31 '13 at 07:49

Praveen Kumar Purushothaman

164,888
24
203
252

i have lot of queries, i done with mysql_* functions? should i change everything for my site? no other way to give security? – praveenrsmart Mar 31 '13 at 08:09

score 0 · Answer 3 · edited Apr 30 '13 at 20:30

0

Maybe like this?

$age = "SELECT * FROM rec_patient_reg WHERE p_age  >='$afrom' AND p_age <= '$to'";

edited Apr 30 '13 at 20:30

Andrew Barber

39,603
20
94
123

answered Mar 31 '13 at 11:29

Kamil

39
2

Data Retrieval in MySQL

EXAMPLE

3 Answers3