Get 4 Tables Data In One Query
I am new to mysql relational programming, and creating a tutorial site like pixel2life. I am trying to get the data from 4 tables 1st table is main tutorials table, 2nd is subcategory (subcat) table, 3rd is categories table and 4th is users table. I don't know how to do it exactly, I googled but not got the proper method. I only got some hints http://www.brainbell.com/tutorials/M...ing_A_Join.htm from this site. I've created the following query which selects the title from tutorials table, subcategory (s_cat) from subcat table which reference is available as s_cat_id in tutorials table, then category (cat) from categories table which reference is available in subcategory (subcat) table and username from users table which reference is availble in tutorials table. Can somebody tell me this query is correct or there is any alternate method
PHP
SELECT d.title, s.s_cat, c.cat, u.username FROM tutorials d
JOIN subcat s ON d.s_cat_id=s.id
JOIN categories c ON s.cat_id=c.id
JOIN users u ON d.author_id=u.id
View Complete Forum Thread with Replies
Related Forum Messages:
Query Based On Data Of 3 Tables
this is what I've been trying to implement: I have, say, three tables. One is a user table, with id, username, etc Second is a question table with Question ID, Question, Category etc And third is an "answers" table that keeps answers given by the users. It has the classic id key, and Question id and User Id columns. What I want to do is this: Select a random question of category 1 lets say from the questions table for which user X has no record of answering in the answers table.
View Replies !
Retrieve Data From 2 Tables In 1 Query
I've got 2 tables content & sections I need to: SELECT id, title, section_id , status FROM content and now I'd like to get title from sections WHERE id = section_id from the first part of the query. I've tried with unions but doesn't seem to like it..
View Replies !
Insert Data Into 2 Tables With 1 Query Fails
I found 2 old threads on how to insert data into 2 tables with 1 query. Both of them said that i should seperate the inserts with a ;. But i must be doing something wrong, because it comes up with an error like this ".....right syntax to use near ' INSERT INTO eiland_details" What am i doing wrong? It's possible, right? To insert data into multiple tables with 1 query? $query = " INSERT INTO sub_pages (mainpage_id, intro_text) VALUES ('$main_data->mainpage_id','$sub_intro'); INSERT INTO eiland_details (inwoners, oppervlakte) VALUES ('$detail_inwoners',$detail_oppervlakte')";
View Replies !
Merge Sort-of Similar Data From 2 Tables In Query
A bit of background. I'm not a web person, and I know enough about databases to scrape by. Most of my SQL is generated using templates or handy things (like PHPMyAdmin etc). I'm also not a PHP coder. I am a C/C++ coder so I've found I can hack some PHP together, and I've got roped into helping a friend with a problem Here's the issue. I have a MySQL database with 2 tables of interest. Let's call these tables categorydata and itemdata. Both of these tables contain records that describe individual items - the item name, its ID in the system, the date it was added, some free text about it etc. To be absolutely clear, the purpose of the categorydata table is for a record to define a category (think "puzzles", "board games", "vegetables", whatever), but the record row also includes data on the FIRST item in the category ("banana", "cluedo" etc) along with all its data. The purpose of the itemdata table is to list all the OTHER items in the category ("grapes", "twister" etc). Here's an example record in the categorydata table: categoryname vegetables categoryid 1234 categorycreated 10th Jan 2000 categoryowner Bob firstitemid 4567 firstitemname cucumber firstitemcreated 12th Jan 2000 firstitemdesc It's long and green Here's an example record in the itemdata table: itemid 4568 itemname orange itemcreated 13th Jan 2000 itemdesc It's round and, er, yeah. This seems really bad design to me but what do I know. Here's what I need to do. I need to know how to merge the data in these 2 tables in a single query, ordered by the itemid and firstitemid fields. In other words, I need the query to merge the data, ordering it so that regardless of the table the record came from the itemids are all in correct numerical order. I need to do this so that I can retrieve each item's data in order. itemid in the itemsdata table and firstitemid in the categorydata table are unique, so when merged there won't be any colliding ids. I need to put this in a PHP script (in a loop that dumps out all the items in order), but if someone can just help me with the SQL query syntax I'd appreciate it, I can do the rest. I think this has something to do with inner joins but I really don't understand how to make it work, or how to retrieve and merge data from 2 tables at once bearing in mind the fields (columns? what do you call them) have different names even though they have the same purpose (ie: firstitemid -v- itemid, firstitemname -v- itemname etc).
View Replies !
Determining Which Table To Query Based On Data Within Tables
I have 2 tables: default_categories column 1: category_id column 2: category_name column 3: category_parent custom_categories column 1: custom_cat_id column 2: custom_cat_name column 3: custom_cat_parent The custom_categories table won't necessarily have anything in it but if it does, I need to choose the data from the custom_categories table over the data from the default_categories table. So if the default category has 3 rows with IDs | names: 123 | Dogs 456 | Cats 789 | Fish And the custom category has 1 row with IDs | names: 456 | Very Cute Cats I want my query of these 2 tables to produce the following IDs | names: 123 | Dogs 456 | Very Cute Cats 789 | Fish I've tried joins like the one below but they aren't working because if there is no custom_cat_id, it won't give me the result for the default category_id. MySQL SELECT * FROM default_categories LEFT JOIN custom_categories ON category_id = custom_cat_id WHERE category_parent = '' AND custom_cat_parent = '' ORDER BY $order_by $sort
View Replies !
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.
View Replies !
Add Data To Tables
Iam using full text searches but need some help with them. I just want to know how to add data to the tables etc. Please help me out as I dont know much about MySQL.
View Replies !
Data From Various Tables
I want to get a list of all files and folders inside of a certain folder. One thing is important: can I get rid of all the duplicate data that arises from both the entries in `folder` and in `file` having entries like `name` and `last_modified`? When I just use select * from `folder`, `file`, these show up doubly. This is actually my main problem... So, how would I do this? So basically, there is a certain `folder_id` which I want to examine. All entries in `folder` that have `parent` = that ID should be returned, and all entries in `file` that have `folder_id` = that ID should be returned as well. It doesn't need to be recursive: just one folder's contents are needed.
View Replies !
Data From 2 Tables
SELECT t1.name, t1.items, t2.name, SUM(t2.total) AS tt FROM `table1` AS t1, `table2` AS t2 WHERE t1.name = t2.name AND tt >= 20 GROUP BY t2.name ORDER BY t1.items I am trying to order the items from table1 based on the total from table2. The problem comes from the WHERE tt >= 20, I always get an error from this. Any ideas on how fix this?
View Replies !
Data From Two Tables
I want to output data from two different tables in one thing of code i have one table of songs with artist, name, desc etc....but i have another one with ratings. The rating table has a column of an id number that corrosponds to the auto_increment key column of the songs table i want to get just the rating column from one table and all of the columns from the other i have $query = "SELECT * FROM songs"; but i want it to be like $query = "SELECT * FROM songs AND SELECT rating FROM ratings WHERE id={$songid}"; is there any easy way to do this?
View Replies !
Getting Data From 2 Tables
I have 2 tables table 1 id date other infomation.... table 2 id date other infomation.... I want to know how to get ALL records from both tables, sorted by date. Full joins, two outer left joins, and combine. Can someone please give the statement(s) I need to get the info out. I was thinking of just creating a new table, and dumping the values from both the other tables, but there must be an easier way.
View Replies !
Comparing Data From 3 Tables
I am trying to wrap my head around the sql statement needed to get the data I need by comparing three tables. I have three tables: authors, submissions, and ratings. I need to see all the submissions that a specific author has not rated. In other words, I compare the submissions table to the ratings table and find instances where there is a submission but not a rating from a particular author (signified by the row rater_id) I've tried different combinations of INNER and OUTER joins, unions, subqueries... and nothing is working for me.
View Replies !
Comparing Data In 2 Tables
I have a table of users which is updated once a week by the client sending me a spreadsheet, and me uploading the CSV to my scripts which then import the data into the user table. This all works fine. However instead of sending me incremental data that can be just imported, the client has now taken to sending me a complete data list. The problem with this is that I cannot just empty the user table and re-populate with the new data as I will lose the primary key values which I use elsewhere. My solution to this was to create a new table for importing with the same structure as my user table, import the csv to there, then somehow compare the tables and add the rows missing from the import table into the user table. My question then, is this possible using a SQL query. Can I compare 2 tables, and add missing data from 1 into the other as new records? I could code a software solution, but with around 30k records in the user table I think a SQL query would be the best approach.
View Replies !
Dividing Data Into Two Tables
I was wonder at which point it is wise to divide data into two tables rather than have it all in one table. For example, a web forum should have the thread information in one table and the actual content in another table (these two tables are of course linked). What is the case with a table that contains user information such as uid, username, password, session_id, name, address, phone, email etc. Some of the data is requested on every page load (such as session_id and uid) and other data is only requested on specific pages (name and address is only requested when the user profile details are needed). Will I gain any significant performance advantage if I separate the user information into two tables, one that contains the data selected on every page request and another table containing the information for less frequently selected data? All fields are either of the type int or varchar. Let's say there's a maximum of 30 columns in the user information table.
View Replies !
Get Data From Multiple Tables
I have three tables: PRICES_2006, PRICES_2007 and PRICES_2008 I want to find all the rows between certain dates and then order them by date (TIMESTAMP). My current code looks like this: .......
View Replies !
Inserting Data Into Two Tables
if I want to insert data into two tables how do I do this? Is this right? I'm guessing not as it doesn't work What should I do? $query = "insert into requests (word,status) values ("$req","pending") AND insert into known_by (word,user_name) values ("$req","$user_name")";
View Replies !
Retrieving Data Across Two Tables
What is the easiest way to join one table to another table in a select query? Here's the break down. I've written an MLS IDX solutin that has pretty much every field I need in one table (br2data). I can handle that. But now my client wants the ability to add "acreage" as one of the search criteria. The problem is acreage is in another table(br2feats). The common key between the two is `mls number` To further complicate this is the two worded column header (`mls number`) How do you represent that in a join? br2data.`mls number` So my question is what is a way to write this with a select statement such as: select * from br2data, br2feats where < br2data criteria> and <br2feats acreage criteria>
View Replies !
Fetch Data From 3 Tables
I have 3 tables in my marketplace/store. Please note that products can be stored in multiple categories, hence the need for the joining table products product_id name title description categories id name parent_category product_category product_id category_id How would I select and list 30 products for any given category, like category ID #1 for example?
View Replies !
Selecting Data From 7 Tables
I have 7 tables in database. All of them shares a common coloumn called PMID.. i want to select data from all these 7 tables on the bases of PMID and am using the following query.Code: $query = "Select * from pmstep1,pmstep2,pmstep3,pmstep4,pmstep5,pmusa,pmca where pmstep1.pmid = pmstep2.pmid = pmstep3.pmid = pmstep4.pmid = pmstep5.pmid = pmca.pmid = pmusa.pmid ='shasha'"; but when i execute it on database using phpmyadmin. or command line to check my system hangs and it start to behave like 386 computers.. Please guide me that how can i select data based on this common coloumn from 7 tables. i never used JOINS and am not sure whether it has to do with joins or not.
View Replies !
Data From Multiple Tables
I am working on a website about sea shells and basically I have 3 tables: - shell_tb; (table of shells) - site_tb; (sites where they can be found) - instrmt_tb; (instruments used for it) As you may already have guessed there are two columns in shell_tb (site and inst) which may use more than one item from the site_tb and instrmt_tb. 1st. How do I configure the columns in shell_tb in order to accept more than one value? (I tried: CREATE TABLE shell_tb ( shell_id INT(1) NOT NULL AUTO_INCREMENT, shell_name CHAR(40) NOT NULL, site INT(20) NOT NULL, inst INT(20) NOT NULL, PRIMARY KEY (shell_id) ) TYPE=MyISAM; but no luck at all when inserting more than on value into inst column.) 2nd. How do I retrieve the info from there?
View Replies !
Counting Data Using 2 Tables
I have two tables, one has students information in, the key fields for this problem are StudentID which is the Primary Key and Gender. The second table has details of reports the students have done, the key fields required for this problem are ReportID (PK), StudentID, ReportDay, Report Date. As detailed below. Table 1 called Students StudentID (PK) Gender Table 2 Called Reports ReportID (PK) StudentID ReportDay ReportDate What I need to do is count the number of Boys who have been on report on Monday between two set dates, I can query Table 2 to get the total number of students who have been on report on Monday between $StartDate and $EndDate but I don't know how to extract the data from Table 1 so that only Boys are counted. The common link is the StudentID, unique in Table 1. Code:
View Replies !
Pulling The Data From Two Tables?
i already experienced this before the problem is i can't recall the right function to use to pull data from MySQL. Let say i have two tables TABLE 1= cclp_players TABLE 2= cclp_players_stats Now i'm going to use cclp_players to pull data from cclp_players_stats.
View Replies !
Insert Data Into 2 Tables Using Php
i was wondering how i would go about insert data collected on a php form into 2 separate tables. i want to collect email and password for an admin table and everything else for a separate table why cant i just do this? $query = "INSERT INTO user_admin (s_email, s_password, salt) VALUES ('$email', '$encrypted', '$salt')"; mysql_query ($query) or die ('Could not register new account.'); $query = "INSERT INTO student (s_forname, s_surname, s_uni_id, s_email, s_course,) VALUES ('$forename', '$surname', '$uni_id', '$email', '$course')"; mysql_query ($query) or die ('Could not register new account.2'); the email and password get stored into the user_admin table but nothing is collected for teh student table do the values such as $forename need to be declared anywhere else before?
View Replies !
Displaying Data From Two Tables
have two tables table 1 = "product_list" with the following fields: ID prodct_name unit_price parent_category_id data displays as follows: id product_name unit_price parent_category_id optional_stock_id 1233 Brunsfelsia 20lt 125.50 344 table 2 = "category_structure with the following fields: Id category_name parent_category_id id category_name parent_category_id 341 Plants & Trees 0 342 Conifers 341 343 Plugs & Trays 341 344 Standards 341 345 Shrubs & Ground Covers 341 346 Palms 341 347 Toparies 341 ........
View Replies !
Select Data From 3 Tables
How to select data from three tables with one query? table1 {id,field1} table2 {id2,field2} table3 {id,id2} I know the 'field1'. In the 'table3' there are connections with the 'table1' and 'table2' id's defined. So i have to find out the 'id' for the 'field1' in the 'table1', then to look in 'table3' for the 'id2' that is connected with the 'id' and in the end return 'field2' with the found 'id2'.
View Replies !
Displaying Data From Three Tables
I have three tables I am working with. There is one table I would consider the "main table" which includes log entries. The log entries in the "main table" store username as a number. A second table has a primary key of a number and the number maps to an actual active directory username. How can I select data from these three tables but have it display the actual username instead of just the userid number (which in and of itself doesn't mean anything)?
View Replies !
Using Sum And Selecting Data Fom 2 Tables
Here is my prob.. ADMIN TABLE --------------------------------------- adm_id | adm_login --------------------------------------- FILE TABLE ---------------------------------------------------- id | file | size | adm_id ---------------------------------------------------- Now the result i want is to list all admins with there sum of total file size everyone have uploaded eg..
View Replies !
Searching Two Tables For Data
i have created a website where the user logs in with an assigned username and password, on login they are asked to enter their prefared username and password to login with in the future. I have 2 MySql tables piclogin (original table with preset details) and newlogin which stores the new prefered username and passwords, I used the coding below to do the original validation : if($rec=mysql_fetch_array(mysql_query("SELECT * FROM piclogin WHERE username='$username' AND password = '$password'"))){ if(($rec['username']==$username)&&($rec['password']==$password)){ my question is: can a query be created to search both tables for the username and password entered? and depending on which table the information is found in, redirect the user to different web pages? Can some form of OR statement be used???
View Replies !
Returing Data From Two Tables
i have set up a db with two tables, users and current_users in users the fields are: UsrID UsrEml UsrPwd UsrLvl UsrBlked and in current users the fields are: UsrID UsrIP LstAccess when a user is logged in, a record is created in the current_users table and deleted when they log out, what i would like to do is to write an sql statment that returns the UsrID, UsrEml, UsrLvl, UsrBlked and LstAccess for every user in the users table, if there is not a record in the current users field, i would like LstAccess to be returned blank. How do i acchieve this?
View Replies !
Importing Data Into New Tables
I'm trying to add several of the same work fields into different database tables but I'm not sure what syntax to use or if thats even possible. For example if i export an already fully defined table like this one: Code:
View Replies !
Electing Data From 2 Tables
I have two tables in a database (MS SQL, but close enough). One table, called Table1 has a field BillingAddress. The other, Table2, has a field ShippingAddress. Is there any way to search both tables for either a BillingAddress or ShippingAddress which starts with something (say, WHERE field LIKE '1 Street%'), then weed out the duplicates and return them all as one field?
View Replies !
How To Insert Data Into 2 Tables At Once
I am realizing now that I have never done this before. I need to insert information - all at once into two seperate tables joined by a fk. So essentially I need to insert into one table then get the primary id from it and then insert this value long with the other fields - into the 2nd table. Can I do this without writing 2 insert statements - perhaps using a join? If so what is the basic syntax?
View Replies !
How To Fetch Data From Two Tables
I am facing some problem to fetch data from two tables.. I have two tables.one is loguser other is access. In loguser thier is icnumber and creationdate fields In access their is acccode field now i want to print results from these two tables... i try the query but gives error...
View Replies !
Another Data In 2 Tables Thread...
I've searched several sites, but joining doesn't make sense to me here. Though the concept is confusing to me anyway, so I could be wrong. I've done a lot of work interacting with single tables, but never more than one. Using MySQL database with PHP. Table 1: cat_list -- Has two columns: "id" and "catName" Table 2: business -- Has several columns, most of which are not relevant here, except: -- "industry" which contains an int that is listed in the "id" column from cat_list. Now, there could be dozens of records within the business table that use the same id from cat_list. But I need to return the list from business and replace the number in the "industry" column with the text in "catName". In other words, a simple query of business might return: Name Industry Street Marathon 15 111 North Main St But I really want it to replace the 15 with the text from catName so that it would be: Name Industry Street Marathon Gas Station 111 North Main St So what would the code be to do this? Have tried a few things without success.
View Replies !
Getting Data From Two Tables In One Column
I have 3 tables, as follows: keywords: id, name glossary: id, term, definition synonyms: id, synonym, glossary_id synonyms contains synonyms of the terms in my glossary. Keywords contains a list of keywords that we use throughout the site. What I need is to have a list of my keywords, and then have a list of any synonyms and glossary terms that match or are related to those keywords. So, basically, if keywords had 'cap', and glossary had 'hat' with a synonym of 'cap', my list needs to contain both 'cap' and 'hat'. Or, if my keywords and glossary had 'cap', and I had a synonym of 'hat', I still need both 'cap' and 'hat'.
View Replies !
Two Tables In One Query
i'd love if anyone could help me out on the following situation: I do have a db with two tables for price comparisations - my example:table 'items' contains an entry with the itemno '123' and a basic price of '5.00' plus additional stuff like description etctable 'prices' contains 3 entries for the itemno '123' with the prices '6.00', '10.00' and '20.00' Now i need to catch several data from the table 'items' and additionally the highest price for the item '123' in one query. The tricky thing is, that an item possibly could have no entries in the 'prices' table and in that case the kinda default price from the table 'items' should be taken.
View Replies !
Two Tables--One Query
I've got two tables: carddb_games and carddb_cards. Their structures are like so:Code: carddb_games { game_id tinyint(3) game_name tinytext } carddb_cards { card_id smallint(5) game_id tinyint(3) card_num tinytext card_name tinytext card_effects tinytext card_special tinytext Is it possible to make a query such that it will go through each game in carddb_games AND return a count of how many cards exist with a matching game_id within the carddb_cards table? I am not sure as to how to go about it, if such is even possible.
View Replies !
Can You Query Against 2 Tables?
In one table I have a list of users and in the other a register to keep track of when they are in. The query I need to build will check that the user belongs to a particular group (in the user table) AND that there is no data against there name for today i.e. date("YY/mm/dd") in the register table....
View Replies !
Query From Two Tables As If It Were One
I have two tables with identical columns videos and photos. I basically need to do this: SELECT * FROM MERGE(`videos` `photos`) ORDER BY `created` DESC So they can be displayed in chronological order on a result page.
View Replies !
Need To Query Two Tables
I am new to MySQL, but I'm sure the pros here can figure this one out pretty easily. There are two tables; one that lists all of the clicks from all members and one that lists all of the links. Basically, when the member logs in I want them to see a list of links that they have not clicked on. The clicks table has a user_id and link_id column. The links table has the link_id column and some other info.
View Replies !
Using Many Tables In A Query
I'm developing a script, which creates a bunch of tables for each registered member. And I need to sum up some statistical information, like records count, based on tables of each user, say: user1_posts, user2_posts and so on. How efficient would it be to use a query with its from clause consisting of thousands of tables in this case? Should I look for another way to do this?
View Replies !
Adding Data Into Joined Tables
I recently started playing around with joining tables together to be able to retrieve data from them using one query. However, as embarrassing as it may be, I can't figure out how to add data to multiple tables. For the sake of practice I creates three tables: table1 Code: ID Name table2 Code: ID Age table3 Code: ID Gender PHP Code:  $query = mysql_query("                     SELECT name, age, gender FROM join1, join2, join3 WHERE join1.id = join2.id AND join1.id = join3.id         ") or die(mysql_error());Â
View Replies !
|
TRACKER
