Jump to content

select questions


Squeaker

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!

Link to comment
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.

Link to comment
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

Link to comment
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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.