How do I combine tables in MYSQL so that 2 different sites share the same username an


I have a site using WebNetwork by WebScribble and phpbb.... They share the same database. My goal is to log into one WebNetwork and not have to log in again when bridging to phpbb... phpBB is the application I use to run the web forum on my WebNetwork site. I need to sync username and password functions only... as I don't want administrators and moderators of the forum to have that power on the webnetwork site....

More info...

table for WebNetwork is "sn_user"
table for phpBB is "phpBB_user"

Please help!

- Ken
Posted On: Wednesday 24th of October 2012 01:23:56 AM Total Views:  172
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




how to combine two columns of a table to some columns of another table

i have 2 separate databases... in the 1st database i have a table with 14 columns and in 2nd database i have a table with 7 columns.i want to make this as a single table with 3 columns from 1st table and 2 columns from 2nd table. also both tables contain some common columns.how to match two tables using .net
VIEWS ON THIS POST

184

Posted on:

Saturday 6th October 2012
View Replies!

Can you combine two SELECTs which join on the same field (non trivial)

I have a table (choices) which includes two fields (first_choice,second_choise) which contain ID numbers of items, and an identifier for the row (choiceid) I have another table (items) which contains fields including the item ID (itemid), name of the item (name) and others. 1) Is it possible to create a MYSQL statement that would enable me to create a table containing the names of the choices If I was doing this in PHP I would code two seperate SELECTs... SELECT choiceid,name FROM choices,items WHERE choices.firstchoice=items.itemid ORDER BY choiceid SELECT choiceid,name FROM choices,items WHERE choices.secondchoice=items.itemid ORDER BY choiceid ... and then write a function to combine the two results by matching IDs. 2) Is it possible for the statement to work when the second choice has a value of 0 (and so will not have a value in the items table) My guess is that this may be possible with some form of JOIN statement. 3) I could do with the results being ordered in alphabetical order by first choice, and then second choice. This may be impossible, but I can hope.
VIEWS ON THIS POST

168

Posted on:

Thursday 11th October 2012
View Replies!

How can I combine my two queries?

I am trying to SELECT all the scheduled rules of all the suupliers but cant figure out how to join my two queries: 1.) THIS QUERY SELECTS ALL THE SCHEDULE OF ALL THE SUPPLIERS. THE FIELD condition_value is the suppliers unique code number: "select rules.rule_id, name, start_date, end_date, condition_value from rules, rules_schedule where rules.rule_id = rules_schedule.rule_id" sample output: rule_id| name | start_date | end_date | condition_value | 1 | first rule | 2009-01-01 | 2009-02-02 | 1234 | 2.)THIS QUERY SELECTS THE SUPPLIERS PP-ID- JUST SOME ID AND "NAME" AND "ASI" - WHICH IS THE SUPPLIERS UNIQUE CODE NUMBER OR SAME AS THE condition_value in query#1: "SELECT pp_id, (SELECT value FROM suppliers_attribute_map INNER JOIN suppliers_varchar USING (value_id) WHERE suppliers.supplier_id=suppliers_attribute_map.supplier_id AND meta_attribute_id=215 AND approved=1) as supplier_name, (SELECT value FROM suppliers_attribute_map INNER JOIN suppliers_int USING (value_id) WHERE suppliers.supplier_id=suppliers_attribute_map.supplier_id AND meta_attribute_id=127 AND approved=1) as asi FROM suppliers" sample output: pp_id | supplier_name | asi | 00-01 | ABC CO. | 1234 |
VIEWS ON THIS POST

161

Posted on:

Friday 12th October 2012
View Replies!

How to combine text with 'IN' and a subquery?

Quite hard coming up with a title for this post This is formatted with some PHP but I thought this forum was probably more appropriate. Basically, I have the following query: Code: SELECT objName,objID,objImg FROM wwObjects WHERE objLocation = 'loc_" . $_POST['loc'] . "' OR objLocation IN (SELECT locID from wwLocations WHERE locParent = '" . $_POST['loc'] . "') ORDER BY objName Now, my problem is that objLocation is stored with a prefix (in this case "loc_", whereas locParent is just the number which follows the prefix. This is fine with the '=' because I can just insert the string before the number into the query, however I can't for the life of me work out how to do the same thing with the 'IN' and subquery.
VIEWS ON THIS POST

276

Posted on:

Friday 12th October 2012
View Replies!

UPDATE combined with SELECT gives headaches

Hi all, I am trying to combine an UPDATE with a SELECT statement to change multiple rows. The query works without errors, but whatever I try, zero rows are affected (and that's not what it should do, or better, what i would like it to do). I've searched everywhere on the Internet to find a solution but to no avail, so that's why I'm trying here. The SELECT statements all work fine on the table when I run them seperately, with the deed results, but combined in the UPDATE, I get no results (no affected rows). Here's the query: UPDATE forum_article SET childs = (SELECT COUNT(`response_to_forum_article_id`) FROM (SELECT * FROM `forum_article`) AS X WHERE `response_to_forum_article_id` '0' GROUP BY `response_to_forum_article_id`) WHERE forum_article_id = (SELECT `response_to_forum_article_id` FROM (SELECT * FROM `forum_article`) AS Y WHERE `response_to_forum_article_id` '0' GROUP BY `response_to_forum_article_id`) Hope somebody has a clue. The subqueries are there because I learned that MySQL needs aliased subqueries when the UPDATE and SELECT statements are affecting the same table. Also, to explain a little bit more, the 'childs' is the column that needs to be populated.
VIEWS ON THIS POST

173

Posted on:

Friday 12th October 2012
View Replies!

Possible to combine these 3 SELECT's into one query?

I have 3 SELECT queries and I am trying to combine them into 1. 3 tables: 1. 'article' table (id, title, ...) -- holds title, subtitle etc. 2. 'subtypes' table (id, subtype) -- holds the subtype of each article (e.g if article type is "news", the subtype might be "local".) 3. 'keyword' table (id, keyword) -- holds any number of keywords associated with each article. [Note in my particular case only one type of article has a subtype, so that is why I had to split it off into it's own table.] The 3 queries I have for pulling an article (with for example, id = 266), it's subtype (if any) and it's kewords from the database are: Code: select * from articles where id = 266 select subtype from subtypes where id = ( select subtype_id from articles2subtypes where article_id = 266 ) select * from keywords where id in ( select keyword_id from articles2keywords where article_id = 266 ) 'articles2subtypes' table matches articles with their subtype (if any), and 'articles2keywords' table matches articles up with their associated keywords. I would really like to combine those 3 queries into 1 if possible. Is that possible, and what would the query be THANKS!
VIEWS ON THIS POST

158

Posted on:

Friday 12th October 2012
View Replies!

Can this query with two tables be combined?

Right now I have two different webpages that give me the data I need. But I was wondering if I could combine them onto one report. Here are the two queries I use: $dailySearches = "SELECT DATE_FORMAT(date, '%Y-%m-%d') as Date, COUNT(*) as Searches FROM searches GROUP BY DATE_FORMAT(date, '%Y-%M-%D') order by Date desc limit 120;"; $dailyLogins = "SELECT DATE_FORMAT(last, '%Y-%m-%d') as Date, COUNT(*) as Logins FROM users GROUP BY DATE_FORMAT(last, '%Y-%M-%D') order by Date desc limit 120;";
VIEWS ON THIS POST

178

Posted on:

Sunday 21st October 2012
View Replies!

Speeding up queries with combined indexes

Does it speed up queries to use a combined index on the fields you're searching one, rather than indexes on each field eg if we were to query with the condition: Code: SELECT * FROM my_table WHERE col1 < 100 AND col2>300 If we have an index on col1 and and index on col2, the optimiser would only use one of the indexes, would it not however if we added Code: alter my_table add index combined_index(col1,col2) The optimiser seems to prefer this joined index. I haven't run any benchmarks, but is this the way to go Utilising a combined index for a combination of fields you are likely to use in your condition Maybe there's a better term for it that I could look up.
VIEWS ON THIS POST

159

Posted on:

Sunday 21st October 2012
View Replies!

Can these queries be combined?

I am retrieving a set of rows and then running a loop for each of the rows. Inside the loop there is a query to find out the total number of related rows. Is it possible to do it all in one shot instead of running separate queries Code PHP: $questions=mysql_query("SELECT id, key, content FROM table WHERE type='question'"); while($row=mysql_fetch_array($questions)){ $answers=mysql_query("SELECT COUNT(id) FROM table WHERE key='{$row['key']}' AND type='answer'"); }
VIEWS ON THIS POST

179

Posted on:

Sunday 21st October 2012
View Replies!

Anyway to combine these together?

Hi , Is there anyway of combining these to pieces of code together So the query can count the messages that are stored for that user Code: // count how many new messages they are for that user $new_count = mysql_query(" SELECT COUNT(new) as NumNew FROM gallery_messages WHERE userId='".$got_id[0]."' AND new='1' "); $new_counted = mysql_fetch_row( $new_count ); mysql_free_result( $new_count ); // count how many messages have been replied and stored for that user $rep_count = mysql_query(" SELECT COUNT(replied) as NumReplied FROM gallery_messages WHERE userId='".$got_id[0]."' AND replied='1' "); $rep_counted = mysql_fetch_row( $rep_count ); mysql_free_result( $rep_count ); Thank you for your help and time, spence
VIEWS ON THIS POST

158

Posted on:

Monday 22nd October 2012
View Replies!

Querying unrelated tables

I have an inventory database with three tables: hardware, location, and user. Code: hardware location users --------------- --------- -------- hware_id loc_id user_id users_user_id loc_name user_name location_loc_id etc... etc... hware_serial hware_model etc... Hardware is assigned to only one user at only one location. Locations and users have multiple hardware assigned to them, that is why I added the fields to the hardware table to handle the associations. But, I have users that are assigned to multiple locations so I wasn't sure how to create that relationship since a location would have many users and a user could have many locations. So what I am trying to do is get a list of hardware models (hardware.hware_model) and user names (users.user_name) that hardware is assigned to based on the location (location.loc_name). So it will look like this: Code: hware_name | user_name -----------|---------- MacBook | Steve Jobs ThinkPad | Bill Gate I don't have anything I have tried yet because I don't know where to begin. I can add more fields or another table if it makes it easier.
VIEWS ON THIS POST

74

Posted on:

Wednesday 24th October 2012
View Replies!

Dividing totals from two tables

I am writing a query where I need to get the total points the student received on assignments, this is in the grade table. I then need to divide that total from the total amount of points possible, located in the assignment table. At the end all I should see is the student_id and the percentage of the students grade. Here is where I am so far SELECT s.student_id, SUM(g.points)/SUM(a.points_possible) "GRADE" FROM student s, grade g, assignment a WHERE s.student_id = g.student_id AND g.assignment_id = a.assignment_id ORDER BY student_id; However when I execute this is the error I get: WHERE s.student_id = g.student_id * ERROR at line 3: ORA-00937: not a single-group group function The asterisk's should be under the g I couldn't get it to line up.
VIEWS ON THIS POST

84

Posted on:

Wednesday 24th October 2012
View Replies!

MySQL query help - querying two tables with count(*), sum() and sorting

Hi I am developing an ASP.NET application using MySQL as data storage. I am using an ORM mapper because most my queries are very simple, but also because I am not a big expert in SQL/MySQL queries. Here is my scenario: I have two tables (will only mention the columns I need here): Author (Id, Name) Post (Id, LikeCount, Author) What I would like to get out of a single query is a list of the top 25 authors sorted by Total(LikeCount) of all their posts combined, also I would like to get out the total number of posts for each Author. I hope someone will be able to help me with this query which I hope is simple to most people. Thank you.
VIEWS ON THIS POST

147

Posted on:

Wednesday 24th October 2012
View Replies!

[MS Access] How to update data between 2 tables and delete the old table

I have 2 tables "stock" and "entries" stock looks like: PHP Code: ID\t\tProduct\t\tAvailable 1\t\tMouse\t\t\t20 2\t\tscreen\t\t5 3\t\tSpeakers\t9 etc... entries looks like: PHP Code: ID\t\tClient\t\tProduct\t\tAmount 1\t\tBertus\t\tscreen\t\t3 2\t\tBertus\t\tMouse\t\t\t8 3\t\tAdam\t\t\tMouse\t\t\t4 etc... I want to be able to update Stock.Available like this Stock.Available = Stock.Available + Entries.amount where entries.Client = Bertus and then the Bertus entry should be deleted and it should look for next bertus entry and do the same. so after the query the stock table should look like this: PHP Code: ID\t\tProduct\t\tAvailable 1\t\tMouse\t\t\t28 2\t\tscreen\t\t8 3\t\tSpeakers\t9
VIEWS ON THIS POST

131

Posted on:

Wednesday 24th October 2012
View Replies!

Selecting difference of two tables

i have a table. subject : sub_id, sub_name,sub_type and a view classSubjects: sub_id i want to select those sub_ids that are not in classSubjects. I have tried a lot of different things but it results in this way. subject : sub_id has values 1,2,3,4,5 classSubjects: sub_id has values 2,3 the result i get will be 1 1 2 3 4 4 5 5
VIEWS ON THIS POST

95

Posted on:

Wednesday 24th October 2012
View Replies!

MySQL and 2 heavy tables

I have a MySQL DB and I work with as language: PHP In this database, two tables are too heavy, because the insertion is made every second. I want to make sure that I have, in these two tables, the data from the last 2 months. If I do with normal queries and everything, it will crash the server. If I create a new table and I copy the data from the last 2 months, it could crash the server too. Have you any idea how to deal with this kind of problem Thank you very much
VIEWS ON THIS POST

55

Posted on:

Wednesday 24th October 2012
View Replies!

Latest comments from 3 tables

i have 3 different tables to keep the comments for 3 diff modules Posts, Events and directories post_comments event_comments directory_comments Each comment table has the following structure ----------------------------------------------------------- | id post_id user_id cur_date Comment | | (event_id) | | (direct_id) | ----------------------------------------------------------- I need to list the latest 5 comments from the three tables in order the posted date and time when i used the below query ( ( SELECT `comments`.`added_date` AS `ad_date` , `comments`.`id` AS `a_id` , `users`.`nick_name` AS `nickname` , `posts`.`post_perma` AS `perma_link` FROM `comments` LEFT JOIN `users` ON `users`.`id` = `comments`.`user_id` LEFT JOIN `posts` ON `posts`.`id` = `comments`.`post_id` ORDER BY `comments`.`added_date` DESC LIMIT 5 ) UNION ( SELECT `event_comments`.`added_date` AS `ad_date` , `event_comments`.`id` AS `a_id` , `users`.`nick_name` AS `nickname` , `events`.`event_perma` AS `perma_link` FROM `event_comments` LEFT JOIN `users` ON `users`.`id` = `event_comments`.`user_id` LEFT JOIN `events` ON `events`.`id` = `event_comments`.`event_id` ORDER BY `event_comments`.`added_date` DESC LIMIT 5 ) UNION ( SELECT `business_comments`.`added_date` AS `ad_date` , `business_comments`.`id` AS `a_id` , `users`.`nick_name` AS `nickname` , `business`.`business_perma` AS `perma_link` FROM `business_comments` LEFT JOIN `users` ON `users`.`id` = `business_comments`.`user_id` LEFT JOIN `business` ON `business`.`id` = `business_comments`.`busi_id` ORDER BY `business_comments`.`added_date` DESC LIMIT 5 ) ) ORDER BY `ad_date` DESC LIMIT 5 I am getting an error but it if giving the output correctly when i checked separately without UNION function Please help
VIEWS ON THIS POST

96

Posted on:

Wednesday 24th October 2012
View Replies!

Insert values from separate tables. Please help.

Can someone please help me I am simply trying to insert a value from one table into another table using a SELECT statement. I'm too new to MySQL however to understand what I am doing wrong. My two tables, which are both contained in my database named "comp", with their associated columns are the following: hr_people(id, first_name, last_name, national_id, cdate) hr_jobs(id, hr_people_id, position, start_time, end_time, cdate) I am trying to insert new rows into the the hr_jobs table by using values from the id column of hr_people table. I am doing something wrong however and I have a feeling it is something very simple but because I am new, I am simply overlooking it. The query which I thought would work is: INSERT INTO comp.hr_jobs (id, hr_people_id, position, start_time, end_time, cdate) VALUES (NULL, SELECT id FROM comp.hr_people WHERE national_id LIKE 'TSIA-12544', 'CEO', '1258437600', '1262325600', UNIX_TIMESTAMP() ) Can anybody tell me what I am doing wrong with my select statement It would be greatly appreciated.
VIEWS ON THIS POST

166

Posted on:

Wednesday 24th October 2012
View Replies!

Sort tables/columns help please

can someone please tell me how i would do the following: I have a music website with a top ten charts page. Currently the top artist is displayed by highest rating. I want to remove the rateing system and show top artists by how many listeners (frineds) they have. So the top artists would be the artists with the most listenrs(friends) added to there profile. the rating colum is in a seperate table called users or user_profile users_profile.mid=users.id order by users.rating desc limit $limit"); My members friends that have been added to their profile are stored in user_friends table with the columns... id, *mid, *fid and more that don't matter. mid is the id of the account that hold the friends and the "fid" is the id of the friends approved to add in the account. so to be on the top of the charts first it needs to find the artist (*mid) and than sort anddisplay the artists with the most friends (*fid). this mostly makes no snese to me so I hope you can figure out what I am getting at and help me.
VIEWS ON THIS POST

164

Posted on:

Wednesday 24th October 2012
View Replies!

JOIN with three tables

So I'm used to JOIN with two tables, but not with three. Just need an example of how it would work. So let's say I have one table called cleaners: ID | name ------------ 1 | cleanorex 2 | suavey Another called manufacturers: cleanerID | manufacturer ------------------------- 1 | procter 2 | johnson And a final one called colors: cleanerID | color -------------------- 2 | red How would I connect these all on the first table's ID I'd normally do: "FROM cleaners AS a JOIN manufacturers AS b ON a.id = b.cleanerid" for connecting two tables. But I don't know how to do three. I tried "FROM cleaners AS a JOIN manufacturers AS b ON a.id = b.cleanerid, a JOIN colors AS c ON a.id=c.cleanerid" to no use.
VIEWS ON THIS POST

82

Posted on:

Wednesday 24th October 2012
View Replies!