insert data into mysql table via a while statment

[sssaudddahmed]

i have created a test ecommerce website where people can order either single mp3 tracks or an whole album.

The problem that i have got is that when a customer wants to order the whole album i want the each track from the album to be inserted into a order table in mysql.


in the mysql database i have:

tracks table // contains tracks of albums - foriegn key = albumid // contains album id
album table // contains albums - primary key = albumid
order table // contains customer id, tracks name, album id etc

Once the customers hits the buy album buttons they are sent to the orderprocess.php page with a url paramter id = 'albumid'

Within the orderprocess.php i do this code:

$id = $_get["id"];
$dum = date("D dS M,Y h:i a");
$_SESSION["id"] = "1";
$id = $_SESSION["id"];

$host = "localhost";
$username="root";
$password="waterwater";
$database="p36311";

mysql_connect($host,$username,$password);
@mysql_select_db($database) or die( "Can't retrieve database.");
$query="SELECT * FROM tracks WHERE albumid = '$id' ";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();
$i=0;

while ( $i <= $num) {

$line = mysql_fetch_array($result);
$Trackid = $line[1];
$Albumid = $line[6];
$Cost = $line[7];

mysql_connect($host,$username,$password);
@mysql_select_db($database) or die( "Can't retrieve database.");
$query="INSERT INTO order (cusid, trackid, albumid, cost, orderdate) values
('$id','$Trackid','$Albumid','$dum')";
mysql_query($query);
$i=$i+1;
}

}

Basically i want to retrieve all tracks that belong to the url parameter (albumid). I then want to insert each track indivdually into the orders table.

The above page gets processed but no data is entered into the database.

Can someone please help me.

[poirot]

[b]Don't close the MySQL link just to reopen it again[/b]

Try this:

[code]$id = $_get["id"];
$dum = date("D dS M,Y h:i a");
$_SESSION["id"] = "1";
$id = $_SESSION["id"];

$host = "localhost";
$username="root";
$password="waterwater";
$database="p36311";

mysql_connect($host,$username,$password);
@mysql_select_db($database) or die( "Can't retrieve database.");

$result = mysql_query("SELECT * FROM tracks WHERE albumid = '$id' ");

while ($line = mysql_fetch_row($result)) {

   $Trackid = $line[1];
   $Albumid = $line[6];
   $Cost = $line[7];

   $query = "INSERT INTO order (cusid, trackid, albumid, cost, orderdate) VALUES
   ('$id','$Trackid','$Albumid','$dum')";

   mysql_query($query);
}[/code]