[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!

$table = 'telephone';
$query = "SELECT * FROM $table";
$result = mysql_query($query);
$num = mysql_numrows($result);
$i=0;
echo '</pre><table id="sortable" style="width: 100%;" align="center" border="0" cellspacing="0" cellpadding="5">';
echo "";
echo "";
echo 'Internal Extension External Number Mobile Number Name / Department Other Info';
echo "";
echo "";
echo "";
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 "";
echo '', "$int_no";
echo '', "$ext_no";
echo '', "$mob_no";
echo "$name";
echo " $other";
echo "";

$i++;
};
echo "";
echo "</table>";<br><br>?&g

 

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?

 

</pre><form id="directory_search" name="directory_search" method="GET" action="telephone-search.php">
 Search:
 
 
</form><br>$dir_search = ($_POST['search_directory']);<br>$table = 'telephone';<br>$search = "SELECT * FROM $table WHERE int_no LIKE $dir_search";<br>$result = mysql_query($search);<br>$num = mysql_numrows($result);<br>....bla bla b

[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

Edited November 25, 2010 by webman
Re-read question :)

[budgester]

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

 

</pre><form id="directory_search" name="directory_search" method="GET" action="telephone-search.php">
 Search:
 
 
</form><br>$dir_search = ($_POST['search_directory']);<br>$table = 'telephone';<br>$search = "SELECT * FROM $table WHERE int_no LIKE $dir_search";<br>$result = mysql_query($search);<br>$num = mysql_numrows($result);<br>....bla bla b

 

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.