Sending "LIKE qualifier" in query to MySQL db

[pallen]

So I have a basic database of books set up. I am trying to use PHP to query the db and send back the results. I can sort of get it to work, but not working how I would like. The issue is that the search term that queries the db has to match the cell contents exactly. I'm going to paste both the form and the php:

<div class="bookSearch">
			<form action="book_search.php" method="post">
			  <span id="spryBookSearch">
			  <input type="text" size="50" name="author" />
			  <span class="textfieldMaxCharsMsg">Exceeded maximum number of characters.</span></span><br />
              <span id="sprytitle">
              <input type="text" size="50" name="title" />
<span class="textfieldMaxCharsMsg">Exceeded maximum number of characters.</span></span><br />
              <span id="spryyear">
              <input type="text" name="year" />
              <span class="textfieldMaxCharsMsg">Exceeded maximum number of characters.</span><span class="textfieldInvalidFormatMsg">Invalid format.</span></span><br />
              <span id="spryISBN">
              <input type="text" name="isbn" />
<span class="textfieldMaxCharsMsg">Exceeded maximum number of characters.</span><span class="textfieldInvalidFormatMsg">Invalid format.</span></span><br />
              <input type="submit" name="submit" value="Submit" />
          </form>
		</div>

<?php
		require_once 'trinity_database_login.php';
		
		$db_server = mysql_connect($db_hostname, $db_username, $db_password);
		if (!$db_server) die("Unable to connect to MySQL: " . mysql_error());
		
		mysql_select_db($db_database, $db_server) or die("Unable to select database: " . mysql_error());
	?>	
		<pre><table><tr> 
			<th>Author</th> 
			<th>Title</th> 
      		<th>Year</th> 
			<th>ISBN</th>
			</tr></pre>
	<?php			
		if (isset($_POST['submit']))  {
			$query = " ";
			$author = $_POST['author'];
			$title = $_POST['title'];
			$year = $_POST['year'];
			$isbn = $_POST['isbn'];
			
			$query = "SELECT * FROM classics WHERE author='$author' OR title='$title' OR year='$year' OR isbn='$isbn'";
			$result = mysql_query($query);
			$num=mysql_numrows($result);
			
			$i = 0;
			while ($i < $num)	{
				
				$f1 = mysql_result($result, $i, "author");
				$f2 = mysql_result($result, $i, "title");
				$f3 = mysql_result($result, $i, "year");
				$f4 = mysql_result($result, $i, "isbn");
	?>		
				<tr>
					<td><?php echo $f1; ?></td>
               	 	<td><?php echo $f2; ?></td>
                	<td><?php echo $f3; ?></td>
                	<td><?php echo $f4; ?></td>
                	</tr>
				</table>
	<?php
			$i++;
			}
		}
		
		mysql_close($db_server);
	?>

So, for example, if I search for "Mark Twain", I get back the the author, title, year of pub and ISBN. That's all good. But if I search for just "Mark" or just "Twain", I get nothing. I took most of this code from here: http://www.siteground.com/tutorials/php-mysql/display_table_data.htm (I'm not advertising anything, just letting you know one of my resources).

Anyone see the where I can fix things? I know there is a LIKE qualifier that can be used on the command line, but obviously the web doesn't offer access to the command line.

[Borzoi]

I believe this should work:

$query = "SELECT * FROM classics WHERE author LIKE '% $author %' OR title LIKE '% $title %' OR year LIKE '% $year %' OR isbn LIKE '% $isbn %'";

The percent symbols are wildcards so if "$author" was set to "Mark" then it should return anything with "Mark" under the author column in your database whether that be Mark Twain, Graham Marks. or anyone else with "Mark" in their name.

If the LIKE qualifier doesn't work then try with your original way but still using the wildcards.

[pallen]

I believe this should work:

$query = "SELECT * FROM classics WHERE author LIKE '% $author %' OR title LIKE '% $title %' OR year LIKE '% $year %' OR isbn LIKE '% $isbn %'";

The percent symbols are wildcards so if "$author" was set to "Mark" then it should return anything with "Mark" under the author column in your database whether that be Mark Twain, Graham Marks. or anyone else with "Mark" in their name.

If the LIKE qualifier doesn't work then try with your original way but still using the wildcards.

Thanks for the response. Unfortunately, when I try your sugesstion:

$query = "SELECT * FROM classics WHERE author LIKE '% $author %' OR title LIKE '% $title %' OR year='$year' OR isbn='$isbn'";

I get nothing as a result. When I remove the LIKE qualifiers and keep the wildcards, I again get nothing. I'm pretty well stumped at this point. Any other suggestions would be welcome.

As an aside, I'm using the O'Reilly book 'PHP, MySQL and JavaScript' by Robin Nixon. It's terrible. You would think covering something like what I'm trying to do here would be a priority. It's not. The book does cover how to query a MySQL db, but not through an HTML form. Does anyone know of a better book to use? The examples throughout the book suck in my opinion.

[rch1231]

Hello,

Here is something from the reference I use:

<p>
Search the online resources database:<br />
<form action="fulltextsearch.php" method="post">
    Keywords:<br />
    <input type="text" name="keywords" size="20" maxlength="40" value="" /><br />
    <input type="submit" value="Search!" />
</form>
</p>
<?php
    // If the form has been submitted with supplied keywords
    if (isset($_POST['keywords'])) {
       // Connect to server and select database
       $mysqldb = new mysqli("localhost","websiteuser","secret","corporate");
       // Retrieve the search keyword string
       $keywords = $mysqldb->mysqli_real_escape_string($_POST['keywords']);
       // Create the query
       $result = $mysqldb->query("SELECT name, url FROM bookmarks
                              WHERE MATCH(description) AGAINST('$keywords')");
          // Output retrieved rows or display appropriate message
          if ($result->num_rows > 0) {
             while ($row = $result->fetch_object())
                echo "<a href=\"$row->url\">$row->name</a><br />";
          } else {
             echo "No results found.";
          }
       }
    ?>

[pallen]

This is the result I came up with, which seems to be working at this point:

$query = "SELECT author,title,year,isbn FROM classics WHERE author LIKE '%$author%' AND title LIKE '%$title%' AND year LIKE '%$year%' AND isbn LIKE '%$isbn%'";

These are the only changes I made. The problem now is that it doesn't make sense to me. First of all, why is an AND required? I can enter data in just one field and get a result. When I used OR, I had to enter data in every field. Second, I don't get why 'year' and 'isbn' needed the LIKE qualifiers and the wildcards. 'year' is always 4 digits and isbn is always 13. There can't be anything before or after, therefore why would I need LIKE or a wildcard. If I did the following instead and entered something only for the 'year' or 'isbn', the entire database would print:

$query = "SELECT author,title,year,isbn FROM classics WHERE author LIKE '%$author%' AND title LIKE '%$title%' AND year='$year' AND isbn='$isbn'";

I guess I shouldn't complain because it works, but I don't like getting to an answer through trial and error unless I understand the result, which I don't.

rch1231, which reference is that you're using?

[Stefano Mtangoo]

These few concise tutorials have good examples.
See if something is wrong with your code
http://www.weberdev.com/ViewArticle/PHP%3A-A-simple-MySQL-search
http://www.htmlite.com/mysql011.php
http://www.iamcal.com/publish/articles/php/search/

[Borzoi]

I thought I responded to this last night... I must have closed the browser before the page loaded.

All I mentioned was that I made an error and shouldn't have put the space before/after the wildcards since it will then search the database for those spaces but by the looks of it, you have realised my error.

[richieking]

Hello pallen,

try this

this works like a search engine script.

// concatenate all your variable like this.
$foo=$bar.$name.$isnd.$momo;
			$query = "SELECT * FROM classics WHERE MATCH  (author,title,year,isbn) AGAINST ('$foo' IN BOOLEAN MODE);

Know that this will work just like how google search engines work. it will pull out fields that has the search words.

You can attach this to the other basic search.
NOTE: if only one field qualifies, it will pull only that.

Explore :)

[pallen]

Just want to say thank you. This is the best forum to get questions answered. People seem genuinely interested in helping. Thanks folks

[richieking]

You are welcome good friend :)