PHP and MySQL search engine

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
suz_1234
Forum Newbie
Posts: 24
Joined: Sat Sep 04, 2004 11:22 pm

PHP and MySQL search engine

Post by suz_1234 »

Hi...

I am trying to develop a search engine using php and my database is mysql.. My question is: How does a search engine work? If I have four tables in a database, will it search thru all four tables or I can only query one table??

Thanks,
Breckenridge
Forum Commoner
Posts: 62
Joined: Thu Sep 09, 2004 11:10 pm
Location: Breckenridge, Colorado

Post by Breckenridge »

The search works by your code pulling the proper data from your database tables.

Code: Select all

$query = "select * from user_data where first_name ='John' and last_name='green' ";
You can also pull data from more than one table at a time example:

Code: Select all

$Query = "select user_data.*, data1.notes from user_data, data1 where user_data.uid=data1.user_id";
for more information about mysql selects i recommend reading:

http://www.mysql.com/search/?q=select&c ... iso-8859-1
User avatar
Calimero
Forum Contributor
Posts: 310
Joined: Thu Jan 22, 2004 6:54 pm
Location: Milky Way

...

Post by Calimero »

First you use 3 commands to connect to the DB

Code: Select all

<?php
mysql_connect ('your_host','your_user','your_pass');
mysql_select_db('name_of_your_database');
$query = mysql_query("Mysql_code_goes_in_here");
?>
/Note, in mysql_query I used "" - quotes instead '', because you will use '' inside the "" inside SQL code

after that you have to enter the code to get the data that MySQL found and place it into variables to place on the page. Usualy, for simple search engines WHILE is used.


MySQL:

You start like this

Code: Select all

SELECT columns_from_your_tables
FROM names_of_those_tables
WHERE conditions to join or compare several tables and conditions to extract the rows where the conditions are met.

EXAMPLE:

SELECT table1.first_column, table1.second_column, table2.first_column, table2.second column 
FROM table1, table2 
WHERE table1.first_column=table2.first_column and table1.second_column='$variable_from_the_form' order by some_column;
/Note, if you need to calculate the number of rows of let's say table2.second_column you use COUNT(table2.second_column) --- and place it between SELECT and FROM

Hope this helps for basic understanding of mysql and php search engine.
suz_1234
Forum Newbie
Posts: 24
Joined: Sat Sep 04, 2004 11:22 pm

PHP Search engine

Post by suz_1234 »

Hi,

Thanks for helping me out with the previous question. I did get the search engine working using one table at a time. But the search on multiple tables is not quiet working right.

I believe the problem is my SELECT statement. Would someone please take a look at it, and let me know how to write SELECT staement the correct way???

This search engine is trying to search multiple tables at one time. Here's what mine looks like:

Code: Select all

$query = "SELECT $table2.itemDesc, $table3.itemDesc FROM $table2, $table3 WHERE $table2.itemDesc=$table3.itemDesc and $table2.itemDesc LIKE "%$trimmed%" order by $table2.itemName";
$trimmed is the variable from the textfield.

Any sort of help is appreciated.
Thank you,
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

there isn't much point in selecting both itemDesc's when they are required to be the same..
suz_1234
Forum Newbie
Posts: 24
Joined: Sat Sep 04, 2004 11:22 pm

Select statement MySQL

Post by suz_1234 »

ok

Here's what my code looks like now,

Code: Select all

$query = "SELECT $table2.itemDesc, $table3.itemDesc FROM $table2, $table3 WHERE $table2.itemDesc like"%$trimmed%" OR $table3.itemDesc like"%$trimmed%"";
It outputs each item 12 times from the query..

Please help if you could...

Thanks,
User avatar
Calimero
Forum Contributor
Posts: 310
Joined: Thu Jan 22, 2004 6:54 pm
Location: Milky Way

...

Post by Calimero »

Ok,
To solve this I need following info:

Can you show me the structure of your tables (only these two),

Then, Columns with description should not be compared (to my expirience), because there is a lot of load in the fields that needs to be checked before the match is found. Instead create a new column "id" or something like that, and in it you place only tiny strings (2-3 digit number or something like that) so that comparing and matching goes much faster.
NOTE for this idea you must in both tables have the same ID value for the product description you want to join.

And if possible - tell us your idea - what do you want to accomplish with this query (what type of info are you getting out of the DB), maybe the tables can be better structured.

With the present info, thats all, read ya. :wink:
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

your new select lacks a [mysql_man]join[/mysql_man]ing connection between the tables. With out that, you get all rows in table2 or table3 depending on which one the match happens in.
Post Reply