Member Avatar for nadnakinam

hi to all,

this might be very simple, but i don;t know how to get correct output, someone help me to complete it...

1st i need to get result from this query

$sql="SELECT * FROM $tbl1_name WHERE sex='$show_all'";
$result=mysql_query($sql);

this'll result either all the boys or all the girls;

from the above result again i need select according to their age using this 2nd query.

$sql="SELECT * FROM $tbl1_name WHERE age BETWEEN '$from' AND '$to'";
$result=mysql_query($sql);

Help me to do this in single subquery...

Thanx in advance...

Edited by Ezzaral because: Added [code] [/code] tags. Please use them to format any code that you post.
  • 4 Contributors
  • 9 Replies
  • 126 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by nadnakinam

Dani AI

Generated

A single query with both filters is the right idea (as showed) and is much simpler and faster than doing a subselect on the same table (as warned). A few practical points to make the solution robust and safe:

  • Treat age as numeric: validate and cast $from and $to, ensure $from <= $to, and handle empty values by omitting that filter. If show_all is used as a sentinel value, omit the sex filter when it indicates "all" (this keeps the query simple, matching 's advice).
  • Avoid string-interpolated SQL. Use prepared statements or parameterized queries to prevent injection and to let the driver bind numeric types correctly.
  • Do not use SELECT * in production; list only needed columns. Use EXPLAIN if the query seems slow and consider appropriate indexes.

Practical pattern (PDO example) for optional filters and safe binding:

$conds = [];
$params = [];

if ($show_all !== 'all') {
  $conds[] = 'sex = :sex';
  $params[':sex'] = $show_all;
}

if ($from !== '' && $to !== '') {
  $conds[] = 'age >= :from';
  $conds[] = 'age <= :to';
  $params[':from'] = (int)$from;
  $params[':to'] = (int)$to;
}

$sql = 'SELECT id, username FROM ' . $tbl1_name;
if ($conds) $sql .= ' WHERE ' . implode(' AND ', $conds);

$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$rows = $stmt->fetchAll();

Schema and performance tips: index the column(s) used in WHERE but note sex is low-cardinality so a single-column index on it may give little benefit; a composite index that matches the query pattern can help. For more accurate/maintainable data, store birthdate and compute age at query time (for example with date-diff functions) rather than storing a static age value.

Recommended Answers

All 9 Replies

Member Avatar for pritaeas 
$sql="SELECT * FROM $tbl1_name WHERE sex='$show_all' AND age BETWEEN '$from' AND '$to'";
Member Avatar for pritaeas

Duplicate again.

Edited by pritaeas because: n/a
Member Avatar for baki100 
SELECT  * 
FROM $tbl1_name 
WHERE age BETWEEN '$from' AND '$to'
AND sex='$show_all'
Edited by peter_budo because: Correcting code tags
Member Avatar for nadnakinam

U done it again Pritaeas...!
Thank You...

Member Avatar for nadnakinam

i done like this,

$sql="SELECT * FROM $tbl1_name WHERE username='$name' IN (SELECT * FROM $tbl1_name WHERE sex=$sex01)";
$result=mysql_query($sql);

I knew it is not better than yours, Pritaeas...
is it right way?

Member Avatar for pritaeas

That is not a valid query, but I am unsure of what you want to achieve.

Member Avatar for baki100

that's horribly inefficient using an inner select on the same table

Member Avatar for network18

why to complicate the simple query, he just have two simple reuirements on the same table as -
1)age BETWEEN something AND something
and 2)sex as all on same table

SELECT * FROM $tbl1_name WHERE sex='$show_all and age BETWEEN '$from' AND '$to'

It aslo depends upon what you pass as in $from and $to

Member Avatar for nadnakinam

You are right, its better to not to get complicated...

Thank You for All those who replied...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.