how to SEARCH a record from multiple tables using mysql

[sogate]

Hi everyone,

I would like to ask your expertise.
The code writtten to search for a record(name) from multiple tables is this:

$query="SELECT * FROM `table1`,`table2` WHERE `table1.name,table2.name` like'". $_POST["searchword"]."' ;";

This is as far as I know. And the tables are independent of each other. But when the <search> is being tested, the error that appears is: "Could not connect: Unknown column 'table1.name,table2.name' in 'where clause' ".
What would be the possible solution for this?

Thanx in advance

[emailroy2002]

Hi everyone,

I would like to ask your expertise.
The code writtten to search for a record(name) from multiple tables is this:

$query="SELECT * FROM `table1`,`table2` WHERE `table1.name,table2.name` like'". $_POST["searchword"]."' ;";

This is as far as I know. And the tables are independent of each other. But when the <search> is being tested, the error that appears is: "Could not connect: Unknown column 'table1.name,table2.name' in 'where clause' ".
What would be the possible solution for this?

Thanx in advance

$search = $_POST['searchword'];
$query = "SELECT t1.id,name1,t2.id,t2.name2 FROM t1 left join t2 on t1.id= t2.id where table1.name like $search and table1.name like $search";
or
$query = "SELECT t1.id,name1,t2.id,t2.name2 FROM t1 left join t2 on t1.name = t2.name where table1.name like $search";

[vern]

On a side note, I hope you seriously aren't using $_POST directly.

[xiao_xiao]

On a side note, I hope you seriously aren't using $_POST directly.

mao...
vulnerable kaayo na ug sql injection...

here's a sample code to prevent your page from sql injection

if(get_magic_quotes_gpc()) {
            $product_name        = stripslashes($_POST['product_name']);
            $product_description = stripslashes($_POST['product_description']);
}

else {
            $product_name        = $_POST['product_name'];
            $product_description = $_POST['product_description'];
}

// Make a safe query
$query = sprintf("INSERT INTO products (`name`, `description`, `user_id`) VALUES ('%s', '%s', %d)",
                    mysql_real_escape_string($product_name),
                    mysql_real_escape_string($product_description),
                    $_POST['user_id']); 

[sogate]

Hi everyone,

I would like to ask your expertise.
The code writtten to search for a record(name) from multiple tables is this:

$query="SELECT * FROM `table1`,`table2` WHERE `table1.name,table2.name` like'". $_POST["searchword"]."' ;";

This is as far as I know. And the tables are independent of each other. But when the <search> is being tested, the error that appears is: "Could not connect: Unknown column 'table1.name,table2.name' in 'where clause' ".
What would be the possible solution for this?

Thanx in advance

by the way, the situation here is this: first, the name is being searched in table 1 and if not found, then it will search the name to other table which is table 2. I hope this will clarify things.

[personalmgt]

snip

Thanks for this one. I regularly use POST for variables used once and i'm gonna stop using it. It never came into my mind that using POST is vulnerable to injection however I was critical about sql injection.

By the way, on original post. You can use something like SELECT a.Name, b.Address FROM Name_Details AS a, Address_Details AS b WHERE <insert conditions here>. You can use this method so you won't have to retype the table name all over again.

[sogate]

Thanks for this one. I regularly use POST for variables used once and i'm gonna stop using it. It never came into my mind that using POST is vulnerable to injection however I was critical about sql injection.

By the way, on original post. You can use something like SELECT a.Name, b.Address FROM Name_Details AS a, Address_Details AS b WHERE <insert conditions here>. You can use this method so you won't have to retype the table name all over again.

Thanx bro, but can you please give more details on the 'where clause' because i think the main error is found there.

[sogate]

mao...
vulnerable kaayo na ug sql injection...

here's a sample code to prevent your page from sql injection

if(get_magic_quotes_gpc()) {
            $product_name        = stripslashes($_POST['product_name']);
            $product_description = stripslashes($_POST['product_description']);
}

else {
            $product_name        = $_POST['product_name'];
            $product_description = $_POST['product_description'];
}

// Make a safe query
$query = sprintf("INSERT INTO products (`name`, `description`, `user_id`) VALUES ('%s', '%s', %d)",
                    mysql_real_escape_string($product_name),
                    mysql_real_escape_string($product_description),
                    $_POST['user_id']); 

Thanx very much bro for giving tips on the vulnerability issue regarding sql injection.

[personalmgt]

Use the OR condition instead of comma or "AND"?

WHERE table1.name LIKE $_POST["searchword"] OR table2.name LIKE $_POST["searchword"]

PS, dont use the post(in my WHERE clause) above. be creative

[sogate]

Use the OR condition instead of comma or "AND"?

WHERE table1.name LIKE $_POST["searchword"] OR table2.name LIKE $_POST["searchword"]

PS, dont use the post(in my WHERE clause) above. be creative

oh of course i tried OR and AND already but same error appears : Could not connect: Unknown column 'name' in 'where clause'