preacher715 Posted November 16, 2010 Share Posted November 16, 2010 I'm building a simple employee database... this will be accessible to the call center people as well as any other user with access to a computer... this is to replace the HUGE excel spreadsheet currently being used as our phonebook. I'm having an issue with trying to use the variable (?request) in the SELECT statement when i query the mysql database... I've "googled" this issue and have tried EVERYTHING!!!... I'm lost and need help... any and all help is GREATLY APPRECIATED! Here is my two files.... <--SEARCH.HTM--> <body> <fieldset> <legend>Directory Search</legend> <form action="search.php" methond="post"> Search by Last Name: <input type="text" name="request" /> <input type= "Submit" value="request" /> </form> </fieldset> </body> <--END--> <--SEARCH.PHP--> mysql_connect($hostname, $username, $password) or die("CAN NOT CONNECT TO THE SERVER BECAUSE THE PROGRAMMER SCREWED UP!!!"); @mysql_select_db($database) or die( "HAHAHAHA... CAN NOT CONNECT TO DATABASE BECAUSE THE PROGRAMMER SCREWED UP!!!"); $query=("SELECT Enumber, Fname, Minitial, Lname, Etitle, Gapprove FROM employees WHERE Lname LIKE $request"); $result=mysql_query($query); $num=mysql_numrows($result); mysql_close(); ?> <table border="0" cellspacing="2" cellpadding="2"> <tr> <th><font face="Arial, Helvetica, sans-serif">Employee Number</font></th> <th><font face="Arial, Helvetica, sans-serif">First Name</font></th> <th><font face="Arial, Helvetica, sans-serif">Middle Initial</font></th> <th><font face="Arial, Helvetica, sans-serif">Last Name</font></th> <th><font face="Arial, Helvetica, sans-serif">Title/Position</font></th> <th><font face="Arial, Helvetica, sans-serif">Gaming Approved</font></th> </tr> <?php $i=0; while ($i < $num) { $f1=mysql_result($result,$i,"Enumber"); $f2=mysql_result($result,$i,"Fname"); $f3=mysql_result($result,$i,"Minitial"); $f4=mysql_result($result,$i,"Lname"); $f5=mysql_result($result,$i,"Etitle"); $f6=mysql_result($result,$i,"Gapprove"); ?> <tr> <td><font face="Arial, Helvetica, sans-serif"><?php echo $f1; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><?php echo $f2; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><?php echo $f3; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><?php echo $f4; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><?php echo $f5; ?></font></td> <td><font face="arial, helvetica, sans-serif"><?php echo $f6; ?></font></td> </tr> <?php $i++; } ?> <--END--> Quote Link to comment https://forums.phpfreaks.com/topic/218855-select-queary-using-variable/ Share on other sites More sharing options...
Pikachu2000 Posted November 16, 2010 Share Posted November 16, 2010 In the code you've posted, $request is undefined, and unquoted. Quote Link to comment https://forums.phpfreaks.com/topic/218855-select-queary-using-variable/#findComment-1135012 Share on other sites More sharing options...
preacher715 Posted November 16, 2010 Author Share Posted November 16, 2010 @Pickachu: You referring to it in the SELECT statement? If so, I have tried with all the suggested methods on many websites... I.E. having it as '$request' and etc... I was also hoping to use the wildcard "%" as well with the select statement... Here is another way i've tried it: $query=("SELECT Enumber, Fname, Minitial, Lname, Etitle, Gapprove FROM employees WHERE Lname LIKE "$request"'); Or whats your thoughts on: //Set the variable $var1 = $_POST["user"]; $var2 = $_POST["password"]; // use the '%s' as a place holder and the substitute in the variables. // You can have as many subs as you want, just make sure you list the variables // in the order they come in the statement. $query = sprintf("SELECT * FROM table WHERE user = '%s' AND password = '%s'", $var1, $var2); // Then run the query. $result = mysql_query($query); Would the last code i posted be better...? I'm a newb on PHP/MYSQL... Quote Link to comment https://forums.phpfreaks.com/topic/218855-select-queary-using-variable/#findComment-1135016 Share on other sites More sharing options...
Pikachu2000 Posted November 16, 2010 Share Posted November 16, 2010 When the form is submitted, variables don't magically have values (or at least if the server is configured properly, they shouldn't). You have to either assign the value from the incoming array of submitted values to your own variable, or just use it directly. Since your form used the post method (which is misspelled as methond, BTW), you need to access that value from $_POST array. Since it is also user-submitted data, and as such can't be trusted, it also needs to be sanitized so it can be safely used in the database query. (numeric data is handle somewhat differently than below). Your form's text field is named 'request', therefore its value will be in $_POST['request']. To sanitize the data and assign it to a new value, you can do this. This does not validate that the data is of the correct type, however. It's possible a user could enter '123' and run a search against it, it just wouldn't return any results (well, unless someone's last name was '123abc', of course). $request = trim($_POST['request']); // removes leading/trailing whitespace, etc. $request = mysql_real_escape_string($request); // escapes characters that can be used to break db queries (SQL injection attacks) Once the variable is assigned and sanitized, you can use it in the query string. Since the value is of the string type, it is enclosed in single quotes, and I added a % wildcard to the end of the variable, so a search for 'smi' would return Smith, Smithfield, and Smitts, but not Nesmith. $query=("SELECT Enumber, Fname, Minitial, Lname, Etitle, Gapprove FROM employees WHERE Lname LIKE '$request%'"); Quote Link to comment https://forums.phpfreaks.com/topic/218855-select-queary-using-variable/#findComment-1135030 Share on other sites More sharing options...
preacher715 Posted November 16, 2010 Author Share Posted November 16, 2010 I appreciate your quick replies.... here is how i added everything.... Scold me if necessary... <?php $username="sray"; $password="Password1"; $database="companyDir"; $hostname="srv-tst-sql04"; $request=$_POST['request']; $request = trim($_POST['request']); // removes leading/trailing whitespace, etc. $request = mysql_real_escape_string($request); // escapes characters that can be used to break db queries (SQL injection attacks) mysql_connect($hostname, $username, $password) or die("CAN NOT CONNECT TO THE SERVER BECAUSE THE PROGRAMMER SCREWED UP!!!"); @mysql_select_db($database) or die( "HAHAHAHA... CAN NOT CONNECT TO DATABASE BECAUSE THE PROGRAMMER SCREWED UP!!!"); $query=("SELECT Enumber, Fname, Minitial, Lname, Etitle, Gapprove FROM employees WHERE Lname LIKE '$request'"); $result=mysql_query($query); $num=mysql_numrows($result); mysql_close(); ?> With the % wildcard... it displays the whole table... search criteria is doesn't matter... it just pulls it all on the page... and when i removed the % wildcard... nothing will display when i type a search... lol... Ideas? Quote Link to comment https://forums.phpfreaks.com/topic/218855-select-queary-using-variable/#findComment-1135035 Share on other sites More sharing options...
PFMaBiSmAd Posted November 16, 2010 Share Posted November 16, 2010 Before you go further, please set error_reporting to E_ALL and display_errors to ON in your master php.ini. Stop and start your web server to get any changes made to the master php.ini to take effect and confirm that the two settings actually got changed by using a phpinfo(); statement (in case the php.ini that you changed is not the one the php is using.) Doing this will get php to point out errors that will save you a ton of time. Specifically the non-existent $result variable that Pikachu2000 pointed out would have result in an error and your current problem of using mysql_real_escape_string() before you have a connection to the mysql server would result in an error that would call your attention to what is wrong. Quote Link to comment https://forums.phpfreaks.com/topic/218855-select-queary-using-variable/#findComment-1135039 Share on other sites More sharing options...
Pikachu2000 Posted November 16, 2010 Share Posted November 16, 2010 Before the query is executed, echo the query string to make sure the $request variable has a valid value. echo "<br>Query string: $query <br>"; $result=mysql_query($query); Also, mysql_real_escape_string() needs a connection to the db established for it to function, so simply move the mysql_connect() and mtsql_select_db() calls up to the line immediately following $hostname="srv-tst-sql04";. See what happens after you do that, and then try it with the wildcard again. Quote Link to comment https://forums.phpfreaks.com/topic/218855-select-queary-using-variable/#findComment-1135042 Share on other sites More sharing options...
preacher715 Posted November 16, 2010 Author Share Posted November 16, 2010 IT WORKS!!!.... lol... figured it'd be some simple things i did or didn't do... plus i did stare at the "methond" quite a bit and never noticed the type-o... thanks again guys... Quote Link to comment https://forums.phpfreaks.com/topic/218855-select-queary-using-variable/#findComment-1135044 Share on other sites More sharing options...
Pikachu2000 Posted November 16, 2010 Share Posted November 16, 2010 You're welcome. PFMaBiSmAd makes a very good point about enabling error reporting/display errors also. It's always a good idea to have it enabled when developing, and to log errors to a file when in production. Quote Link to comment https://forums.phpfreaks.com/topic/218855-select-queary-using-variable/#findComment-1135045 Share on other sites More sharing options...
preacher715 Posted November 16, 2010 Author Share Posted November 16, 2010 yeah i need to get the "SERVER KEEPER" to do that... i dont see why he wouldn't... thanks again... Quote Link to comment https://forums.phpfreaks.com/topic/218855-select-queary-using-variable/#findComment-1135048 Share on other sites More sharing options...
Pikachu2000 Posted November 16, 2010 Share Posted November 16, 2010 It can also be done on a per-script basis, it just isn't able to report any fatal runtime parse errors. error_reporting(-1); ini_set('display_errors', 1); Quote Link to comment https://forums.phpfreaks.com/topic/218855-select-queary-using-variable/#findComment-1135056 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.