CODEWITHSUNDEEP
phpmysql
Sunil Sorout
Sunil Sorout

Reputation: 11

Search from multiple tables

This is a code which is working fine without any error giving result.

SELECT student.id, student.name, fee_slip.payamount, fee_slip.time, student.class
FROM student
LEFT JOIN fee_slip
ON student.id=fee_slip.student
where (fee_slip.student) is null

But now I want to make it more dynamic.

I have a code which is also working good but I am using it on a single table.

$query = "SELECT  id, name,  std_reg_number, class, section  FROM student  where id IS NOT NULL ";

if ($name != "") {
    $query .= " AND `name` LIKE '" . $name . "%'"; // id is greater then
}

if ($status != "") {
    $query .= " AND `status` LIKE '" . $status . "%'"; // id is greater then
}

if ($class != "") {
    $query .= " AND class IN($class) ORDER BY class DESC"; // Selecting class
}

if ($section != "") {
    $query .= " AND section IN($section)"; // selecting section
}

if ($sort != "") {
    $query .= " ORDER BY $sort ASC"; // Selecting religion
}

$result = mysql_query($query);

Now I tried to use subquery

$query .= " SELECT  fee_slip.student_id,  fee_slip.std_reg_number, fee_slip.payamount, fee_slip.totalamount ";

But I didn't get results.

What should I do?

Upvotes: 1

Views: 315

Answers (1)

Atli
Atli

Reputation: 7930

There is a big difference between the terms "subquery" and "multi-query". You seem to be mixing those concepts up.

Assuming the query you posted works the way you need it to, it seems you could just replace the $query value in the code with that, and it would work just as well as the query you have there now. (You might have to add the table names/aliases to the fields in the WHERE clauses though, to avoid ambiguity. Depends on your table structure.)

In any case, adding another SELECT after the query you have now isn't the way to go.

I would also, like others have before me, point out that the old MySQL API functions are outdated, and that your code is riddled with security issues. - Prepared Statements through either PDO or MySQLi should be used these days.

Upvotes: 1

Related Questions