gettingit Posted January 29, 2009 Share Posted January 29, 2009 Hi. I have 3 tables. 1 table has users with different memberships and membership levels associated with them like: membership_id | membership_level -------------------------------- 1 | premium -------------------------------- 5 | basic ------------------------------- 9 | premium -------------------------------- 7 | gold --------------------------------- 11 | basic -------------------------------- The second has sites like: user _id | site_id ------------------ 2 | 44 ------------------ 7 | 44 ----------------- 21 | 12 ------------------ 3rd table links site ids and user ids based on membership_level from table 1 like: id | membership_id | site_id ------------------------------- 1 | 1 | 44 ----------------------------- 2 | 9 | 44 ---------------------------- 3 | 5 | 21 ---------------------------- 4 | 11 | 21 ------------------------------ what I need to do is get the membership_id(s) from table one based on membership_level, and the user_id(s) from table 2 based on site_id and place them into table 3 in one pass. I have tried various things with different results. The biggest problem I am having is because there are numerous user_id (s) and numerous site_id(s) I end up with only the first record. Can anyone shed some light on this problem for me? I was told to grab the data and place into arrays and loop thru the results on the insert, but it just isn't hitting home with me. Examples much appreciates as is any help. Quote Link to comment Share on other sites More sharing options...
fenway Posted January 29, 2009 Share Posted January 29, 2009 I don't understand.. you show a third table. Quote Link to comment Share on other sites More sharing options...
Mchl Posted January 29, 2009 Share Posted January 29, 2009 I can not see how membership_id is assigned to site_id... Quote Link to comment Share on other sites More sharing options...
FezEvils Posted January 29, 2009 Share Posted January 29, 2009 what kind of result that u want? i not fully understand what you are saying, but my idea based on your table , u should using site_id to retrieve what user and what membership_level . ex: 1 premium --> site_id 44 --> user_id(2).. Quote Link to comment Share on other sites More sharing options...
fenway Posted January 29, 2009 Share Posted January 29, 2009 I can not see how membership_id is assigned to site_id... Oh good,it's not just me. Quote Link to comment Share on other sites More sharing options...
gettingit Posted January 29, 2009 Author Share Posted January 29, 2009 I don't understand.. you show a third table. what I need to do is get the membership_id(s) from table one based on membership_level, and the user_id(s) from table 2 based on site_id and place them into table 3 in one pass. Quote Link to comment Share on other sites More sharing options...
gettingit Posted January 29, 2009 Author Share Posted January 29, 2009 what kind of result that u want? i not fully understand what you are saying, but my idea based on your table , u should using site_id to retrieve what user and what membership_level . ex: 1 premium --> site_id 44 --> user_id(2).. what I need to do is get the membership_id(s) from table one based on membership_level, and the user_id(s) from table 2 based on site_id and place them into table 3 in one pass. Sorry I dont have the luxury of changing any table layouts or field names. I must retrieve the information from the two tables as shown and place it into the third table. Quote Link to comment Share on other sites More sharing options...
Mchl Posted January 29, 2009 Share Posted January 29, 2009 I must retrieve the information from the two tables as shown and place it into the third table. But on what basis? Quote Link to comment Share on other sites More sharing options...
gettingit Posted January 29, 2009 Author Share Posted January 29, 2009 I must retrieve the information from the two tables as shown and place it into the third table. But on what basis? what I need to do is get the membership_id(s) from table one based on membership_level, and the user_id(s) from table 2 based on site_id and place them into table 3 in one pass. Membership levels have been set in other pages functions and assigned to users. Site ids and user ids in second table are being created in the process. Quote Link to comment Share on other sites More sharing options...
Mchl Posted January 29, 2009 Share Posted January 29, 2009 OK.... so there's a table somewhere, where each user_id has membership_id assigned to it? Quote Link to comment Share on other sites More sharing options...
gettingit Posted January 29, 2009 Author Share Posted January 29, 2009 OK.... so there's a table somewhere, where each user_id has membership_id assigned to it? No. Tables as shown. No way to do a join. Quote Link to comment Share on other sites More sharing options...
Mchl Posted January 29, 2009 Share Posted January 29, 2009 No. Tables as shown. No way to do a join. No way to do what you want. Quote Link to comment Share on other sites More sharing options...
gettingit Posted January 29, 2009 Author Share Posted January 29, 2009 No. Tables as shown. No way to do a join. No way to do what you want. Isnt there a way to get the info from the first table, place it into an array, second table to another array then do an insert with the 2 arrays? Quote Link to comment Share on other sites More sharing options...
Mchl Posted January 29, 2009 Share Posted January 29, 2009 There needs to be some relation between data in both tables. In other words, how do you know, which membership_id assign to which site_id? Quote Link to comment Share on other sites More sharing options...
gettingit Posted January 29, 2009 Author Share Posted January 29, 2009 There needs to be some relation between data in both tables. In other words, how do you know, which membership_id assign to which site_id? Thats the step I am at, assigning a link between the two ids in the third table. There has to be some way of doing a call and placing into an array do another call into an array then loop and place into the third table. Thats the part that has me stumped. Perhaps someone can just give me an example of getting membership ids from table one based on level and placing into array and inserting into another table. Perhaps I can go from there... Quote Link to comment Share on other sites More sharing options...
Mchl Posted January 29, 2009 Share Posted January 29, 2009 UPDATE table3 AS t2, table1 AS t1 SET t3.membership_id = t1.membership_id WHERE t1.membership_level = 'premium' AND t3.site_id =... That would be one part, but you need to have something after AND. Otherwise it will update all rows in table3 Quote Link to comment Share on other sites More sharing options...
gettingit Posted January 29, 2009 Author Share Posted January 29, 2009 UPDATE table3 AS t2, table1 AS t1 SET t3.membership_id = t1.membership_id WHERE t1.membership_level = 'premium' AND t3.site_id =... That would be one part, but you need to have something after AND. Otherwise it will update all rows in table3 Thats not the missing part of the puzzle. The missing part is getting the information into an array. Quote Link to comment Share on other sites More sharing options...
Mchl Posted January 29, 2009 Share Posted January 29, 2009 You just want to get data form table1 and table2 into arrays? Quote Link to comment Share on other sites More sharing options...
gettingit Posted January 29, 2009 Author Share Posted January 29, 2009 You just want to get data form table1 and table2 into arrays? that was the original request then to place the array into table 3 with a loop thru the results. If I could just get some pointers on the arrays, perhaps I could figgure out the loop. Quote Link to comment Share on other sites More sharing options...
Mchl Posted January 29, 2009 Share Posted January 29, 2009 $query = "SELECT * FROM table1"; $result = mysql_query($query) or die(mysql_error().": $query"); while ($row = mysql_fetch_assoc($result)) { $array1[] = $row; } $query = "SELECT * FROM table2"; $result = mysql_query($query) or die(mysql_error().": $query"); while ($row = mysql_fetch_assoc($result)) { $array2[] = $row; } //Now you should have both tables loaded into arrays. Let's display them. var_dump($array1); var_dump($array2); Quote Link to comment Share on other sites More sharing options...
FezEvils Posted January 31, 2009 Share Posted January 31, 2009 like mchl said , u have to create 2 sql. 1st include table2 and table3... "select from table2, table3 where table2.site_id = table3.site_id" then you have table3.membership_id result, create 1 more sql for table1, set $result = table3.membership_id "select from table1 where table1.membership_id ='$result'" Quote Link to comment Share on other sites More sharing options...
gettingit Posted January 31, 2009 Author Share Posted January 31, 2009 like mchl said , u have to create 2 sql. 1st include table2 and table3... "select from table2, table3 where table2.site_id = table3.site_id" then you have table3.membership_id result, create 1 more sql for table1, set $result = table3.membership_id "select from table1 where table1.membership_id ='$result'" As stated nothing in common to join tables all id numbers are different and unique as they are for different purposes. I have been playing with the arrays, for some reason when testing when I should get 2,3 I get 0,1,2 . I feel like theres something still missing ... The idea is what I need tho, the 2 tables into arrays then loop thru the results and insert into table 3. Quote Link to comment Share on other sites More sharing options...
Mchl Posted February 1, 2009 Share Posted February 1, 2009 I can't even grasp why you even want this, as the contents of third table will be completely unrelated...? Quote Link to comment Share on other sites More sharing options...
gettingit Posted February 1, 2009 Author Share Posted February 1, 2009 I can't even grasp why you even want this, as the contents of third table will be completely unrelated...? It is pretty complex, there are some 67 tables total being dealt with by the program. I didnt really think that the reasoning would matter, just the functions. I am becomming more and more convinced it may not be possible at all as when I do use the arrays as shown even with false results as described, the results from the second table are not placed in the third table in the while loop. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.