Query Single Table, Multiple Times
Before i narrate my issue, i would like you to preview my table structure :
Table name = machine_info
+------------+----------+------------+
| machine | tag_name | tag_type |
+------------+----------+------------+
| machine101 | sge | farm |
| machine101 | US | site |
| machine101 | CRITICAL | status |
| machine102 | CANADA | site |
| machine102 | UP | status |
| machine102 | sge | farm |
| machine103 | CHINA | site |
| machine103 | DOWN | status |
| machine103 | sge | farm |
| machine104 | US | site |
| machine104 | CRITICAL | status |
+------------+----------+------------+
Requirement :
I would like have machines from all 'sites' (almost 15 in the actual db) with
1)status='DOWN'
2)status='CRITICAL'
3)status='UP'
Example: (Here the results would be like)
SITE|DOWN|CRITICAL|UP
US |0 | 2 | 0
CANADA|0|0|1
CHINA|1|0|0
I use php to extract information, here's what i do :
<?php
$db = mysql_connect("hostname", "user", "password")
or die(mysql_error());
$selected = mysql_select_db("dbname") or die(mysql_error());
$site_codes = mysql_query("select distinct(tag_name) from machine_info where tag_type='site';");
echo "SITE|DOWN|CRITICAL|UP
";
View Complete Forum Thread with Replies
Related Forum Messages:
Joining A Table Multiple Times
I was wondering if it was possible to join 2 tables on 3 different feilds, but I don't want the 3 feilds to limit my results... I'll try and explain: Table 1 = "offices" Table 2 = "employees" The offices table has the addresses of all the offices and 3 extra feilds "manager", "supervisor", and "employee". (these 3 feilds have a numeric value equal to the primary key in the employees table) The employees table has the employee information like address and phone number and such. I would like to write a query that would pull the office name as one feild, followed by "manager", "supervisor" and "employee". Currently I have this, but it only pulls the employee and I'm not sure how to get the manager and supervisor to be a part of the query: SELECT CONCAT(office.id_store, ' - ', office.storename) as offices, CONCAT(user.lastname, ', ', user.firstname) as fullname FROM office LEFT JOIN user ON office.empoyee = user.id_user
View Replies !
Linking Table Entries Multiple Times
I have no idea how to handle certain kinds of relations between tables: I have a table called 'labels' and a table called 'artists'. Every artist belongs to at least one label, but could also belong to any higher number of them. How would I solve this in my database architecture? I discarded the idea of simply adding 10 columns called 'label1', 'label2', etc; Also I have found the ENUM and SET datatypes, but the possible values entered there should be set at table creation time which is also not quite what I'm looking for.
View Replies !
Multiple Table Calls And Slow Loading Times... Joins?
just recently started using MySql and I think I've got most of the basics down - everything WORKS just not well. Essentially, I'm making an image gallery and the search/landing pages have thumbnails, pretty straightforward. The thumbnails are rollover slideshows of the images in the gallery so for each thumbnail preview there are a varying amount of actual thumbnail images that are loaded. The table structure is to this effect: gallery table: gallery_id, gallery_views, gallery_rating etc etc thumbnails table: gallery_id, thumbnail_link So i may have an entry as such: gallery_id = 21, gallery_views = 300, gallery_rating = 60 With several thumbnail entries: gallery_id = 21, thumbnail_link = url/image1.png gallery_id = 21, thumbnail_link = url/image2.png gallery_id = 21, thumbnail_link = url/image3.png What I have been doing thus far, say for the index page I grab the top 30 by views, is something like "SELECT * FROM galleries ORDER BY views DESC LIMIT 30" and then after bringing that into PHP in my while statement on each iteration I make a separate call that is like "SELECT thumbnail_link FROM thumbnails WHERE gallery_id = '$gallery_id'" and then output all the thumbnails for the rollovers. So let's say then for example that I want to show the top 30 by views, as well as the top 30 by user rating. At that point I'm making two calls to the galleries table, which arbitrarily we'll say contains 50,000 entries, but I'm also making 60 individual calls to the thumbnails table, which contains in some cases 20 or 30 thumbnail links per gallery and contains upwards of a million rows. Obviously my loading times are much higher than I would like them to be and I can't imagine this is the optimal way of making these calls. It seems like a fairly elementary concept but I can't seem to find something that works.
View Replies !
Insert Multiple Rows Single Query
I create a table with 7 columns id Mdate col1 col2 col3 col4 col5 as above shown id is small int, mdate is date and col1 to 5 are tinyint and i want to insert the values . id is auto increment and i want mdate a day for one row. and other column set to zero what i want is to enter in single query.
View Replies !
UPDATE Multiple Rows In Mysql (in One Single Query)
trying to UPDATE multiple rows with mysql. I know how to do it with multiple queries but i think it would be less resource consuming generating mysql query code with php and update all one single step. here is the method i usually employ: $value_column_1 = array(); $value_column_2 = array(); .....
View Replies !
Multiple COUNTs Against A Single Table
I want to get some counts from a single database table and I can't figure out how (or if) I can do it in one statement. I have a table of private messages and would like to get counts on new, read and trashed messages for a user. The table: pms ---- msgID (int) (idx) toUserID (int) fromUserID (int) msgSubject (varchar) msgBody (text) msgRead (enum 'y','n') msgTrashed (enum 'y','n') I would love to do know if it can be done without using multiple separate COUNT queries. Any way you can do this with one query? Right now I'm using 3 SELECT queries which all check against the toUserID... new: SELECT COUNT(*) FROM pms WHERE toUserID=1 AND msgRead='N' AND msgTrashed='N' read: SELECT COUNT(*) FROM pms WHERE toUserID=1 AND msgRead='Y' AND msgTrashed='N' trashed: SELECT COUNT(*) FROM pms WHERE toUserID=1 AND msgTrashed='Y'
View Replies !
Joining Multiple Fields To A Single Table?
I have 1 table with 2 columns, 'id' and 'name': tbl_names: idname ------ 1Bob 2Jeff 3Fred 4Joe 5Bill I then have another table which contains several fields which hold id's from the above table: tbl_output: idperson1person2person3 ----------------------- 1231 2543 I need a query that will return the names for the specified id from tbl_output. If I have just one 'person' field in tbl_output I would do it with an inner join like this: SELECT name from tbl_names INNER JOIN tbl_names on tbl_names.id = tbl_output.person WHERE tbl_output.id = ? but I can't figure it out when theres multiple fields to be joined from the same table...e.g I want to specify tbl_output.id = 1, and it give me: person1person2person3 --------------------- JeffFredBob
View Replies !
Multiple Foreign Keys To A Single Table
I searched for this topic but the closest thing I found was a listing for Multiple Foreign Keys to Multiple Tables. I could also have not been searching for the right thing. I have a table that contains a list of people. And I have another table that has two foreign key columns, each setup as a foreign key to the people table. I am trying to do a join on the tables so that I can retrieve the names of both people from the people table in one record. Is this something I can do with a single join or will I need to use nested queries? Will I need to make a mysql table or a view?
View Replies !
Multiple Tables Of Data, Single Category Table
I've searched and can't find what I'm after, so apologies if this has been covered before. I'm working on a small and simple CMS for a site I'm doing, and just as I was going to start the database I realised something... (I'm using PHP and MySQL) When it's finished, there will be articles, weblogs and content/features. Previously I've done a seperate categories table for each table I have, for example articles and articles_cats. Then a field in the articles table for the category. Now I'd like to use the same categories table for everything on the site. However, I'd really like to have a link table, so each article can have multiple categories. Would I have a table to link articles and cats, then a table to link weblog posts and cats?
View Replies !
Multi Table Query, Grab More Than Single Row From Secondary Table
I have a query like so: PHP Code: $selectt = mysql_query("SELECT trailers.trailer_id, trailers.trailer_title, trailers.description, trailers.file_url, trailers.runtime, trailers.views, trailers.rating, trailers.tt, trailers.keyw, trailers.date, tags.snub, films.film_title, films.studio, films.keyw, films.image FROM films,tags,trailers WHERE trailers.trailer_id=tags.trailer_id AND trailers.film_id=films.film_id ORDER BY trailers.trailer_id DESC LIMIT 8",$dbh); I know this is pretty sloppy, I have multiple 'tags' per trailer ID, and I was hoping to grab all of them in a single query; maybe into an array? So that it shows the row, and then all the tags that belong to it. Possible?
View Replies !
Query A Single Table, But Check A 2nd Table First
I have two tables. tblUsers and tblOrders. tblUsers contains the user information, userID, userName, userPassword, etc. tblOrders contains a list of all the orders placed by the users, it includes a userID column to link an order to a user. What I want to do is run a query and return a list of users who have NOT ordered anything. Because tblOrders has a userID column, I can pull up a list of users who have ordered. But I want to do the opposite.
View Replies !
One To Many Multiple Times
I have a 'links directory' database that currently contains 4 tables: category, subcategory, region and links. My links table contains company names and, among others, three fields catID, subcatID and regID. Each of these are related to primary keys in the other three tables, category (catID), subcategory (subcatID) and region (regID). My issue is that the records in the links table can be in multiple regions, multiple subcategories and multiple categories, but the way I have it designed, each record in links can only be associated with a single catID, subcatID and regID. Currently the only workaround I can think of it to have duplicate entries in the links table for a single company, but with every variation of catID, subcatID and regID. This will result in a very large table, but I cannot currently think of any better idea.
View Replies !
Select Same Row Multiple Times Using Column
I've seen this asked before and although someone answered that it is possible, they did not post the solution, so if anyone can enlighten me that would be helpful. Basiclly with table info name | quantity john | 3 anne | 1 will | 2 Return results john john john anne will will
View Replies !
Check Same Column Multiple Times
What would like help with is how to best structure a query that will select user_id and username from Table1 where user_id matches user_id in Table2 and BOTH (group_id = 6 where value = 10) AND (group_id = 8 and value = 15) in Table2 (or even more comparisons) If both conditions are not met in table two, then no results are returned.....
View Replies !
Select Multiple Rows With Multiple Values In A Single Statement
when selecting multiple rows with different values in a certain row.. is this proper syntax? SELECT * WHERE name IN ('hello', 'cookie', 'smile', 'police', 'fun') It seems to work fine, but It came from another SQL version I believe .. not mySQL. Just wanted to double check, or see if there is a more correct way.
View Replies !
Select / Insert Multiple Rows As A Single Row Of Multiple Columns
I have a nice database set up that contains information about orders and the items on those orders. If an order has 10 items on it, I can select the item data which returns 10 rows of data (let's say 5 colums each). Beautiful! Now I find myself needing to satisfy a program that requires all of the data on a single row. I can do this in a higher level language, but if I could accomplish it all in mysql it would be better. I don't need to sum or do any calculations. I just want to select those 5 columns of data about those 10 rows worth of items as a single row with 50 columns. For example, I'd want this: 1-1,1-2,1-3,1-4,1-5 2-1,2-2,2-3,2-4,2-5 To become: 1-1,1-2,1-3,1-4,1-5,2-1,2-2,2-3,2-4,2-5 The first complication is that the number of items on an order is variable, but is always at least 1 and can not exceed 20. The closest I've been able to get is to do something like: SELECT GROUP_CONCAT(item_number,",",qty,","",description,"",",price,",",location_number SEPARATOR ",") FROM items WHERE order_number=12345 This will give me a single text string containing the value content of the INSERT query (which will need to be manipuated outside of the SQL query to pad it with NULL values for the unused items' columns etc).
View Replies !
Would Like To Avoid Iterating Through Data And UPDATING Multiple Times
I've got product data that belongs to a category table. The business requirement is for the user to be allowed to update the order in which products appear in that category. There is a field called product.sequence for that very purpose. I'm being given the product id's in order for each category. Do I have to iterate through the data in PHP and UPDATE the table w/ many SQL statements, or is there one SQL statement where I can provide the list of products to update in order? I've used MySQL user variables before (e.g. SELECT @m:=0;) but I don't see how I can use it here... Is what I want possible in one query?
View Replies !
Measuring Query Times
I have read a few posts here and see that some people have questions regarding the length of time it can take to perfrom their queries. I would be quite happy reaching that stage because at least I would know how long mine take. :| How can the time delay be forced to output? Using MySQL 5.0.
View Replies !
Report Of Query Execution Times
Does mysql have a tool to record and report a list of all queries executed and how long it took to execute each query? If not can anyone suggest a tool I can use to do this? I've heard of mysql query profiler but does it capture queries from all sessions?
View Replies !
How To Find Out How Many Times A Query Has Been Executed
How can you count the number of times your query has been executed? So I'm trying to set it up, so that I get a variable on every time someone views a review. I have two querys right now, but honestly don't really know what I'm doing. And I'm assuming I have to increment viewcount everytime that query gets executed. This query looks up the review:
View Replies !
Single / Multiple Instance(s)
I'm Using MySQL 5.1 and very new to Databases/Coding. i have a helpdesk system at work which i've been asked to write a statistics page for.. i have created one view which is to show the closed tickets and how long they took to resolve (results), but how dow i create a view to give an average resolve time per user. I tried the following code; SELECT `owner` as User FROM `phpdesk_tickets` WHERE 1 which displays user 1 | 2568 seconds user 1 | 2568 seconds user 1 | 2568 seconds user 2 | 2533 seconds user 2 | 2533 seconds user 2 | 2533 seconds How do i get it to display a single instance for each user instead of one for each ticket closed? I also tried; SELECT `owner`, (SUM(resolve_time)/COUNT(ticket_number)) as Average_Resolve from results where 1 but this only showed user 1 and not user 2 aswell
View Replies !
Multiple Records Single Value
I have been developing a realtor intranet system. I am currently writing a php script that deals with 2 tables "property" and "photos" property table: id | address | etc.. ---------------------------- 1 56 My Road 2 389 Your Street photo table: id | file_id | default | property_id --------------------------------------- 1 3434... 0 1 2 343c... 1 1 3 3udu... 0 1 I have added a field "default" in the "photos" table. The purpose of this is to set the default photo for the property listing. My problem is: I want to perform a single update query as I currently only know how to perform this operation with two. eg: $sql1 = "UPDATE `photos` SET `default` = 0 WHERE `property_id` LIKE {$var}"; $sql2 = "UPDATE `photos` SET `default` = 1 WHERE `file_id` LIKE {$var}"; Any way of doing the above 2 queries in a single query?
View Replies !
Multiple Table Query
I'm building an entertainment/food/festival website for my community using PHP and MySQL and have hit my first "speedbump" in the process. I have a simple text form that I need to query 3 seperate tables in my database (venue_profiles, artist_profiles and events) so if someone searches for a term, it will return a numbered list of everything regardless if it is a venue/artist profile or event. Everything was working fine when I only had to query 1 table but now I've been thrown a curveball and need it to search 3. Here are the tables:fields in question. 1) venue_profiles: id, name, city, category, description 2) artist_profiles: id, name, description 3) events: id, title, city, description What will the query look like for what I need? and how do I go about creating one simple list that includes all the results from all 3 tables?
View Replies !
Multiple Databases Or Single Database
Can anyone plz tell me the advantages and disadvantages of using multiple databases / single database for all tables. I have a site with more than a 100 tables. Current all the tables are in a single database, so that I can access it with a single Database Wrapper Object (PDO). Should I split it into multiple databases? Will it affect the speed?
View Replies !
Multiple Values In Single Field
If there is an existing field with values in it (1,2,5,6) comma deliminated, what is the process for querying and pulling these out? Is there something that would be combined with IN that would convert the values somehow?Thank you for any thoughts, and please rest assured that I am aware of the ill nature of multiple values in a single field.
View Replies !
Multiple Sums In A Single SELECT
I must have a single select execute 2 sums, from two different tables. For example: SELECT tbl1.ID as ID, SUM(tbl1.value1) AS Money_Earned, SUM(tbl2.value2) AS Money_Spent FROM income tbl1, expenses tbl2 WHERE tbl1.ID = tbl2.ID GROUP BY tbl1.ID I get a table back in the form I expect, but instead of a sum, it seems I'm getting a product of the number of entries in the table that meet the criteria * the sum of those values (value1 for example in the first sum statement). In other words, if my table income table has 2 entries for ID = 1, and each value has a 5 and a 6 respectively, the result I'll get back is not 11, but rather 22.
View Replies !
Single Foreign Key To Multiple Tables?
I have 3 tables as follows: Table A: id (int) (PK), codeOfTypeA (Varchar), description (Varchar) Table B: id (int) (PK), codeOfTypeB (Varchar), description (Varchar)...(some more columns) Table C: id (int) (PK), form(varchar) I need to create a table to hold relationship between Table A - Table C and Table B - Table C So, the table will be something like: Table D: id (int) (PK), formid(int) (FK referencing TableC.id), codeid(int) Is is possible to have a a constraint on Table D to ensure that codeid column can have values that exist in either TableA.id OR/AND TableB.id? I just can't image what kind of a constraint that would be. Can TableD.codeid be a foreign key on both TableA.id and TableB.id so that it would require that AT LEAST TableA or TableB have a record with that ID?
View Replies !
Multiple Table Query Problem
I have two tables users and cart. SELECT users.id, cart.id FROM user, cart WHERE users.id = cart.id when i run above query it pull the result correct. but SELECT users.id, cart.id FROM user, cart WHERE users.id != cart.id when i change the query not equal its pull multiple results.
View Replies !
Creating A Single Field From Multiple Row Results
I know how to combine multiple columns to get a single result field but I'm not sure how to combine rows from the same column into a single result field. Basically what I want to do is the following. If my table looks like: risk | reference -----|------ a | 1 a | 2 a | 3 I want a query that will give me: risk | references -----|------------ a | 1, 2, 3
View Replies !
Insert Into Single / Select From Multiple Tables
I have two tables from two databases (joomla2.jos_content and maxdev.jos_content) that I need to pull data from in order to populate a single table (joomla2.jos_magazine) Here is what I want to do (I know this query doesn't work but you get the idea what I am trying to do) insert joomla2.jos_magazine_articles (joomla2.jos_magazine_articles.name, joomla2.jos_magazine_articles.article_id, joomla2.jos_magazine_articles.category_id, joomla2.jos_magazine_articles.catid) select joomla2.jos_content.title, joomla2.jos_content.id, joomla2.jos_content.catid, maxdev.jos_content.catid
View Replies !
Is It Possible To Sum The Values Of Multiple Fields On A Single SQL Record?
I have fields for Goals and Assists. I want to take those 2 fields and sum them to make a 3rd field PTS, then send them to variable in my PHP code to be printed to the screen. My statement is this: SELECT player_stats.G, player_stats.A, SUM (player_stats.G + player_stats.A) PTS FROM player_stats WHERE player_stats.PID =10 AND player_stats.season =1 The statement breaks with the bolded part.
View Replies !
Problem With Multiple Table Query Using Group By
I am running MySQL version 5.0.27-standard-log SELECT COUNT( rant_rants.* ) FROM rant_rants, rant_users GROUP BY rant_rants.userid LIMIT 0 , 15 Why won't this work? Foreach each rant_rants.userid I want the total number of rows in rant_rants with that userid. I have the second table in there because I am going to add more to this query once I figure this out. Any ideas?
View Replies !
Query A Single Row
whats the best command to only get a single row, currently ive just been using mysql_fetch_array for querying multiple rows (in a loop) and also single rows. apparently theres other ways to query single rows.
View Replies !
Single Query
I have a question here: I am trying to count/list all the distinct countries in my database but if one of my customer lives there. I tried : SELECT COUNT(DISTINCT country) FROM countries,customer WHERE customer.country_ID = countries.country_ID; I know ther is something wrong here. How do I connect the two tables with their shared country_ID as a primary key? Is it possible to do this in a single query? Or do I have to first find the customers seperately then store the number in temporary table and then use the above statement?
View Replies !
How To Access Single Row From SQL Query
$result = mysql_query($QueryNEWEST); $arrayIndex = 0; while ($row = mysql_fetch_row($result)) { $LATEST PRICE[$arrayIndex] = $row[10]; $arrayIndex += 1; } The query only returns one row, so is there a better way to get that row without using the while statement (which seems inefficient)?
View Replies !
Update A Set Of Records In One Single Query
My records contain a position, from 1 to n. This position is not the primary key. In normal conditions, the position of this records should be as follows: 1, 2, 3, 4, 5, 6, 7, 8, 9 . etc... One of my utitities is meant to check the consistency of the table - one of the things it has to do is scan the table as follows: SELECT position FROM employe_records WHERE employe_id = ? ORDER BY position From the result of this query I verify each entry (JDBC) one by one, looking for inconsistancies. In a for() loop I look at the value of position and check that they start from 1 and are incremented by one for each record. At the first inconsistency I come accross I want to run a query to update the records so it is consistant again - it is something like this: UPDATE employe_records SET position = updatedValue FROM employe_records WHERE employe_id = ? ORDER BY position This is not valid query of course - my question is what should my query look like?! How can I make 'updatedValue' incremented by one automatically so each of the records are updated from 1 to n.
View Replies !
How Can I Update A Set Of Records In One Single Query
My records contain a position, from 1 to n. This position is not the primary key. In normal conditions, the position of this records should be as follows: 1, 2, 3, 4, 5, 6, 7, 8, 9 .etc... One of my utitities is meant to check the consistency of the table - one of the things it has to do is scan the table as follows: SELECT position FROM employe_records WHERE employe_id = ? ORDER BY position From the result of this query I verify each entry (JDBC) one by one, looking for inconsistancies. In a for() loop I look at the value of position and check that they start from 1 and are incremented by one for each record. At the first inconsistency I come accross I want to run a query to update the records so it is consistant again - it is something like this: UPDATE employe_records SET position = updatedValue FROM employe_records WHERE employe_id = ? ORDER BY position This is not valid query of course - my question is what should my query look like?! How can I make 'updatedValue' incremented by one automatically so each of the records are updated from 1 to n.
View Replies !
Need Single COUNT Value For Complex Query
I have a moderately complex query and I want to page the results, limiting to 50 on a page. I know that in order to do so, I need to know the total number of records so that I know whether there are more records remaining so I can show a 'NEXT PAGE' link. I'm having trouble constructing a COUNT query from my original query because the query accesses 4 tables, two of which are many-to-one association tables. Here is the original query, without a LIMIT clause:
View Replies !
How To Get MY SQL Query Messages Into A Single Outfile
I want to run set of queries and want to get the messages after executing them into one outfile. e.g when I run query "Select * from accounts", when the query is successfully executed, it gives message "1000 rows in set (0.01) sec". I want to get all such messages from different query sets into one outfile. Can anybody guide me on how to get the same?
View Replies !
|
TRACKER
