filtering results based on dropdown paramenters using php/mysql

Question 1

I'm creating a small mock-library adminstrator panel. I'm new to php so was hoping someone out there can help with my code.

I'm trying to filter results based on whatever dropdown option/paremter is selected. The dropdowns refer to three columns within my database table where the required information is due to be pulled from.

So for instance: - book_title selected - search book_title WHERE query? IS LIKE '%'; I know it's along these lines but would like some guidance.

<?php
 // Ignore warning and error messages
error_reporting(E_ALL);
ini_set('display_errors', '1');

$username="";
$password="";
$database="books";
$value= $_POST["filter-query"];    

mysql_connect(localhost,$username,$password);
@mysql_select_db($duncan_library) or die( "Unable to select database");

//catalog1//
if ($_POST['filter-query'] == 'catalog_number')
{
$query = "SELECT * FROM books WHERE catalog_number LIKE '%user-search'%;
}
elseif ($_POST['filter-query'] == 'book_title')
{
$query = "SELECT * FROM books WHERE book_title LIKE '%user-search'%;
}
elseif ($_POST['filter-query'] == 'book_author')
{
$query = "SELECT * FROM books WHERE book_author LIKE '%user-search'%;
}

$sql = mysql_query($query);
while ($row = mysql_fetch_array($sql)) {
        $catalog_number = $row["catalog_number"];
        $book_title = $row["book_title"];
        $book_author = $row["book_author"];

    echo $row["myrow"]."";
    }
    }
?>

    Filter:<br>
                <select name="filter-query"">
                    <option value="catalog_number">Catalog Number</option>
                    <option value="book_title">Book Title</option>
                    <option value="book_author">Book Author</option>
                </select>

</select>
</form>

Current Error

Parse error: syntax error, unexpected '' (T_ENCAPSED_AND_WHITESPACE), expecting identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING) in C:xampphtdocslibraryindex.php on line 20

Question 2

Put your LIKE string inside quotes even the % sign and also you missed closing double quotes

//catalog1//
    if ($_POST['filter-query'] == 'catalog_number')
    {
    $query = "SELECT * FROM books WHERE catalog_number LIKE '%user-search%'";
    }
    elseif ($_POST['filter-query'] == 'book_title')
    {
    $query = "SELECT * FROM books WHERE book_title LIKE '%user-search%'";
    }
    elseif ($_POST['filter-query'] == 'book_author')
    {
    $query = "SELECT * FROM books WHERE book_author LIKE '%user-search%'";
    }

Question 3

<?php
$sql = new mysqli('127.0.0.1','root','Qwert12345','plot_io_db');
//echo $sql->query('Select * From players');
?>

It will work. Just remove port from localhost (127.0.0.1)

Question 4

mysqli_connect("","username" ,"password","databasename");//Server name cannot be NULL

use loaclhost for server name(In Loacl)

<?php
    $con = mysqli_connect("localhost","username" ,"password","databasename");

    if (mysqli_connect_errno())
    {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }
?>

Or can use MySQLi Procedural

<?php
    $servername = "localhost";
    $username = "username";
    $password = "password";

    // Create connection
    $con = mysqli_connect($servername, $username, $password);

    // Check connection
    if (!$con) {
        die("Connection failed: " . mysqli_connect_error());
    }
    echo "Connected successfully";
?>

EDIT 01

$servername = "localhost";
$username = "root";
$password = "";

Question 5

You will need to write a custom filter on server side to filter the results. What you type in the box is saved in 'term' and then 'q' is sent as a request parameter with the ajax call. So the ajax call to
url:"/concepts/names_for_search?q=deri"
should only return the filtered results and not all the results.

Update
Select2 will make an AJAX call every time you type in the search box. You have to filter the results on server side and then return the results based on the text in search box.
I am using it in my JSP/Servlet application as below

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException  
{
    String searchTerm = request.getParameter("q");

    String json = getOptions(searchTerm);
    //getOptions method will return a string of  in JSON format
    //[{"id":"1","name":"Derivatives"}]
    response.getWriter().write(json);
}

Your JavaScript code is correct.

I found Select2 is used here. If you open the link http://www.indiewebseries.com/search?q=ind and http://www.indiewebseries.com/search?q=in the results obtained are different and based on the 'q' parameter.
While in you case, the results for calls to url '/concepts/names_for_search?q=d' and '/concepts/names_for_search?q=deri' are the same(i.e. not filtered)

Question 6

One method uses window functions and aggregation:

with cp as (
     select customerid, paymentmethod, count(*) as cnt,
            rank() over (partition by customerid order by count(*) desc) as seqnum
     from t
     group by customerid, paymentmethod
    ),
    cd as (
     select customerid, deliveryservice, count(*) as cnt
            rank() over (partition by customerid over by count(*) desc) as seqnum
     from t
     group by customerid, deliveryservice
    )
select cp.customerid
from cp join
     cd
     on cp.customerid = cd.customerid
where (cp.seqnum = 1 and cp.PaymentMethod = 'CreditCard') and
      (cd.seqnum = 1 and cd.DeliveryService = '24hr');

Because you need the ranks along two different dimensions, I think you need two subqueries (or the equivalent).

filtering results based on dropdown paramenters using php/mysql

Answers

php,mysql,mysqli