[SOLVED] Loop mysql insert

[johntp]

Hey guys,

 

My table is setup like below. I'm trying to insert the UserID to completed for every TopicID there is with a done value of 0. I can get all the topicIDs with a simple sql query, but how do make a insert the same username for every topicid?

 

I feel im not explaining this that well, but i hope someone gets what i mean.

 

UsersCompletedTopics

UserID (primary)UserID (forign)Title

UsernameTopicID (forign)TopicID(primary)

emaildoneq1

[/td][td]recno (primary)a1

 

Here is the code I'm working with.

<?php
include "php/conn.php";
include "php/code.php";

$name     = ($_POST['name']);
$email    = ($_POST['email']);
$uname2    = ($_POST['uname']);

$query  = "INSERT INTO Users (Username, email) VALUES ( '$uname2', '$email' )";
  mysql_query($query) or die('Error, insert query failed: '.mysql_error());

$query2 = "Select UserID FROM Users WHERE username = '$uname'";
$query2=mysql_query($query2);
while($query2=mysql_fetch_object($query2))
{
$UserID=$query2->UserID;
}
$query3 = "SELECT TopicID FROM Topic"; 
$result3 = mysql_query($query3) or die(mysql_error());
	while($row3 = mysql_fetch_array($result3)){
}

[roopurt18]

UPDATE `completed` SET UserID='theuserid' WHERE `done`=0;

[johntp]

I explainded it pretty bad. I need loop through the Topics table and insert and insert every topic id with the same userid. so if i had topics 1 to 3 then it would loop through and

 

insert into completed (UserID, TopicID, done) Value ("$userid", "TopicID[1]", 0);
insert into completed (UserID, TopicID, done) Value ("$userid", "TopicID[2]", 0);
insert into completed (UserID, TopicID, done) Value ("$userid", "TopicID[3]", 0);

 

But i want to do it with one line, and the it can change from 1 to 3, to 1 to 10 and so on.

[roopurt18]

Just to make sure I understand.  You have a `topics` table with N records, where N is an arbitrary integer.  For a given user, U, you want to duplicate the existing N topics records but with U's user id?

 

If topics is:

UserID        TopicID          done
Joe            ABC               0
Joe            XYZ               0
Joe            LMNO             1

 

And the user ID is 'Betty', then you want topics to look like:

UserID        TopicID          done
Joe            ABC               0
Joe            XYZ               0
Joe            LMNO             1
Betty         ABC               0
Betty         XYZ               0

 

Is this correct?

 

[johntp]

Kind of.

 

Say you have a topics table with topicid of 1-6. then when you have somone enter their userinformation, it inserts their userid and every topicid into the completed table.

 

Topics Table.

TopicID
1             
2
3
4
5
6

 

Users Table (somone inserts the user john.

UserID
john

 

 

Completed Table (Then for every TopicID i want to create a row in completed with the userid john.)

TopicID       UserID       Done
   1           John         0
   2           John         0
   3           John         0
   4           John         0
   5           John         0
   6           John         0

[sasa]

INSERT INTO Completed ( UserID, TopicID, done ) 
SELECT $UserID, TopicID, 0
FROM Topics

[johntp]

Thanks sasa, Thats exactly what i needed. I can't beleive it's that simple.