Member Avatar for stangfl

I am fairly new to PHP and MYSQL and was having a few problems getting a database search script to work. The data is input through an HTML form with an input field and drop down box to narrow results. This code will work for searching one table but as soon as I try and search multiple tables it returns 0 results. When using the drop down box PHP or MYSQL seems to only look at the table fields where the data is rather then the table as a whole. Those fields are name, address, website, telephone, location. Below the fields for the drop down are setup to how I would want them to work, as they are the table names : soda, chocolate, wine, cigars etc...If you can please help I'd appreciate it.... Heres the code

// HTML

...

<select name="metode" class="category_select">
<option value="name">Beer</option>
<option value="chocolate">Chocolate</option>
<option value="cigars">Cigars</option>
<option value="glass">Glass</option>
<option value="wine">Wine</option>
</select><br><br>

...

// PHP stuff

<? $hostname = "localhost"; // The Thinkhost DB server.
$username = "**"; // The username you created for this database.
$password = "**"; // The password you created for the username.
$usertable = "soda"; // The name of the table you made.
$dbName = "**"; // This is the name of the database you made.

MYSQL_CONNECT($hostname, $username, $password) OR DIE("DB connection unavailable");
@mysql_select_db( "$dbName") or die( "Unable to select database");
?>
<?
//error message (not found message)begins
$XX = "No Record Found, to search again please close this window";
//query details table begins
$query = mysql_query("SELECT name, website FROM $usertable WHERE $metode LIKE '%$search%' "); // problem AREA
while ($row = @mysql_fetch_array($query))
{
$variable1=$row["soda"];
$variable2=$row["chocolate"];
$variable3=$row["cigars"];
$variable4=$row["glass"];
$variable5=$row["wine"];
//table layout for results


echo "&nbsp; <b>", $variable1.": <b> <br>";
echo "&nbsp; ", $variable2.": <b> <br>";
echo "&nbsp; ", $variable3.": <b> <br>";
echo "&nbsp; ", $variable4.": <b> <br>";
echo "&nbsp; ", $variable5.": <b> <br>";

}
//below this is the function for no record!!
if (!$variable1)
{
print ("$XX");
}
//end
?>

  • 2 Contributors
  • 8 Replies
  • 126 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by Troy

Recommended Answers

All 8 Replies

Member Avatar for Troy

I'm quite confused both by what you say you want to do and by your code. Are you able to re-phrase this to make it clearer?

For example, is the <SELECT> dropdown supposed to be table names or column names?

How does the value of $search get populated?

Member Avatar for stangfl

I'm quite confused both by what you say you want to do and by your code. Are you able to re-phrase this to make it clearer?

For example, is the <SELECT> dropdown supposed to be table names or column names?

How does the value of $search get populated?

Orginally the <SELECT> had the column names, I am going to need it to be the table names. The $search is populated from the <input> tag on the form.

Thanks

Member Avatar for Troy

You haven't cleared up overall what you want, though. Please try to explain the concept of what you want again. Sorry.

Member Avatar for stangfl

You haven't cleared up overall what you want, though. Please try to explain the concept of what you want again. Sorry.

Overall I am looking to have a form that when submitted will search multiple tables in a database, in this case the tables are the soda, chocolate, cigars, glass, and wine. On the search page there is a <input> area as traditional on any search page to search all the database tables as well as a drop down box to refine the choice of searches to a particular table like soda or chocolate ... etc. The original one I made worked but with only column names in the drop down area and selecting one table to search from. When I tried to modify it to search multiple tables and changed the input of the columns to the tables no results could be found.

Thanks

Member Avatar for Troy

To my knowledge, you'll have to query each table individually.

SELECT name, website FROM beer WHERE name LIKE '%$search%' or website LIKE '%$search%'
// Collect results here.

SELECT name, website FROM wine WHERE name LIKE '%$search%' or website LIKE '%$search%'
 // Collect results here.

SELECT name, website FROM cigars WHERE name LIKE '%$search%' or website LIKE '%$search%'
 // Collect results here.

You may also decide to setup fulltext indexes on your tables and use the match() function. http://dev.mysql.com/doc/mysql/en/fulltext-search.html

Member Avatar for stangfl

To my knowledge, you'll have to query each table individually.

SELECT name, website FROM beer WHERE name LIKE '%$search%' or website LIKE '%$search%'
// Collect results here.

SELECT name, website FROM wine WHERE name LIKE '%$search%' or website LIKE '%$search%'
 // Collect results here.

SELECT name, website FROM cigars WHERE name LIKE '%$search%' or website LIKE '%$search%'
 // Collect results here.

You may also decide to setup fulltext indexes on your tables and use the match() function. http://dev.mysql.com/doc/mysql/en/fulltext-search.html

Am I placing the table names in the drop down box for the form?

Member Avatar for stangfl

To my knowledge, you'll have to query each table individually.

SELECT name, website FROM beer WHERE name LIKE '%$search%' or website LIKE '%$search%'
// Collect results here.

SELECT name, website FROM wine WHERE name LIKE '%$search%' or website LIKE '%$search%'
 // Collect results here.

SELECT name, website FROM cigars WHERE name LIKE '%$search%' or website LIKE '%$search%'
 // Collect results here.

You may also decide to setup fulltext indexes on your tables and use the match() function. http://dev.mysql.com/doc/mysql/en/fulltext-search.html

Also was just curious where the drop down box values are used to SELECT information in the above code.
Thanks

Member Avatar for Troy

I'm not providing complete code for you, although I do provide paid programming consultation if you need a complete solution. Instead, I am trying to show you the concept to solve your problem. You already have code (apparently) to build the form and receive the form data. Your question is how to build queries to find the matches. My posts above are an attempt to answer that for you or at least help you find the answer.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.