I have a list of checkboxes on my website that let you search my database via genre. I have it search the database and it comes back with the right infomation but if a Anime_Name has Action='1' and Adventure='1' it will show up on the list twice because of the two MySQL querys. I am looking for a way around that or a better way to do what I am trying to accomplish. I am fairly new to PHP and MySQL so I'm sure there is a easier way. The HTML looks like this. <input type="checkbox" name="Action" value="1"> <a href="#Action">Action</a> <input type="checkbox" name="Adventure" value="2"> <ahref="#Adventure">Adventure</a> Code (markup): Then I have in my php if (isset($_GET['submit'])){ if (isset($_GET['Action'])) { $query = mysql_query("SELECT Anime_Name FROM Anime WHERE Action='1'"); $num_rows = mysql_num_rows($query); if($num_rows > 0) { while($row = mysql_fetch_assoc($query)){ echo $row['Anime_Name']."</br>"; } } } if (isset($_GET['Adventure'])) { $query = mysql_query("SELECT Anime_Name FROM Anime WHERE Adventure='1'"); $num_rows = mysql_num_rows($query); if($num_rows > 0) { while($row = mysql_fetch_assoc($query)) { echo $row['Anime_Name']."</br>"; } } } } Code (markup): Ideally I would have them in a array like this and save my self the trouble of having 32 if statments <input type="checkbox" name="genre_check[]" value="1"> <a href="#Action">Action</a> Code (markup): but I have no idea how I could turn that array into a MySQL Query that could search multiple genres if they choose more then one to search with. Any help would be appreciated. Thanks In case you wanted to look at it you can view the page www.redplanetanime.com/search.html Edit: OMG I can submit links now! The forums knows I'm not a bot! *tear*
Yep, you shouldn't use so many if statements. Actually, you should use switch statement. But running different queries for every category users checked is...well...silly. No offense. It would be great to see your database structure because it seems you do have columns for every category. If you have a chance, redesign your db. There should be only one category column storing the data. This way you can search with one single query instead of the many you do this way and the size of the db is also lower.
Yea my database has nothing in it as of now and I am open to redo it. I knew that the approach I was taking was not the right one and was looking for some one more experience with designing databases to help. Here is what my database currently looks like... (Couldn't figure out how to get describe to work in phpMyAdmin) [TABLE="class: data ajax"] [TR] [/TR] [/TABLE] ID,Anime_Name,Info_Page,Poster,Type,Year,Status,Summary,Action,Adventure,Comedy,Demons,Drama,Ecchi,Fantasy,Harem,Horror,Magic,Martial_Arts,Mecha,Music,Mystery,Ninja,Parody,Psychological,Reverse_Harem,Romance,Samurai,School_Life,Science_Fiction,Seinen,Shoujo,Shounen,Slapstick,Slice_of_Life,Sports,Supernatural,Thriller,Tragedy,Vampire,Movie,OVA,Top_Rated,Currently_Airing,Ended,Ongoing Code (markup): So indeed I do have many. How would I go about storing all of them in one column? Would the person still be able to pick multiple genres?
Build a proper SQL statement: $where = ''; if (isset($_GET['submit'])){ $sql = 'SELECT Anime_Name FROM Anime'; if (isset($_GET['Action'])) { $where = ' WHERE Action=\'1\''; } if (isset($_GET['Adventure'])) { if(strlen($where) > 0) { $where .= ' AND Adventure=\'1\''; } else { $where = ' WHERE Adventure=\'1\'; } } $query = mysql_query($sql.$where); $num_rows = mysql_num_rows($query); if($num_rows > 0) { while($row = mysql_fetch_assoc($query)) { echo $row['Anime_Name']."</br>"; } } } PHP:
See, I had something like this pictured in my head where the sql statment was dynamic to what was being searched for but had no way of actually producing it. Seeing it out like this is a big help as to understanding what is really possible. Its late here and I will try this tomorrow. Thanks for your suggestion!
<?php if(isset($_GET['submit'])) { $query="SELECT Anime_Name FROM Anume WHERE "; } if(isset($_GET['Action'])) { $query.= "Action='1' AND "; } if(isset($_GET['Adventure'])) { $query.= "Adventure='1' AND "; } $pos = strpos($query, "AND"); if ($pos === false) { $newquery=substr($query,0,-7); } else { $newquery=substr($query,0,-4); } $result=mysql_query($newquery); mysql_num_rows($result>0) { while($row = mysql_fetch_assoc($result)) { echo $row['Anime_Name']."</br>"; } ?>
Because eventually when I get the core functions of the search down there will be links there that link to just that genre in general, right now they are just place markers.
@developer.designer: If you ever end up with a dozen possible choices, your code is going to get so complex that you won't be able to figure it out the morning after you wrote it. Remember - you may have to come back and modify it some day, so you want it logical and just as simple as possible (but no simpler either).
if you do not mind you can give me your database dump with a few data then I can give you more accurate suggestion about your problem. Here your query is good, if "Action" is selected then action is in process or if "Adventure" then .... it will give all rows where "Adventure" or "Action" == 1. there is no chance both query are executing. webshore88
Currently everything is working pretty nicely. Now I just need to find a way to get links to populate my database. I really do want to thank everyone that helped in this thread. You saved me a day or two of headaches. Just for anyone who finds this thread searching for what I was trying to make I used Rukbat's code and made a few tweaks so it would work perfectly with my database. You can view the finished product at my website (http://www.redplanetanime.com) at least when its finished, right now it just shows test data in the database I'm sure I will be back with future problems that I can't even imagine yet. So long and thanks for all the fish!
To throw a new question on the fire... How would I go about sorting the results from the genre in alphabetical order, not like ORDER BY but by having each letter in its own div with a anchor points at the top. Kind of like this. A|B|C|D|E|F... ----- A ----- AAB ABA ABB ABC ----- B ----- BAA BBA BCB BCC Code (markup): The only way I can think of is by doing a LIKE query for each genre but that would be extremely inefficient. Was hoping some one else had an idea that I could work on and get working. Thanks
Sorry, I though I explained it well. My first problem has already been solved and is working perfectly, I just didn't want to open a new thread when this is relevant to the last question. What I am trying to accomplish now is how to sort the results into a alphabetical list, like... EG. A|B|C|D|E|F... ----- A ----- AAB ABA ABB ABC ----- B ----- BAA BBA BCB BCC Code (markup): I know this can be accomplished by using ORDER BY but that would just make one huge list and I was trying to get all the A's into one div, the B's into another div, and so on. That way I can put anchor points at the top and then when the user clicks on them they are linked to that section of the page. I don't know any other way to explain it, sorry.
sorry mate I was busy around here Ok your explanation is good, now here is your solution $collect = array(); // array where you will collect your name seperately // array1 is an example array as like your fetched data array from DB $array1[0] = "atul"; $array1[1] = "ankur"; $array1[2] = "ansu"; $array1[3] = "bisu"; $array1[4] = "biswas"; $array1[5] = "chnachal"; $array1[6] = "divya"; $array1[7] = "ganga"; // $appAlp is the array where all alphabets have stored{be careful about upper and lower case} $arrAlp = array(0=>'a',1=>'b',2=>'c',3=>'d',4=>'e',5=>'f',6=>'g'); for($i=0;$i<count($arrAlp);$i++){ $count = 0; foreach($array1 as $key=>$value){ if(strpos($value, $arrAlp[$i]) !== 0){ continue; } else{ echo "found in seperate: ".$value."in ".$arrAlp[$i]."<br />"; // you an remove this line, just displaying some information $collect[$i][$count] = $value; } $count++; } } print_r($collect); hope this will help you otherwise ping me back!!!