MYSQL :: Search Database Query

Date Search Query Help
I'm trying to filter my search results by date. The field name I'm running this for is final_date and is a DATE field.

I want my results to show records that have sales_reps.final_date within the past 30 days.

Query:

SELECT
sales_reps.sr_id,
sales_reps.name,
sales_reps.job_number,
sales_reps.status,
sales_reps.stage,
UNIX_TIMESTAMP(sales_reps.final_date) as final_date,
UNIX_TIMESTAMP(sales_reps.date_to_shop) as date_to_shop,
shop_orders.community,
users.user_id,
u.fname,
u.lname,
users.builder,
users.division,
ei.shop_result,
ei.good_tape,
sa.shopper_id,
shop_orders.order_id,
UNIX_TIMESTAMP(shop_orders.date) as order_date,
pi.fname as pfname,
pi.lname as plname ,
ei.exit_id
FROM sales_reps
LEFT JOIN shop_orders ON sales_reps.order_id=shop_orders.order_id
LEFT JOIN users ON users.user_id=shop_orders.builder_id
LEFT JOIN report_types ON report_types.type_id=sales_reps.report_type
LEFT JOIN shop_assignments as sa ON sa.sr_id=sales_reps.sr_id
LEFT JOIN exit_interviews as ei ON ei.shop_id=sa.shop_id
LEFT JOIN users as u ON u.user_id=sa.shopper_id
LEFT JOIN users as pi ON pi.user_id=sa.pi_id
WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <=UNIX_TIMESTAMP(sales_reps.final_date)
AND (sales_reps.status = 'Completed' OR sales_reps.status = 'Rejected')
ORDER BY users.builder, users.division, community ASC, final_date ASC
Is this:

WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <=UNIX_TIMESTAMP(sales_reps.final_date)
incorrect?

I'm getting dates such as 2007-03-22 and 2007-03-19 in my result set and they shouldn't be there.

Could I get some pointers?

Correct Search Query
Can anyone help me out with this please. I am looking at searching among comma delimited id's stored inside "IDs", trying to figure out what the correct SQL query for that would be. I need to extract the ID where IDs contains the value 7. I know I can't use something like "WHERE IDs LIKE '%7%'" because the result would be inaccurate. So how do I go about it, please? Any directions would be appreciated, thanks!

+------+-----------+
| ID | IDs |
+------+-----------+
| 1 | 3,17,20 |
+------+-----------+
| 2 | 1,7,9,12 |
+------+-----------+

Mysql Search Query
i would like to write a query to seach for the related information to display.

let say the user key in the word "sql", and in one of my keyword field i have data such as "mysql, database, java" and i want to call this row out, how can i do this?

Search Query By Date
I'm trying to adjust an older search query to perform a search by date where the date column is an INT field (unix date stamp).

SELECT * FROM incidents WHERE date < DATE_ADD (CURDATE(), INTERVAL $searchDate DAY);

Where date, formally a DATE field, is now an INT(11) field. $searchDate is an int, indicating how many days back to search. 1 (day), 5 (days), etc.I'm assuming mysql's DATE_ADD function is specific to the DATE field. How can I achieve the same result, but with an INT field?

Query Search Direction
It's possible to tell mysql to start searching rows from highers indexes to lowers?

MySQL Search Query
I have two tables as follows:

table 'question' with fields 'patient_id', 'answer_id' and 'terms', and

table 'answer' with fields 'answer_id' and 'answer_path'.

I need to search question.terms for a keyword match and then join that match to the corresponding foreign key (answer_id) in table answer to return 'answer_path'.

How To Search '+','-' Or '(' In Database
How to search " +, - , ( " signs in mysql database....

Database Search
how to setup a search engine for my database. Right now I have 4 different tables, all relating to each other thru different keys. I currently use a search for where you must use drop downs to fill in text fields. I want to change over to a search system that is just one text field that searches all the fields.

First question, do I need to create an additional table for keywords? If so, how do you keep that up to date? I get photos and information added every hour.

Do I need to look into a full text search or something different than the simple search/where?

Search The Whole Database
Is there a way to search all tables in a database for a keyword using 1 query?

Something like SELECT * FROM ??? WHERE ..ummm you know what,,I cant do it ,,please help

Database Search
I am looking for a query that will search all of my database without having to use all the individual table names and fields as there are 6 tables and over 30 fields in the database is there any special expression for this the search criteria would only be one word.

[PHP, MySQL, FLASH] - Search Query
just a quick question what would be the best way to search a database, how should i query it? i have this so far:

<?
$name=$_GET['name'];
$company=$_GET['company'];
$address=$_GET['address'];
$phone=$_GET['phone'];
$email=$_GET['email'];
$web=$_GET['web'];

mysql_pconnect("host","random","random") or die ("didn't connect to mysql");
mysql_select_db("database") or die ("no database");

$query = "SELECT * FROM table_name WHERE Name, Company = '$name' AND '$company'";
$result = mysql_query( $query ) or die ("didn't query");

$num = mysql_num_rows( $result );
//echo ($num);
if ($num == 1){
while ($line=mysql_fetch_array($result)) {
$profile = "name_show=" . $line['Name'];
$profile = "company_show=" . $line['Company'];
//$profile = "address_show=" . $line['Address'];
//$profile = "phone_show=" . $line['Phone'];
//$profile = "email_show=" . $line['Email'];
//$profile = "web_show=" . $line['Web'];

}
print $profile;
} else {
print "error=Sorry, but I can't show results";
}
?>

it works but not very dynamically, when both companys are commented out in the while and query statements.

What i hope to have is a search where users can type in 1 letter and it will give them a result with all records having that letter in them and just by filling in one item in flash instead of all of them in order to begin the search.

Incorporate OR Operator In Search Query
I have 2 tables, one with restaurants and one with US centroids, latitudes longitudes, zipcodes for varius central cities in the us.

I have a query that I got working, that uses these 2 tables, it selects all restaurant data from table Restaurants city/state/zip... where the restaurant zipcodes are within the dynamic radius specified of the zipcode(centroid) specified.

This works fine, It also returns the distance as well.

I would also like to select restaurants records regardless if the the distance between zipcodes only IF the Restaurant City matched the CIty I pass to the query. I tried adding an OR operator but my query hung.

Here is my query:

Query A Thesaurus For Search Terms?
I was wondering what the best way is to expand on user-inputted search terms.

For instance, when conducting a search, I would like to ALSO query all synonyms and related words to the inputted search terms.

The database my script searches is very large, so inputting my own related words and synonyms alongside every searched value in the database would be a real pain in the neck if I had to go that route.

Is there some sort of downloadable database of all words and their relations I could use?

Query Search In Different Table Columns
I'm trying to set up an sql-query to find a value in the table, which can exist in different columns......

Tire Size Search Query
I have a table of tire sizes... example: 245/40 YR18, 245/40 W18
I need to search through that table, and pick out sizes based on a search that would like like this: P245/40ZR18
I want to find tires based on the 'P245/40' and '18'... any combination of letters between the two are fine....
seems like a regular expression seach?

Query To Search Multiple Fields
I have a table with over 50 columns that contain yes/no values. I want to know if there is a way to write a query to get all fields that have a value of "no".

Search & Replace In Database?
I want to change a website from XHTML to HTML, so I have to get rid of my closing tags, such as...

<meta tags />
<image src="" />
<br />

I can clean up most of my site with Dreamweaver's search and replace function. Is there a way to do something similar with my database, preferably using phpMyAdmin? If so, will it let me use regex (for meta tags and images)?

Also, I know how to use phpMyAdmin's Search function, but is there a way to search for a specific item in a field, like " />" or "<br />"?

String Search In Whole Database
I want to search a string in whole database [not in a table]? Is it possible in MYSQL? If so then how?

How To Search Mysql Database Using Php
in my mysql database table, i have

1. "this is mysql practice search test"
2. "that was my mysql practice search test"

if i use **
"SELECT * FROM `table` WHERE `def` like '%mysql%' order by id"
it returns both rows but ..
if
"SELECT * FROM `table` WHERE `def` like '%mysql my%' order by id"
doesn't return anything.

how to search table with more than one word from that may be in same field or different field of any row of table.?

Very Basic Database Search
Have a very basic database of contacts that I want to make searchable via PHP. Have successfully constructed the database, and can connect and get a basic search, but not sure how to get what I want.

Table name: Business

Fields:
ID (key),Name,Address,City,State,Zip,County,Region,Phone,Fax,Email,Category (4 main categories),Type,Web,Contact

Vision for search page: unified search, possibly with Category field being main selection (checkboxes, maybe), then refining search by City, County, Region, Type only. Results will be set off in a <div> to the right.

Techniques Available To Search A Database
Can someone provide a list of the most common techniques used to search a database for matches based on user-entered keywords etc?

At this point the only SQL syntax I'm aware of to search a db is using the LIKE clause:

SELECT article FROM mytable WHERE article LIKE "php variable";

Is this an efficient enough way, even for a large table? For example, what kind of algorithm do commercial scripts like VB or blog use to search its content?

Database Search Approaches
can i put together a site search tool for a e-comerce site that draws from a mySQL database. They are currently using a tool (Swish-e) that relies on an index of their site.
I don't know a lot about searches & it got me wondering if it is faster to query the database directly or may a daily index and search that, like they are currently doing? I don't believe they get a ton of hits on their search engine. I think they get around 120 orders a day, if you can deduce anything from that.
I'm also curious about the flexibility I would have to modify the way the program searches in the future. If I were using an indexed version it seems like it might be hard to produce advanced searches (all <color> tv's from <manufactuer>)

SQL Query Problems (for Use With A Sort Of Live Search)
I created some code that loaded information from an XML file, but many thanks to jimfraser on here I'm working on a solution that'll drag it directly from the database.

Problem is, it's not displaying anything at all, so I assume there's a problem with the SQL query or the way it's being presented on the page.
PHP
<?php

$network = new COM("WScript.Network");
include_once('../../../functions/datalib.php');

$db = new oracleClass();
$conn = $db->connect();

//get the q parameter from URL
$q=$_GET["q"];

//lookup all links from the xml file if length of q>0
if (strlen($q) > 0)
{
$hint="";

// connect to the database
$sqlstatement = 'select * from TEST."WFO_REPORTS_ALLEMPS" order by "Surname", "Forenames"'
$sqlListings = OCI_Parse($conn, $sqlstatement);

OCI_Execute($sqlListings);
while(OCI_Fetch($sqlListings)) {

$name = oci_result($sqlListings, "Forenames") . ($sqlListings, "Surname")

//find a link matching the search text
if (strchr($name,$q))
{
if ($hint != "") {
$hint .= "<br />";
}

$hint .= "<a href='#' onclick='employee_form.Line_Manager_Staff_No.value="
. oci_result($sqlListings, "Gc_Staff_Number")
. ";checknumber('Line_Manager_Staff_No','displayusername');'>"
. oci_result($sqlListings, "Surname") . ", " . oci_result($sqlListings, "Forenames")
. "</a>";
}
}
}

// Set output to "no suggestion" if no hint were found
if ($hint == "")
{
$response="No user found.";
}
else
{
$response=$hint;
}

//output the response
echo $response;
?>

On a side note, is there a way I can change it so that the search will present any users based on their first name, as well as the surname?

Mysql Query Search And Find In String
I have following values in a field of mysql table. These values are stored as string [varchar] in the field.

Values : 3,4,10,21,20,8,100,2,6

How can I check through MYSQL query that 100 or 21 or 4 exists in the string?

Query To Search And Retrieve File For Download
I am new to web development and have this challenge. I want to design a dynamic website for a college community using PHP and MYSQL. The objective of the site is to hold the annual report of all the departments such that visitors can select the desired department and the year of the report. The years could stretch back to as long as the college existed but I want to limit the backdate to 10 years for now. The visitor should be able to select a department and desired year, view the report online and also be able to download the same report stored in a directory in ms word or PDF format. What should the table structure look like? Also the PHP and SQL script to search for the stored files and make them available for download.

SELECT Search Query - Table Join Required? Help Please!! (PHP + MySQL)
I have a search form that has:
- drop down with states (nsw, vic etc)
- drop down with all business categories (retail, commercial etc)
- keyword / postcode field (2000, or 'builders')

The user gets results returned from the business table filtered by state (mandatory), which category is selected (mandatory) and by keyword (optional) or postcode (optional).

If keyword / search phrase is given then it will do search of the keywords fields of the business table (has already been indexed) in the selected category only. If postcode it will return all businesses in that category in order of distance from the given postcode.

I have 5 tables (additional fields ommitted):

'state'
state_id, name

'businesses'
business_id, keywords, name, postcode, state

'postcodes'
fromPostcode, toPostcode, distanceKMS

'categories'
category_id, name

'business2category'
business_id, category_id

Please dont thing i'm just pawning off my work here!!
Basically, i've got this working already, but only just, and in a very long and convulted format. Its far too long to post here, but since i'm not fully versed in table joins, i've been searching individual tables (e.g. SELECT *,MATCH AGAINST etc), building arrays, searching arrays again, and then building results at the end to fit into the paginator. Now the search is taking too long to perform, and i need a leaner alternative. not to mention theres way too many lines of code, and i just know theres a better way.

There must be a very simple way to achieve the following searches using table joins, can anybody please help me with 3 search examples below so i can try to understand joins better?

Search 1:
State + Category only

Search 2:
State + Category + Keyword

Search 2:
State + Category + Postcode

If there is no postcode, the others still need to display the data
filtered by distance from a default postcode of 2000.

I'd really appreciate if anybody has a few minutes free to help out here, and hopefully teach me something about effective table joins and searching. I've omitted the extra fields and tables from the real structure, and just left the relevant ones above - if theres anyhing missing or not making sense please let me know and i'll fix up asap.

Query Across More Than One Database
I have to create a feed which involves query across two database. there is one production database and other is reporting database, however, one set of data is present in reporting database table which has an id ( a key) which I have to use to retrieve data from another table in the production database.

How To Know The Database In Use By A Query
How can I know the database im using by a query?

does mySql have a function to do that?

Udf And Database Query
i'm creating my own mysql function (udf), in which i need to query the database for specific values according to the provided parameter values without the use of mysql_connect() or mysql_real_connect() function, therefore i need access to the current MYSQL type structure that was created when query that issued my function was called

e.g.
i do a querry in my application (php, java, whatever) like:
select * from db.table where column > myfunction(param1, param2);

in myfunction() i want to use the connection already established to mysql server in my application and issue another query. is that even possible? if so how?

Query Database
currently i am using two different databases.
Table A in database A contain customer information
Table B in database B contain customer problem
When I insert a query condition to find for a list of customer whom age is more then 20 from table A. The list is stored in an array in PHP so that the list can be used to find the customer problem from table B. I wants the problem to be sorted accordingly.
I am wondering if i am able to throw a list of customer into an sql query and order the problem accordingly

Matching SQL And Database Query
If I open up phpMyAdmin, click on a database named Gypsy, then click on the SQL tab and paste in the following:

GRANT ALL PRIVILEGES ON Gypsy.* TO 'James_Bond'@'localhost' IDENTIFIED BY î‘'

FLUSH PRIVILEGES;
I know it's at least partially working because I see James_Bond listed among the users when I click on "Privileges" - and I don't see James_Bond listed for any other database.

But when I try the following database connection...

PHP

$link = mysql_connect ("localhost" , "James_Bond", "007") or die(mysql_error());
mysql_select_db ("Gypsy", $link) or die(mysql_error());

I get this error message:

Quote:

Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'James_Bond'@'localhost' (using password: YES) in /Users/MyName/Sites/Geobop/index.php on line 194
Access denied for user 'James_Bond'@'localhost' (using password: YES)

Why Does Database Take Longer On First Query?
I was just wondering that when I do the first query on my database it can take around 3.15 seconds and when I do another query on the same database, but search for something else it does it in something like 0.50 seconds or something in that range. When I then do the first query that I had done it will also be much quicker, say around 0.50 seconds.

It seems that it takes a while for my database to do its first query? Why could that be and can it be amended as the first visit from my visitors is the most important as if it query's slow on their first view of my site then they will probably just click off my site.

Query To Return Database Name
Can anybody suggest a query which returns the db name, I need to use it at run time.

Query Crashes Database
The following query sometimes gives problems, can you see something that could upset the mysql database?

SELECT DISTINCT t1 . *
FROM cdb_producten AS t1, cdb_stijlen AS t2
WHERE (
(
t1.p_brutoprijs * 1.19
) <=750
)

Relational Database, Normalized, Need Query
So I am working on a project that deals with a large normalized database. The database used to be constructed with one users table that had a location field which contained a comma separated string of location abbreviations signifying which location that employee worked at. However, I have now changed the database to as follows:

CREATE TABLE `location` (
`LocationID` smallint(6) NOT NULL auto_increment,
`LocationName` varchar(120) NOT NULL,
PRIMARY KEY (`LocationID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

CREATE TABLE `users` (
`UserID` int(11) NOT NULL auto_increment,
`fname` varchar(255) NOT NULL,
`minitial` varchar(2) NOT NULL,
`lname` varchar(255) NOT NULL,
`department` mediumtext NOT NULL,
`positionTitle` varchar(255) NOT NULL,
`supervisor` varchar(4) NOT NULL,
PRIMARY KEY (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=229 ;

CREATE TABLE `locationrelationship` (
`LocRelationID` int(11) NOT NULL auto_increment,
`UserID` int(11) NOT NULL,
`LocationID` int(11) NOT NULL,
PRIMARY KEY (`LocRelationID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;
There are a lot more fields in the users table but the above should do.

My problem is I am now trying to query the users table and include the location name that the employee works at.

The below query should do this if the employee only has one location otherwise it will display multiple of the same user:

SELECT replace(concat_ws(' ', users.fname, null, users.minitial, null, users.lname), ' ', ' ') AS userName, users.department, location.LocationName FROM users JOIN locationrelationship ON users.UserID = locationrelationship.UserID JOIN location ON location.LocationID = locationrelationship.LocationID
An example of this could be:

John Doe, Billing, South Bend
John Doe, Billing, Elkhart
Jane Smith, Sales, South Bend
However I am trying to create a query that joins multiple locations and separate them by a comma then a space then applies it to some locations value in a query.

An example of what I am looking to do is:

John Doe, Billing, "South Bend, Elkhart"
Jane Smith, Sales, South Bend
Any ideas?

Query With Row Being Used To Identify Database In A Subquery
- FOODS -
id | food
1 | taco
2 | burrito

and the following tables named taco and burrito

- TACO -
id | food_name
1 | soft taco
2 | hard taco

- BURRITO -
id | food_name
1 | fried burrito
2 | super good burrito

I want to be able to create a query that will select the id from foods and then use the food row to determine which database to use in a subquery.

select id, (select food_name from taco.my_food) from foods;

The taco part that is red in the query above is where i need to pull the reference from the foods table in the food row.

(please keep in mind this is a super generalized version of the query, so there are no where statements,

Query Database From Remote Location.
I have a database sitting on my db server, which is part of my office network (which has a domain controller). Access to the outside world is via a router, which is well locked down.

Currently, remote users access the database front end via Terminal Services but this is only a method of accessing an application stored and running on the Database Server.
What I want to do is allow access to the database from a server, which is located in another building (another part of the country). This is essentially an inter/intranet server.

What is the best way for me to get this external server to be able to query my database server?

I Get Ignoring Query To Other Database Message
Does anyone know why MySQL Monitor generates this message:

Console: Ignoring Query to other Database

I get this when I try to do anything but the "use databasename" command. example:

Console: use users>create table person1 .....

Database Design And Query Question
I have a database that has a users table, a jobtitles table, and groups table (among others).

What I need to know is, how to reference the info in the jobtitles and groups tables from the users table. Should I put a users foreign key (users_ID) in the jobtitles table and groups table, or have a foreign key for the jobtitles table and groups table in the users table?

My thinking is that putting a users foreign key in the jobtitles (and groups) table(s) would bloat the database because there would then need to be an entry for each user in the jobtitles table (and groups table). Where as if I did it the other way around, then no matter how many users there are, the number of jobtitles will stay relatively low (I expect about 3-5).

Query List Of Tables In Database
Is there anyway I can query list of tables of a database just using one query string? But not using two seperate query strings -

"USE [database];"
"SHOW tables;"

How To Query Table Names In A Database
Is there a way to query a table name and the count of rows in it in one query? I know how to do these in separate queries but for the sake of readability I would like to do them in one query.

I am working with 5.0 version.

Default Database In Select Query
I have a (seemingly) simple query problem:
SELECT COLUMN FROM MYDB.MYTABLE works great.
SELECT column FROM mytable fails, because it trys to find the table 'mysql.mytable', which doesn't exist.

I am trying to migrate from Oracle to MySql, and I am using the toplink connector. With Oracle, I could do SELECTs without referencing the specific database, and it would work fine.

Intermittently Losing Database Connection During Query
I am using mysql version 4 and java clients written as a windows desktop application using ConnectorJ. The server hardware is a Windows XP machine with service pack 2 running as a virtual server on a windows platform. There are a maximum of 10 concurrent users from the client XP platforms and versions of my program have run pretty well for a couple of years in a similar configuration.

On my current project the charts and reports that I produce keep crashing due to losing connection to the mysql server.

I use auto reconnect and have upped the number of tcp/ip connections on both the virtual server and the client pc. This seems to have helped but problems still arise.

What kind of issues can create this kind of problem? and what are the possible solutions?

Create URL From To Database Fields In Query Results
I have a MySQL database with table that works as document library. Table keeps details about docs on our server. There are bunch of fields but the key ones here are FileName, URL and location. I'm using PHP to work with the MySQL Database hosted on a Mac OS X Server.

Basically, I have a query set up to pull certain records based on a field called Keywords. On the result page I want a field (or item) that combines the URL field and Filename field that ultimately points to the phycial location of the file. For every record thr url field contents is the same since the physical location of the files are in the same directory on the server. Right now the URL field has http://www.mysite.com/docs/.

I'd like the result page to have the filename shown, but that filename would be a link to URL+filename.

Is there a way to do this? .....

General Question About Special Query For News Database
I have a question about understanding how I should accomplish my aim:

I have a database called news.
It has the rows headline, date, category and the text of the news in it.

These news should appear on my index page but only the five latest news should be visible to the user.
(Older news should be archived and the user can look at them in a link at the bottom of the most recent news (5). When he/she clicks on the link all news are listed on a seperate page.)

I would like to know how the query is for the most recent 5 news to display and whenever I add another news again only the latest news including the last added will be displayed on my index page.

If this isn't possible with an MySQL query, maybe there's a different approach to this with PHP?

Error Code : 1044 Cross Database Query
I am using a query editor UI and I am connected to two database and trying to write a query from within one database connection that joins both databases and am receiving the error;

Error Code : 1044
Access denied for user 'user1'@'localhost' to database 'test2'

The query looks something like this where I am in the test1 database UI and trying to included data from the test2 database.
select *
from test1
where test1.id in (select test2.id from test2)

I'm assuming I am having a syntax issue but I cannot find the proper syntax.