how to SEARCH a record from multiple tables using mysql

[xiao_xiao]

---

by the way, the situation here is this: first, the name is being searched in table 1 and if not found, then it will search the name to other table which is table 2. I hope this will clarify things.

since pag-reformat sa ako pc, wla ko ka-install ug apache/php/mysql so dili ko sure kung wla ni errors... but gina-review nako ug ayu and I think wla ni error...

here's the code... wla lng nako gigamitan atong anti-sql injection... gitapulan ko..

<?php
$keyword = $_POST['searchword'];

$query1 = "SELECT * FROM table1 WHERE name LIKE '%".$keyword."%'";
$query2 = "SELECT * FROM table2 WHERE name LIKE '%".$keyword."%'";

$result1 = mysql_query($query1) or die(mysql_error());
$result2 = mysql_query($query2) or die(mysql_error());

$num_rows1 = mysql_num_rows($result1);
$num_rows2 = mysql_num_rows($result2);

if ($num_rows1 == 0)
{
    $finalResult = $result2;
} //end if

else //if num_rows1 != 0
{
    $finalResult = $result1;    
} //end else

while ($row = mysql_fetch_assoc($finalResult))
{
    $name = $row['name'];
    echo $name.'\n';
} //end while
?>

[xiao_xiao]

or...

<?php
$keyword = $_POST['searchword'];

$query1 = "SELECT * FROM table1 WHERE name LIKE '%".$keyword."%'";
$query2 = "SELECT * FROM table2 WHERE name LIKE '%".$keyword."%'";

$result1 = mysql_query($query1) or die(mysql_error());
$result2 = mysql_query($query2) or die(mysql_error());

$num_rows1 = mysql_num_rows($result1);
$num_rows2 = mysql_num_rows($result2);

if ($num_rows1 == 0)
{    
    if ($num_rows2 == 0)
    {
        $displayResult = "no";
    } //end if
    
    else
    {
        $finalResult = $result2;
        $displayResult = "yes";
    } //end else
    
} //end if

else //if num_rows1 != 0
{
    $finalResult = $result1;
    $displayResult = "yes";
} //end else

if ($displayResult == "yes")
{
    while ($row = mysql_fetch_assoc($finalResult))
    {
        $name = $row['name'];
        echo $name.'\n';
    } //end while
} //end if

else
{
    echo "0 results found.";
} //end else
?>

[sogate]

since pag-reformat sa ako pc, wla ko ka-install ug apache/php/mysql so dili ko sure kung wla ni errors... but gina-review nako ug ayu and I think wla ni error...

here's the code... wla lng nako gigamitan atong anti-sql injection... gitapulan ko..

<?php
$keyword = $_POST['searchword'];

$query1 = "SELECT * FROM table1 WHERE name LIKE '%".$keyword."%'";
$query2 = "SELECT * FROM table2 WHERE name LIKE '%".$keyword."%'";

$result1 = mysql_query($query1) or die(mysql_error());
$result2 = mysql_query($query2) or die(mysql_error());

$num_rows1 = mysql_num_rows($result1);
$num_rows2 = mysql_num_rows($result2);

if ($num_rows1 == 0)
{
    $finalResult = $result2;
} //end if

else //if num_rows1 != 0
{
    $finalResult = $result1;    
} //end else

while ($row = mysql_fetch_assoc($finalResult))
{
    $name = $row['name'];
    echo $name.'\n';
} //end while
?>

I already tried this bro and it worked out well (this is what i'm looking for). Thank you very much for sharing your expertise, not only to me but to all the people who will read this and benefit from this.

CASE SOLVED!!! LOL!!!

[xiao_xiao]

no probz. your welcome.

[personalmgt]

I think the problem came from SELECT * on two different tables with WHERE clauses. If I recall correctly, this was one of my banes and the only way that I solved it was to select definite tables instead of selecting all (SELECT *).

It would have been nice if you can do $query1 and $query2 on a single query. But why torture yourself, complex problem with a simple should do it.

[sogate]

since pag-reformat sa ako pc, wla ko ka-install ug apache/php/mysql so dili ko sure kung wla ni errors... but gina-review nako ug ayu and I think wla ni error...

here's the code... wla lng nako gigamitan atong anti-sql injection... gitapulan ko..

<?php
$keyword = $_POST['searchword'];

$query1 = "SELECT * FROM table1 WHERE name LIKE '%".$keyword."%'";
$query2 = "SELECT * FROM table2 WHERE name LIKE '%".$keyword."%'";

$result1 = mysql_query($query1) or die(mysql_error());
$result2 = mysql_query($query2) or die(mysql_error());

$num_rows1 = mysql_num_rows($result1);
$num_rows2 = mysql_num_rows($result2);

if ($num_rows1 == 0)
{
    $finalResult = $result2;
} //end if

else //if num_rows1 != 0
{
    $finalResult = $result1;    
} //end else

while ($row = mysql_fetch_assoc($finalResult))
{
    $name = $row['name'];
    echo $name.'\n';
} //end while
?>

and i assume that this is not true if there are more than 2 tables, right

[xiao_xiao]

pwede ra mn yata single query ra jud... using distincts and joins...

[xiao_xiao]

pwede ra mn jud tingale single query ra using distincts and joins...
murag ang sayop ni emailroy2002 kay ani...
dapat from table1 t1 left join table2 t2
iya mn gud kay kuan ra from t1 left join t2

[xiao_xiao]

ani mn tingale

<?php
$keyword = $_POST['searchword'];

$query1 = "SELECT * FROM table1 WHERE name LIKE '%".$keyword."%'";
$query2 = "SELECT * FROM table2 WHERE name LIKE '%".$keyword."%'";
$query3 = "SELECT * FROM table3 WHERE name LIKE '%".$keyword."%'";

$result1 = mysql_query($query1) or die(mysql_error());
$result2 = mysql_query($query2) or die(mysql_error());
$result3 = mysql_query($query2) or die(mysql_error());

$num_rows1 = mysql_num_rows($result1);
$num_rows2 = mysql_num_rows($result2);

if ($num_rows1 == 0)
{
    $finalResult = $result2;
} //end if

elseif ($num_rows2 == 0)
{
    $finalResult = $result3;    
} //end elseif

else
{
    $finalResult = $result1;
}

while ($row = mysql_fetch_assoc($finalResult))
{
    $name = $row['name'];
    echo $name.'\n';
} //end while
?>

[xiao_xiao]

ani mn tingale

<?php
$keyword = $_POST['searchword'];

$query1 = "SELECT * FROM table1 WHERE name LIKE '%".$keyword."%'";
$query2 = "SELECT * FROM table2 WHERE name LIKE '%".$keyword."%'";
$query3 = "SELECT * FROM table3 WHERE name LIKE '%".$keyword."%'";

$result1 = mysql_query($query1) or die(mysql_error());
$result2 = mysql_query($query2) or die(mysql_error());
$result3 = mysql_query($query2) or die(mysql_error());

$num_rows1 = mysql_num_rows($result1);
$num_rows2 = mysql_num_rows($result2);

if ($num_rows1 == 0)
{
    $finalResult = $result2;
} //end if

elseif ($num_rows2 == 0)
{
    $finalResult = $result3;    
} //end elseif

else
{
    $finalResult = $result1;
}

while ($row = mysql_fetch_assoc($finalResult))
{
    $name = $row['name'];
    echo $name.'\n';
} //end while
?>