I want to filter records being displayed from a MySQL table if the value of a field = one of multiple predeclared values. I am already running a query on my table which displays records based on which option from a dropdown menu the user selects. However before the user selects an option from the dropdown box when the page has just loaded I want the table to display only some records.

<?php
require_once("includes/session.php");
include_once("includes/masterinclude.php");

$preferences = getPreferences();
$category = "";
$attribute1 = ""; $attribute2 = ""; $attriute3 = ""; $attribute4 = "";
$top_level="0";

$name = $_GET['member'];
$name = $_GET['countries'];

$information = getInformationPage($name);
$infopagename=$information->IN_NAME;
//meta data for information pages now taken from the information table
$pageTitle = $information->IN_NAME . html_entity_decode($information->IN_TITLE);
$pageMetaDescription = html_entity_decode($information->IN_META_DESC);
$pageMetaKeywords = html_entity_decode($information->IN_META_KEYWORDS);
$pageCustomHead = html_entity_decode($information->IN_CUSTOM_HEAD, ENT_QUOTES);

//initialise screen fields
$selected_member = "";
$id = "";
$username = ""; $username_original = "";
$password = ""; $password_original = "";
$password_test = "";
$title = "MR"; $first_name = ""; $last_name = ""; $company_name = "";
$address1 = ""; $address2 = ""; $town = ""; $county = ""; $country = ""; $postcode = ""; $phone = ""; $mobile = ""; $email = "";
$member_confirmed = "N";
$ast_first = 0; $ast_last = 0; $ast_company = 0; $ast_add1 = 0; $ast_add2 = 0; $ast_town = 0; $ast_county = 0; $ast_country = 0; $ast_post = 0; $ast_phone = 0;
$ast_mobile = 0; $ast_email = 0;
$ast_user = 0; $ast_pass = 0; $ast_passconf = 0;

$selected_product = "";
$members = Get_All_Members("ALL");
$counties = Get_All_Counties("ALL");
$_GET['searchdata'] = $_POST['SEARCH_DATA']; $_GET['searchmember'] = $_POST['MEMBER'];

$selected_county = $counties->CTY_COUNTY;
$_GET['searchdata'] = $_POST['SEARCH_DATA']; $_GET['searchcounty'] = $_POST['COUNTY'];

$selected_country = $countries->CTY_COUNTRY;
$_GET['searchdata'] = $_POST['SEARCH_DATA']; $_GET['searchcountry'] = $_POST['COUNTRY'];

include_once("includes/header.php");
?>

<!-- start: Page header / Breadcrumbs -->

<div id="breadcrumbs">
    <div class="container">
        <div class="breadcrumbs"> 
            <a href="/">Home</a><i class="icon-angle-right"></i>Export Stockists
        </div>
    </div>
</div>

<!-- end: Page header / Breadcrumbs -->


<!-- start: Container -->
<div id="container">
    <div class="container">
        <div class="row-fluid">

        <!-- start: Page section -->
        <section class="span12">
            <div class="row-fluid shop-result">
                <div class="inner darken clearfix">
                    <h1>Export Stockists</h1>
                </div>
            </div>

            <div class="row-fluid">

            <?php
            $sql = "SELECT * FROM member ";

            if (isset($_POST['search'])) {

                $search_term = mysql_real_escape_string($_POST['search_box']);

                $sql .= "WHERE MB_COUNTRY = '{$search_term}' ";

                $sql .=" OR MB_COMPANY = '{$search_term}' ";
            }
            $query = mysql_query($sql) or die(mysql_error());
            ?>

            <form name="search_form" method="POST" action="stockists_country.php" enctype="multipart/form-data" class="form-search">
                <div class="input-append">
                    <select name="all_countries" onchange="MM_jumpMenu('parent',this,1)" type="submit"> 
                        <option disabled="disabled" selected="selected" hidden="hidden">Select County</option>
                        <option value="103">Republic of Ireland</option>
                        <option value="39">Canada</option>
                        <option value="149">Netherlands</option>
                        <option value="193">South Africa</option>
                        <option value="194">Spain</option>
                        <option value="228">United States</option>
                    </select>
                    <input type="submit" name="search" value="Search for Stockist" class="btn btn-primary">
                </div>
            </form>

            <?php
            $sql = "SELECT * FROM member ";

            if (isset($_POST['search'])) {

                $search_term = mysql_real_escape_string($_POST['all_counties']);

                $sql .= "WHERE MB_COUNTRY = '{$search_term}' ";

                //$sql .= "WHERE MB_COUNTRY IN ('103','39','149','193','194','228')";

                $sql .=" OR MB_COMPANY = '{$search_term}' ";
            }
            $query2 = mysql_query($sql) or die(mysql_error());
            ?>

            <table width="70%" cellpadding="5" cellspace="5" class="table table-hover table-striped">

            <tr>
                <td><strong>Company Name</strong></td>
                <td><strong>Website</strong></td>
                <td><strong>Phone</strong></td>
                <td><strong>Address</strong></td>
            </tr>

            <?php
            $a = "MB_COUNTRY";
            $b = "103";
            $c = "39";
            $d = "149";
            $e = "193";
            $f = "194";
            $g = "228";

            if( in_array($a, array($b,$c,$d,$e,$f,$g)) ){
            ?>

            <?php  while ($row = mysql_fetch_array($query)) { ?>
            <tr> 
                <td><?php echo $row['MB_COMPANY'];?></td>
                <td><a href="http://<?php echo $row['MB_MOBILE'];?>"><?php echo $row['MB_MOBILE'];?></a></td>
                <td><?php echo $row['MB_PHONE'];?></td>
                <td><?php echo $row['MB_ADDRESS1'];?>, <?php echo $row['MB_ADDRESS2'];?>, <?php echo $row['MB_TOWN'];?>, <?php echo $row['MB_COUNTY'];?></td>
            </tr>
            <?php } ?>
            <?php } ?>



            </table>

            <a class="btn btn-primary" href="login_member.php">Member Login</a>

        </section>

        <!-- end: Page section -->

        </div>

    </div>
</div>

<!-- end: Container -->

<?php
  include_once("includes/footer.php");
?>

I have tried putting an IF around my table that says if MB_COUTRY = $b,$c,$d,$e,$f,$g then print records. However when using that my table displays no records however there are no errors.
I want to run both queries on the same field (MB_COUNTRY) but at different times.
If anyone knows of a query that would help and how to run them at the right times that would be a massive help. Thanks!

Recommended Answers

All 2 Replies

Member Avatar for diafol

Line 110 should be all_countries not all_counties

Seriously, please whittle down your extraneous code and markup - it's preventing contributors from chipping in. Perhaps over half of the posted code/markup is not related or required - pointless in other words.
Still using mysql_* functions I see. OK, won't help with that, anybody else?

I'm not going to try and sort through all that, but let's see if I can give a more abstract answer that points you in the correct direction.

If I understand your question correctly, you want to display rows based on some condition? You have a few choices. The best choice is to only pull the ones you need, let MySQL do that filtering -- you would do this in your query string, something like this:

"SELECT * FROM tablename WHERE name = $name"

for instance. Remember that you can now use PHP variables directly in your string like above as long as you use double "quotes".

Another option is to sort it all out after you grab everything from the table. This is a less good choice unless you plan on using all that data soon as I believe there is a performance advantage to having SQL do the work. But if you were to do something like:

"SELECT * FROM tablename WHERE 1 = 1"

it would pull everything, then you can iterate over the results to find what you want -- something like this (pseudo):

- Query for everything
- Loop through to toss everything in an array
- foreach over the array to check for the fields you want against the values you want
- Toss good ones into a new array that only contain the rows you want to keep

I will be back tonight if you need some more help but I'm at work atm and don't have time to code things and check them, etc. Hope that, at least, points you in the correct direction.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.