SELECT queary using VARIABLE

preacher715 · 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-->

Pikachu2000 · November 16, 2010

In the code you've posted, $request is undefined, and unquoted.

preacher715 · 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...

Pikachu2000 · 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%'");

preacher715 · 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?

PFMaBiSmAd · 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.

Pikachu2000 · 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.

preacher715 · 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...

Pikachu2000 · 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.

preacher715 · November 16, 2010

yeah i need to get the "SERVER KEEPER" to do that... i dont see why he wouldn't... thanks again...

Pikachu2000 · 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);

Sign In

SELECT queary using VARIABLE

Recommended Posts

preacher715

Link to comment

Share on other sites

Pikachu2000

Link to comment

Share on other sites

preacher715

Link to comment

Share on other sites

Pikachu2000

Link to comment

Share on other sites

preacher715

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

Pikachu2000

Link to comment

Share on other sites

preacher715

Link to comment

Share on other sites

Pikachu2000

Link to comment

Share on other sites

preacher715

Link to comment

Share on other sites

Pikachu2000

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information