outchy Posted August 23, 2008 Share Posted August 23, 2008 I have a concert database where I've recently implemented a simple search form. I have it working so I can successfully search for "dateofshow", "headliner", and "venue" by choosing the proper option from a drop down list and typing the search string into a text field. Now, I would like to be able to add "opener" to that drop down list, but the problem is I don't have just one database entry called "opener"... I have six separate entries ("opener1", "opener2", "opener3", "opener4", "opener5" and "opener6"). Is there a way to add a variable called $opener and have it search through all six entries at once, perhaps with some sort of wildcard character at the end of the word opener? My code is below: <? //connect to mysql //change user and password to your mySQL name and password mysql_connect("localhost","root","root"); //select which database you want to edit mysql_select_db("test2"); $search=$_POST["search"]; $type=$_POST["type"]; //get the mysql and store them in $result //change whatevertable to the mysql table you're using //change whatevercolumn to the column in the table you want to search $result = mysql_query("SELECT * FROM shows WHERE $type LIKE '%$search%'"); // Define $color=1 $color="1"; echo '<head>'; echo '<title>Search Results</title>'; echo '<link rel="stylesheet" href="style.css" type="text/css" media="screen" />'; echo '</head>'; echo '<body>'; echo '<center>'; echo '<br /><a href="index.php"><img src="niksshowlist.png" border="0"></a><br /><br />'; echo '<form method="post" action="search.php"> <select name="type" size="1"> <option value="dateofshow">Date</option> <option value="headliner">Headliner</option> <option value="venue">Venue</option> </select> <input type="text" name="search" size=25 maxlength=25> <input type="Submit" name="Submit" value="Search"> </form>'; echo '<table width="740" border="0" align="center" cellpadding="12" cellspacing="0" class="main">'; echo '<tr class="topper"><th>Date</th><th>Headliner</th><th>Venue</th><th>Opener</th></tr>'; //grab all the content while($r=mysql_fetch_array($result)) { //the format is $variable = $r["nameofmysqlcolumn"]; //modify these to match your mysql table columns $dateofshow=$r["dateofshow"]; $headliner=$r["headliner"]; $venue=$r["venue"]; $city=$r["city"]; $state=$r["state"]; $opener1=$r["opener1"]; $opener2=$r["opener2"]; $opener3=$r["opener3"]; $opener4=$r["opener4"]; $opener5=$r["opener5"]; $opener6=$r["opener6"]; if($color==1){ echo "<tr class='bg1'> <td valign=\"top \">".date('Y > M d',strtotime($dateofshow))."</td> <td valign=\"top \">$headliner</td> <td valign=\"top \">$venue<br />$city, $state</td> <td valign=\"top \">$opener1<br />$opener2<br />$opener3<br />$opener4<br />$opener5<br />$opener6</td> </tr>"; // Set $color==2, for switching to other color $color="2"; } else { echo "<tr class='bg2'> <td valign=\"top \">".date('Y > M d',strtotime($dateofshow))."</td> <td valign=\"top \">$headliner</td> <td valign=\"top \">$venue<br />$city, $state</td> <td valign=\"top \">$opener1<br />$opener2<br />$opener3<br />$opener4<br />$opener5<br />$opener6</td> </tr>"; // Set $color back to 1 $color="1"; } } echo '</table><br /></center></body>'; mysql_close($con); ?> Quote Link to comment Share on other sites More sharing options...
Hooker Posted August 24, 2008 Share Posted August 24, 2008 Something like this should do the trick (obviously changing opener1, opener2 etc to your column names): SELECT * FROM shows WHERE LOWER(CONCAT_WS(' ',opener1,opener2,opener3,opener4,opener5,opener6)) LIKE LOWER('%$search%') Quote Link to comment Share on other sites More sharing options...
outchy Posted August 24, 2008 Author Share Posted August 24, 2008 Okay cool, that works if I replace this line... $result = mysql_query("SELECT * FROM shows ORDER BY dateofshow DESC"); with this line... $result = mysql_query("SELECT * FROM shows WHERE LOWER(CONCAT_WS(' ',opener1,opener2,opener3,opener4,opener5,opener6)) LIKE LOWER('%$search%')"); but then my other drop down options won't work anymore. Is there a way to combine them so I can search for dateofshow, headliner, venue, AND opener? Quote Link to comment Share on other sites More sharing options...
fenway Posted August 25, 2008 Share Posted August 25, 2008 Sure, just add more part to the where clause... Quote Link to comment Share on other sites More sharing options...
outchy Posted August 25, 2008 Author Share Posted August 25, 2008 Yeah that works, I'll just forego the drop down altogether. I don't really need it anyhow. Thanks! 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.