[PHP] MySQL Database Search

[wiggum123]

Ive got a bit of knowledge of PHP but not enough to construct a working search box for the school site.
I have created a table full of telephone numbers, names and departments. I have created a page that displays the info, all I need is a simple search to find records in a table named telephone.
There are 6 rows
ID (primary) - not really fussed about this one
int_no - Internal Number
ext_no - External Number
mob_no - Mobile Number
name -
other - location / department info

It would be amazing if I could construct some kind of ajax search, but this is not essential.

I can connect to the db and table alright, the problem comes when I start searching. I want the user to be able to search the last 5 rows (I dont want users searching the ID as this will just confuse things) and display the results in a table. This is the code for the table which displays all the info. If I could use the same one that would save so much time!

 <?php
$table = 'telephone';
$query = "SELECT * FROM $table";
$result = mysql_query($query);
$num = mysql_numrows($result);
$i=0;
echo '<table id="sortable" style="width: 100%;" align="center" border="0" cellspacing="0" cellpadding="5" >';
echo "<thead>";
echo "<tr>";
echo '<th style="text-align:center" scope="col">Internal Extension</th> <th style="text-align:center" scope="col">External Number</th> <th style="text-align:center" scope="col">Mobile Number</th> <th style="text-align:center" scope="col">Name / Department</th> <th style="text-align:center" scope="col">Other Info</th>';
echo "</tr>";
echo "</thead>";
echo "<tbody>";
while ($i<$num){
    $int_no=mysql_result($result,$i,"int_no");
    $ext_no=mysql_result($result,$i,"ext_no");
    $mob_no=mysql_result($result,$i,"mob_no");
    $name=mysql_result($result,$i,"name");
    $other=mysql_result($result,$i,"other");
echo "<tr>";
echo '<td style="text-align:center" width="5px">', "$int_no</td>";
echo '<td style="text-align:center" width="100px">', "$ext_no</td>";
echo '<td style="text-align:center" width="100px">', "$mob_no</td>";
echo "<td>$name</td>";
echo "<td> $other</td>";
echo "</tr>";
 
$i++;
};
echo "</tbody>";
echo "</table>";
 
?>  

I have tried searching around to get a working search script but I'm starting to loose the will... Any help would be greatly appreciated

[zag]

just add a $query2 variable to use LIKE whatever database field you want to search and loop the result 5 times only

[wiggum123]

Thanks Zag
How do I link the Like to a form search box?

[zag]

Create a form, with a search box in it.

In the form action properties load the same page when you submit the form but assign the $query2 variable to the SQL statement instead of the current $query

[wiggum123]

This is what I have changed, what am I doing wrong?

<form id="directory_search" name="directory_search" method="GET" action="telephone-search.php">
  <label for="search_directory">Search:</label>
  <input type="text" name="search_directory" id="search_directory" />
  <input type="submit" name="dir_search" id="dir_search" value="Submit" />
</form>
<?php
$dir_search = ($_POST['search_directory']);
$table = 'telephone';
$search = "SELECT * FROM $table WHERE int_no LIKE $dir_search";
$result = mysql_query($search);
$num = mysql_numrows($result);
....bla bla bla  

[webman]

There are 6 rows
ID (primary) - not really fussed about this one
int_no - Internal Number
ext_no - External Number
mob_no - Mobile Number
name -
other - location / department info

These are your columns; not rows - the rows are the actual records/data that's inserted and stored there.

I want the user to be able to search the last 5 rows (I dont want users searching the ID as this will just confuse things) and display the results in a table.

Got ya! You want them to be able to search for something, and have the DB look up data matching this; but only in the int_no, ext_no, mob_no, name, other fields. Yes? OK...

Your query should look something like this:

SELECT * FROM telephone 
WHERE (int_no LIKE '%$dir_search%') 
OR (ext_no LIKE '%$dir_search%') 
OR (mob_no LIKE '%$dir_search%') 
OR (name LIKE '%$dir_search%') 
OR (other LIKE '%$dir_search%')

Hope that helps

[budgester]

This is what I have changed, what am I doing wrong?

<form id="directory_search" name="directory_search" method="GET" action="telephone-search.php">
  <label for="search_directory">Search:</label>
  <input type="text" name="search_directory" id="search_directory" />
  <input type="submit" name="dir_search" id="dir_search" value="Submit" />
</form>
<?php
$dir_search = ($_POST['search_directory']);
$table = 'telephone';
$search = "SELECT * FROM $table WHERE int_no LIKE $dir_search";
$result = mysql_query($search);
$num = mysql_numrows($result);
....bla bla bla  

Ok your using different request variables. $_GET and $_POST

Try dropping in some debugs.

var_dump($_GET);
var_dump($_POST);
var_dump($_REQUEST);

Next why user $table variable if it is only used once, hard code it into the SQL string.

Try changing

$search = "SELECT * FROM $table WHERE int_no LIKE $dir_search";

to

$search = "SELECT * FROM $table WHERE int_no LIKE " .$dir_search;

It's a bit cleaner and should parse better as the variable is not embeded in the string.

And this script is definately insecure, see

http://xkcd.com/327/

[wiggum123]

Hey Guys

Webman
Thanks for the code, I've put that in and it works perfectly. Thank you very much!!

budgester
Thanks for pointing out the $POST and $GET issue, I had changed it at the top within the form, but I hadnt updated it within the PHP. I have also put the table name into the SQL query. That was left over from another version of the code, just hadnt updated it yet. As for the security issue, there shouldn't be any way of anyone adding data to this table unless they are authorised, and as I'm the only one with access to the table, this cant happen....right?

[budgester]

Try it in the search box put "test; INSERT into telephone...... 'the rest of the insert statement';"

Or be evil to yourself and try "test; DROP Table telephone;"


Read

AskBee.NET | php | SQL Injection | sql injection

and

PHP: mysql_real_escape_string - Manual

You might also want to look at permissions on the database etc...

Welcome to the nasty world of web based sql. Have fun.

Now the question you have to ask is... do you care ?

[powdarrmonkey]

Try it in the search box put "test; INSERT into telephone...... 'the rest of the insert statement';"

Or be evil to yourself and try "test; DROP Table telephone;"

PHP injection is actually a bit more subtle than that; the default driver doesn't allow you to run two statements in the same call for exactly that reason.