CODEWITHSUNDEEP
phpmysqlsearchmultiple-columns
Micky
Micky

Reputation: 25

PHP MySQL search using multiple text boxes to search multiple columns

I'm learning PHP and working on a project for searching books in a MySQL database. The user should be able to search by Book Title, Book Author and by the Category, using all, one or any combination of the 3.

At present here is my code:

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Welcome to Library Management System</title>
<link href="main.css" rel="stylesheet" type="text/css" />
</head>
<body>

<?php

require_once "db.php";
include "header.html";


if(isset($_POST["bookTitle"]))
{
$bookTitle = mysqli_real_escape_string($con, $_POST["bookTitle"]);
}
else
{
$bookTitle = NULL;
}

if(isset($_POST["bookAuthor"]))
{
$bookAuthor = mysqli_real_escape_string($con, $_POST["bookAuthor"]);
}
else
{
$bookAuthor = NULL;
}

if(isset($_POST["category"]))
{
$category = mysqli_real_escape_string($con, $_POST["category"]);
}
else
{
$category= NULL;
}



echo "Results by Book Title Search";
$bookTitle = mysqli_real_escape_string($con, $_POST["bookTitle"]);
$query = "Select * From book NATURAL JOIN category where category.CategoryDesc LIKE '%" .$category ."%' OR book.BookTitle LIKE '%" .$bookTitle ."%' OR book.Author LIKE '%" .$bookAuthor."%'";
$result=mysqli_query($con, $query) or die(mysqli_error());

echo '<table border="1" width="95%">'."\n";
echo "<tr><th>ISBN</th><th>Title</th><th>Author</th><th>Edition</th><th>Year</th><th>Category ID</th><th>Reserved</th><th>Reserve?</th><tr>";

while($row = mysqli_fetch_array($result, MYSQLI_BOTH)){
echo "<tr><td>";
echo(htmlentities($row[0]));
echo("</td><td>");
echo(htmlentities($row[1]));
echo("</td><td>");
echo(htmlentities($row[2]));
echo("</td><td>\n");
echo(htmlentities($row[3]));
echo("</td><td>\n");
echo(htmlentities($row[4]));
echo("</td><td>\n");
echo(htmlentities($row[5]));
echo("</td><td>\n");
echo(htmlentities($row[6]));
echo("</td><td>\n");
echo('<a href="edit.php?id='.htmlentities($row[1]).'">Edit</a> 
/ ');
echo('<a 
href="delete.php?id='.htmlentities($row[1]).'">Delete</a>');
echo("</td></tr>\n");
}
echo "</br>";

If I search using all three fields, the query returns the relevant results. If one or more of the fields is left blank, the entire database is returned, which is not what i want.

Is there a better approach to this?

Upvotes: 0

Views: 3020

Answers (2)

innovative kundan
innovative kundan

Reputation: 631

you can use this

$condition="sasaaa";
$bookTitle=trim($_POST['bookTitle']);
$bookAuthor=trim($_POST['bookAuthor']);
$category=trim($_POST['category']);
if(isset($bookTitle))
   $condition="booktitle=$bookTitle";
if(isset($bookAuthor))
   $condition="bookAuthor=$bookAuthor";
if(isset($category))
   $condition="category=$category";

and use this $condition variable in your SQl. use mysqli_real_escape_string(). Hope it will help you :)

Upvotes: 2

Laurent S.
Laurent S.

Reputation: 6946

It would be better to skip all your tests at the beginning, and simply build your query dynamically, only putting where conditions when your post variables are set. But if you wish to keep this logic (which isn't too good) , just replace your NULLvalues with empty string, and that should do the trick...

Upvotes: 0

Related Questions