Searching from a form in mySQL...PLEASE HELP ><

hopkins

Hi guys i did a search and couldnt find what i needed. Im making a DB search form for a DB which holds names of items.

I've got the DB table I want to search from with around 6 columns (name, effects, stackable etc). I made a php page which displays ALL hits in a table easy enough with :

 $result = mysql_query("SELECT * FROM items);

The problem is i'm trying to make it searchable from a search form which uses text boxes. I want whatever is entered into the text box to search the NAME column from the DB and display the results in the table instead of "*". So display names LIKE instead of all the hits.

I'm unsure of how to integrate the SUBMIT button into the query.

I tried this : (button code)

Button:
td width="100%" height="190"> <form name="search" method="post" action="dbitemstable.php">
          <p align="left"><strong><font size="4">Search the Database: (Fill one text field and select an option from below and click submit)</font><br>
            <br>
            Name:</strong> 
            <input type="text" name="item" value="">
            <br>
            <br>....etc

And in another php file have the script which displays the hits :

<html>
<head>
<title>Search Results</title>
<head>
<body>

<table border="1" cellspacing="0" cellpadding="6" align="center"><tr>

<?php
$dbcnx = @mysql_connect("localhost", "user", "pass");
if (!$dbcnx) {
  echo( "<p>Unable to connect to the " .
        "database server at this time.</p>" );
  exit();
}

// Select the items database
  if (! @mysql_select_db("fantasy") ) {
    echo( "<p>Unable to locate the fantasy " .
"database at this time.</p>" );
    exit();
  }
?>
<p> Here are all the items you have selected </p>
<blockquote>
<tr>
<?php

$result = mysql_query("SELECT * FROM items WHERE name LIKE ". $_GET['item']);

echo "<tr><th>Item Name</th><th>Item Effects</th><th>Stackable?</th><th>Approx Trader Value</th><th>Used in Tradeskill</th><th>Notes</th></tr>";

$first=1;
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{

// if first loop, make the headings
if($first)
{
echo " ";
// do a loop for each field in the $row, make labels 
reset($row);
while(@list($fieldname, $fieldvalue) = each($row))

//echo " <td> $fieldname </td>  ";

echo " 
";

// not first loop anymore
$first=0;
}

echo " ";

// now go through each field and print the value 
reset($row);
while(@list($fieldname, $fieldvalue) = each($row))

echo " <td>$fieldvalue </td>  ";
echo "<tr></tr>
";

} // end while

?>
</blockquote>
</table>

</body>
</html>

Can anyone suggest a way of integrating the 2 together? How to get whatever is typed in the text box form and make it appear in the mySQL query is what is troubling me.

Thanks in advance and my apologies for so much text ><

Hopkins

archangel_617b

Uh, I'm still not clear as to what exactly you need help on, but you have such an excellent post that I'd like to try to help you anyhow...

You said you're having trouble getting the input from the text field into the SQL query? It looks to me that you need to wrap your $GET['item'] in quotes for the SQL query to work. You should have something like:

$result = mysql_query("SELECT * FROM items WHERE name LIKE '". $_GET['item']."'");

I'm sorry if this is not the solution to your problem, perhaps you can clarify exactly what you have working, what you expect this code to do, and what it is doing. I'll keep an eye on this thread, so post away and lets see if we can get your code working.

hopkins

What im trying to achieve is have a HTML page from which i can execute multiple queries based on what is entered into the fields.

However I discovered I couldnt even get a text field to successfully pass a value to a PHP file so I simplified it down for starters. So I have a text field which has a "Name" entered into it, this will pass through to the PHP file which will have some query like "select * from items with name like '....' etc

Ill give your suggestion a go. Thanks.

Char

It looks to me like you have a $GET or $POST problem. Your original form has a method="POST", however in the script that displays the search results, you are trying to call it with the $_GET array.

This line:

$result = mysql_query("SELECT * FROM items WHERE name LIKE ". $_GET['item']);

Should be

$temp = $_POST['item'];
$result = mysql_query("SELECT * FROM items WHERE name LIKE '$temp'");

Also, I'm not sure if you know about the wildcard in mysql. The percent sign (%) is often used in conjunction with LIKE in searches.

Here's an example of what the code might look like:

$result = mysql_query("SELECT * FROM items WHERE name LIKE '$temp%'");

This would find all the rows where the column name is set to $temp followed by any other character.

For example, if $temp were set to "cat", mysql would return rows with "caterpillar" and "category" in addition to just "cat".

For more on wildcards and LIKE, go to the following URL.

http://www.mysql.com/doc/en/String_comparison_functions.html

Hope that helped,
Charlotte Genevier
Webligo Group
http://www.webligo.com

hopkins

Excellent thats working.

Thanks allot, im learning lots from all this 🙂

ComCool

Is there no way to use the wildcard % on the $_GET?

$result = mysql_query("SELECT * FROM items WHERE name LIKE '". $_GET['item']."'");

Or is the only way

$temp = $_POST['item']; 
$result = mysql_query("SELECT * FROM items WHERE name LIKE '$temp%'");

😕

archangel_617b

Wow... This is an old thread.

You will need to URL encode whatever is coming through the GET variables. Check the documentation on urlencode() or just run that function on the string in code before using it.