Easy search method?

[Radon3k]

I know you can do the following to do a search:

<?php
$srch = mysql_query("SELECT * FROM tbl_nm WHERE field LIKE %$search% OR field LIKE %$search%")?>

However, I've got 4 tables with numerous fields in each. Is there a way I can easily search the entire database without having to type all of that in? That would take forever. Thanks. 🙂

[OhLordy]

Depends how you want to search teh database.
You could use left joins. They are quick to write and just dump everything from all the tables really. but they are ineffeciant and slow to perform. It may be tedious to write four different queries and then analyse the results in PHP but it is often quicker.

Hope this Helps
Rob
PS
SELECT *
FROM
table_one
left join table_two on table_two.unique_id=table_one.same_key

[snaack]

A (dangerous) trick I once used:

Give your form-elements the exact name of that searchable field in your database. Next, dynamically build your query by looping through the post-vars.

Something like this:

	<?
	$first = true;
	$sql = "SELECT * from tblFoo ";
	$glue = "WHERE ";

foreach($_POST as $key => $value) {
	if ($first)
		$first = false;
	else
		$glue = "AND ";
	$sql .= $glue . $key . " LIKE '" . $value . "' ";			
}
?>

There is a nicer and much better way, query the database. I'm working right now, but I could write it out for you later.

[Radon3k]

I honestly don't understand either method that was posted. OhLordy, I'll look more up on left joins but will this produce what I want it to? Basically this is what is going to happen: When a user types in text it will then search the four tables for matching text and display only that row in the database.

[snaack]

why don't you try out my example? It dynamically builds a SQL-string, just setup your searchform correctely..

it's a bit hard coded though 😃

[Radon3k]

No offense, but I like to understand what I'm using before I use it. This program isn't for me and before I present it to the person I'm doing it for I'd like to be able to understand it. I will try it though, but I may not use it until I have a full understanding of what it does. 🙂

[snaack]

Originally posted by Radon3k
No offense, but I like to understand what I'm using before I use it. This program isn't for me and before I present it to the person I'm doing it for I'd like to be able to understand it. I will try it though, but I may not use it until I have a full understanding of what it does. 🙂

But ofcourse 😃

This script is not so hard to understand, if you create a form in which the textboxes have the name of the corresponding field in the database you can use my script (edit it first though, build in validation checks) to dynamically build a query.

[Radon3k]

There is only one text field in the form. All it needs to do is cross-check the word typed into the text box with anything that matches it in the database. If it finds something, it will return that entire row.

[snaack]

😕 how many fields do you want to search?

And could you post a dump of your database structure? If you're using mySql you can use bin/mysqldump

[OhLordy]

In retrospect snaaks answer would be more helpfull. Post up your database structures and it won;t take a second to knock up a little loop so search.
As it was snaak's idea I'll be gracious and let him 😃 😉

[Radon3k]

Thanks guys. How exactly would I create a dump of the db structure and post it here? Is it doable via phpMyAdmin? Thanks. 🙂

[snaack]

if you're using MySQL go to mysql/bin and type mysqldump [name database] -d -r [filename to dump to]

[Radon3k]

I'm in SSH right now trying to find that. Can I just type this in phpMyAdmin?

[Radon3k]

OK well I just found out that now the guy I'm doing this for only wants 2 fields searched, instead of the entire database. That makes my job much easier. So I have the code and I'm testing it out and now I'm getting this error:

Warning: Supplied argument is not a valid MySQL result resource in /home/sites/site27/web/sql/ryan/sql.php on line 25

Here is the search bit of the code:

<?php
if($_POST['submit'] == "Search") {
	$search = mysql_query("SELECT * FROM accounts,courses,family,members WHERE accounts,courses,family,members LIKE '%$famname%' OR accounts,courses,family,members LIKE '%$famid%'");

$num = mysql_num_rows($search);
if($num > 0) {
	while($row = mysql_fetch_array($search)) {
		echo("Family ID:" . $row['fam_id']);
	}
}
}?>

Line 25 refers to the $num = mysql_num_rows($search); line. I thought I was doing this right. Apparently not. Any suggestions? Thanks. 🙂

[snaack]

there is an error in your query :rolleyes:

[Radon3k]

Well I figured that much 😃

Is it not possible to do what I did? I've never tried a query such as that before so I am unsure. I suppose I'll try some other things, maybe using, "AND" in there. 🙂

[snaack]

I coulp help you with your query if you post your databasestructure here..

[Radon3k]

Sorry, I didn't realize you still needed the database structure. Here you go, see attached zip file. There are four tables. 🙂

[OhLordy]

Your database structure is not very clear at all.
Firstly you have No Primary keys in any of your tables :eek:
Secondly you have the id's being used in all different tables in such a way that it makes havin different tables almost pointless.
Take a look here

http://www.phpbuilder.com/columns/barry20000731.php3

Good Luck
Rob

[Radon3k]

I didn't set the database up. I just have to work with this. Anyways, is there a way that I can get the query to work? I really do appreciate the help. 🙂