Adding search filters to my table columns

Question

I need someone to show me how you would create a simple table to search each column of data that I insert from this form. I am looking for the easiest way to create a table with search boxes that can filter each column. DB: Mysql Field types: text Below shows the variables I am using for my form that posts the data to the database.

<?php

if (isset($_POST["submit"]) && $_POST["submit"] == "Submit")
{
    for ($count = 1; $count <= 9; $count++)
    {
        $fields[$count] = "";
        if (isset($_POST["field" . $count . ""]))
        {
            $fields[$count] = trim($_POST["field" . $count . ""]);
            //echo $fields[$count] . "<br />";
        }
    }

    $con = mysql_connect("local", "user", "pass");
    mysql_select_db("DB", $con);

    $fromzip = mysql_real_escape_string($_POST['fromzip']);
    $tozip = mysql_real_escape_string($_POST['tozip']);

    $insert = "INSERT INTO Carriers (`fromzip` ,`tozip` ,`date`) VALUES('$fromzip' ,'$tozip' , NOW())";
    mysql_query($insert) or die(mysql_error());

    $select = "SELECT `fromzip` ,`tozip` , FROM `Carriers` ORDER BY `date` DESC;";
    $result = mysql_query($select) or die(mysql_error());

}
?>
<style ="text-align: center; margin-left: auto; margin-right: auto;"></style>
</head>
<body>
<div
 style="border: 2px solid rgb(0, 0, 0); margin: 16px 20px 20px; width: 400px; background-color: rgb(236, 233, 216); text-align: center; float: left;">
<form action="" method="post";">
  <div
  style="margin: 8px auto auto; width: 300px; font-family: arial; text-align: left;"><br>
  <table style="font-weight: normal; width: 100%; font-size: 12px;"
 border="1" bordercolor="#929087" cellpadding="6" cellspacing="0">
   <table
 style="font-weight: normal; width: 100%; text-align: right; font-size: 12px;"
 border="1" bordercolor="#929087" cellpadding="6" cellspacing="0">
    <tbody>
            <tr>
              <td style="width: 35%;">Pick Zip:</td><td> <input id="fromzip" name="fromzip" maxlength="50"
 style="width: 100%;" type="text">
        </tr>
        <tr>
              <td style="width: 35%;">Drop Zip:</td><td> <input id="tozip" name="tozip" maxlength="50"
 style="width: 100%;" type="text">
        </tr>
        </tbody>
  </table>
  <p style="text-align: center;"><input name="submit" value="Submit"
 class="submit" type="submit"></p>
  </div>
</form>
</div>
<p style="margin-bottom: -20px;">&nbsp;</p>
</body>

The original MySQL extension is now deprecated, and will generate E_DEPRECATED errors when connecting to a database. Instead, use the **[MYSQLi](http://www.php.net/manual/en/book.mysqli.php) or [PDO_MySQL](http://www.php.net/manual/en/ref.pdo-mysql.php) extensions.**.. more info [here](https://stackoverflow.com/a/21797193/8484512) — danish-khan-I, Jul 10 '19 at 05:25

topherg · Answer 1 · 2012-11-06T23:27:25.733

0

Not sure of the structure of the database, but if the area you want to search from is a TEXT, then you can put this where clause into you query:

SELECT * FROM `Carriers` WHERE `carriername` LIKE '%{searchParams}%' ORDER BY date DESC, paymentamount ASC

After that, you could break it down by using explode(" ", $searchParams) and then concatenating them together like:

SELECT * FROM `Carriers` WHERE `carriername` LIKE '%{searchParams[0]}%' OR `carriername` LIKE '%{searchParams[1]}%' ORDER BY date DESC, paymentamount ASC

and so on for each item. Then you could use OR or AND depending on how accurate you want the search to be

edited Nov 06 '12 at 23:27

answered Nov 06 '12 at 23:19

topherg

4,203
4
37
72

Yes the feilds are TEXT. Can you use my code and show me an example as to where your putting this so that it refers to say my first colum. As you can see would be "Carrier Name" fetching field[0] – user1801996 Nov 06 '12 at 23:25
is `Carrier Name` the only column you are searching by? – topherg Nov 06 '12 at 23:26
the feild name is carriername – user1801996 Nov 06 '12 at 23:26
no I want to virtually search every colum but indiviually. So example: If I search 97858 in the fromzip colum and 90808 in the tozip colum it will find what matches and show the rows – user1801996 Nov 06 '12 at 23:29
so `WHERE fromzip = 97858 AND tozip = 90808`? – topherg Nov 06 '12 at 23:34
Pick zip and Drop Zip. fromzip and tozip are the field names. – user1801996 Nov 06 '12 at 23:51
Still not sure what you're getting at, nor what is the issue. – topherg Nov 06 '12 at 23:55
I want to make search boxes that allow me to search the data accourding to the colums. – user1801996 Nov 07 '12 at 18:07
And what's stopping you from doing that with the code provided? – topherg Nov 07 '12 at 18:11
I need assistance on the whole search process. I dont have a box or anything. I looking for a pure example based on the code above. From what I think you have me was more on the back end but now I need front end examples on how to connect search boxes to each colum – user1801996 Nov 07 '12 at 18:46
Righto, well it has to be done on the back end, so you create a form, `method="POST"` and `action=""`. In the form, have an input text box for each column, and name them appropriately. Then in the script, assemble the beginning part of the `SELECT` query, and then check each `$_POST` variable to ensure its set. If any of them are set, append ` WHERE ` to the query, followed by a collection of clauses for each set `$_POST` you're using. Does that answer your question? – topherg Nov 07 '12 at 19:03
Yeah kinda. Ok I have two searchable fields. Can you show me how you would make a form based on what I use to insert the data. – user1801996 Nov 07 '12 at 19:44
see above for what I am using to submit the data into the DB. If you can please show me how you would make a form for these two fields. And or if I have to do anything on my post page. – user1801996 Nov 07 '12 at 19:47
fyi I am reusing code and have not sucessfully put searchs on any table to do what I'm looking for. So I would need a full example. I can get it when I see it work, and when its used in the simplest examples. – user1801996 Nov 07 '12 at 19:58
Can you advise how you would add ORDER BY date DESC, paymentamount ASC to this line: $sql = "SELECT * FROM `Carriers` WHERE tozip <= '".mysql_real_escape_string($_REQUEST["to"])."'".$search_string.$search_loadtype; – user1801996 Nov 08 '12 at 21:38
The order of the `SELECT` syntax is: `FROM` `WHERE` `GROUP` `HAVING` `ORDER BY` and `LIMIT` (as taken from http://dev.mysql.com/doc/refman/5.0/en/select.html), so it goes on the end of your query. n.b. are you sure the `tozip` is supposed to be `less than or equal`? – topherg Nov 08 '12 at 22:05

Adding search filters to my table columns

1 Answers1