Save Query Results
In other words, how do I build a new table from the results of a query, so I can access pieces of this data later without performing the same query (a very heavy one) over and over again?
View Complete Forum Thread with Replies
Related Forum Messages:
Filter Results Of One Query
I'm looking for the syntax that would allow me to filter one querys results using another query i.e. QUERYA = SELECT * FROM table WHERE id < 100 QUERYB = SELECT * FROM QUERYA WHERE id <> 49 I understand that I can do the above example in one query - I'm just using that to simplify matters.
View Replies !
Order Query Results
Regarding a dummy set of data below, ID Status 1 Open 2 Closed 3 Expired 4 Closed 5 Open 6 Cancelled 7 Expired 8 Cancelled I want to view the results but order them according to the status. i.e. i want to view in order of Open, Closed, Expired, Cancelled I can only think of a long drawn out way by which i would create the four seperate queries.
View Replies !
Query Results By Pages
I would like to display query results in pages of say 20 results each. e.g. a table with two columns, serial# and descrition, with a few thousand entries. What would be the SQL query to find a specific serial number, and display all 20 results within the "page" that row happens to be in? e.g say serial#=3211 is in row 64, display rows 60->79.
View Replies !
Formatting Query Results?
I have a shell script (sh) that queries a MySQL database and then formats the results to an html page. I realize shell is probably not the best choice for this but it's all I know. My problem is that I need to do a mysql query for every field in every record in order to put it into the html page. This is obviously very slow. This is an example of what I'm doing now: ProductLine=`mysql -u www -s support <<EOF SELECT ProductLine FROM Documents WHERE ID = '$ID'; EOF` DocType=`mysql -u www -s support <<EOF SELECT DocType FROM Documents WHERE ID = '$ID'; EOF` DocName=`mysql -u www -s support <<EOF SELECT DocName FROM Documents WHERE ID = '$ID'; EOF` FileName=`mysql -u www -s support <<EOF SELECT FileName FROM Documents WHERE ID = '$ID'; EOF` Like I said, I am doing this for every field in every record. It works but it is VERY slow. I thought if there was a way to tell the query to insert a special charater between each field then I could use awk to pick out the fields and only do one query per record... something like this: SELECT ProductLine, DocType, DocName, FileName FROM Documents WHERE ID = '$ID'; I don't know how to take that output and put it into my shell variables.
View Replies !
Saving Query Results
Is there any way to save the results of a query without the quote marks? When i try to import the text file that I saved the query to, I have to go thru and delete all of the quote marks.
View Replies !
Query Giving Me Results I Don't Want.
query: SELECT sales_reps.sr_id, sales_reps.order_id, sales_reps.name, sales_reps.job_number, UNIX_TIMESTAMP( shop_orders.date ) AS date, sales_reps.stage, sales_reps.status, pi.fname AS pfname, pi.lname AS plname, sales_reps.date_to_shop, users.builder, users.division, shop_assignments.editor_id, shopper.fname AS sfname, shopper.lname AS slname, ei.shop_result, ei.good_tape FROM sales_reps LEFT JOIN shop_orders ON shop_orders.order_id = sales_reps.order_id LEFT JOIN users ON users.user_id = shop_orders.builder_id LEFT JOIN shop_assignments ON shop_assignments.sr_id = sales_reps.sr_id LEFT JOIN users AS shopper ON shopper.user_id = shop_assignments.shopper_id LEFT JOIN users AS pi ON pi.user_id = shop_assignments.pi_id LEFT JOIN exit_interviews AS ei ON ei.shop_id = shop_assignments.shop_id WHERE sales_reps.status = 'Rejected' AND ( ei.shop_result != 'Trip x1' OR ei.shop_result != 'Trip x2' OR ei.shop_result != 'Non-Reg Trip x1' OR ei.shop_result != 'Non-Reg Trip x2' OR ei.shop_result != 'Office Closed' ) ORDER BY users.builder, users.division, shop_orders.date DESC , sales_reps.stage ASC It's not filtering out the records with the shop_result field matching what I've listed.
View Replies !
How To Get Distinct Hour(s) In Query Results?
I need to write a query that will count the number of records that have been written for each hour of the day. This must be done entirely in SQL, and I am not an SQL expert. I know how to break the hour out of a timestamp using the hour() function, but I'm unclear on how to get the breakdown like I described above.
View Replies !
Eliminating Duplicate Results From Query
I'm trying to dynamically build pull down menus based on the contents of a column (in order to build a search query). But the column may have the same data entered multiple times. This results in a pull down that has the same option listed several times. I wonder if I can eliminate duplicates from that array? So that it only appears once in the array.
View Replies !
Update Query Results To Another Table
So I'm learning, bear with me, this is probably pretty easy(but I am a bit perplexed) but I'm writing a php page for a cron. I am selecting a user name from a table basically selecting all row entries in that table with a certain username, and adding them together. $query=mysql_query(SELECT UserName, SUM(AcctOutputOctets) FROM radacct GROUP BY UserName) The output is this(when I run a query in mysql) UserName..............SUM(AcctOutputOctets) test..................... 345566 Basically i'm adding up all the users entries and totaling their Usage. Now the question. I want to write that to another table named mtotacct. format like this. Username..............TotalOctets. I'm just a little uncertain how to properly set this up to pass the results from the query to the UPDATE statement.
View Replies !
Showing Query Results In HTML
In mysql I can find the minimum data from a particular column by doing as follows: SELECT MIN(Freq_Min) FROM datatable; and mysql will display the results. Now I want to do the same thing but I want to do it in html, so I wrote the following code. When I open up the webpage stats.php it says that it couldn't execute the query. Can someone help get me on track? .....
View Replies !
Use Query-results As Delayed Insert?
Can I use the results of one query like: -> SELECT user_id FROM tbl_customers And use the results to fill the VALUES-statement in another table like: -> INSERT INTO tbl_postal ('user-id') VALUES (<previous query results>) If so, could someone complete my second SQL-statement, since I'm staring at my CRT for over 2 evenings and I cannot get it to work.
View Replies !
Query Is Not Returning Desired Results.
My query is not returning desired results. I want to structure my query to return the Post Subject, Post Text, and Poster. Instead, it is returning the correct Post Subject, correct Post Text, but the Poster is just repeating the username from the '$_POST[username]' variable. How can I alter the query to return the correct name? the query PHP <?php$conn = mysql_connect( $domain, $user, $password ) or die("Err:Conn");$rs = mysql_select_db($db, $conn) or die("Err:Db");$sql = "SELECT `table_posts_text`.`post_subject`, `table_posts_text`.`post_text`, `table_users`.`username` FROM table_categories, table_forums, table_topics, table_posts, table_posts_text, table_users WHERE username='$_POST[student]' ANDcat_title='$_POST[category]' AND`table_categories`.`cat_id`=`table_forums`.`cat_id` AND `table_forums`.`forum_id`=`table_topics`.`forum_id` AND `table_topics`.`topic_id`=`table_posts`.`topic_id` AND `table_posts`.`post_id`=`table_posts_text`.`post_id`AND `table_topics`.`topic_poster`=`table_users`.`user_id`AND`table_posts`.`poster_id`=`table_users`.`user_id`ORDER BY `table_topics`.`topic_id`, `table_posts`.`post_id`"; $rs=mysql_query($sql,$conn);while($row=mysql_fetch_array($rs)){ echo($row['post_subject'] . $row['post_text'] . $row['username']);}?> There are six tables involved: 1. table_categories (cat_id medint, cat_title varchar) 2. table_topics (topic_id medint, forum_id smallint topic_title varchar topic_poster medint) 3. table_forums (forum_id smallint, cat_id medint, forum_name varchar) 4. table_posts (post_id medint, topic_id medint, forum_id smallint, poster_id medint) 5. table_posts_text (post_id medint, post_subject varchar, post_text text) 6. table_users (user_id medint, username varchar)
View Replies !
Reversing Query Results (not The Same As ORDER BY)
I am trying to reverse the order in which the results of my query are given. I am tweaking a gallery. I need to get the previous 3 pics based on the pic I am looking at. The following code is a simplified version of what I have. The problem is, when I echo the results, the thumbnails are displayed in DESC order (i need them to display in ASC order) but if I order the query using ASC, the query gets the wrong images. SELECT pic_id WHERE pic_id < $current_pic_id ORDER BY pic_id DESC LIMIT 3 I need the results to show like this: oldest pic | older pic | old pic | current pic | new pic | newer pic | newest pic I've got the new pics sorted out but the old pics are causing a little problem... So basically, is there a way that I can reverse the results of my query?
View Replies !
Mixed ORDER BY Query Results
Is there a way to order a query result a specific way? For instance, I'm querying using WHERE id IN(23, 25, 19) I would like the results to stay in that order instead of by ASC or DESC. Is this possible?
View Replies !
Case Insensitive Query Results?
I've been noticing that the MySQL 4.1.19 server on a client's Web server returns case-insensitive results for SELECT statements when using PHP's PDO extension. I didn't notice this happening on my XP dev server with MySQL 5.0, and have never seen it happen on any other server. Does anyone know why this happens and what can be done about it?
View Replies !
Limiting Results In Query Question
I've got a SQL query which i want to return one result from each development in the database. The result i want to be returned is the first image in the database related to a development. There are currently two developments in my database however all the images that are stored in the database are returned and not one per development. QUERY: select development_images.development_id, developments.title, developments.description, development_images.url, development_images.image_title from developments, development_images WHERE developments.id = development_images.development_id order by developments.id DESC LINK: http://demo2.pixel-room.net/developm...dex.php?page=1
View Replies !
Combine All Query Results In One Column
I got a query similar to that: Code: SELECT c_01, c_02, c_03, c_04, c_05 FROM table WHERE c_01 != '' OR c_02 != '' OR c_03 != '' OR c_04 != '' OR c_05 and I get results similar to: Code: +---------+ ---------+ ---------+ ---------+ ---------+ | c_01 | c_02 | c_03 | c_04 | c_05 | +---------+ ---------+ ---------+ ---------+ ---------+ | 0 | 0 | 0 | 0 | data_06 | | data_01 | data_02 | 0 | data_04 | 0 | | 0 | 0 | data_03 | data_05 | 0 | | data_07 | 0 | 0 | 0 | 0 | +---------+ ---------+ ---------+ ---------+ ---------+ But the result I need is something like: Code: +---------+ | c_ALL | +---------+ | data_01 | | data_02 | | data_03 | | data_04 | | data_05 | | data_06 | | data_07 | +---------+ I just can't figure out how to combine several column in 1 column. I could loop through everything in PHP but that data is also sometimes redundant so I'd like use GROUP BY first before it gets to PHP. I have no control over the architecture of the DB...
View Replies !
Query Exact Results And The Wildcard
I need to run a query that pulls 100% accurate data. For this I need to use the "equal" rather than "LIKE". To throw a monkey wrench in the equation, not all fields are mandatory. When the user does not choose to search by a field, I simply want the query to treat it as ALL. Normally I would use the wildcard for this, but the wildcard only works on LIKE. If I use EQUAL, MySQL wants to match the wildcard which of course returns no records. EXAMPLE DETAILS: Variables are retrieved and sanitized from POST. For the sake of this example, I will keep it simple. Each variable collects a the POST if it exists, and if it does not exist it will set the default to "%" (the mySql wildcard). The variables are as follows: $city, $state, $gender The Query would be: SELECT * FROM users WHERE city = '$city' AND state = '$state' AND gender = '$gender'
View Replies !
Retreiving Large Query Results In Chunks
I'm running queries with MySql 4.0.17 that return thousands of records. Because I need to present them in GUI, I returieve the results in chunks using LIMIT, for example - get first 100, then the range 100-2000 and so on. The problem is as follows: in the first chunk, MySQL uses one strategy to fetch the results, and in the following chunks - a different strategy. This means that records from the subsequent queries might have records that already appeared in the first query or that some records will be left out. For performance issues it is a problem to add a unique secondary sorting criteria (like id) to the query. Is there a clean way to force MySQL to relate to the first (initial) query result set?
View Replies !
Conditional Select Based On Query Results
I want to print a different message on the database, if a query returns an empty set and a different if the query returns any records. How can i accomplish that? I looked at the case statement but i can't get it working with that.
View Replies !
Running A 'Distinct' Query Returns Some Results TWICE !?
I've spent hours trying to find out why a perfectly simple query that uses the 'distinct' keyword such as: Select distinct(field1) From tablename; works fine based on eg. field1, but when running it on eg. field2 in the very same table, it returns 2 results (yet, only on some words!). Pls see example of result below: Eg of a 'faulty' result: .....
View Replies !
Query To Ignore Results If Condition True
I am having trouble defining a query that ignores rows if a query if true IE. Column 1 = 1,2,3,4,5 Column 2 = a,b,a,a,c For example: I search for results from column 1 and if '3=a', ignore all 'a' rows thus returning the result b=>2, c=>5.
View Replies !
How To Concat Sub-query Results In A Single Cell
I have an user table, a product table and a product_affect_user table. +------+------------------+ | user | other column.... | +------+------------------+ | 6478 | firstname, lastn | +------+------------------+ +------+-------+ | prod | descr | +------+-------+ | 0932 | blaah | +------+-------+ +------+----------+ | user | product | +------+----------+ | 6478 | 923 +------+----------+ That I try to do is to fetch all product affected to an user in the same cell, something like that: +------+------------------+ | user | affected product | +------+------------------+ + 6478 | 1,45,324,673,923 | +------+------------------+
View Replies !
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? .....
View Replies !
Returning 1 Tables Results From Two Table Query
I am executing a query which finds a criteria from certain records in table A and returns all values from table B if the criteria in table A is true. I want to return only the data from table B but can't find an easy way to do that? E.g the MySQL 'From' expression contains both tables.
View Replies !
Use A Mapping Table To Get Query Results From Several Tables
Ok, so I have 4 tables... Projects -> id, project_name, project_type Variables -> id, variable_name Symbols -> id, symbol_name Mapped -> project_id, variable_id, symbol_id if I had to find (project_id=5, variable_id=2, symbol_id=3) AND (project_id=2, variable_id=14, symbol_id=1) AND (project_id=34, variable_id=78, symbol_id=44) How could I write a query that would return the results for these all at once? I would like project_name, project_type, variable_name and symbol_name returned from their respective tables for each. Is that possible? I've been looking at inner joins and managed to make it work for 2 ids but not 3
View Replies !
Difficult Query - Need To Group Results By Id And Sum Values
I have got the meat of this query done but I am facing a problem. I am doing a VAT analysis whereby I have every shoe that is over size 7 I pay tax on and every shoe below that I don't pay tax on. I also pay tax on accessories. What I want the query to return is this: Date | ShopperID | VATable Amount | Non VATable Amount| 2006-3-1 | 802135 | 146.95 | 54.00 | Basically each shopperID will only appear once which is why I am grouping the results (which I have managed). But I also need it to total up all the VATable amounts that it finds too for both the fields on the right above. I can get the date and order number without difficulty and I have managed to get the data like this so far where I have the same ShopperID where a shopper has bought more than 1 product: +------------+----------+-----------------+---------------------+ | Date | ShopperID| VATable Amount | Non VATable Amount | +------------+----------+------------+---------------+ | 2006-09-04 | 805284 | 0 | 64.00 | | 2006-09-04 | 805287 | 2.95 | 0 | <-- Here a | 2006-09-04 | 805287 | 3.25 | 0 | customer has | 2006-09-04 | 805287 | 3.45 | 0 | bought 4 products | 2006-09-04 | 805287 | 4.95 | 0 | - I need total | 2006-09-04 | 805327 | 0 | 53.95 | under each | 2006-09-04 | 805335 | 0 | 58.95 | ShopperID | 2006-09-04 | 805414 | 0 | 64.95 | | 2006-09-04 | 805414 | 3.25 | 0 | | 2006-09-04 | 805414 | 0 | 64.00 | | 2006-09-04 | 805414 | 0 | 69.00 | | 2006-09-04 | 805423 | 0 | 64.95 | | 2006-09-04 | 805423 | 0 | 69.00 | | 2006-09-04 | 805423 | 0 | 64.00 | | 2006-09-04 | 805423 | 3.25 | 0 | +------------+-----------+----------------+--------------------+ Heres the query currently getting these results (without the GROUP BY ShopperID). SELECT Date,orders.ShopperID, CASE WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9][7-9]( )?(' THEN items_ordered.price -- If size 7 or above add price WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9]10( )?(' THEN items_ordered.price -- If size 7 or above add price WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9]11( )?(' THEN items_ordered.price -- If size 7 or above add price WHEN SUBSTRING_INDEX(Product,',',1) REGEXP 'Insole|Helmet|Laces|Wheels|Removal|Protection|Bag' THEN items_ordered.price -- If it is an accessory add VAT ELSE 0 END AS 'VATable Amount', CASE WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?( )?(Kids)?( )?( )?(UK)?( )?( )?(Kids)?( )?( )?[^1-9][1|2|3|4|5|6]( )?(' THEN items_ordered.price -- If size 1 - 6 add price to other column WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?( )?(UK)?( )?( )?Kids( )?( )?(UK)?( )?12|13( )?( )?(' THEN items_ordered.price -- If size kids 12/13 add price to other column ELSE 0 END AS 'Non VATable Amount' FROM items_ordered,orders WHERE (items_ordered.ShopperID = orders.ShopperID) AND (Date >= ��-1-30') AND (Date <= ��-9-31');
View Replies !
Query Showing More Results In PHPMyAdmin Than In My Application
I am trying to run a query on my database, my database class returns 5 results, when I run it on PHPMyAdmin (direct copy), it returns 15 results. I am 100% sure I am copying the exact query. I tried my customer db class and ADODB. I've tried many search queries, so it's not just this one. MySQL: 4.0.27-standard PHP: 4.47 Any ideas would be great appreciated, I am pulling my hair out......
View Replies !
Unique Results On Join/Union Query
I have 2 tables: Table 1: groupid, groupname etc, Table 2: groupid, firstname, lastname etc; I have these data in table 1: 1, groupname1 2, groupname2 In Table 2 I have: 1, Fname1, lname1, 1, fname2, lname2 1, fname3, lname3 2, Fname1, lname1, 2, fname2, lname2 For each loop, i'm looking for these results: ROW1 Groupname1 + all matching fname,lname using its groupid ROW2 Groupname2 + all matching fname,lname using its groupid
View Replies !
FullText In Boolean Mode Search Query Missing Results
I have this search query that I originally put together from a mixture of tutorials but I’m not sure that it’s working correctly. Secondly, if I change the search to “Leeds United” or try anything in quotes it fails and the same if I try a string. Thirdly, it would just be good to get some professional eyes over it to see if it can be improved. Code: ....
View Replies !
How To Construct Query For Results With Limit (Grouped By Values In Another Field)
Field 1 : GroupName Field 2 : IndexNumber The sample data could be {(HR, 1), (HR, 3), (HR, 5), (MANAGER, 10), (MANAGER, 12)} I wanted to pick the first two values for each GroupName sorted by IndexNumber ascending. So if I wanted 2 results for each GroupName, the result would be {(HR, 1), (HR, 3), (MANAGER, 10), (MANAGER, 12)} This will exclude the third set of data (HR, 5).
View Replies !
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.
View Replies !
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.
View Replies !
Query From Query Results
I have a report I'm working on that is sort of like google adsense, where it tracks ad clicks and views. I need to provide the option for the user to narrow down the results by date. I have my query worked out, but would it be better for me to run the query again and add the date information to the query, or should I cache the results and then query them? If I were to cache the results and then run queries off that, what would be the advantages or disadvantages? I also have no idea how to go about doing that. Should I create temporary tables to hold the queried information or is there another way?
View Replies !
|
TRACKER
