shcKr- Posted July 25, 2008 Share Posted July 25, 2008 i have a problem, i used a UNION so it would search all the tables and now it is picking up all of the items there and not filtering them by what i searched :S this is what it is doing : http://bluid.co.uk/new/search.php?search=bed and heres my code: <?php require 'config.php'; mysql_connect ($dbhost, $dbusername, $dbuserpass); mysql_select_db($dbname) or die('Cannot select database'); if(isset($_GET['search'])) { $search = $_GET['search']; } $search = trim($search); $search = preg_replace('/\s+/', ' ', $search); $keywords = explode(" ", $search); $keywords = array_diff($keywords, array("")); if ($search == NULL or $search == '%'){ } else { for ($i=0; $i<count($keywords); $i++) { $query = "SELECT * FROM accessories UNION SELECT * FROM bedroom UNION SELECT * FROM chaises UNION SELECT * FROM contemporary UNION SELECT * FROM dining UNION SELECT * FROM garden UNION SELECT * FROM kids UNION SELECT * FROM lighting UNION SELECT * FROM living UNION SELECT * FROM mirrors " . "WHERE title LIKE '%".$keywords[$i]."%'". " ORDER BY title"; } $result = mysql_query($query) or die(mysql_error()); } if ($search == NULL or $search == '%'){ } else { $count = mysql_num_rows($result); } echo "<html>"; echo "<head>"; echo "<title>Your Title Here</title>"; echo "<link rel=\"stylesheet\" type=\"text/css\" href=\"style.css\" />"; echo "</head>"; echo "<body onLoad=\"self.focus();document.searchform.search.focus()\">"; echo "<h2>Search</h2>"; echo ""; echo "<form name=\"searchform\" method=\"GET\" action=\"index.php?search\">"; echo "<input type=\"text\" name=\"search\" size=\"20\" TABINDEX=\"1\" />"; echo " <input type=\"submit\" value=\"Search\" style=\"border:1px solid #f2f2f2;background-color:#d7d7d7;font-weight:bold;font-family:Tahoma;color:#888888;height:22px;\"/>"; echo "</form><br>"; if ($search == NULL) { } else { echo "<span style=\"text-transform:capitalize\">You searched for "<b><FONT COLOR=\"#198bc7\">"; foreach($keywords as $value) { print "$value"; } echo "</font></span></b>""; } echo ""; echo ""; if ($search == NULL){ echo "<b><FONT COLOR=\"red\">Please enter a search parameter to continue.</font></b><br />"; } elseif ($search == '%'){ echo "<b><FONT COLOR=\"red\">Please enter a search parameter to continue.</font></b><br />"; } elseif ($count <= 0){ echo "<b><FONT COLOR=\"red\">Your query returned no results from the database.</font></b><br />"; } else { echo "<center><br><table id=\"search\" bgcolor=\"#\" width=\"100%\">"; echo "<tr>"; echo "<td height=\"30\"><b>SEARCH RESULTS: </b></td>"; echo "</tr>"; $color1 = "#efefef"; $color2 = "#"; $color3 = "#177cb1"; $color4 = "#299cd8"; while($row = mysql_fetch_array($result)) { $row_color = ($row_count % 2) ? $color1 : $color2; $text_color = ($row_count % 2) ? $color4 : $color3; echo "<tr bgcolor=".$row_color." style=\"color:#198bc7;padding:10px\">"; echo "<td style=\"padding:5px;padding-left:20px;color:".$text_color."\"><strong>- ".$row['title']."</strong></td>"; echo "</tr>"; $row_count++; } echo "</table>"; } echo "</body>"; echo "</html>"; if ($search == NULL or $search == '%') { } else { mysql_free_result($result); } ?> Quote Link to comment Share on other sites More sharing options...
fenway Posted July 26, 2008 Share Posted July 26, 2008 If you want that where clause to apply to all of the unioned tables, you need to wrap them in parens. Quote Link to comment Share on other sites More sharing options...
shcKr- Posted July 28, 2008 Author Share Posted July 28, 2008 im sorry, but i dont fully understand what you mean, i am new to this - could you provide an example and explain it or explain how to do it on my code ? Quote Link to comment Share on other sites More sharing options...
fenway Posted July 28, 2008 Share Posted July 28, 2008 Post the EXPLAIN output... I'm sure you'll see the WHERE clause is only being applied to the last table. Quote Link to comment Share on other sites More sharing options...
shcKr- Posted July 29, 2008 Author Share Posted July 29, 2008 this is my explain, i think this is what you wanted, all my tables are like this: Field Type Null Key Default Extra id int(5) NO PRI NULL auto_increment title varchar(255) NO NULL productid varchar(99) NO NULL type varchar(100) NO NULL range varchar(255) NO NULL price varchar(50) NO NULL size1 varchar(50) NO NULL size2 varchar(50) NO NULL size3 varchar(50) NO NULL size4 varchar(50) NO NULL size5 varchar(50) NO NULL filename varchar(50) NO NULL filename2 varchar(50) NO NULL Quote Link to comment Share on other sites More sharing options...
shcKr- Posted July 31, 2008 Author Share Posted July 31, 2008 bump? Quote Link to comment Share on other sites More sharing options...
fenway Posted July 31, 2008 Share Posted July 31, 2008 bump? There's a sticky on this forum that describes how to obtain the EXPLAIN output -- you've provided your table definition (also helpful). Quote Link to comment Share on other sites More sharing options...
shcKr- Posted August 1, 2008 Author Share Posted August 1, 2008 i dont wanna sound like a pain in the ass. but i really dont understand what you mean.. i have tried following: http://dev.mysql.com/doc/refman/5.0/en/explain.html but it just ends up confusing a frustrating me.. any chance you could like tell me exactly what to do ? Quote Link to comment Share on other sites More sharing options...
fenway Posted August 5, 2008 Share Posted August 5, 2008 You just need to add the word "EXPLAIN" in front of your query... Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.