php and mysql database not working when i do search

Question

I am having problem with my php code. I have created a database with

    id int(11) NOT NULL AUTO_INCREMENT,
    isbn varchar2  NOT NULL,
    title varchar2 NOT NULL,
    author varchar2 NOT NULL,
    publisher varchar2 NOT NULL,
    year date NOT NULL,
    PRIMARY KEY (`id`)

I have a list of books in mysql database:

ISBN: 0763754891
Title: Web Development with JavaScript and AJAX Illumination
Author: Richard Allen, Kai Qian , LiXin Tao, Xiang Fu
Publisher: Jones& Bartlett
Date: 2009

ISBN: 9780763754204
Title: Software Architecture and Design Illuminated
Author: Kai Qian, Xiang Fu, LiXin Tao, Jorge Diaz-Herrera,Chong-wei Xu
Publisher: Jones & Bartlett
Date: 2009

ISBN: 0763734233
Title: Java Web Development Illuminated
Author: Kai Qian, Richard Allen, Mia Gan,Bob Brown
Publisher: Jones & Bartlett
Date: 2007

ISBN: 0471644463
Title: Component Oriented Programming
Author: Andy Wang, Kai Qian
Publisher: Wiley
Date: 2005

ISBN: 9781441906052
Title: Embedded Software Development with C
Author: Kai Qian, David den Haring, Li Cao
Publisher: Springer
Date: 2010

Here is my html and php code

<html>
<head>
<title>Book Store</title>
</head>
<body>
<h3> Book Store </h3>

<p> *****Welcome to use book store system***** <p>
<form action = "" method = "GET">
Find entries that are:<br/>
Author: <input type = "text" name = "author" />
Title : <input type = "text" name = "title"  />
<p></p>
Year  : 
    <p></p>
    <select name = "select">
    <option value=""/></option>
    <option value=2005/>2005</option>
    <option value=2007/>2007</option>
    <option value=2009/>2009</option>
    <option value=2010/>2010</option>
    </select>
    <p></p>
        <input type = "submit" name="submit" value="search"/>
</form>
</body>
</html>



<?php
session_start();

if($_GET){

    $author = $_GET['author'];
    $title = $_GET['title'];
    $select = $_GET['select'];

    $connect = mysql_connect("localhost", "root", "") or die(mysql_error());

    if($connect)
    {
        mysql_select_db("mysql", $connect);

        $query = "SELECT * FROM bookstore WHERE author='" . $author . "' or      Title='" . $title . "' ";

        $query2 = "SELECT * FROM bookstore WHERE Year = '" . $select ."' ";

        $result = mysql_query($query) or die(mysql_error());
        $result2 = mysql_query($query2) or die(mysql_error());

        while($row = mysql_fetch_array($result)){

            echo $row['ISBN'] . "<br/>" . $row['Title'] . "<br/>" .  $row['Author'] . "<br/>" . $row['Publisher'] . "<br/>" . $row['Year'] . "<br/>";

        }
        while($row2 = mysql_fetch_array($result2)){
            echo $row2['ISBN'] . "<br/>" . $row2['Title'] . "<br/>" .  $row2['Author'] . "<br/>" . $row2['Publisher'] . "<br/>" . $row2['Year'] . " <br/>";
        }
    } else {
        die(mysql_error());
     }
mysql_free_result($result);
mysql_close();
}

?>

I should be able to perform Search for "Qian" as author and "Java" as title but it only works when I write the entire book title or all authors' names in search area. How do I fix this?

You are open to SQL injections with this code. http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 — chris85, Jun 20 '15 at 00:07

score 1 · Accepted Answer · answered Jun 19 '15 at 23:58

1

= does an exact match. You need to use LIKE to search for a pattern.

$query = "SELECT * 
          FROM bookstore 
          WHERE author LIKE '%" . $author . "%' 
          OR Title LIKE '%" . $title . "%' ";

answered Jun 19 '15 at 23:58

Barmar

741,623
53
500
612

OllyBarca · Answer 2 · 2015-06-20T00:00:01.617

0

Use the LIKE wildcard:

"SELECT * FROM bookstore WHERE author LIKE '%".$author."%'";

edited Jun 20 '15 at 00:00

answered Jun 19 '15 at 23:58

OllyBarca

1,501
1
18
35

I doubt he needs to use a wildcard for the year. – Barmar Jun 19 '15 at 23:59
Oops, wrong variable ;) – OllyBarca Jun 20 '15 at 00:00

score 0 · Answer 3 · answered Jun 20 '15 at 00:00

0

You'll want to do a like comparison like this:

SELECT * FROM Authors WHERE Name LIKE '%Qian%'

This combined with a "utf_general_ci" collegation will have a case in-sensative search for names that contain "qian".

answered Jun 20 '15 at 00:00

CalebB

597
3
17

php and mysql database not working when i do search

3 Answers3