Jump to content

Archived

This topic is now archived and is closed to further replies.

sssaudddahmed

insert data into mysql table via a while statment

Recommended Posts

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.

Share this post


Link to post
Share on other sites
[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]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.