Multiple DB Pull
Lets say there is a database of special users and a database of favorite movies
SPECIAL USERS (username)
bob
jim
FAVORITES (username - movie_title)
bob - movie 1
kim - movie 3
jim - movie 1
larry - movie 2
jane - movie 2
How would I write a query that lists the most popular movies of special users without explicitly doing:
SELECT * FROM FAVORITES WHERE username = 'bob' or username = 'jim' GROUP BY movie_title
I want something like this:
SELECT * FROM FAVORITES WHERE username = [all the usernames from the SPECIAL USERS DB] GROUP BY movie_title
View Complete Forum Thread with Replies
Related Forum Messages:
How To Pull Data From Multiple Tables
I have a db, with several tables in it, I have now reached to the point where I need to pull data from several tables. What is the best way to do this? Should I put a column in each table, and then insert a unique key in all of them at the time the record(row) is being created? If this is the way to do it, when I am putting the unique key in each table would I just do an insert in each of the tables?
View Replies !
Pull Data From Multiple Tables
Can you pull data from 2 tables in the same database onto the same page? What about more than 2 tables? Here is my dilema: I currently have a database with 1 table, and a ton of rows. The information is property information, and each property has multiple empty suites. As of now, I have columns for Building Name, Address, and then columns for Unit Type A (for the first unit), Rent A, Type B, Rent B, etc.... I was wondering if I would be able to put the listings we have into thier own property table, and then one bigger table of all the properties individual information. Keep in mind I need to call all tables together on my portfolio pages, so I would be connecting to quite a few tables at once. Or my other option I was thinking about was to just have 2 tables: 1 Buildings info, and 1 units info. Which of these would you do, or how would you organize this data.
View Replies !
Pull All Records
if it were possible to write a statement in SQL where one could select several fields from a table and then limit the records returned by only displaying those where, say, FieldA (a varchar) contains a given short string which could be only a part of the field's contents. As long as a record has this short string in Field A, it should be included in the resulting set of records.Something like this: SELECT FieldA, FieldB, FieldC FROM TABLE Table1 WHERE FieldA *contains string* Is there anything in SQL that could examine the contents of a field for a partial string, and then return the records that have that partial string in that field?
View Replies !
Mysql Pull
I got this in my mysql databe table. a:1:{i:0;O:19:"test_Enclosure":4:{s:4:"link";s:36:"http://grabber/v/CAcpLiw4qt4.swf";s:4:"type";s:29:"application/x-shockwave-flash";s:6:"length";N;s:10:"javascript";s:2:"js";}} I want to pull out only the http://grabber/v/CAcpLiw4qt4.swf part?
View Replies !
Can Input Japanese, Can't Pull It Out
I figured out how to store Japanese characters by setting the collation of a particular field to utf8_bin. I can input Japanese characters, and I can see them just fine under PHPMyAdmin. The problem is that when I execute a query and print them to a web page, all I get is question marks instead of Japanese characters. This is while using the same browser that allows PHPmyAdmin to display the characters correctly. I added the following metadata to the <head> portion of the document but it didn't help: <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
View Replies !
Populate A Pull Down Menu From Db
how do I populate a pull down menu from a MySQL table? Using PHP/MySQL 4.0 Have table1 with names and ID and tableb with ID and statistical info.wnt to be able to scroll names and have users able to click on the name to follow a link to a page created using tableb data for each name listed. Table examples: tablea ID l Name --------------------- 1 Person, Joe 2 Smith, Joe tableb ID l units l margin -------------------------------- 1 5023 123 2 2536 72
View Replies !
Code To Pull Amount
I'm certain there is a function to do this but I forget what it is... Say I am pulling values from the database, but I only want to pull like the first 10 chars of the value.. how do I do that?
View Replies !
Pull Hosting Plans
I am currently creating a web hosting website for a client of mine, for the packages and the order process we are using a script called "Auto-Host (http://www.idevspot.com/AutoHost.php)", which stores all the hosting plans into a mysql database. This is a great script and it does exactly what we want it too, the only thing missing is it doesnt create a page where people can view the hosting plans and click a sign-up button. Can someone please give me guidance on how i can get a page with all the hosting plans on please?, alternatively i am willing to pay somebody for doing this on my behalf
View Replies !
Pull The Last 10 Records From A Transactions From A Table
I'm trying to pull the last 10 records from a transactions from a table using this query: SELECT * FROM transactions ORDER BY timestamp DESC LIMIT 10 But I want to display the rows in ascending order by timestamp. I can't get the subquery below to work and not sure why: SELECT * FROM (SELECT * FROM transactions ORDER BY timestamp DESC LIMIT 10) ORDER BY timestamp ASC;
View Replies !
Pull Related Info Using Subqueries
I'm creating a tool to browse a database of movie listings. The browser pulls up 25 results at a time, and you can page through them using 'Next' and 'Prev' tools. Pretty basic stuff. Here are my tables: movies directors comments movies_directors movies_comments etc... (primary key is movies.id) But here's where I'm stuck: for each film, I need to pull the movie info in 'movies', plus any related data from other tables, like this: The Lord of the Rings | Peter Jackson | 3 comments Episode II | George Lucas | 0 comments Indiana Jones | Steven Spielberg | 15 comments I seem to run into problems when I try to join info from all of these related tables. If there's a match, great. But, if not (like a movie with no comments), the movie is excluded from the result set. I've tried all sorts of SQL queries to make this work, but nothing seems to do the trick. SO, HERE'S MY QUESTION, IS THERE ANY WAY TO DO THIS QUERY WITHOUT SUBQUERIES/MYSQL 4.0? Or would the best approach be to use PHP to do all the subquery lookups?
View Replies !
Pull Similar Data From Two Tables
I need a query to search two seperate tables for similar data and return them in one result. I know you can do multiple select statements in one query but I dont know how to exactly organize them into one result. Does anyone know how to accomplish this.
View Replies !
Result Pull And Rating Sort
I have two databases. What I would like to happen is when I have a summary of products, I would like the query to pull the info from the product table and based on the id from that table cross reference with the reviews for that particular product and calculates all the reviews for that product into a single percentage. So when I access the assoc array from PHP I would like to see : Array(Array(['id'] => '2', ['prod_name'] => 'gummy bears', ['rating'] => '72'), Array(['id'] => '57', ['prod_name'] => 'socks', ['rating'] => '52')).
View Replies !
Regular Expression :: Pull 3 Or 4 Digit Numbers
I would like to pull all the model number from my products table that are strictly only a 3 or 4 digit number ie: 034 or 3542 or 0243 or 934 etc Can someone advise me on how to construct a regular expression to give me the data I need?I have tried the following but it wasnt 100% accurate: Code: products_model REGEXP "[[:<:]][0-9][0-9][0-9][0-9][[:>:]]" or products_model REGEXP "[[:<:]][0-9][0-9][0-9][[:>:]]"
View Replies !
Pull Info From One Database To Save In A Session To Push To A Different DB
I'm building a dynamic site that has 3 data bases. One DB contains all of my store info ID#, Name, Address, Phone. Another DB contains member info ID, Name, address, email,phone,etc... The 3rd DB Tracks when a user logs in and logs out at a store. This DB has the following columns Store ID, Member ID, log in time,log out time, time in store. The stores log in in the morning that starts a session-I need the session to pull the store's info ID#, Name,etc... so when a user logs in, in the store. The login page inserts the store ID, Store name, and user ID into the 3rd DB. There are a lot of stores how do I get the store variables to be inserted into the DB?
View Replies !
Pull Data From 2 Database Tables Into 1 Table On My Site.
I got 2 tables: a dev_tasks and a task_history table. The task table has to record information about the task I am working on AND the history table is for the task history logs. I am trying to display info from both tables into 1 table on my site. Most of the info will come from the dev_tasks table BUT i want to dedicate on column of the displayed info for the latest log made to the task_history table. The common column to both tables is the 'taskid' column. What would the query look like to pull the info from both tables, knowing that the taskid is common to both and that I want to display the last log per tasks from the task_history table?
View Replies !
Trying To Pull Id, Count And Title But Lose Rows When I Add Extra Join
i'm trying to extract some information from my database, the query being PHP SELECT grps_c.catid, grps_c.title, COUNT(grps.groupid) AS COUNT FROM grps RIGHT JOIN grps_category grps_c ON (grps_c.catid = grps.catid) GROUP BY grps_c.catid ORDER BY grps_c.title which works fine. however some of the groups (grps.groupid) are hidden and i don't want to count them, so my thinking was add PHP LEFT JOIN grps_setting grps_s ON (grps_s.groupid = grps.groupid AND grps_s.hidden_group != Ƈ') however adding that removes the rows that have a 'count' or NULL or Zero.
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 !
Select Statement With Multiple Counts/multiple Joins Trouble
SQL SELECT g.group_id, g.name, g.description, g.icon, g.user_id, g.date, u.username, count(c.comment_id) as comment_count, count(v.video_id) as video_count, count(m.user_id) as user_count FROM duo_groups as g LEFT JOIN duo_group_members as m on m.group_id=g.group_id LEFT JOIN duo_users as u on u.user_id=m.user_id LEFT JOIN duo_videos as v ON g.group_id=v.group_id LEFT JOIN duo_video_comments as c on v.video_id=c.video_id GROUP BY g.group_id, g.name, g.description, g.icon, g.user_id, g.date, u.username Will return My current problem with the above statement is with the counts. The count is accurate when only one of the counts is returned. However when more than 1 of the counts is returned the other counts (if they are not 0) will return the same number. For example look at the screenshot above. The first row, all three counts are the same but they are not accurate. They alll point to 8 because there are 8 comments, but there aren't 8 videos or 8 users. Same with the second row. There aren't 2 users only 2 videos.
View Replies !
Joins With Multiple Tables And Multiple Rows
I'm making a good ol' forum, and i have three tables, users, threads and posts. when i query my threads table with a join, i need to access the users table twice to get the username of the first poster and last poster. But how? I can only figure out how to get one or the other. Is my design bad? eg SELECT TopicID, FirstPostID, LastPostID, Replies, Views, Topic, username FROM DiscussionThreads, users WHERE DiscussionThreads.FirstPostID=users.ID ORDER BY FirstPostDT DESC LIMIT 10 .
View Replies !
Select Multiple Keywords From Multiple Tables
am trying to do. I have a database called "members" that contains a table called "Reviews". This table has fields S1, S2, S3, S4, etc. that I want to search for the words ad, advertisement, and promotion. Here is the syntax I came up with that doesn't work: SELECT * FROM `REVIEWS` WHERE S1, S2, S3, S4, S5, S6, S7, S8, S9, S10, XTRA='ad, advertisement, promotion' It would be perfectly fine, if easier, to search all fields in the table.
View Replies !
Multiple Update By Multiple Users
I'm looking at producing an application that will allow multiple users to order multiple items/ parts from what would effectively be an online store. These users could be ordering several hundred items/ parts at a time, each item/ part ordered representing one box or several hundred. Each part record will require the "total stock" to be down dated and each unit will require updated to show it as having been picked (plus other info). I'm looking at programming this in php 4.3 or php 5 using a current version of mysql. Looking around etc. I believe there are various options options open to me : 1 Create a pick list in a database table and have a "pick" routine pick the stock. This would ensure that the actual pick could only be carried out by a single process and the result of the pick request could be emailed to the user. This approach would provide the user with a very fast, responsive acceptance of an order request (but not real time - albeit almost). 2 Use Table Locks - This I understand may be the fastest but potentially could leave users with apparently hung screens, eg. if three picks were issued each taking 10 secs, then pick 3 would not start until the first two picks completed (20 secs). 3 Record Locks - I'm concerned that doing this would increase the time taken to pick the stock. But if the times were not excessive this would provide the user with an immediate "real time" response.
View Replies !
Multiple Batabases Vs. Multiple Tables
I am about migrate from an old program database to MySql (Running under RH LINUX)and I'm wondering which is the best option to do : I currently have one file for each of my modules, (I'm using filepro plus) , so like CUSTOMER, WARRANTY, ZIPCODES, INV, etc... like 2,000 files each with their own fields. 1) Should I create multiple databases on MySQL 2) Should I create a single database on MySql and with multiple tables ? Which one of these options is the best and more safer ?
View Replies !
Multiple Databases Or Multiple Tables
I have an application that is supposed to make multiple connections [around 10 connections per second] to a mysql database. The connections are done by mutlitple users at potentially the same time. I have to decide if I should use : 1)one database with one table for all users. 2)one database for each user. 3)one database with one table for each user. I would really appreciate if you can tell me what choice is best and why.
View Replies !
Multiple Books & Multiple Classes
The field names are as follows: bookList: "bookID", "title", "edition", "author", "ISBN", "newPrice", "usedPrice" classList: "CRN", "className", "professor", "courseNum" linkList: "CRN", "bookID" The goal is to run one query inputting the CRN and have it return all of the fields in bookList and classList. LinkList is only there to join the 2 since some classes have multiple books and some books are used for multiple classes. The erroneous query I was trying to use was as follows. SELECT * FROM (classList INNER JOIN linkList ON classlist.CRN = linkList.CRN) INNER JOIN bookList ON linkList.bookID = bookList.bookID WHERE CRN = 1 This query was the original. It worked but did not provide me any class information only the CRN of the class. SELECT * FROM linkList INNER JOIN bookList ON linkList.bookID = bookList.bookID WHERE CRN = 1
View Replies !
Multiple Languages, Multiple Products
I posted before and was helped immensely. I am designing a multilingual-capable site where each product may (or may not!) have a description in the user's chosen language. In this case, I want to get the default language description. Say my product descriptions table has fields prod_id, language, and description. When I have a single product and I want its description, I can retrieve it as follows:
View Replies !
Multiple Value
i know how to do a query with a single value... SELECT * FROM `category` where quiz_id = 32707 but how do i select records from several different 'quiz_id' values? i.e. 32707, 34501, 23450 i tried... SELECT * FROM `category` where quiz_id = 32707, 34501, 23450 SELECT * FROM `category` where quiz_id = 32707 or 34501 or 23450
View Replies !
WHERE With Multiple IDs
i am using JDBC. I want to get a ResultSet of all rows that have any of 10 different IDs in a certain field named 'IDN'. I tried this: 'select * from testTable where IDN = 'ID_1','ID_2','ID_3',....'ID_10'; ' this gave me an error. How can i pull off this query.
View Replies !
Use Multiple Between
I am trying to use a between statement to select multiple ranges from a database so for instance cabon_km could be between 0 and 120 or 151 and 180 SELECT * FROM elvdata WHERE carbon_km BETWEEN '0 and 120 OR 121 and 150' AND cost BETWEEN '0 and 6000 OR 6001 and 8000' AND engine_size BETWEEN '1000 AND 1200' OR '1201 AND 1400' AND fuel_source='Petrol' AND seats='5'
View Replies !
Multiple Or Or And
I am having some difficulty working on a query for a website that needs to pull information from several criteria. eg by default i need it to pull all the info and evaluate other expressions only if the variable is not null. for eg. select * from table where (length('var1') > 0 and (id = var1)) or (length('var2') > 0 and (size = var2)) or (length('var3') > 0 and (music = var3)) and id is not null. for some reason this just does not work.. It always returns all the records. I should note the value of the variables are numbers and not txt. i am not sure if that has something to do with it..
View Replies !
Multiple WHERE
I have a query: $sql = "SELECT * FROM notes WHERE uid='$_SESSION[uid]', class='$_GET[class]' ORDER BY time ASC LIMIT 10"; I want to get everything where the uid is equal to the session variable and the class is equal to the class var in the url... I have no idea whats wrong.
View Replies !
Multiple Count()s
I have a table with links, a log table to record clicks, and a vote table to record votes on the links. Is it possible to get the link data, the click count, vote count and vote total in one query? I can get the click count OK, but as soon as I add the vote count to the query, it multiplies them; e.g. for a link with 14 clicks and 2 votes, it tells me 28 for both figures. Here's the query: select lnk.LinkID, URL, LinkTitle, Description, count(log.LinkID) Clicks, count(Rated) Votes from fn_links lnk left join fn_log log using (LinkID) left join fn_rate vot using (LinkID) where CatID = $CatID and Status = $Active group by lnk.LinkID order by Created Then of course I want to include sum(Rating). I tried grouping on different things, but it makes no difference.
View Replies !
How-to Multiple Select
I'm studying a blog program. Given two tables : post and comment, i would like to retreve the number of comments for each post. In postgres the following is doable : select id, title, body, (select count(*) from comment where post_id = post.id) as total_comment from post How-to do the same thing with MySQL?
View Replies !
STD Of Multiple Columns
I want to calculate the standard deviation of data that are in multiple columns. I know how to calculate the STD of 1 column (e.g. X1 of table_X) using: SELECT STD(X1) FROM table_X; but I want to calculate now the STD of the union of data of columns (e.g. X1, X2, ..., X100 of table_X). Does anyone has any suggestion on how to do that? I hoped something as SELECT STD(X1,X2,...,X100) FROM table_X existed, but apparently it does not.
View Replies !
Multiple Parents
I have a table that stores listings, the type of listing can be either an event at a night-club event, at a gig or at a bar. So i could just assign a number to define the type. But, the listing needs to have a parent of either club, gig or bar. What's the best way to handle this? At the moment there are 3 FK-fields that if not set to zero contain the id of either club bar or gig. It somehow doesn't seem right.
View Replies !
PHP Multiple SQL Statements
When i try to do the following in PHP I get errors. Is this not permitted or is this due to a setting somewhere? Can i do only one SQL statement per call to mysql_query? $sql = "CREATE TEMPORARY TABLE tmp SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1'; INSERT INTO tmp SELECT field1_index, field2_index FROM test_table WHERE field2_index = '1'; SELECT * from tmp; DROP TABLE tmp;"; $res = mysql_query($sql);
View Replies !
Multiple Row Join?
I have two tables - one called parent, the other called group. table group is made up of ID,parentID,groupHash and groupName. table parent is made up of ID,title,group1Hash,group2Hash,group3Hash (and other columns) Each of the group hashes in table parent correspond to a group hash in table group (ie parent.group1Hash exists in/as group.groupHash and parent.group2Hash also exists (on another row) in/as group.groupHash). This means table parent can have up to three groupHash's noted in a single row and each of these groupHash's will have its name in a corresponding row in table group (using groupHash as key). Can I perform a select whereby I read a row from parent and use its group1Hash, group2Hash and group3Hash to retrieve their corresponding names from table group (all in one select statement)? Or must I perform three or four individual selects to get the desired values from the two tables.
View Replies !
Multiple Results
I'm using PHP to display a list of statistic information about a site. Sometimes I need to retrieve mixed information from the database but I don't know which is the best method to do it. For example I need the top requested html pages and the most repeated value from a column. I dont know if I should make two different queries in the php file or it's more efficient to make only one query with an extra column like id | ip | date | page | max | --------+--------------+----------+----------+---------| 1 + 24.125.24.25 + 24/5/03 + index + 3 | 2 + 24.125.24.25 + 24/5/03 + top + 3 | 3 + 20.12.12.21 + 24/5/03 + index + 3 | 4 + 200.12.24.25 + 24/5/03 + left + 3 | 5 + 24.1.6.255 + 24/5/03 + left + 3 | 6 + 24.125.24.12 + 24/5/03 + index + 3 |
View Replies !
Multiple Sorting ?
In my table i've got a column subject, a column content and a date. I'd like to sort the data first by date, then by topic, and then by date gain, if you see what i mean. I would like to group entries that have the same subject together, sort them by date, and inside each group sort by date as well.
View Replies !
Multiple Sorting
I'm pretty new to SQL and databases, and i've come up with a problem to sort my data. I'm trying to do a basic forum thing. In my table i've got a column subject, a column content and a date. I'd like to sort the data first by date, then by topic, and then by date gain, if you see what i mean. I would like to group entries that have the same subject together, sort them by date, and inside each group sort by date as well. Am i making sense ? Is it possible to do that or am i thinking the whole thing in a wrong way?
View Replies !
Multiple Datadir's
we have several dozen clients and it would be nice to have a seperate folder structure for each one, as we zip up data and send it off to them and we can't have the possibility of accidently sending other clients data. We can have a seperate database for each client, but what would be really nice is to be able to have several different mysql "data" folders (one for each client) but have MySql be aware of all of them
View Replies !
Multiple Domains
have multiple domains on winodws 2003 (IIS) what best way to setup mysql i would like a limit on each domain on the number of databases if possible (see other post), if not i gues i will have to create them i also wish each domain the have phpmyadmin access.
View Replies !
|
TRACKER
