MYSQL Searching multiple tables in a specific order

Question

I had previously asked a question MYSQL Searching multiple tables with different columns using LIKE

Which I was unable to get help on: so I'm compromising. Right now if a person makes a search and its found in multiple tables it does not search them in the order I want. I'd like for results to come first from the connectors table, then adapters table, then components table. If a result is found in connectors, dont search adapters or components, just continue.

I've also tried putting them in this order without using else if: components, adapters, connectors. That didn't work though.

if(isset($_GET['num'])) {
$num = $_GET['num'];
$numresult = mysql_query("SELECT * FROM productnumber WHERE part_num LIKE '%$num%'");

  if ($numresult) {

    while ($row = mysql_fetch_array($numresult)) {

        if ($row["title"] == "connectors") {
            $numtitle = "connectors";
            $result = mysql_query("SELECT * FROM connectors WHERE part_num LIKE '%$num%'");
        } else if ($row["title"] == "adapters") {
            $numtitle = "adapters";
            $result = mysql_query("SELECT * FROM adapters WHERE part_num LIKE '%$num%'");
        } else if ($row["title"] == "components") {
            $numtitle = "components";
            $result = mysql_query("SELECT * FROM components WHERE part_num LIKE '%$num%'");
        }   

    }
  }
}

you are open to SQL Injection. Do not use `mysql_*` methods. They cause a big security hole on your site. Use PDO instead.. — Miro Markaravanes, Sep 07 '12 at 06:45

Miro Markaravanes · Accepted Answer · 2012-09-07T07:33:52.987

This is the code using the mysql_* methods: This code will search in connectors if nothing is found then it will search in adapters and so on. but as you requested If it has found something in connectors it won't check in adapters or components!

<?php
if(isset($_GET['num'])) {
$num = $_GET['num'];
    // We will first search for connectors
    $result = mysql_query("SELECT * FROM connectors WHERE part_num LIKE %'$num'%");
    if(mysql_num_rows($result) == 0) {
    // Now if we don't find anything in connectors, we will search in adapters
    $result = mysql_query("SELECT * FROM adapters WHERE part_num LIKE %'$num%'%");
    if(mysql_num_rows($result) == 0 ){
    // And if we don't find anything in adapters, finally we will search in components
    $result = mysql_query("SELECT * FROM components WHERE part_num LIKE %'$num%'%"); 
    if(mysql_num_rows($result) == 0) {
        echo "We Found Nothing! :( "
    }
    }}
}
?>

And this is a sample code ( just your code ) converted to PDO. but not a right answer for your question.

 <?php
    try {
        $conn = new PDO('mysql:host=localhost;dbname=myDatabase', $username, $password);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $qry = $conn->prepare('SELECT * FROM productnumber WHERE part_num LIKE %:part_num%');
        $qry->execute(array(':part_num'=>$_GET['num']));
        $row = $qry->fetch(PDO::FETCH_OBJ)
            switch ($row->title) {
                case 'connectors':
                $numtitle="connectors";
                $qry2=$conn->prepare('SELECT * FROM connectors WHERE part_num LIKE %:num%');
                $qry2->execute(array(':num'=>$_GET['num'])));
                break;
                case 'adapters':
                $numtitle="adapters";
                $qry2=$conn->prepare('SELECT * FROM adapters WHERE part_num LIKE %:num%');
                $qry2->execute(array(':num'=>$_GET['num'])));
                break;
                case 'components':
                $numtitle="components";
                $qry2=$conn->prepare('SELECT * FROM components WHERE part_num LIKE %:num%');
                $qry2->execute(array(':num'=>$_GET['num'])));
                break;
            }
    } catch(PDOException $e) {
        echo 'ERROR: ' . $e->getMessage();
    }
    ?>

Thanks for giving me an example with PDO. Can you give me a quick example using my current code though? Even if it may be not as secure. I'd be glad to give you the accepted answer — Jweb, Sep 07 '12 at 06:57

MYSQL Searching multiple tables in a specific order

1 Answers1