Page 1 of 1
PHP and MySQL search engine
Posted: Mon Sep 27, 2004 1:51 am
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,
Posted: Mon Sep 27, 2004 2:30 am
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
...
Posted: Mon Sep 27, 2004 2:49 am
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.
PHP Search engine
Posted: Fri Oct 01, 2004 10:32 pm
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,
Posted: Fri Oct 01, 2004 11:54 pm
by feyd
there isn't much point in selecting both itemDesc's when they are required to be the same..
Select statement MySQL
Posted: Sat Oct 02, 2004 4:39 am
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,
...
Posted: Sat Oct 02, 2004 6:01 am
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.

Posted: Sat Oct 02, 2004 10:10 am
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.