Jump to content

Archived

This topic is now archived and is closed to further replies.

Squeaker

select questions

Recommended Posts

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!

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites


$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\'"; 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.