mods mods - 4 months ago 26
SQL Question

PHP MYSQL HTML Search with multiple filters/ fields

I have a MySQL database with 5 tables

- companies
- place
- category
- sub-category
- company-sub-category





'companies' consist of 6 column:

- company_id (**PK**)

- company name

- place_id (**FK**)

- address

- cat_id (**FK**)

- company_subcat_id (**FK**)





'place' consist of 2 column:

- place_id (**PK**)

- place_name (**FK**)





'category' consist of 2 column:

- cat_id (**PK**)

- cat_name (**FK**)





'sub-category' consist of 2 column:

- sub-cat_id(**PK**)

- sub-cat_name (**FK**)





'company-sub-category' consist of 3 column:

- company_subcat_id (**PK**)

- company_id (**FK**)

- sub-cat_id (**FK**)





I would like to make a :-


  1. Search with using PHP HTML using the database in the MySQL.

  2. The search form also has a filtering system like the ones showed in the html below.

  3. i would like the results to include the company's name

  4. I would also like to make the results appear in another page "search-results.html"



I am fairly new to php and am not sure how to go about making the search form work.



<!-- Search -->

<nav id="c-menu--slide-top" class="c-menu c-menu--slide-top">
<button class="c-menu__close" style="float: right;background: transparent;font-size: 45px;">
<strong>x</strong></button>
<ul class="c-menu__items">
<li class="c-menu__item">
<form action="" target="search-results.html" method="post" id="nl-form" class="nl-form">
I am looking for
<select name="category" id="category">
<option value="0" selected>category choice</option>
<option value="1">Women</option>
<option value="2">Men</option>
<option value="3">Kids</option>
</select>'s
<br />
<select name="sub-category" id="sub-category">
<option value="" selected>-sub-category</option>
<option value="1">Jackets</option>
<option value="2">Tops</option>
<option value="3">Bottoms</option>
<option value="4">Accesories</option>
<option value="5">Undergarments</option>
<option value="6">Shoes</option>
</select>
<br />around
<select name="price_range" id="price_range">
<option value="0" selected>price range</option>
<option value="$">$</option>
<option value="$$">$ $</option>
<option value="$$$">$ $ $</option>
<option value="$$$$">$ $ $ $</option>
</select>
eatery
<br /> and would prefer
<select name="pickup_choice" id="pickup_choice">
<option value="0" selected>prefered chioce</option>
<option value="1">Home Delivery</option>
<option value="2">Self pick-up</option>
</select>
<br />at
<input type="text" value="" name="place" placeholder="location" data-subline=" eg.<em>little India</em> or <em>Chinatown</em>etc"/>
<div class="nl-submit-wrap">
<button class="nl-submit" type="submit" href="search-result.html" name="submit"><a href="search-result.html"> Find a store</a></button>



</div>
<div class="nl-overlay"></div>
</form>
<script src="js/nlform.js"></script>
</li>
</ul>
</nav>

<!--End of Search-->





This is the html file I've used, I didnt include the CSS and JS file as I figured would not be necessary, however if anyone would like me to show it, tell me and I'll be more than glad to do it.

Your help is greatly appreciated and i would like to thank you in advance.




this is the PHP that i came

<?php


$whereClauses = array();
if (! empty($_POST['category'])) $whereClauses[] ="category='".//**mysql_real_escape_string($_POST['category'])."'";

if (! empty($_POST['sub_category']))
$whereClauses[] ="category='".mysql_real_escape_string($_POST['sub_category'])."'";
$where = '';

if (! empty($_POST['price_range']))
$whereClauses[] ="price_range='".mysql_real_escape_string($_POST['price_range'])."'";
$where = '';

if (! empty($_POST['pickup_choice']))
$whereClauses[] ="pickup_choice='".mysql_real_escape_string($_POST['pickup_choice'])."'";
$where = '';

if (count($whereClauses) > 0)
{
$where = ' WHERE '.implode(' AND ',$whereClauses);
}

$sql = mysql_query("SELECT * FROM mytable ORDER BY id DESC $limit" .$where);//**
$result=mysql_query($sql);
while ($row = mysql_fetch_assoc($result)) {
echo $row['cuisine'];
echo $row['Jobs'];

}
?>

<!-- end snippet -->





and here is the 'search-result.html' page :_



</head>


<body>
<div class="container">
<div class="well well-sm list-grid-toggle">
<strong>Category Title</strong>
<div class="btn-group">
<a href="#" id="list" class="btn btn-default btn-sm"><span class="glyphicon glyphicon-th-list">
</span>List</a> <a href="#" id="grid" class="btn btn-default btn-sm"><span
class="glyphicon glyphicon-th"></span>Grid</a>
</div>
</div>
<div id="products" class="row list-group">
<div class="item col-xs-6 col-lg-4">
<div class="thumbnail comany-detail">
<img class="group list-group-image" src="http://placehold.it/400x250/000/fff" alt="" />
<div class="caption">
<h4 class="group inner list-group-item-heading">
Company Name</h4>
<p class="group inner list-group-item-text">
Product description... Lorem ipsum dolor sit amet, consectetuer adipiscing elit,
sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat.</p>
<div class="row">
<div class="col-xs-12 col-md-6">
<p class="lead">
$$$$</p>
</div>
<div class="col-xs-12 col-md-6 company-link-btn">
<a class="btn btn-success company-link" href="company-profile.html">View Menu</a>
</div>
</div>
</div>
</div>
</div>
<div class="item col-xs-6 col-lg-4">
<div class="thumbnail company-detail">
<img class="group list-group-image" src="http://placehold.it/400x250/000/fff" alt="" />
<div class="caption">
<h4 class="group inner list-group-item-heading">
Company Name</h4>
<p class="group inner list-group-item-text">
Product description... Lorem ipsum dolor sit amet, consectetuer adipiscing elit,
sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat.</p>
<div class="row">
<div class="col-xs-12 col-md-6">
<p class="lead">
$$$$</p>
</div>
<div class="col-xs-12 col-md-6 company-link-btn">
<a class="btn btn-success company-link" href="company-profile.html">View Menu</a>
</div>
</div>
</div>
</div>
</div>
<div class="item col-xs-6 col-lg-4">
<div class="thumbnail company-detail">
<img class="group list-group-image" src="http://placehold.it/400x250/000/fff" alt="" />
<div class="caption">
<h4 class="group inner list-group-item-heading">
Company Name</h4>
<p class="group inner list-group-item-text">
Product description... Lorem ipsum dolor sit amet, consectetuer adipiscing elit,
sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat.</p>
<div class="row">
<div class="col-xs-12 col-md-6">
<p class="lead">
$$$$</p>
</div>
<div class="col-xs-12 col-md-6 company-link-btn">
<a class="btn btn-success company-link" href="company-profile.html">View Menu</a>
</div>
</div>
</div>
</div>
</div>
<div class="item col-xs-6 col-lg-4">
<div class="thumbnail restaurant-detail">
<img class="group list-group-image" src="http://placehold.it/400x250/000/fff" alt="" />
<div class="caption">
<h4 class="group inner list-group-item-heading">
Restaurant Name</h4>
<p class="group inner list-group-item-text">
Product description... Lorem ipsum dolor sit amet, consectetuer adipiscing elit,
sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat.</p>
<div class="row">
<div class="col-xs-12 col-md-6">
<p class="lead">
$$$$</p>
</div>
<div class="col-xs-12 col-md-6 restaurant-link-btn">
<a class="btn btn-success restaurant-link" href="restaurant-profile.html">View Menu</a>
</div>
</div>
</div>
</div>
</div>
<div class="item col-xs-6 col-lg-4">
<div class="thumbnail restaurant-detail">
<img class="group list-group-image" src="http://placehold.it/400x250/000/fff" alt="" />
<div class="caption">
<h4 class="group inner list-group-item-heading">
Company Name</h4>
<p class="group inner list-group-item-text">
Product description... Lorem ipsum dolor sit amet, consectetuer adipiscing elit,
sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat.</p>
<div class="row">
<div class="col-xs-12 col-md-6">
<p class="lead">
$$$$</p>
</div>
<div class="col-xs-12 col-md-6 company-link-btn">
<a class="btn btn-success company-link" href="restaurant-profile.html">View product</a>
</div>
</div>
</div>
</div>
</div>
<div class="item col-xs-6 col-lg-4">
<div class="thumbnail company-detail">
<img class="group list-group-image" src="http://placehold.it/400x250/000/fff" alt="" />
<div class="caption">
<h4 class="group inner list-group-item-heading">
Company Name</h4>
<p class="group inner list-group-item-text">
Product description... Lorem ipsum dolor sit amet, consectetuer adipiscing elit,
sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat.</p>
<div class="row">
<div class="col-xs-12 col-md-6">
<p class="lead">
$$$$</p>
</div>
<div class="col-xs-12 col-md-6 company-link-btn">
<a class="btn btn-success company-link" href="company-profile.html">View product</a>
</div>
</div>
</div>
</div>
</div>
</div>

<script src='http://cdnjs.cloudflare.com/ajax/libs/jquery/2.1.3/jquery.min.js'></script>

<script src="js/search-result.js"></script>


</body>
</html>





Answer

change <form action="" target="search-results.html"

to change<form action="search-results.html"

Action is the page to submit to, target defines if it should be in a new page or same page window.

And about the submit button, no need to link in the button or in the text. The submit button is linked to the form and will submit to whatever you set in the action attribute in the form tag.

Change <button class="nl-submit" type="submit" href="search-result.html" name="submit"><a href="search-result.html"> Find a store</a></button>

to: <button class="nl-submit" type="submit" name="submit">Find a store</button>

Does that help?

Also, submitting the form data to a HTML page will not work. It will just load the HTML page. You will need a PHP page to extract the form data, search the database and then display it on the page. Try learning here: W3schools PHP form handling tutorial

EDIT:

Stack Overflow is here to help you with programming problems. Not programming for you. You should really do some tutorials on PHP. Take a look at W3Schools. You might want to read up on SQL also. (W3Schools). And a combination of PHP and SQL: W3Schools PHP SQL.

Your php code is full of errors and should not be used. Things to note when you try to write a new one: Use mysqli instead of the old deprecated mysql. Where is your database connection? You tried to do mysql_query TWICE. This will result in an error. If you run into any problems, feel free to ask. But please, try to learn some PHP/SQL by yourself. There are many good and simple tutorials on this, as i linked and as you can google.

Good luck.

Comments