PHP Mysql Search Page

Question

I have a simple search page setup that works great however when i add a column to the mysql database it returns no results. take out the column and it works again. I just cant figure out what it is i'm doing wrong.

here is my searchresults.php i can also take screenshots of my database layout if that will help. I"ve been struggling over this for a few weeks now and i need to wrap it up

if(!$conn)
{
    die("Connection error: " . mysqli_connect_error());    
}
$searchterm = $_POST['searchterm'];
$qry = "select * from addresslighting WHERE name LIKE '%".$searchterm."%' UNION 
    select * from addresslighting WHERE catagory LIKE '%".$searchterm."%' UNION
    select * from arealighting WHERE name LIKE '%".$searchterm."%' UNION 
    select * from arealighting WHERE catagory LIKE '%".$searchterm."%' UNION 
    select * from bollards WHERE name LIKE '%".$searchterm."%' UNION 
    select * from bollards WHERE catagory LIKE '%".$searchterm."%' UNION 
    select * from canopyandgarage WHERE name LIKE '%".$searchterm."%' UNION 
    select * from canopyandgarage WHERE catagory LIKE '%".$searchterm."%' UNION 
    select * from canopyandgaragelinears WHERE name LIKE '%".$searchterm."%' UNION 
    select * from canopyandgaragelinears WHERE catagory LIKE '%".$searchterm."%' UNION 
    select * from ceilingflush WHERE name LIKE '%".$searchterm."%' UNION 
    select * from ceilingflush WHERE catagory LIKE '%".$searchterm."%' UNION 
    select * from commercialceiling WHERE name LIKE '%".$searchterm."%' UNION 
    select * from commercialceiling WHERE catagory LIKE '%".$searchterm."%' UNION 
    select * from commercialwall WHERE name LIKE '%".$searchterm."%' UNION 
    select * from commercialwall WHERE catagory LIKE '%".$searchterm."%' UNION 
    select * from customcreations WHERE name LIKE '%".$searchterm."%' UNION 
    select * from customcreations WHERE catagory LIKE '%".$searchterm."%' UNION 
    select * from directindirectlinears WHERE name LIKE '%".$searchterm."%' UNION 
    select * from directindirectlinears WHERE catagory LIKE '%".$searchterm."%' UNION 
    select * from floodlighting WHERE name LIKE '%".$searchterm."%' UNION 
    select * from floodlighting WHERE catagory LIKE '%".$searchterm."%' UNION 
    select * from linears WHERE name LIKE '%".$searchterm."%' UNION 
    select * from linears WHERE catagory LIKE '%".$searchterm."%' UNION 
    select * from pendantsandchandeliers WHERE name LIKE '%".$searchterm."%' UNION 
    select * from pendantsandchandeliers WHERE catagory LIKE '%".$searchterm."%' UNION 
    select * from polelight WHERE name LIKE '%".$searchterm."%' UNION 
    select * from polelight WHERE catagory LIKE '%".$searchterm."%' UNION 
    select * from poles WHERE name LIKE '%".$searchterm."%' UNION 
    select * from poles WHERE catagory LIKE '%".$searchterm."%' UNION 
    select * from hilowbaylinears WHERE name LIKE '%".$searchterm."%' UNION 
    select * from hilowbaylinears WHERE catagory LIKE '%".$searchterm."%' UNION 
    select * from posttopfixtures WHERE name LIKE '%".$searchterm."%' UNION 
    select * from posttopfixtures WHERE catagory LIKE '%".$searchterm."%' UNION 
    select * from retrofits WHERE name LIKE '%".$searchterm."%' UNION 
    select * from retrofits WHERE catagory LIKE '%".$searchterm."%' UNION 
    select * from undercabinetlinears WHERE name LIKE '%".$searchterm."%' UNION 
    select * from undercabinetlinears WHERE catagory LIKE '%".$searchterm."%' UNION 
    select * from vandalresistantlinears WHERE name LIKE '%".$searchterm."%' UNION 
    select * from vandalresistantlinears WHERE catagory LIKE '%".$searchterm."%' UNION 
    select * from vanity WHERE name LIKE '%".$searchterm."%' UNION 
    select * from vanity WHERE catagory LIKE '%".$searchterm."%' UNION 
    select * from wallpacks WHERE name LIKE '%".$searchterm."%' UNION 
    select * from wallpacks WHERE catagory LIKE '%".$searchterm."%' UNION 
    select * from wallsconce WHERE name LIKE '%".$searchterm."%' UNION 
    select * from wallsconce WHERE catagory LIKE '%".$searchterm."%'";
$rs = mysqli_query($conn,$qry);
$index = 0;
$position = "first";

// This while loop gets all the results and places each one in the $getRowAssoc variable (makes parsing easier)
while ($getRowAssoc = mysqli_fetch_assoc($rs)) {

    if($index == 0) {
        echo '<div id="container">';
        $position = "";
    }
    elseif($index == 1) {
        $position = "";
    }
    elseif($index == 2) {
        $position = "";
    }
    elseif($index == 3) {
        $position = "";
    }


    echo '<li class="one_quarter'.$position.'">';
    echo '<article class="clear">';
    echo '<figure class="post-image">';
    echo '<a href="'.$getRowAssoc['pdf'].'">';    
    echo '<img src="'.$getRowAssoc['pic'].'" alt="" style="border:1px solid orange"" height="585" width="585"></img></a>';
    echo '<header>';
    echo '<h2 class="auto-style3"><a href="'.$getRowAssoc['pdf'].'">'.$getRowAssoc['name'].'</a></h2>';
    echo '<div class="blog-post-meta">';
    echo '<ul>';
    echo '<li class="blog-post-cats">Catagories:<a href="'.$getRowAssoc['catagorylink'].'">'.$getRowAssoc['catagory'].'</a>';
    echo '</li>';
    echo '</ul>';
    echo '</div>';
    echo '</header>';
    echo '<p>'.$getRowAssoc['description'].'</p>';
    echo '<p>*See Specification Sheet for Details.</p>';
    echo '<footer class="read-more"><a href="'.$getRowAssoc['pdf'].'">Download Specification Sheet &raquo;</a>';
    echo '</footer>';
    echo '</article>';
    echo '</li>';    

    $index++;
    if($index == 4) {
        echo '</div>';
        $index = 0;
    }
}
?>

Your code is vulnerable to [**SQL injection**](https://en.wikipedia.org/wiki/SQL_injection) attacks. You should use prepared statements with bound parameters, via either [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php). [**This post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has some good examples. — Alex Howansky, Jul 18 '18 at 16:07

score 0 · Accepted Answer · answered Jul 18 '18 at 17:11

0

A UNION will only work if your tables have the same number/name of columns. Unless you specify your SELECT to SELECT null values for all the tables that do not have the column. So, I assume all of these tables have the exact same layout? If so, any column you add to one, you will need to add to all.

In addition, there are a lot of unions here. Ever thought about using OR?

select * from canopyandgaragelinears WHERE name LIKE '%".$searchterm."%' OR catagory LIKE '%".$searchterm."%'

answered Jul 18 '18 at 17:11

Bleach

561
4
11

I hadent thought of that i'll run a test and see if that or statement will work. thank you for the advice i'll give it a try adding the same column in all my tables – Justin Everett Jul 18 '18 at 17:22
After trying out your suggestions that worked like a charm thank you so much for the help!!!!!! – Justin Everett Jul 18 '18 at 17:33
Happy it helped. – Bleach Jul 18 '18 at 18:07

PHP Mysql Search Page

1 Answers1