0

Hello,

I'm trying to create a search engine for my databse. I be able to select multiple tables

$sql = mysql_query("SELECT * FROM table1, table2, table3 WHERE ...");

I was wondering if this even works?

If it does, how do I get rows from different tables?

while($row = mysql_fetch_array($sql)){
$fromtable1 = $row['column1'];
$fromtable2 = $row['column1'];
//etc..
}

Thanks

3
Contributors
14
Replies
15
Views
6 Years
Discussion Span
Last Post by andrewliu
0

Have you tried inner|outer joins? Perhaps that should do the trick...

Exactly what information is your search engine after?

0

This will be sloooow. You can stack similar tables using the UNION keyword. Your statement doesn't really make much sense unless you have a relational model. Even then, it looks a bit weird without the JOIN syntax (it's not essential). Use of * should also be discouraged. This is slow. Name your fields implicitly.

0

through the relational database, you can access to several tables through common columns, such as elements' ids, registry numbers, etc.

Edited by Nichito: n/a

0

Yeah i have relational database. I don't know exactly how unions or joins look like. If I would to use these, how would i output specific rows? I'm trying to output words from my database.

0
$sql_result_query = mysql_query("SELECT * FROM certificates,client,contacts,dicipline,email,forms,marketsector,notes,owner,soq WHERE (product LIKE '%".$search_term."%') LIMIT $first_pos, $RESULTS_LIMIT ");

This is what I have for my code so far. I guess this isn't the best thing to do?

And when I need to output values

while($row = mysql_fetch_array($sql_result_query))
    {

   
        
 echo $row['Name']; //from certificates
 echo $row['PhoneNumber']; //from contacts... etc

How does php know which table to echo from?

Edited by andrewliu: n/a

0

In the first place, you can't do that:

SELECT * 
FROM certificates,client,contacts,dicipline,email,forms,marketsector,notes,owner,soq 
WHERE product LIKE '%@search_term%'

You can instead call specific columns from each separate table and join them with right joins, like this:

SELECT T1.certificate, T2.client_name, T3.contact_name, T3.contact_discipline,...
FROM Products T1
Inner Join Clients T2
   ON
       T1.product_id = T2.product_id
...
WHERE T1.product_name LIKE '%@search_term%'

Remember to compare table columns herever they are related to join tables.

0

Can I do

mysql_query("SELECT.... WHERE products.* LIKE '%@search_term%'");...

where the it is searching everything in the products table. I don't just want to search 1 column, but all of the columns in the table.

0

no you can't. You have to specify which columns you wish to compare, since MySQL cannot process that command.

0

Is there a way at all to get all columns? Or specifying each one is the only way?

Thank you for helping.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.