PHP MySQL search using multiple text boxes to search multiple columns

Question

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?

innovative kundan · Accepted Answer · 2013-11-22 18:07:00Z

2

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 :)

edited Nov 22, 2013 at 18:07

answered Nov 22, 2013 at 17:17

innovative kundan

6316 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Micky Over a year ago

Thanks for the Reply. I'm getting a "Fatal error: Can't use function return value in write context in" error on the first IF line of that code.

innovative kundan Over a year ago

use the updated one, any sorry for the problem you have faced to earlier code.

Micky Over a year ago

When i use this code, the searching returns no results at all.

Laurent S. · Accepted Answer · 2013-11-22 17:03:41Z

0

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...

answered Nov 22, 2013 at 17:03

Laurent S.

6,9373 gold badges32 silver badges42 bronze badges

Collectives™ on Stack Overflow

PHP MySQL search using multiple text boxes to search multiple columns

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related