PHP and MySQL search engine
Moderator: General Moderators
PHP and MySQL search engine
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,
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
The search works by your code pulling the proper data from your database tables.
You can also pull data from more than one table at a time example:
for more information about mysql selects i recommend reading:
http://www.mysql.com/search/?q=select&c ... iso-8859-1
Code: Select all
$query = "select * from user_data where first_name ='John' and last_name='green' ";Code: Select all
$Query = "select user_data.*, data1.notes from user_data, data1 where user_data.uid=data1.user_id";http://www.mysql.com/search/?q=select&c ... iso-8859-1
...
First you use 3 commands to connect to the DB
/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
/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.
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");
?>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;Hope this helps for basic understanding of mysql and php search engine.
PHP Search engine
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:
$trimmed is the variable from the textfield.
Any sort of help is appreciated.
Thank you,
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";Any sort of help is appreciated.
Thank you,
Select statement MySQL
ok
Here's what my code looks like now,
It outputs each item 12 times from the query..
Please help if you could...
Thanks,
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%"";Please help if you could...
Thanks,
...
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.
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.