conan318 Posted April 20, 2011 Share Posted April 20, 2011 hi iam trying to make a simple search form to search the members tables based on there input. iam new php so most of my code is guess work <form method="GET" action="<?php echo $_SERVER['PHP_SELF'];?>" name="searchForm"> <label for="country">Country:</label> <input type="text" name="country" /> <label for="state">State:</label> <input type="text" name="state" /> <label for="city">City:</label> <input type="text" name="city" /> <input type="submit" value="submit" /> </form> <? if ($_GET == array ()) { //Define a variable that will be used to query the members; in this case, it would select all members $query = "SELECT * FROM users"; } else { //If the user typed at least one thing (in the form OR the url) if (count ($_GET) == 1) { //If what they typed is only for one criteria, define a variable that creates a query for only ONE criteria to search for $query = "SELECT * FROM users WHERE 1"; foreach ($_GET as $field => $value) { $query .= " AND $field = '$value'"; } //If the user has typed in more than one field and hits search } else { //Define a variable for a query that selects members based off each criteria $query = "SELECT * FROM users WHERE 1"; foreach ($_GET as $field => $value) { $query .= " AND $field LIKE '%$value%'"; } } while($info = mysql_fetch_array( $data )) { Echo "<img src='http://datenight.netne.net/images/".$info['img'] ."' width='150' height='250''> <br>"; Echo "<b>Name:</b> ".$info['username'] . "<br> <hr>"; Echo "<b>Sex:</b> ".$info['sex'] . " <br><hr>"; Echo "<b>Intrested in</b>" . "<br><hr>"; Echo "".$info['rel'] . " "; Echo "".$info['frwb'] . " "; Echo "".$info['ons'] . " "; Echo "".$info['fr'] . "<br><hr>"; Echo "<b>About me:</b> ".$info['aboutme'] . "<br><hr> "; Echo "<b>Looking for:</b> ".$info['looking'] . " <br><hr>"; Echo "<a href='login_success.php'>'Back'</a>"; } ?> </body> </html> while($info = mysql_fetch_array( $data )) { is not vaild error Quote Link to comment Share on other sites More sharing options...
KevinM1 Posted April 20, 2011 Share Posted April 20, 2011 You never actually run your query. $data is undefined. Quote Link to comment Share on other sites More sharing options...
conan318 Posted April 20, 2011 Author Share Posted April 20, 2011 <form method="GET" action="<?php echo $_SERVER['PHP_SELF'];?>" name="searchForm"> <label for="country">Country:</label> <input type="text" name="country" /> <label for="state">State:</label> <input type="text" name="state" /> <label for="city">City:</label> <input type="text" name="city" /> <input type="submit" value="submit" /> </form> <? $query = mysql_query("SELECT * FROM users ;") or die(mysql_error()); //Puts it into an array if ($_GET == array ()) { //Define a variable that will be used to query the members; in this case, it would select all members $query = "SELECT * FROM users"; } else { //If the user typed at least one thing (in the form OR the url) if (count ($_GET) == 1) { //If what they typed is only for one criteria, define a variable that creates a query for only ONE criteria to search for $query = "SELECT * FROM users WHERE 1"; foreach ($_GET as $field => $value) { $query .= " AND $field = '$value'"; } //If the user has typed in more than one field and hits search } else { //Define a variable for a query that selects members based off each criteria $query = "SELECT * FROM users WHERE 1"; foreach ($_GET as $field => $value) { $query .= " AND $field LIKE '%$value%'"; } } while($info = mysql_fetch_array( $query )) { Echo "<img src='http://datenight.netne.net/images/".$info['img'] ."' width='150' height='250''> <br>"; Echo "<b>Name:</b> ".$info['username'] . "<br> <hr>"; Echo "<b>Sex:</b> ".$info['sex'] . " <br><hr>"; Echo "<b>age:</b> ".$info['age'] . " <br><hr>"; Echo "<b>country:</b> ".$info['country'] . " <br><hr>"; Echo "<b>city:</b> ".$info['city'] . " <br><hr>"; Echo "<b>Suburb:</b> ".$info['suburb'] . " <br><hr>"; Echo "<b>Intrested in</b>" . "<br><hr>"; Echo "".$info['rel'] . " "; Echo "".$info['frwb'] . " "; Echo "".$info['ons'] . " "; Echo "".$info['fr'] . "<br><hr>"; Echo "<b>About me:</b> ".$info['aboutme'] . "<br><hr> "; Echo "<b>Looking for:</b> ".$info['looking'] . " <br><hr>"; Echo "<a href='login_success.php'>'Back'</a>"; } } ?> </body> </html> now its just returning every record in the database where am i going wrong? Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted April 20, 2011 Share Posted April 20, 2011 don't use SELECT * - explicitly list each field name. That said - have you tried doing a vardump of the $_GET array after the search button is clicked to make sure you are getting the right values passed? Quote Link to comment Share on other sites More sharing options...
conan318 Posted April 20, 2011 Author Share Posted April 20, 2011 ive been mucking around with it for a few hours now i have 3 input values to search the database which works fine if all 3 values are set but if one value is left null how do tell to ignore the null value Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted April 20, 2011 Share Posted April 20, 2011 $input1 = $_GET['value1']; $input2 = $_GET['value2']; $input3 = $_GET['value3']; if((trim($input1) == '')&&(trim($input2) == '')&&(trim($input3) == '')){ //do something if all values are empty } elseif((trim($input1) != '')&&(trim($input2) == '')&&(trim($input3) == '')){ //do something if only the 1st value is used } // etc. for all the other permutations Quote Link to comment Share on other sites More sharing options...
maxudaskin Posted April 20, 2011 Share Posted April 20, 2011 Who ever it is that's going round telling everyone to use SELECT * had better keep running! I wish more people would read your signature. It's really something that not many people learn, because it's not really a PHP coding practice, but an SQL coding practice. Quote Link to comment Share on other sites More sharing options...
conan318 Posted April 20, 2011 Author Share Posted April 20, 2011 $input1 = $_GET['value1']; $input2 = $_GET['value2']; $input3 = $_GET['value3']; if((trim($input1) == '')&&(trim($input2) == '')&&(trim($input3) == '')){ //do something if all values are empty } elseif((trim($input1) != '')&&(trim($input2) == '')&&(trim($input3) == '')){ //do something if only the 1st value is used } // etc. for all the other permutations will give that a go cheers Who ever it is that's going round telling everyone to use SELECT * had better keep running! I wish more people would read your signature. It's really something that not many people learn, because it's not really a PHP coding practice, but an SQL coding practice. the * is not needed? Quote Link to comment Share on other sites More sharing options...
maxudaskin Posted April 20, 2011 Share Posted April 20, 2011 Who ever it is that's going round telling everyone to use SELECT * had better keep running! I wish more people would read your signature. It's really something that not many people learn, because it's not really a PHP coding practice, but an SQL coding practice. the * is not needed? SQL Statement Tutorial Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted April 20, 2011 Share Posted April 20, 2011 as I said, explicitly name the fields that you want to select ie. SELECT username, age, country, sex FROM user_table WHERE user_table.userID = 1 of cource you wouldn't actualy store someones age in a table, you would store their date of birth and calculate their age - since a persons age isn't a constant, but you get the idea. Quote Link to comment Share on other sites More sharing options...
conan318 Posted April 20, 2011 Author Share Posted April 20, 2011 $country=$_GET['country']; $state=$_GET['state']; $city=$_GET['city']; $data = mysql_query("SELECT FROM users WHERE users.state='$state' AND users.city = '$city' AND users.country = '$country' "); if((trim($country) == '')&&(trim($state) == '')&&(trim($city) == '')){ //do something if all values are empty } elseif((trim($country) != '')&&(trim($state) == '')&&(trim($city) == '')){ while($info = mysql_fetch_array( $data )) { if one of the value's left the search is still returning nothing Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted April 20, 2011 Share Posted April 20, 2011 you're not actualy selecting anything... $data = mysql_query("SELECT FROM users WHERE users.state='$state' AND users.city = '$city' AND users.country = '$country' "); Quote Link to comment Share on other sites More sharing options...
maxudaskin Posted April 20, 2011 Share Posted April 20, 2011 you're not actualy selecting anything... He obviously did not read the tutorial that I linked. It will take three minutes to read, another minute or two to understand and try out. Yet again, the link is http://www.w3schools.com/sql/sql_select.asp Quote Link to comment Share on other sites More sharing options...
conan318 Posted April 20, 2011 Author Share Posted April 20, 2011 reading tutorial now my bad Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted April 20, 2011 Share Posted April 20, 2011 @ maxudaskin - if you Like my sig you should follow the link on Nightslyr's (he posted neer the beginning of the thread. Quote Link to comment Share on other sites More sharing options...
cyberRobot Posted April 20, 2011 Share Posted April 20, 2011 don't use SELECT * - explicitly list each field name. Out of curiosity, what's wrong with using SELECT *? Of course if you only need a couple fields from the database it's more efficient to list out the columns, but if you need all the columns shouldn't you use SELECT *? Quote Link to comment Share on other sites More sharing options...
maxudaskin Posted April 21, 2011 Share Posted April 21, 2011 don't use SELECT * - explicitly list each field name. Out of curiosity, what's wrong with using SELECT *? Of course if you only need a couple fields from the database it's more efficient to list out the columns, but if you need all the columns shouldn't you use SELECT *? It necessarily uses resources that can be used elsewhere. It's like flooring the gas pedal to get to 40kmh (15mph?). It's just not a smart thing to do, but it will still get you where you want to go in seemingly the same amount of time. @ maxudaskin - if you Like my sig you should follow the link on Nightslyr's (he posted neer the beginning of the thread. I do understand that, but at the same time, it's great for inexperienced users to learn the basics of coding. Once you are getting into more complex ideas, the first place I check is the PHPFreaks Tutorials section. Quote Link to comment Share on other sites More sharing options...
cyberRobot Posted April 21, 2011 Share Posted April 21, 2011 It necessarily uses resources that can be used elsewhere. It's like flooring the gas pedal to get to 40kmh (15mph?). It's just not a smart thing to do, but it will still get you where you want to go in seemingly the same amount of time. Sorry, I'm not sure I understand. Let's say I have a database with 30 fields and I need them all for the page. How does typing them out less resource intensive then using SELECT *? Quote Link to comment Share on other sites More sharing options...
maxudaskin Posted April 21, 2011 Share Posted April 21, 2011 It necessarily uses resources that can be used elsewhere. It's like flooring the gas pedal to get to 40kmh (15mph?). It's just not a smart thing to do, but it will still get you where you want to go in seemingly the same amount of time. Sorry, I'm not sure I understand. Let's say I have a database with 30 fields and I need them all for the page. How does typing them out less resource intensive then using SELECT *? If you need every single one of them, then it's better to use the asterisk. Otherwise, the amount of effort it takes the computer to parse the SQL is less than searching through the database, finding multiple rows, parsing those rows into a result, then php having to put all of those extra columns that you don'r need into the array, and then you just dump the array. It's better to use CPU power than RAM. Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted April 21, 2011 Share Posted April 21, 2011 It is never "better" to use SELECT * - you are wasting resources, opeinging up security vulnrabilities and giving up control over the data that you are retrieving - thus reducing scalability of your query. Just don't do it. Quote Link to comment Share on other sites More sharing options...
conan318 Posted April 21, 2011 Author Share Posted April 21, 2011 $data = mysql_query("SELECT username, country, state, city FROM users WHERE users.state='$state' AND users.country='$country' AND users.city='$city' "); if(((trim($country) == ''&&($state) == ''&&($city) == '')){ //do something if all values are empty Echo "u must enter a value"; } elseif((trim($country) !== ''&&($state) !== ''&&($city) !== '')){ $data = mysql_query("SELECT username, country, state, city FROM users WHERE users.state='$state' AND users.country='$country' AND users.city='$city' "); } elseif((trim($country) !== ''&&($state) == ''&&($city) == '')){ $data = mysql_query("SELECT username, country, state, city FROM users WHERE users.country='$country'"); } elseif((trim($country) == ''&&($state) !== ''&&($city) == '')){ $data = mysql_query("SELECT username, country, state, city FROM users WHERE users.state='$state'"); } elseif((trim($country) !== ''&&($state) !== ''&&($city) == '')){ $data = mysql_query("SELECT username, country, state, city FROM users WHERE users.state='$state' AND users.country='$country'"); } elseif((trim($country) !== ''&&($state) == ''&&($city) !== '')){ $data = mysql_query("SELECT username, country, state, city FROM users WHERE users.country='$country' AND users.city='$city'"); } elseif((trim($country) == ''&&($state) !== ''&&($city) !== '')){ $data = mysql_query("SELECT username, country, state, city FROM users WHERE users.state='$state' AND users.city='$city'"); while($info = mysql_fetch_array( $data )) { im still having trouble with this basic search form i think im close now. ive been able to get to work with when someone searchers city country and state, and country and state but when i add the rest of the if statement it returns nothing Quote Link to comment Share on other sites More sharing options...
conan318 Posted April 21, 2011 Author Share Posted April 21, 2011 fixed it closed the last elseif statement in the wrong place thanks your help Quote Link to comment Share on other sites More sharing options...
cyberRobot Posted April 21, 2011 Share Posted April 21, 2011 You could streamline the code by doing something like (the code is untested): <?php ... //REMOVE EXTRA SPACE $country = trim($country); $state = trim($state); $city = trim($city); //PREPARE SQL QUERY $sql = "SELECT username, country, state, city FROM users WHERE"; $connector = ''; if($country != '') { $sql .= $connector . " users.country='$country'"; $connector = ' AND'; } if($state != '') { $sql .= $connector . " users.state='$state'"; $connector = ' AND'; } if($city != '') { $sql .= $connector . " users.city='$city'"; $connector = ' AND'; } //RUN THE QUERY $data = mysql_query($sql); while($info = mysql_fetch_array( $data )) { ... ?> Also note that the "users." part in the WHERE clause isn't needed if you don't plan to use a SQL Join. Note that you should look into alternatives to using $_SERVER['PHP_SELF']. There are security risks involving its use. http://www.google.com/search?q=php_self+security+issues I usually just type the page name instead. Some say you can leave action attribute blank, but I've heard leaving it blank also has some security risks. I haven't heard what those risks are though. I apologize for assisting in the slight takeover of the thread with all the SELECT * talk. I'm just very curious on the why. 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.