The explanation of this may seem a bit long and convoluted but please bear with me. In essence what I want to do is fill a mysql table(A) from another mysql table(B) in my database but in order to do so I need to duplicate values in table (A) so that there will be enough entries to accomodate for the values in table B.
对此的解释可能看起来有点长而且令人费解,但请耐心等待。本质上我想要做的是从我的数据库中的另一个mysql表(B)填充mysql表(A),但为了这样做,我需要复制表(A)中的值,以便有足够的条目来容纳表B中的值
Now for a more concrete example
现在来看一个更具体的例子
How the tables look
表格看起来如何
course_details table
course_details_id | course_id | year_id | teacher_id
+------------------+-----------+-----------+------------+
1 1 To be Set 36
2 2 To be Set 54
3 3 To be Set 78
4 4 To be Set 23
year table
year_semester_id | year | semester
+-----------------+------+---------+
1 2012 1
2 2012 2
3 2012 3
4 2012 4
5 2013 1
6 2013 2
7 2013 3
8 2013 4
How I want the table to look
我希望桌子看起来如何
course_details_id | course_id | year_id | teacher_id
-------------------+-----------+---------+------------+
1 1 1 36
2 1 2 36
3 1 3 36
4 1 4 36
5 1 5 36
6 1 6 58
7 1 7 36
8 1 8 47
9 2 1 54
10 2 2 54
11 2 3 54
12 2 4 67
13 2 5 67
14 2 6 54
15 2 7 54
16 2 8 54
How the code looks
代码看起来如何
<?php
require_once('open_db.php');
get_dbhandle();
$query_year = "SELECT * FROM year";
$result_year = mysql_query($query_year) or die(mysql_error());
$num_year_rows = mysql_num_rows($result_year);
$num_year_rows = ($num_year_rows - 1);
$query_yearid = "SELECT year_semester_id FROM year";
$result_yearid = mysql_query($query_yearid) or die(mysql_error());
$result_ccheck = mysql_query("SELECT course_id FROM courses");
while($row = mysql_fetch_array($result_ccheck))
{
$course_id = $row['course_id'];
for($i = $num_year_rows; $i >= 0; $i--)
{
$query_cdetails = "INSERT INTO course_details (course_id) VALUES ('$course_id')";
$result_cdetails = mysql_query($query_cdetails);
while($row = mysql_fetch_array($result_yearid))
{
$year_semester_id = $row['year_semester_id'];
$query = "INSERT INTO course_details(year_semester_id) SELECT year_semester_id FROM year";
$result = mysql_query($query);
}
}
}
?>
What it does vs what I want it to do: As it currently is set, it correctly creates duplicates of each course_id in course_details table to match the number of year_semester_id's which exist in the years table which is perfect. The problem comes to inserting the year_semester_id's in each corresponding table slot of the table course_details.
它与我想做的事情有什么关系:正如它当前设置的那样,它正确地在course_details表中创建每个course_id的重复项,以匹配year表中存在的year_semester_id的数量,这是完美的。问题是在表course_details的每个对应表槽中插入year_semester_id。
In other words, to ensure that when course_id =1 , year_semester_id=1, course_id=1, year_semester_id =2,....course_id=1, year_semester_id=8, course_id=2, year_semester_id=1, course_id=2, year_semester_id=2......course_id=2, year semester_id =8, course_id=3, year_semester_id =1 etc and so on.... Therein lies the issue.
换句话说,要确保当course_id = 1,year_semester_id = 1,course_id = 1,year_semester_id = 2,.... course_id = 1,year_semester_id = 8,course_id = 2,year_semester_id = 1,course_id = 2,year_semester_id = 2 ...... course_id = 2,year semester_id = 8,course_id = 3,year_semester_id = 1等等......这就是问题所在。
A recap of how the code works, it counts the number of year_semester_id's in the years table, it then subtracts that number by 1 which is the amount of times the course_id is currently in the course_details table and it duplicates it by that number. This total number (the duplicates) plus the original course_id should be the total amount of year_semester_ids. I now want to insert every year_semester_id for every course_id that exists and loop through until each course_id is accounted for. Thank you
回顾代码如何工作,它计算年表中year_semester_id的数量,然后将该数字减去1,这是course_id当前在course_details表中的次数,并且它按该数字重复。此总数(重复项)加上原始course_id应为year_semester_ids的总数。我现在想要为每个存在的course_id插入每个year_semester_id并循环直到每个course_id被考虑。谢谢
2 个解决方案
#1
0
This is what im talking about check the code iy you have a trouble understanding, let me know.
这就是我正在谈论检查代码,你理解有困难,让我知道。
#2
0
It looks to me like what you're attempting to do could easily be done without bloating your database by taking advantage of relational tables. In this case, if I'm understanding you correctly, the end result here is you want to have duplicates of all the rows from course_details with the empty column set to each of the rows from the year table.
在我看来,通过利用关系表,可以轻松完成您尝试做的事情,而不会使数据库膨胀。在这种情况下,如果我正确理解你,那么最终结果就是你想要从course_details中获取所有行的副本,并将空列设置为year表中的每一行。
That being true, you could select that data using JOIN statements:
如果是这样,您可以使用JOIN语句选择该数据:
SELECT `a`.`course_id` , `b`.`year_semester_id` as `year_id` , `a`.`teacher_id` FROM `course_details` `a` INNER JOIN `year` `b`
That should return the data you want in a MySQL resultset. If you want to insert that data into a table, just make sure the table has the correct columns, and set the course_Details_id field to auto increment and do:
这应该在MySQL结果集中返回您想要的数据。如果要将该数据插入表中,只需确保该表具有正确的列,并将course_Details_id字段设置为自动递增并执行:
INSERT INTO `tablename` ( `course_id` , `year_semester_id` , `year_id` ) VALUES (
SELECT `a`.`course_details_id` , `a`.`course_id` , `b`.`year_semester_id` as `year_id` , `a`.`teacher_id` FROM `course_details` `a` INNER JOIN `year` `b`
)
This should insert all the data you need into the new MySQL table without the need for PHP scripts.
这应该将您需要的所有数据插入到新的MySQL表中,而无需PHP脚本。
#1
0
This is what im talking about check the code iy you have a trouble understanding, let me know.
这就是我正在谈论检查代码,你理解有困难,让我知道。
#2
0
It looks to me like what you're attempting to do could easily be done without bloating your database by taking advantage of relational tables. In this case, if I'm understanding you correctly, the end result here is you want to have duplicates of all the rows from course_details with the empty column set to each of the rows from the year table.
在我看来,通过利用关系表,可以轻松完成您尝试做的事情,而不会使数据库膨胀。在这种情况下,如果我正确理解你,那么最终结果就是你想要从course_details中获取所有行的副本,并将空列设置为year表中的每一行。
That being true, you could select that data using JOIN statements:
如果是这样,您可以使用JOIN语句选择该数据:
SELECT `a`.`course_id` , `b`.`year_semester_id` as `year_id` , `a`.`teacher_id` FROM `course_details` `a` INNER JOIN `year` `b`
That should return the data you want in a MySQL resultset. If you want to insert that data into a table, just make sure the table has the correct columns, and set the course_Details_id field to auto increment and do:
这应该在MySQL结果集中返回您想要的数据。如果要将该数据插入表中,只需确保该表具有正确的列,并将course_Details_id字段设置为自动递增并执行:
INSERT INTO `tablename` ( `course_id` , `year_semester_id` , `year_id` ) VALUES (
SELECT `a`.`course_details_id` , `a`.`course_id` , `b`.`year_semester_id` as `year_id` , `a`.`teacher_id` FROM `course_details` `a` INNER JOIN `year` `b`
)
This should insert all the data you need into the new MySQL table without the need for PHP scripts.
这应该将您需要的所有数据插入到新的MySQL表中,而无需PHP脚本。