select questions

[Squeaker]

Hello to you all, I have a quick question regarding select queries. I searched on the site for answers but could not find anything due to the vast results I kept getting no matter how narrow I made the search.

 

But how do I select information from more then one table? Actually, how do I do this from the whole database?

Also, I am setting up a search on our company site and the text field you type in is called $searchterm, so how do I get the query to use the words stored in $searchterm to look in all tables and then return the results? Everything I\'ve tried has failed me so far.

 

Thanks for the help!

[nabeel21]

From the mysql manual http://www.mysql.com/doc/en/SELECT.html

 

SELECT t1.name, t2.salary FROM employee t1, info t2 WHERE t1.name = t2.name;

 

For the second part...

1. Make a form

2. define an action

3. put your textfield in there

4. make a submit button

 

then on the other page just call the value from the precious page using echo $searchterm;

 

 

--hope this helps. If you need more help, paste your code.

[Squeaker]

So to do my search on the site with the keywords from $searchterm, I would do the following:

 

SELECT * FROM table1, table2, etc WHERE table1, table2, etc LIKE %$searchterm%;

 

Or am I missing something here?

 

Edit:

 

Here is my current code for the results page once the user has input text into the search textarea and pressed submit:

 

 

[php:1:657c526e8a]<?php

$username = \"suser\"; // your username

$password = \"pass\"; // your password

$hostname = \"localhost\"; // usually localhost

 

trim($searchterm);

if (!$searchterm)

{

echo \"You have not entered any search criteria. Please try again.\";

exit;

}

 

$searchterm = addslashes($searchterm);

 

$dbh = mysql_connect($hostname, $username, $password) // define how to connect to the database

or die(\"Unable to connect to MySQL\"); // error message if unable to connect

$selected = mysql_select_db(\"my_database\",$dbh) // what database to connect to

or die(\"Could not select database\"); // error message if unable to select database

$query = \"select query i\'m trying to figure out now\";

$result = mysql_query($query);

while($row = mysql_fetch_row($result))

{

printf(\"<strong>Page ID:</strong> %s\", $row[0]);

printf(\"<p><strong>Page Title:</strong> %s\", $row[1]);

printf(\"<p><strong>Page Content:</strong> %s\", $row[2]);

}

?>[/php:1:657c526e8a]

 

Thanks

[biffta]

you dont need to addslashes as your not going to be inserting the search term into your dbase.

 

Also you will probably have to do

$searchterm = $_POST[\'searchterm\']; 

first as well.

[nabeel21]

$query = "select * from table_name where column_name like \'$searchterm%\'"; 

 

If you want your search to be exactly the same as $searchterm then

 

$query = "select * from table_name where column_name like \'$searchterm\'"; 

[Squeaker]

Ok, so I took some suggestions from here and another forum and here is my entire PHP script for the results page:

[php:1:24ecf0eed6]

<?php

$username = \"user\"; // your username

$password = \"pass\"; // your password

$hostname = \"localhost\"; // usually localhost

 

trim($searchterm);

if (!$searchterm)

{

echo \"You have not entered any search criteria. Please try again.\";

exit;

}

 

$searchterm = $_POST[\'searchterm\'];

 

$dbh = mysql_connect($hostname, $username, $password) // define how to connect to the database

or die(\"Unable to connect to MySQL\"); // error message if unable to connect

$selected = mysql_select_db(\"mydatabase\",$dbh) // what database to connect to

or die(\"Could not select database\"); // error message if unable to select database

$DatabaseTables=array(\"company_background\", \"mission_statement\"); // List all the tables in database

 

$arrwords=explode(\' \',$searchterm);

 

for($i=0;$i<count($arrwords);$i++)

{

for($j=0;$j<count($DatabaseTables);$j++)

{

$query=\"select * from \".$DatabaseTables[$j].\" WHERE page_content LIKE \'%\".$arrwords[$i].\"%\'\";

$result=mysql_query($query); // $result has all the matches in current table

}

}

 

while($row = mysql_fetch_row($result))

{

printf(\"<strong>Page ID:</strong> %s\", $row[0]);

printf(\"<p><strong>Page Title:</strong> %s\", $row[1]);

printf(\"<p><strong>Page Content:</strong> %s\", $row[2]);

}

?>[/php:1:24ecf0eed6]

 

Now when I enter in some text in the text area of some pages and press the search button, it just takes me to the results page and displays nothing.

If I take out the [php:1:24ecf0eed6]while($row = mysql_fetch_row($result))

{

printf(\"<strong>Page ID:</strong> %s\", $row[0]);

printf(\"<p><strong>Page Title:</strong> %s\", $row[1]);

printf(\"<p><strong>Page Content:</strong> %s\", $row[2]);

}[/php:1:24ecf0eed6] lines then I get a result saying \"resource id #5\" or #7 in some cases even though that data isn\'t even in any of my tables. Any help or suggestions would be appreciated.

 

Thanks