Best Way To Write This Query
Query 1: obtains results in boolean mode from products table based on keywords
Query 2: Would like to grab all manufacturers names from manufacturers table based on mf_id in products table to then create a brand filter.
What is the best way to do this in the most efficient query possible.
I was thinking of building a php array from query 1 of mf id's and the putting them in query 2 and separating them using OR operators.
any ideas?
here is the query fyi:
PHP
$sql = "SELECT products_id as prodid,
products_name as name,
manufacturers_id as manufacturers_id,
products_price as price,
products_msrp as msrp,
products_date_added as pda,
products_status as status,
products_sku as sku,
products_weight as weight,
categories_id as cat_id,
"
.boolean_sql_select(
boolean_inclusive_atoms($search_string),
$fulltext_key)." as relevance
"
."FROM $table_name
"
."WHERE
"
.boolean_sql_where($search_string,$fulltext_key)."
"
."HAVING relevance>0
";
if (isset($HTTP_GET_VARS['sort'])) {
if ($_GET['sort']=="brand") {
$sql.="ORDER BY products_name ";
} elseif ($_GET['sort']=="size") {
$sql.="ORDER BY products_weight ";
} elseif ($_GET['sort']=="price_low") {
$sql.="ORDER BY products_price ASC ";
} elseif ($_GET['sort']=="price_high") {
$sql.="ORDER BY products_price DESC ";
} elseif ($_GET['sort']=="fav") {
$sql.="ORDER BY products_name ";
}
} else {
$sql.="ORDER BY relevance DESC
";
}
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
How To Write Query To Compare Tables
I give in. I can't figure it out, and I know it's one of those things that once I see it I'll think, "OF COURSE!". I have 2 tables, same DB. Table 1 is named Policies Table 2 is named Assignments Both contain a PolicyID field. I want to find out which PolicyID entries are ONLY in Policies, and not in Assignments. So if "Select PolicyID from Assignments" returned the following: 1, 2, 3, 4, 5, 6 And "Select PolicyID from Policies" returned: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 What do I use to return this? 7, 8, 9, 10 Any nudge, help, clue, etc. would be appreciated. I've looked thru examples of Outer Joins, Inner Joins, Lefts, Rights, Unions, Intersects, etc. and can't figure out which to use, all for what seems to be a simple operation!
To Re-write Complex Query With JOINS
I have a problem with one of the JOIN query here. Below is a brief description of the problem. tablename : test RecordId EffectiveDateothertableidvalue -------- ------------- ------------ ----- 12004-01-10110 22004-01-20120 32004-01-20270 42004-01-10280 52004-01-15110 62004-01-25310 Output : RecordId EffectiveDateothertableidvalue -------- ------------- ------------ ----- 22004-01-20120 32004-01-20270 62004-01-25310 Now I want to use a single SQL query to find a result where there exist one record for each unique "othertableid" where the record selected for the "othertableid" should be the recent one with regard to "EffectiveDate". That is from the above records, I want to select Records with "RecordId" = 2 and 3 because they are the recent one for "othertableid" = 1 and 2 respectively. Please be sure that I want to retrive all fields including "RecordId". The result should not depend on any other fields but "EffectiveDate" only. I am using MySQL 4.0.12 and it does not support "SUBQUERIES" which is now given support in latest MySQL edition. But I have read in the manual of MySQL that any "SUBQUERY" SQL statement can be written with proper "JOINS".
How To Write Query To Select The Max(version) For Each Unique File_name Record?
I am a MySQL newbie trying to write a query that selects file_name records possessing the highest numbered version for that unique file_name. I show sample data and two trial queries below. Logically I want to use max(version) as a constraint in a Where Clause. However, the max() function is not allowed directly in a where clause. I have contemplated a second table to track the max version for each file name. I would like to structure the data in an efficient manner for query performance when the data set grows to many thousands of unique file_name records with many hundreds of versions each........
Search And Write, Or Write And Recover?
The problem: I need to generate a 'unique string' for each row in a table. I already use auto_increment for system dependencies between tables. What is the best approach one of these or another? After generating a candidate 'unique string' the two strategies that came to mind are: 1. to then search the table's column to see if it is already assigned; locking the table for write while searching and writing the new row, or 2. set the column to UNIQUE when defining the table. Just go ahead and write the new row if you get a "non-unique" exception, generate another 'unique string' and try again. I've tried both on a small XP laptop and get "lock timeout exceptions" rather quickly using #1. But replace those with lots of re-writes when there starts to get "collisions" of 'unique string's.
Help Me Write This Join?
Hi there, been ages since I wrote any sql, and I'm a little stuck but I figure this is easy for you folks. I have this original query: $posts = $wpdb->get_results("SELECT ID, post_title FROM " . $wpdb->posts . " WHERE post_status='publish' ORDER BY post_date DESC"); And this gives a lists of all posts. However, I want to only grab posts by a certain category now, say category #9. This information is kept in an intersection table called wp_post2cat. The columns in that table are: rel_id post_id category_id category_id is what I want, and post_id is the key pointing back to the original post. Posts can have multiple categories, but I just want the one listing where it's category 9, which is audio. So pseudocode would be something like: SELECT all posts WHERE post_status equals "publish" and the category is equal to audio (9).
How To Write This Select?
I'm having some troubles writing a select. I have the following tables: theater ------- - id - name movie ----- - id - name showtime --------- - id - theater - movie - from - to - schedule I would like to list all the theaters with all the available showtimes and movies. I tried to, but i didn't get the result i was expecting. Can somebody help?
How To Write This In MySQL?
I Want to check if a certain value doesnt exist in a ceratain field in my table as a condition. "select id from employees inner join translog on employees.emp_id=translog.empid inner join outofofficedays on employees.emp_id=outofofficedays.emp_id inner join holidays where (here is my question: ��-04-02' doesnt exist in the column translog.vtransdate) and (��-04-02' not between holidays.H_START and H_END) order by translog.vTransDate,employees.emp_id limit 1"
How Do I Write This Trigger?
I’ve been searching all over for the answer to this one. If you have the answer, it would help me out a lot! Using MySQL triggers, I want to sync content between two tables; I have table “user” and table “users”, each have similar columns “last_name” “first_name” “user_id” …etc, and when a row gets written to table “users”, I wanted that record to be replicated and inserted to “user” (with all the same field info). How do I write this trigger? I know, it’s anti-normalization, but it will really help me out with testing one of our site's authentication.
Write A Mysql Db Onto A Cd
how to write the db of a mysql on one system onto a cd so as to copy that db to the mysql on another system. The 2 systems are not connected to each other.
Can't Write To File
Using MySQL 4.1.11-max on OS X 10.3. Using command line. Can't successfully use "INTO OUTFILE". Getting the following: mysql> select * -> into outfile '/users/steves/desktop/test.txt' -> fields terminated by ',' -> from industry; ERROR 1 (HY000): Can't create/write to file '/users/steves/desktop/test.txt' (Errcode: 13) At the shell, I get this: steves2ndmac:~ steves$ perror 13 OS error code 13: Permission denied Newbie in both Unix shell and MySQL. Can anyone tell me how to set permissions for Unix User mysql?
Cant Write Or Delete
I cant get my php script to insert or delete records into a mysql table. I can view them all fine but that's about it. I've checked the user permissions on mysql and have set them all to 'Y' incase that was the problem but still no joy. Can anyone suggest what the problem may be? I'm tried the usual RTFM but cant spot the problem
Write Data To Row With Via C API
I have an app written in C that sends data to text files. The files receive 8 variables and look like this: May 19 20:42:09 2002 7 Services 3.23 4.34 0 None 2.31 Rather than writing data to a text file, I need that data to go to a MySQL 5.0.41 database row. I've already got the C API working and connecting to the "localhost" database, but now I need to write data with each transaction. Optimally, I'd like to send the data directly to the database row, but there doesn't seem to be an INSERT statement to do that. What's the best method to write the data to the database directly?
How To Write Like Comparison In Procedures?
this sample procedure takes in an username, searches for number of entries matching the pattern of the username. delimiter $$ create procedure sampleDB.sampleProcedure( IN m_username varchar(30), OUT m_username_matched_count int ) deterministic begin if length(m_username) = 0 then set m_username = 'jason' #set temporary name if empty end if set m_username_matched = 0; #finds number of matches for the given username select count(*) into m_username_matched #store into the variable to be returned from sampleDB.sampleTable where userName like '%m_username%' #this is the statement that does not work# end$$
Using INSERT To Write To A Directory
I have a problem that I'm not sure if it can be done. I'm trying to use the MySQL C API to be able to use a normal sql insert statement that will send the data or file to a directory and NOT the database. I'm not sure if there is anything out there that will allow this, but I've searched everywhere, and I have found no solution.
C- Program To Write To Myi , Myd File
is it possible to create a myi, myd file for mysql using a C program. I am using Stata that is written in C API, and I want to write the data out to MYSQL database. Using ODBC and loading the data in MYSQL from STATA takes forever. Can anyone please tell me if I can create the myd files by using the data in memory thru C programming.
Cant Write Or Delete To Table
I cant get my php script to insert or delete records into a mysql table. I can view them all fine but that's about it. I've checked the user permissions on mysql and have set them all to 'Y' incase that was the problem but still no joy. Can anyone suggest what the problem may be? I'm tried the usual RTFM but cant spot the problem
MySql Write Vs Read
Essentially I am faced with a design choice; have an oft run (modifiable) query run every time any user wants to run it, comparing criteria againt each and every user in the table and delviering list OR saving that list (user/user/) so the next time all that needs to be queried is the existence of a record in the table. The basic tradeoff; version 1 there are many simultaneous queries of the user db on complex search criteria; version 2 there are fewer but their are as many far more simple queries to a much larger table, not to mention many writes to same to keep the table current. I know this is pretty vague, but I was wondering if in general there is an area that mysql excels in vis a vis complex query smaller tables simple query larger tables. Question 2 is there anyway to guestiate the search/write times of a user log in to both query and update? #Records, fields, etc? I'd like to keep log in to < 10 seconds max.
Write Conflict Error
I have a database with access as front end and Mysql as back end. I am gettting the following 'Write Conflict' Error. when i am on my order form, This form does has a subform where i enter all the products that are ordered. Could any one let me know what could cause this problem. I am the only one using the database as its still in the build stage Code:
Slow Write Speeds
Here is what i have IBM x226 - 8GB ram - 2x 3.4 Ghz Xeon (single core) processors - RAID 5E (6x300GB disks) Suse Linux 9.0; MySQL 5.0.13 So, I am migrating from SQL server 2000 on a much smaller Dell PowerEdge (2gb ram, 2 Xeon 3.0 Ghz, Raid 5 over 5x74GB). I am exeriencing very slow write times. For instance, there is one table on both servers that has approx 9mil rows...here is a basic test: Update loan set test_col = 1; Platform Time IBM/Linux/MySQL 5 9:45 Dell/Win/SQL server 1:37 The tests were run during very light server activity and I tried them multiple times. Our IT department is fairly insistant that the IBM server is running per spec...obviously something is not right. I have changed some of the server variables with no improvement.
Write Once Read Many Field
I am designing a database and one of the requirements my client has is that once data is entered into certain fields it cannot, under any circumstances, be changed. Is it possible to create a write once read many (WORM) field in MySQL directly?
Re-write Text File
i'm creating a log file regarding database operations. My problem is i want to append a new record into the log file each time i update my database. Previously I've used the following method to create a file: SELECT * INTO OUTFILE 'c:/"+fileName[i]+" ' FIELDS TERMINATED BY '|' LINES TERMINATED BY ' ' FROM "+tableName[i]+"; MY problem is I cannot re-use the file created. Is there any way to call the same file for the purpose of re-use. Or maybe the OUTFILE do not allow file created to be re-use.
Create/write To The File
I am working on Solaris and seem to get the following error Cant create/write to the file '/home/dslab456/temp/tempoutput.lst' As in windows i cannot specify which drive it is or so ? could anyone help me out with this ? SELECT * INTO OUTFILE '/home/dslab456/temp/tempoutput.lst' FROM stocklist_vokus s,p_tmpr p WHERE s.STOCKLIST =p.stocklist_value ORDER BY p.rid Edited 1 time(s). Last edit at 02/12/2007 05:38AM by summer queen.
Vita And Write In MySQL
My web site use a odbc to a MySQL database. I create it on XP and it's worked but I'm now on vita and the data base on a 2003 server. All the reading access on the database are ok but all the writing access are denied. I haven't error message just error 500 on ie7.
Who Do I Write Select With Dates And Times
i have to cols, date and time. who can i do, SELECT between (date 1,time 1) to (date 2, time 2)? for example i want to get the recordes between 2007-01-01 23:00:00 to 2007-01-03 03:00:00 ?
Can't Create/write To File In C:windows
I installed the 3.51 ODBC driver on a new test machine that is re-imaged each night to a clean OS (Win XP Pro SP2). We place our developer daily builds on this machine and run some automation. In the automation process, MYSQL statements are ran. The issue I get is that sometimes it stops with this error: "Can't create/write to file 'C:WINDOWSTEMP#sql_1808_0.MYD' (Errcode: 13), #-2147217871" or it will run right away, or sometimes it takes 3 tries before it finally gets past whatever it is choking on. I do not have MYSQL installed on that machine, only the driver. I have given the "Everyone" account full permissions, but still no luck. Any suggestions? The environment variable on the machine is set to C:WindowsTemp and it appears to execute some statements before, just is stops on some.
No Read Or Write Between INSERT And UPDATE
I am using mysql with the InnoDB engine. I wrote a perl script that first selects something from a table, and then updates a second table based on the select from the first table. I need to make sure that there is no read or write to the tables while my script performs the insert and update. I looked at http://dev.mysql.com/doc/refman/4.1/en/lock-tables.html and it says this: --------------------------------------------------------- If you are using a storage engine in MySQL that does not support transactions, you must use LOCK TABLES if you want to ensure that no other thread comes between a SELECT and an UPDATE. The example shown here requires LOCK TABLES to execute safely: LOCK TABLES trans READ, customer WRITE; SELECT SUM(value) FROM trans WHERE customer_id=some_id; UPDATE customer SET total_value=sum_from_previous_statement WHERE customer_id=some_id; UNLOCK TABLES; Without LOCK TABLES, it is possible that another thread might insert a new row in the trans table between execution of the SELECT and UPDATE statements. --------------------------------------------------------- However, I am using InnoDB and it DOES support transaction. So, does that mean that even if I don't lock my tables, it will still work? If not, what do I need to do?
Write A Script With Looping Constructs
I want to write a script to insert some rows in a database. The user of the script will login to mysql and paste the script at this point. Is there a way to use looping constructs, variables, etc. Iam thinking of a PL/SQL equivalent.
SQL, Field Name Alias For Write Access
I get an error on the adorec.update line - Unknown column 'MyName' in 'field list'. The code will work for read access but won't let me write to it. How do I update a field with an alias? I know i can lose the "AS" part but that would defeat the purpose of what I'm accually trying to do.
Can't Create/write To File /var/run/mysqlid/mysqld.pid
I have installed mysql(mysql-standard-4.1.14-unknown-linux-gnux86_64-glibc23.tar) on linux.but when i tried to start mysql i am getting an error can't create/write to file /var/run/mysqlid/mysqld.pid can't start server can't create PID file :No such file or directory.
Can't Create/write To Database And Temp Directories
I'm quite new to mySQL, and am having problems being able to create database tables. I'm on my [5th] setup of mySQL/apache/php, just after formatting windows, and everything else seems to be working, and working together, so I'm assuming the installation is ok. I can create databases, but when I try to create a table, I get this error message: ERROR 1 (HY000): Can't create/write to file '.customermidian.frm' (Errcode: 2) I'm assuming I haven't set up my temp folder correctly, but I don't understand how it works properly. After reading the mySQL maual repeatedly, I tried using --tmp=c: emp to set it, and it didn't seem to work, and I created a my.ini file with this in it: [mysqld] tmpdir=C:/temp is there anything else I can try? I am logging into mySQL with the root account, and a password.
Access ODBC 'write Conflict' SOLVED!
I had this problem where a user would attempt to change a record, and always get the 'write conflict', and that another user had changed the record... even though no other users were on the database. I searched high and low for a solution, to no avail. Yes, I had a primary key; I had a TIMESTAMP field. I played around with Access' settings for record locking, no record locking, etc.; I set the MyODBC setting to "return matching rows". None of these worked. Here is what I found: I had a TIMESTAMP field in the MySQL table, NULL = OK. I imported data from a text file into this table. This text file did not have the TIMESTAMP field, so all of the records in the MySQL table had a default timestamp of '00000000000000'. I wrote a SQL statement that changed this TIMESTAMP '00000000000000' value to an arbitrary value (for example, '20030910134340'). Everything worked fine.
How To Write :send Email And Clear Form In Php
I am a student and I am building a webpage. I need to build a formular to send emails. The fields to fill are the name and email of the sender and also the message to send. I would like to test it locally and also later on a remote server. Code:
Can Seqouia/c-jdbc Segregate Read & Write DBs ?
I haven't started using Seqouia/C-jdbc but I am probing the ability to do something like this: Controller CW has 2 controllers under it - C1 & C2. C1 is local and has a host HA with X.db's tables t1 & t2. C2 is remote and has a host HB also with X.db's tables t1 & t2. Controller CR has 2 controllers under it - C3 & C4. C3 is local and has a host HC with X.db's tables t1 & t2. C4 is remote and has a host HD also with X.db's tables t1 & t2. X.db is MySql type, from experience I've found that MySQL is better off doing either only writes or only reads. So I want to segregate my read and write stores. Hence, HA & HB are meant for writing into, while HC & HD are meant for reading. Now writes coming to CW should be treated like transactions which land up on local and remote X.db. These are my WRITE-ONLY-DBs. Likewise I want reads to goto CR for load balancing with higher priority to read from HC than HD. Questions: (a) Can c-jdbc help in replication of data between HA-HC and HB-HD ? Which component would help to set this up ? Or do I need to go for out-of-band MySQL replication ? (b) Is this configuration optimal for high scalability or availability ? Should I have added contollers horizontally instead of vertically ? (c) Also, looks like I'd have to write an app to direct writes to CW and read to CR. Or is there a way to obviate that with the controller settings itself ?
Write/Move/Save Pdf To MySQL Table?
Is upload the only way to pass a file to a table? I have a contract.pdf which is generated via php (FPDF) and saved in a temporary file. I need that file then moved /written to the appropriate customer table in our database (MySQL ) so that the temporary file may then be overwritten by the next Customer/Order. While I am able to upload the file via <input type="file" name="uploadpdf> and related uploader.php, asking our web-users to “browse” for the file does not suit our needs, and of course adding a default value <input type="file" name="uploadpdf" value="http://localhost/crm/modules/Orders/contract.pdf"> doesn’t work either. As the file is always the same (modules/Orders/contract.pdf) how may I manage that move automatically, (say with a ‘Confirm Order’ button) to the database?
How To Optimize Frequency Of Disc Write Operations
I need to optimize frequency of disc write operations for inserted or updated data on MyISAM tables. How can I tune MySQL for large records block disc flushing instead of record-by-record flushing by default? Some DBMS parameters have effect on disc flushing ("flush", "flush_time", "delay_key_write"), but any of these parameters does not allow to reduce intensity of disc write operations. MySQL 5.0 (Win32) is used.
Mysqldump: Got Errno 27 On Write. File Too Large
I have musql 4.1.7 on Solaris 9, 64 bits and I want to mysqldump a +-4 gigas db. The dump I want to do is from a myISAM tables databank. I used to do that dump wihtout any problem until recently while the size of the Db double (+-2 to +-4) Code:
Obtaining Read/Write Ratio -Replication
I am looking into a replication strategies to improve performance and redundancy for a MySQL 4.1 database running on Linux/Apache2/mod_python. I am curious as to the standard way of obtaining a Read/Write Ratio. What patterns of usage suit One-Way or Two-Way replication? In my situation I have a database that intermittent bursts of inserts through the day, a lot of batched deletes/inserts/updates over night and fairly constant selects. I'd expect the RW Ratio would fluctuate dramatically in a 24 hour period.
InnoDB: Right For Write-heavy Table? Server Tuning?
I've decided to merge 4500 identical tables into one. They were previously partitioned with one table per user of the system, but I'm imagining I may not need to do this anymore for this particular table. I'd like to do so to reduce the number of files in this database's directory (currently over 15,000), to reduce backup complexity, and maybe improve performance. That's where I'm still unsure. This is the summary table for W3Counter with fields website_id, date, unique_visits, return_visits, page_views. The website_id and date uniquely identify each row. It is a write-heavy table (probably more than 99% INSERT/UPDATE compared to SELECT). I may be making things up, but I believe I remember InnoDB showing superior performance for this type of table. I'd imagine row-level locking would decrease lock contention when being hit by dozens of threads concurrently -- queries trying to update different rows would be allowed since that row isn't locked, unlike MyISAM where the entire table is locked by each query. Merging all the tables into this one would mean starting with about 1.5 million rows and growing about 4500 per day. So what do you think? Is InnoDB the right way to go for this table? Should I expect better performance than MyISAM for the inserts/updates? Should I expect the same or better performance on SELECTs that SUM() those counts over various date ranges, and sometimes group by WEEK(), MONTH() or YEAR() on the date column? I'm not sure whether the primary key is sufficient for that type of query, if a key on just the website_id would help those, or what. Any opinions? My other reason for this thread is to get some pointers on tuning the server variables for InnoDB tables. There's much less written about this than those mainly applicable to MyISAM tables. Which are most important for a table like this? Right now no other tables will be InnoDB as the rest benefit greatly from MyISAM-specific features (like prefix compression on indexes and maintenance of row counts).
Mysql Client Dont Write Russian Characters
I installed MySQL 5.0.24 and have so problem.I can't type russian characters in mysql client. Database has data in koi8 encoding (from dump, from php form...) and this data correctly shown.But when I switch encoding to russian in mysql client cursor stops moving independent from my keys pressing,when switch it return to English and typing continued.For example,I enter: select fld from tbl where fld="russ",if "russ" in koi8 I cann't type this characters. It is possible type russian characters from OS command line: mysql -p -e "select fld from tbl where fld='russ'" dbname. In this case whole query text will be entered and correctly executed. If anybody know how possibly decide this problem write me please. My OS is ALTLINUX 3.0 : LANG=ru_RU.KOI8-R MySQL's variables: | character_set_client | koi8r | character_set_connection | koi8r | character_set_database | koi8r | character_set_filesystem | binary | character_set_results | koi8r | character_set_server | koi8r | character_set_system | utf8 | character_sets_dir | /usr/local/mysql-standard-5.0.24-linux-i686/share/mysql/charsets/ | | collation_connection | koi8r_general_ci | collation_database | koi8r_general_ci | collation_server | koi8r_general_ci
How To Change Read/write Permission On Mysql Data Base.
I had a total crash and the server guy gave me all my data in a hand basket.. just kidding.. Actually I was given the data all zipped up and the mysql data bases were separate and they were the direct downloads as follows: wp_comments.MYD wp_comments.MYI wp_comments.frm all data was in this format. I used SSH and uploaded it to the proper directory on the server and all is well at least as far as visitors seeing all the data.. but when I log into the site I get the following... WordPress database error: [Table 'wp_options' is read only] UPDATE wp_options SET option_value = 'O:9:"magpierss":18:{s:6:"parser";i:0;s:12:"current_item";a:0:{}s:5:"it ems";a:10:{i:0;a:12:{s:5:"title";s:13:"WordPress 2.2";s:4:"link";s:54:"http://wordpress.org/development/2007/05/wordpress-22/ I used SSH and tried to change the concerned files to writable and it works fine but the same error is generated. I can go direct to the data base using myAdmin and cannot edit (write) to any table. It says the data base is read only. On the same mysql area, I have another data base that was done via the normal way and I can write to it just fine. I can find nothing to change to make the site writeable. Any help here would be greatly appreciated.. SSH to the server and changing the files to writable has no effiect..
Mysql Master/Slave Read Write To Database Question
If I setup my database as master/slave and user enters data into the html form. This data is written to the master database. After html form submission user is directed to view.html to view the submitted data. The view.html page does the read from the slave. Would it be safe to assume that the data that user submitted would be written to master and immediately displayed in the view.html by reading slave data base?
How Can I Make A Query Like Microsoft Access, And A Query From A Query
I am new to MYSQL and am trying to understand how to make queries... I am moving from Microsoft Access where it is GUI driven and easy! I can make a simple single query using MYSQL Query Browser, say: qry1: SELECT ID, Area FROM data GROUP BY Area How can I store this as a query inside MYSQL, rather than having to code it each time? In Microsoft Access I could enter a variable ($VARIABLE) and then pass by code to the query: qry2: SELECT ID, $VARIABLE FROM data GROUP BY $VARIABLE How can I store this as a query and then pass the variable from code? In Microsoft Access I could base a query on the results of another query, so following example above: qry3: SELECT qry1.Area, data.ID FROM qry1 INNER JOIN data ON qry1.Area = data.Area; How can I store this as a query in MYSQL.
How To Create Efficient MySQL Query From A Pseudo Query
I'm trying to build a webapplication where users can search for a person having a particular preference for color and material. To store this information I use the following structure (a MySQL dump can be found at the end of this post): *table person with fields: -persid: autoincrement id -name: name of the person *table material with fields: -materialid: autoincrement id -material: name of the material eg "wood" *table color with fields: -colorid: autoincrement id -color: name of the color eg "green" *table persmaterial with fields: -persmatid: autoincrement id -persid: link to table person -materialid: link to table material *table perscolor with fields: -perscolorid: autoincrement id -persid: link to table person -colorid: link to table color In the webapplication the search can be entered by the users as a kind of pseudo query: (color=red OR color=blue) AND color=green AND material=iron My question is: how can I automatically transform this pseudo query into an efficient MySQL query? I have tried out some different options: Option 1: (SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=1 OR pc.colorid=2) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING (count(DISTINCT pc.colorid)=2 AND count(DISTINCT pm.materialid)=1)) UNION (SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=2 OR pc.colorid=3) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING (count(DISTINCT pc.colorid)=2 AND count(DISTINCT pm.materialid)=1)) Remarks: *I do not see how to turn a general pseudo query into a query like the one in option 1, except for turning the pseudo query into a sum of products form where the sulms would correspond to the UNIONs. IS there a clever way to obtain such a sum of products form from an arbitrary pseudo query? Option 2: SELECT persid FROM person p WHERE (EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=1 AND p.persid=pc.persid) OR EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=3 AND p.persid=pc.persid)) AND EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=2 AND p.persid=pc.persid) AND EXISTS(SELECT * FROM persmaterial pm WHERE pm.materialid=2 AND p.persid=pm.persid) Remarks: *very easy to get from pseudo query to MySQL query but what about performance? Option 3: SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=1 OR pc.colorid=2 OR pc.colorid=3) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING sum(case when pc.colorid in (Ƈ',Ɖ') then 1 else 0 end) >= 1 AND sum(case when pc.colorid=ƈ' then 1 else 0 end)>=1 AND sum(case when pm.materialid=ƈ' then 1 else 0 end)>=1 Remarks: *this option requires the pseudo query to be turned into a product of sums form; again is their a clever way to obtain such a form; Option 4 SELECT DISTINCT pc1.persid FROM perscolor pc1 INNER JOIN perscolor pc2 ON pc1.persid=pc2.persid AND pc2.colorid=2 INNER JOIN persmaterial pm1 ON pc1.persid=pm1.persid AND pm1.materialid=2 LEFT OUTER JOIN perscolor pc3 ON pc1.persid=pc3.persid AND pc3.colorid=1 LEFT OUTER JOIN perscolor pc4 ON pc1.persid=pc4.persid AND pc4.colorid=3 WHERE COALESCE(pc3.persid,pc4.persid) IS NOT NULL Remarks: *this option requires the pseudo query to be turned into a product of sums form Option 5: SELECT p.persid FROM person p, persmaterial pm,perscolor pc1,perscolor pc2,perscolor pc3 WHERE p.persid=pm.persid AND p.persid=pc1.persid AND p.persid=pc2.persid AND p.persid=pc3.persid AND (pc1.colorid=1 OR pc2.colorid=3) AND pc3.colorid=2 AND pm.materialid=2 GROUP BY p.persid Remarks: *very easy to get from pseudo query to MySQL query but what about performance? -- phpMyAdmin SQL Dump -- version 2.6.1 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Oct 19, 2006 at 01:13 PM -- Server version: 4.1.9 -- PHP Version: 4.3.10 -- -- Database: `aston` -- -- -------------------------------------------------------- -- -- Table structure for table `color` -- CREATE TABLE `color` ( `colorid` int(11) NOT NULL auto_increment, `color` varchar(30) NOT NULL default '', PRIMARY KEY (`colorid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Dumping data for table `color` -- INSERT INTO `color` VALUES (1, 'red'); INSERT INTO `color` VALUES (2, 'green'); INSERT INTO `color` VALUES (3, 'blue'); INSERT INTO `color` VALUES (4, 'yellow'); -- -------------------------------------------------------- -- -- Table structure for table `material` -- CREATE TABLE `material` ( `materialid` int(11) NOT NULL auto_increment, `material` varchar(30) NOT NULL default '', PRIMARY KEY (`materialid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; -- -- Dumping data for table `material` -- INSERT INTO `material` VALUES (1, 'wood'); INSERT INTO `material` VALUES (2, 'iron'); -- -------------------------------------------------------- -- -- Table structure for table `perscolor` -- CREATE TABLE `perscolor` ( `perscolorid` int(11) NOT NULL auto_increment, `persid` int(11) NOT NULL default Ɔ', `colorid` int(11) NOT NULL default Ɔ', PRIMARY KEY (`perscolorid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ; -- -- Dumping data for table `perscolor` -- INSERT INTO `perscolor` VALUES (1, 1, 1); INSERT INTO `perscolor` VALUES (2, 1, 2); INSERT INTO `perscolor` VALUES (3, 2, 1); INSERT INTO `perscolor` VALUES (5, 3, 3); INSERT INTO `perscolor` VALUES (6, 3, 2); -- -------------------------------------------------------- -- -- Table structure for table `persmaterial` -- CREATE TABLE `persmaterial` ( `persmatid` int(11) NOT NULL auto_increment, `persid` int(11) NOT NULL default Ɔ', `materialid` int(11) NOT NULL default Ɔ', PRIMARY KEY (`persmatid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ; -- -- Dumping data for table `persmaterial` -- INSERT INTO `persmaterial` VALUES (1, 1, 1); INSERT INTO `persmaterial` VALUES (2, 1, 2); INSERT INTO `persmaterial` VALUES (3, 2, 1); INSERT INTO `persmaterial` VALUES (5, 3, 2); -- -------------------------------------------------------- -- -- Table structure for table `person` -- CREATE TABLE `person` ( `persid` int(11) NOT NULL auto_increment, `name` varchar(30) NOT NULL default '', PRIMARY KEY (`persid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ; -- -- Dumping data for table `person` -- INSERT INTO `person` VALUES (1, 'john'); INSERT INTO `person` VALUES (2, 'emily'); INSERT INTO `person` VALUES (3, 'liz');
Simple Sql Question: Using A Query Result As A Query Variable
EDIT: it works now, I had an error in my code, not my method. I have a very simple question. I have 2 tables: 'users' and 'posts' with the following structure: users: id, username, email_address posts: id, user_id, post_title, post_text in a my own mind's mysql, I would like to: SELECT posts.id, posts.user_id, posts.post_title, posts.post_text users.username FROM users, posts WHERE posts.user_id = users.id I usually do one query for the post data, and then, based on the use_id record, do another of the users table, but today, I'm being forced to do them in one swoop.
Query Based On Results Of A Previous Query
So far I have managed to construct one query which gives me all individuals that have one of three titles. based on this I now want to find all the individuals that are affiliated to those listed in the first query ....
Big Query - Query Not Completely Stored In Memory
I have this query and when executed in mySQL query window throws error that "Big Query - Query not completely stored in memory". Also executed thru app, the program justs hangs. I have checked all indexes, they all look good.....
|
TRACKER
