Jump to content


Photo

select questions


  • Please log in to reply
5 replies to this topic

#1 Squeaker

Squeaker
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 29 December 2003 - 04:27 PM

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!

#2 nabeel21

nabeel21
  • Members
  • PipPip
  • Member
  • 25 posts

Posted 29 December 2003 - 06:35 PM

From the mysql manual http://www.mysql.com.../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.

#3 Squeaker

Squeaker
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 29 December 2003 - 07:32 PM

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

#4 biffta

biffta
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 29 December 2003 - 09:12 PM

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.

#5 nabeel21

nabeel21
  • Members
  • PipPip
  • Member
  • 25 posts

Posted 30 December 2003 - 01:16 AM

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


#6 Squeaker

Squeaker
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 30 December 2003 - 08:09 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users