Using A Query Result In Another Query
Can I use the results of a SELECT query as a "table" in another query?
I want to let my user pick a subset of the data, then refine it further. So ... do I have to repeat all the selection criteria at each step, or can I just do refer to the last query result?
If so, what's the PHP syntax for this?
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
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.
First Query Result Used For Second?
Is this possible / Practical? Function1 I have a form that takes a users single field input and queries the database returning two values as result of what was entered, valueA and valueB. It then calls function2 passing it valueA and valueB. Function2 takes valueA & valueB and queries the database again using these values within the query. Im still learning mysql so the code is a real mess and not working. Could someone give a simplified example of how this could / should be done?
Query In Storing The Result Set
SELECT ProductId FROM product WHERE prod_category_id IN (1,2) INTO OUTFILE '/usr/local/mysql/data/test/test.xml'; But what is my doubt is? I want to save the output of the file in the remote server, is that possible? http://mysql-api.blogspot.com/
Query Result As Column Name?
I now have made a view with results of several years for all the people in the database. I can query the view to get the three winners, sorted decending on Winstperc. The result looks like this: Seizoen Naam Winstperc 2006/07 Jim 1,0909 2006/07 Bill 1,0896 2006/07 William 1,0879 And that's exactly what I want to know but I need it like this: Seizoen Nr1 Nr2 Nr3 2006/07 Jim Bill William Looking through the threads I got some nice ideas but... I tried it with IF and with Case but never succeeded. So, I think I'm overlooking something. Please show me the way?
Unexpected Query Result Using IN()
SELECT post_id, post_user, post_subject, post_category, post_date FROM sb_blogposts WHERE '10' IN(post_category) The above query is only returning results if the 10 is the first ( or only ) category number in the post_category field. post_category has been defined as a varchar(255) and contains a listing of categories that each post belongs in. All of the posts in this database have their categories set properly, and some posts are in more than one category. The query example should be returning with every post that has "10" in the list. but for some reason if the category is less than 10, it won't show. It's a bit strange and I don't understand why it's doing that.
How To Repeat A Result Set From A Query?
I need to repeat any times a result from an query, like this: select name, price, code from `acme`.`table` results: chave de fenda 1pol 10.50 048929 chave de fenda 2pol 10.90 048927 Instead of this, I need 7 times the first product and 5 times the second one, like that: chave de fenda 1pol 10.50 048929 chave de fenda 1pol 10.50 048929 chave de fenda 1pol 10.50 048929 chave de fenda 1pol 10.50 048929 chave de fenda 1pol 10.50 048929 chave de fenda 1pol 10.50 048929 chave de fenda 1pol 10.50 048929 chave de fenda 2pol 10.90 048927 chave de fenda 2pol 10.90 048927 chave de fenda 2pol 10.90 048927 chave de fenda 2pol 10.90 048927 chave de fenda 2pol 10.90 048927 Any idea?
Echo A Query Result
If I have a query: $sql= mysql_query("SELECT a.id, b.id FROM table1 a, table2 b WHERE a.id=b.id"); $get= mysql_fetch_query($sql); Normally, if the query is selecting just from one table, then I just use: echo $get[id] But how do you output both a's id and b's id? I tried echo $get[a.id] but it doesnt work.
Strange Query Result
I have a column (tinyint) that has either 1s or 0s in it. When I run this query counting either 1s or 0s it counts all of the rows, not just the ones with 1s or 0s as stated in the query: SELECT COUNT( cb_paiddues = '0' ) from jos_comprofiler or Code: SELECT COUNT( cb_paiddues = '1' ) from jos_comprofile
Query Result Percentage
how I might be able to get each record's query match percentage? What I mean is, say your query contains multiple criteria, of which, a variable number of fields per row will match. You see this often with dating services, e.g. name, height, weight, hobby, etc. Not all fields will match so a percentage is provided, then you can sort by percentage. Is there a mySql function that can do this or must this be done the tedious way - like using PHP to test each value against each field's data per row, then updating, then sort
Output Query Result
Can I output a mysql query result direct to an Access database? I know (probably) I could use outfile to csv and read that through ODBC but then I've got the problem of not being able to overwrite it. I'm thinking that if I can output direct into an mdb it would get around that limitation.
Sub Query To Comma Separated Result?
I have a query that uses a subquery to return all of the color/size options a particular product has. I would like to get 1 set of result and just have the color/size options outputted to a comma separated result. eg. result: +--------------- size_colors +--------------- small / white, medium / white, large / black I thought I had done this before, but I can't find the code and I looked through my old threads on here and I couldn't find anything. Is this available in MySQL? Am I trying to do this in a really bad way? I'd like to avoid looping through my results and also avoid multiple queries. I also need to do this with category names and a keyword table we have.
Display Result From Query In More Then 1 Column
I want to make a query to a database, and display it on my php page either through php code or with 2 queries to my mysql database. This is how i want it displayed: Data 1 Data 2 Data 3 Data 4 Data 5 Data 6 Data 7 Data 8 ... .... Data X Data X This is my current code which i use and get it displayed in 1 row:
Copy The Result Of A Query Into Another Table.
I am trying to make a select with a sum in it and group by account and copy the results in another table. As the site has the 4.0.25 MySQL version i guess i cant make subconsults. I thought of storying the results in vectors, but up to now it didnt work ...
Join Query Result Difference Between 3.23.49 And 4.0.13
I have what seems to me a very common operation i'm performing. I need to find the balance on an invoice. i was not having any problems until the production server was upgraded to mysql v4.0.13-standard for pc-linux. There must be a better way to query for this information than the method i'm using, since the result with v4.0 is not what I expected, nor what I received with v3.23. I'm including sample data and queries with my results.....
Query Result In A Specific Format
I need a query in which will give me a single column result ----------------------------------- | [any column Name] | ---------------------------------- 8:00 AM ---------------------------------- 9:00 AM ---------------------------------- 10:00 AM ----------------------------------- 11:00 AM ---------------------------------- I want the query to return a single column name with multiple rows in return ...
How To Determine How Many Columns Matched In A Query Result?
I have a client who wants a ranked search using php/mysql. Most of the fields being searched are lacking in much text content ie yes/no, male/female etc, so we want to rank the results by how many columns in the table matched the columns searched in the query. Is this possible? If so, how?
Trying To Count The Number Of Rows In A Result Set After Query
The user fills out this form to sign up to the website, the form checks the database to see if the username has already been taken with the code: $conn = mysql_connect("localhost:3306", "root", "********") or die ("Error With Connection"); echo("connected<br><br>"); $db_sel = mysql_select_db("game",$conn) or die ("Error With Database"); $check = "select * from users where 'username' = '$username'"; $db_sel = mysql_query($check,$conn) or die (mysql_error());
MySQL Query Browser. Result Set Read Only
I'm developing a client/server application with mySQL. The mysql server (plus database) is on a Windows XP LAN pc in another office. I installed mySQL several months ago and have been using Administrator and Querybrowser locally on a regular basis to administer and fiddle with the database on the other pc. I don't believe I've changed any settings. I noticed a couple of weeks ago that if I tried to edit table data from Administrator, Query browser wouldnt display but would remain in task manager process list forever. If I did the same directly from query browser I would receive a result set ready to edit.....
Different Result From Query Made In Mysql-front And From ASP
I have a strange problem with a mysql database and ASP. I try to retrieve a value from a table I have just created with a very simple sql statement. Ex. SELECT myValue FROM myTable WHERE userID = 'myName' If I type the query in mysql-front it works fine but if I execute it from and ASP page I get no value. I have tried something similar with other tables in my database without this problem. My ASP script looks like this: <% sql = "SELECT * FROM t_organisation_salg WHERE brugerID = '10025'" set objRS = objConn.execute(sql) IF NOT objRS.EOF THEN response.write objRS("omsaetning_gruppe") objRS.close set objRS=nothing objConn.close set objConn = nothing %> My database connection is included from another file.
UNION SELECT Strange Query Result
I want to combine the results of two queries. One query with a combination of tables and one query in one of the tables where the combination does not count for [so that all devices will be shown, and not only devices that will show after the first query, but also devices that are not being showed in the first query]. The query I made: ..........
Showing Query Result With Default Values When No Data Available
I have a scenario that user can ask for certain data say income & expense for last 3 months on monthly basis. I have wrote following query which works well but it has an issue: Let say I have asked for data of FEB 2007, JAN 2007, DEC 2006 using the following query it shows me correct result when all three months have some data but it doesn't show me the desired result when you don't have data in one or more months. Any suggestion to get result when there is no data:
Select And Union Result Into Crosstab-type In One Query
Maybe this is impossible? I have four tables of identical structure but not related. I am running a Select on four tables with three Union All. I would like the result from the Select and Unions (which work fine) to be routed into a Crosstab-type query to gvie the result from each table in columns. But here is the difficult bit, I would like to do this in one query. As I am new to MySQL, I am confused by subqueries and temporary tables and how to reference them, though I realise the latter may be the way to go. CODE ...
How To Generate Html Output For Query Result With Perl
I can generate the html with the command like: >mysql -ppassword -uusername -H -e "select distinct depID from tabledepart" databasename but i need to know how to generate html output if i already connected with a database, and how to pass the html result to the perl program.
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 Do A "there Were X Results, This Is First Y" Result In 1 Query?
Let's say I have a database of book entries and I want to only retrieve the first 50 that have a certain author, even though there are more than 200 of them. What I'd like to output is a message saying, "Your search found 2xx results. Here are the first 50" Is there a way to do this in one mysql query? I know how to do it in two queries, a SELECT...LIMIT 50, and a COUNT query.
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');
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.....
Pagination W/1 Query + How To Use Query With Indexes
i'm asking 2 questions in 1 thread because i don't wanna take up too much room, hopefully no one will mind. i have mysql 4.1.10 1) i want to find all the rows that were edited this month. the query i currently have ( MONTH(CURDATE()) = MONTH(date) ) doesn't use indexes. how can i manipulate it so i can take advantage of indexes. 2) this is something i've always wondered, but usually just assumed was not possible. if i am listing some results, say 20 per page, how can i get both the total number of results as well as the 20 items required for that specific page. say there are 2 million total results, so grabbing them all and showing just 20 is not an option. if this is not possible what is the most efficient way of making both queries?
Reusing A Query Output In The Same Query
I am guessing a basic question but not one I can find an obvious answer to. If I create a calculated or modified column in a query (such as a modified text string), and then want to reuse that in the same query as I need to do three or four operations on it in sequence, how do I do it in mySQL 4.1? Do I need to create a new column to store the interim result in an existing table (and then clear or alter it each time I run the query), or create a temporary table, or is there an easy way to reuse the query output in the same query (does the query have a name like a table name)? If it requires a new column or table, are there particular disciplines to ensure it is robust and self maintaining?
Create Single Query From Queries On Two Tables (was "Help With Query...")
I read from other thread that query inside loop is not good idea. May I ask some help how can I create a single query to the following code which I use loop. $sql = "SELECT * FROM mytable order by points desc limit 10"; $rec = mysql_query($sql) or die(mysql_error()); $datas = mysql_fetch_array($rec); do{ $sq = "Select * from secondtable where linkid = '$datas[id]'"; $rst = mysql_query($sq) or die(mysql_error()); $rows = mysql_fetch_array($rst); echo "$rows[somefield]"; }while($datas= mysql_fetch_array($rec)); This works perfectly but I want the second query to be out of the loop if there is a way and how.
Grab 'title' From The Table 'forum' Within The Same Query (was "Help With Query")
I have the following query for my vBulletin database: PHP $get_stats_newthreads = $db->query_read(" SELECT thread.forumid, thread.postuserid, thread.postusername, thread.threadid, thread.title, thread.lastpost, thread.forumid, thread.replycount, thread.lastposter, thread.dateline, thread.iconid, thread.views, IF(views<=replycount, replycount+1, views) AS views, thread.visible FROM " . TABLE_PREFIX . "thread AS thread WHERE NOT ISNULL(thread.threadid) AND $weekold<lastpost AND thread.visible!=0 AND (forumid=34 OR forumid=7 OR forumid=8 OR forumid=11 OR forumid=10) ORDER BY rand() DESC LIMIT 5"); and would like to grab 'title' from the table 'forum' where forum.forumid=thread.forumid
Run A Query On The Results Of A Query?
Lets say I have a query that searches for people living in Colorado. That results in a list on a "results.php" page. Now I want to query that result and search further for people who use Linux. I know I can do this from one query, but I would like to create several checkboxes on my results.php page where I do a further secondary query. Possible? I guess I want to query a query.
Query Inside Query?
I would like to know if its practical to Insert a Select statement inside a previous select statements (array). With that said, the 'inner' select having a WHERE statement thats dependant upon the array results...
Rewriting A Query Without A Sub Query
I've recently changed hosts and found that some of my code broke. The new host is using mysql version 4.0.25 which does not support sub-queries (and they won't upgrade). I'm trying to figure out how to rewrite the following query so it will work on 4.0.25 but not getting anywhere.....
Is It Possible To Run A Query On The Results Of A Query?
I have been trying to figure this out but no luck. Lets say I have a query that searches for people living in Colorado. That results in a list on a "results.php" page. Now I want to query that result and search further for people who use Linux. I know I can do this from one query, but I would like to create several checkboxes on my results.php page where I do a further secondary query. Possible? I guess I want to query a query.
Query 2 Tables For Query
I have 3 tables, products, order_log, and groups. Products is a list of products available, groups are groups that products are put in, and order_log is a log of the current products in an order. I need to sort the order_log by the group the products are in. order_log does not have a group_id in it, however products does. So: SELECT * FROM order_log WHERE product's group_id = 1.
Sub Query And Count Query
i have a database with the following structure id | MoveDate | ItemId | SiteID (a new entry is entered when an item is moved from 1 site to another) and i am trying to forumlate a query so that i can count how many days each item was at a specific location so lets assumes i have the following data 1 | 01/01/2007| 1 | 1 2 | 03/01/2007| 1 | 2 how can i run a query that will tell me that between the dates 01/01/2007 and 08/01/2007 item 1 was at site 1 for 2 days and site 2 for 5
|
TRACKER
