get rows with multiple tables in a mysql select

[andrewliu]

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

[ndeniche]

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

Exactly what information is your search engine after?

[diafol]

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.

[ndeniche]

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

[andrewliu]

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.

[ndeniche]

can you post an example of your tables?

[andrewliu]

$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?

[ndeniche]

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.

[ndeniche]

Consider reading some about SQL Joins and relating queries:

[andrewliu]

Okay, let me look into that. Thank you

[andrewliu]

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.

[ndeniche]

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

[andrewliu]

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

Thank you for helping.

[ndeniche]

you have to specify all the columns in order to compare them with the search string

[andrewliu]

okay thanks