MYSQL :: SQL Query Problems (for Use With A Sort Of Live Search)

Sort A Complex Query
$query =
"SELECT page.* FROM `page` LEFT JOIN `keywords` USING
(`page_id`) WHERE MATCH (`keywords`.`keyword_txt`)
AGAINST ('$radio_keyword' IN BOOLEAN MODE)
UNION
SELECT page.* FROM `page` WHERE MATCH (`title`, `descrip`)
AGAINST ('$radio_keyword' IN BOOLEAN MODE)
UNION
SELECT page.* FROM `page` LEFT JOIN `url_pages` USING
(`page_id`) WHERE MATCH (`url_pages`.`page_url`)
AGAINST ('$radio_keyword' IN BOOLEAN MODE)";

How do I ORDER BY `page`.`title` for the entire query? ie. I want to
get the whole query sorted by the page.title field, but I don't see a
way to slap on an "ORDER BY" on the whole query.

Random Query, How To Keep Sort Order Locked?
PHP Version 5.1.6 - mySQL 5.0.22

Let's pretend we have 100.000 rows in a table (all containing images with details), and I want to display 100 images at a single frontend page, but randomly retrieved and keeping the sort order once initiated to a user session.

With the normal RAND function, you have a chance to stumble on the same images when you navigate to the next page with again 100 items on. But even worse, going back to page 1, will return totally different results, and will freak out the viewer

How to get a (fake) random listing on a fairly large database, where every requested row is unique, and the sort order is being locked (until you press a randomize button ie, or start a new session) while keeping speed in mind (so trying to avoid reading out all rows up front).

Merge Sort-of Similar Data From 2 Tables In Query
A bit of background. I'm not a web person, and I know enough about databases to scrape by. Most of my SQL is generated using templates or handy things (like PHPMyAdmin etc). I'm also not a PHP coder. I am a C/C++ coder so I've found I can hack some PHP together, and I've got roped into helping a friend with a problem

Here's the issue. I have a MySQL database with 2 tables of interest. Let's call these tables categorydata and itemdata. Both of these tables contain records that describe individual items - the item name, its ID in the system, the date it was added, some free text about it etc.

To be absolutely clear, the purpose of the categorydata table is for a record to define a category (think "puzzles", "board games", "vegetables", whatever), but the record row also includes data on the FIRST item in the category ("banana", "cluedo" etc) along with all its data. The purpose of the itemdata table is to list all the OTHER items in the category ("grapes", "twister" etc). Here's an example record in the categorydata table:

categoryname vegetables
categoryid 1234
categorycreated 10th Jan 2000
categoryowner Bob
firstitemid 4567
firstitemname cucumber
firstitemcreated 12th Jan 2000
firstitemdesc It's long and green
Here's an example record in the itemdata table:

itemid 4568
itemname orange
itemcreated 13th Jan 2000
itemdesc It's round and, er, yeah.

This seems really bad design to me but what do I know.

Here's what I need to do. I need to know how to merge the data in these 2 tables in a single query, ordered by the itemid and firstitemid fields.

In other words, I need the query to merge the data, ordering it so that regardless of the table the record came from the itemids are all in correct numerical order. I need to do this so that I can retrieve each item's data in order.

itemid in the itemsdata table and firstitemid in the categorydata table are unique, so when merged there won't be any colliding ids.

I need to put this in a PHP script (in a loop that dumps out all the items in order), but if someone can just help me with the SQL query syntax I'd appreciate it, I can do the rest.

I think this has something to do with inner joins but I really don't understand how to make it work, or how to retrieve and merge data from 2 tables at once bearing in mind the fields (columns? what do you call them) have different names even though they have the same purpose (ie: firstitemid -v- itemid, firstitemname -v- itemname etc).

MYSQL Live Reporting
I am looking on building an application for a call center which provides live updates as records are added and logged into a mysql database.
For example when a user closes a deal I want to detect that a record in the opportunities table changed from pending -> closed won and when that happens
do something. My exact usage will be to flash a big WIN! graphic on a plasma screen in the sales center so this application will likely be using flash as well. Any thought on what can do this or the technologies I should be looking at.

Live Update Of Tables
I do have the following problem:
A local server running MySql includes the database A, another server in another region (connected to the internet) includes a mirror database of A. What I need is to live update the tables of the mirror database via a secure connection. Both networks have an internet connection.

Moving Database To The Live Server
I have an asp application that is currently using Access as a backend database. I have installed mysql on my computer and converted the Access database to mysql. I will be changing the connection string to make it work with asp.

My question is that I need to move the database to a live server. What do I need to have on the live server and how do I move the database from one server to another.

Db On Live Site And Replica On Development PC
I have a Db on my live site, I use DW to manage the site and phpMyAdmin to
manage the mySQL Db, and I'd like to do the following:

1. Replicate the Db on my Dev PC, to modify then reset the live version on
the site to match it.

2. Set phpMyAdmin config to look at the PC version of the Db when developing
and the Live version when its up on the site.

Hope these make some sort of sense - just new to the world of php and mySQL
(actually an MS developer).

Sort With Limit But Sort ALL Results.
I have a query that limits by 50 results at a time and I also have an order on a int field. The issue I'm having is it's only ordering by the 50 it returns, how can I have it order by the entire results set and then give me back only 50 results?

Altering Table Structure In Live Database?
I want to add a field to a table in a database that is live and being
accessed from the web. I'm using phpMyAdmin and when I try to add the field
I get error #1142 (ER_TABLEACCESS_DENIED_ERROR) "ALTER command denied to
user 'xxx' for table 'yyy'"

I'm a newbie to MySQL but I guess that's a reasonable error as there are
probably open connections. If that is the reason, is there a way I can force
all connections to close and not reopen while I make the alteration to the
table structure? It won't matter in this case that users will experience
access problems while this is taking place (all access is read-only too.)

If possible I want to do this without stopping the MySQL server - it is a
shared resource AFAIK and the host is in the US (I'm in the UK) so easiest
to coordinate if I can do it all from here if possible.

How Do I Start Logging On Windows Live MySQL Server?
As I understand it, the server has to be shut down for the logging to be successfully started. Is this correct?

I have been experimenting on a local MySQL server to make sure I know what to do on the production server. My production server has a my.ini file at the location c:Program FilesMySQLMySQL Server 4.1my.ini with various config stuff in it. Would it be ok for me to add the following under the [mysqld] part of the file:

log=/logs/mysqld.log

Thus creating a file named 'mysqld.log' in the folder 'logs'.
The main things I need to log are the SQL Statements being executed and the IP Addresses/hostname of the connections. What type of log would be best for this? Also how do I make it so that the log doesn't grow ridiculously large?

Transfering Database From Local System To Live Server
I have a site running locally on my notebook and I copied my site to a live server. I have no problem getting the PHP & HTML files to the server, but i'm not sure how I can move the database and data to the live server. Can I copy a file to the live server like I can with an MS ACCESS database? Or do I need to recreate the whole database on the live server and somehow figure out how to transfer the data?

Live Backup Of Large Table On Production Server
I have a production server serving ads on our website using Openads (currently version 2.0) via PHP, MySQL, and Apache.

It's been operational for over a year now and the database, two tables in particular, have grown to a few hundred megs. It's live and we can't afford any downtime for this server as it serves our ads to generate revenue for us. Whenever an ad is viewed, a new recorded is added to the database. However, in order to do a backup, usually mysqldump, that requires locking the table for several minutes, during which time, it locks the tables, and any httpd (in our case all of them) processes depending on those tables.

Finally, the point of this post, DOES ANYONE KNOW A WAY OF BACKING UP OR DOING A MYSQLDUMP WITHOUT LOCKING THE TABLES for a very large database?

Can I just run a query that will get me all the information without locking the tables (relational problems shouldn't be a problem yet cause its usually only 1 table being written to).

Dumping My Live Database And Then Putting It On My Local Machine Problem..
I have tried copying the dumped sql code in mysql query browser once dumped from live and it doesn't execute it, i have to extract it line by line and that would take forever to do this all, is there an easier way to do this or can someone run me through the basics on how to achieve this?

i have on my live site: 4.0.25 (patched)
and on my desktop computer : 5.0

Would this be part of the reason? i don't know..

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 |
+------+-----------+

Search Database Query
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.

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'.

[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".

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.

Search: Remove Quotes From Database For Search
I am making a book site and currently in the database are book titles likes "John's Trip" and "Peter's new bike". I have a search function where the customer can type in "Peter's" and it will bring up everything with "Peter's" in it.

But i was wonderind is there any way i can search for "Peters" and "Johns".

Sort
I have a table "users" with fields like:
first_name, last_name, photo_name, date

"photo_name" values are either NULL or
something like
"2_50_tim45.jpg"
"3_50_franky70.jpg"
"4_50_john_long.jpg"

Meaning not all users submitted their photos and those I would like to show at the end
of my HTML table.How do I make a query so that I would get resultset

that would show first users that have "photo_name" and then they would be sorted by
date.

SELECT * FROM users ...

Sort
I would like to know if it is possible to order the results by the quotient of to numbers.For exampel:

$sarray=mysql_query("SELECT * FROM table ORDER BY (rating/votes) DESC");
anyone have a clue how to get it to work?

Keep Sort
The website is an ecommerce site that provides customers with the ability to "browse" the product line. In other words they can move from one item to the next until they get to the last item.
The question is: How can I insert a new item(row) and position it somewhere in the middle of the table? Currently, the table is sorted by the primary key which is numbered 1, 2, 3, etc. (whole numbers). Obviously I can't assign the same primary key to two items. My only thought is to use decimals in the primary key instead of whole numbers. So a row entered between 1 and 2 would be 1.1, 1.2, etc.
Does this make sense? Is there an easier way?

Sort Certain Value
I am displaying results from a table. One row, "position" contains values such as "President", "Vice President", or "None" (many of these).
Is there a way to order the results, alphabetically, by the "position" column, but sort any rows with a position of "None" to the bottom?

Sort Something
sort a list by name and birthplace but I don't really know how to do that. Can anyone give me an example?

Sql Sort
Code:
$sqlquery = "SELECT * FROM $ssl_table WHERE user='$login' ORDER BY date DESC, ticket_host DESC";

I'm using this query and want to sort a list by date and then by Alphabetical order for the teicket_host.

Yet it out puts like this:
2007-01-19 BH
2007-01-18 HM
2007-01-18 HM
2007-01-18 BH

The date is good but the 2nd Sort is not doing Alphabetically. I want it to go:
2007-01-19 BH
2007-01-18 BH
2007-01-18 HM
2007-01-18 HM

SORT BY
I have a very large table that is always sorted by the primary key when I SELECT data from it. Actually, the very purpose of the data being in a database is so that it can be sorted like that. I was wondering, however, if there's some way to use the WHERE clause in SELECT (or a seperate token) to actually give me, say, the 10000th entry if it were to be sorted. Something along the lines of:
SELECT name FROM data WHERE %place% = 10000 SORT BY income DESC
So basically if I had a database of names and their incomes in no particular order, I would need to find the name that has the 10000th highest income.

Automatic Sort
Is there a way to automatically sort "inserts" according to date in mysql? I have searched high and low for something like this in mysql. I know that it can be done in MS sql but cant find it for mysql.

Sort By Date
Sorry i want to show the records if the 'maxdate' is equal or greater than curdate....

No Sort Order?
I have a SELECT statement such as:

SELECT category, title, pubdate FROM ds WHERE category = 3 OR category = 5 OR category = 4 OR category = 101 OR category = 7

I want the result set to return the results in the order that I have queried above (ie: 3,5,4,101,7) but MySQL seems to sort the category so I get the results as (3,4,5,7,101).

Other than splitting into 5 SELECT statements, is there any way I can use a query to say "DO NOT SORT"??

Sort By 2 Colums As 1
I have 2 tables lets say called: files and notes
files has 2 fileds filname and date_added
notes has 2 fields note subject and date_added

if files had 2 records:
1) file1.pdf : 2007-06-14
2) file2.pdf : 2007-06-17

if files had 2 records:
1) great day out : 2007-06-15
2) why me? : 2007-06-18

i want to sort by the date in both fields but as if they were in 1 fields so output would be
2007-06-14
2007-06-15
2007-06-16
2007-06-17
and not
2007-06-14
2007-06-17
2007-06-15
2007-06-18

Sort Problem
I'm new to mysql and ran into this sort problem.

Say I have these entries and this is the sort order using char data type:

C100
C1000
C150
C200

I need the sort order to be this:

C100
C150
C200
C1000

What data type do I use?

Sort Aborted
I am getting Error 1028 Sort Aborted, is there any information available that may help me determine the cause of this error.

How To Sort In Mysql By Own Needs
filename: p1.pdf, p2.pdf, p3.pdf...pN.pdf in database column. select filename from filename from table order by filename -- the returned result won't be in the filename order.

But If I change the filename to p001.pdf, p002.pdf, etc. It will work fine. Is there any way to make it work in the 1st scenario? i.e. sort in filename order.

Sort With A Limit
What I need to do is display results that are sorted based on user preference. These results need to be paged to display 25 results per page. Currently I'm using a simple sort and limit. However, as many of you smart folks know, MySQL only sorts the values it finds within the limits as opposed to sorting the whole table and then limiting results.

SORT BY With Condition?
is there a way to do sorting depence of some condtion directly in the MySQL query?
for example, I have this query:

SELECT cat_id, cat_name, cat_date, cat_parent
FROM categories
ORDER BY cat_name ASC
but I'd like change sorting if cat_parent = 23 for example.
so, is there a way use something like:
SELECT cat_id, cat_name, cat_date
FROM categories
ORDER BY ((IF cat_parent = 23) THEN cat_date ELSE cat_name) ASC

I dont know MySQL at all, so this is just an example of condition idea that I'd like to use.