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?

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