Insert data into SQL tables

Question

I'm having trouble inserting data into a table. I have the following tables: track (id, tracktitle), album (id, albumtitle), composer (id, composername), albumtrack (PRIMARY: trackid, albumid, composerid).

My PHP page allows you to add a track and then select the album and composer connected with it. It adds the track to the tracktable okay but it won't add it to an album.

I keep looking around for how to do it and I keep getting a bit lost. Can anyone tell me how I should be correctly doing this? Thanks

if (isset($_POST['tracktitle'])): 
  // A new track has been entered
  // using the form.

  $cid= $_POST['cid'];
  $tracktitle = $_POST['tracktitle'];
  $albs = $_POST['albs'];

  if ($cid == '') {
  exit('<p>You must choose an composer for this track. Click "Back" and try         again.</p>');   }

  $sql = "INSERT INTO track, albumtrack SET
  track.tracktitle='$tracktitle', albumtrack.albumid='$albs',    albumtrack.composerid='$cid' " ;
  if (@mysql_query($sql)) {
  echo '<p>New track added</p>';
  } else {
  exit('<p>Error adding new track' . mysql_error() . '</p>');
  }

  $trackid = mysql_insert_id();


 if (isset($_POST['albs'])) {
 $albs = $_POST['albs'];
  } else {
 $albs = array();
 }

 $numAlbs = 0;
  foreach ($albs as $albID) {
 $sql = "INSERT IGNORE INTO albumtrack
        SET albumtrack.trackid='$trackid', albumtrack.albumid='$albs',     albumtrack.composerid='$cid'";
if ($ok) {
  $numAlbs = $numAlbs + 1;
} else {
  echo "<p>Error inserting track into album $albID: " .
      mysql_error() . '</p>';
}
  }
 ?>

 <p>Track was added to <?php echo $numAlbs; ?> albums.</p>

  <p><a href="<?php echo $_SERVER['PHP_SELF']; ?>">Add another track</a></p>
  <p><a href="tracks.php">Return to track search</a></p>

 <?php
 else: // Allow the user to enter a new track

  $composers = @mysql_query('SELECT id, composername FROM composer');
   if (!$composers) {
exit('<p>Unable to obtain composer list from the database.</p>');
   }

  $albs = @mysql_query('SELECT id, albumtitle FROM album');
 if (!$albs) {
  exit('<p>Unable to obtain album list from the database.</p>');
  }
  ?>

  <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
  <p>Enter the new track:<br />
  <textarea name="tracktitle" rows="1" cols="20">
   </textarea></p>
   <p>Composer:
   <select name="cid" size="1">
  <option selected value="">Select One</option>
  <option value="">---------</option>
    <?php
  while ($composer= mysql_fetch_array($composers)) {
   $cid = $composer['id'];
   $cname = htmlspecialchars($composer['composername']);
   echo "<option value='$cid'>$cname</option>\n";
  }
?>
 </select></p>
 <p>Place in albums:<br />
  <?php
   while ($alb = mysql_fetch_array($albs)) {
   $aid = $alb['id'];
   $aname = htmlspecialchars($alb['albumtitle']);
   echo "<label><input type='checkbox' name='albs[]'      value='$aid' />$aname</label><br />\n";
  }
  ?>

Read a bit about SQL injection, your current code is vulnerable... — ChristopheD
– ChristopheD, Commented Nov 26, 2010 at 23:02
Are you using MySQL? Where did you see an INSERT INTO tbl_a, tbl_b syntax? That won't work. — Konerak
– Konerak, Commented Nov 26, 2010 at 23:03
Thank you both. I can't remember where I got that syntax from. I probably dreamt it or something. ! Cheers — paj
– paj, Commented Nov 27, 2010 at 9:09
Will do Christophe. I'm aware that I need to make it all a bit secure once I master the functions. Cheers — paj
– paj, Commented Nov 27, 2010 at 9:14
pak, have a look at PDO (php.net/pdo) and use prepared statements (php.net/manual/en/pdo.prepared-statements.php). This is the easiest and most secure way to avoid SQL injection attacks; it's actually easier than inserting variables into your query strings anyway :) — El Yobo
– El Yobo, Commented Nov 27, 2010 at 9:38

Pablo Santa Cruz · Accepted Answer · 2010-11-26 23:02:34Z

1

Your insert sentence is wrong:

Try this:

$sql = "INSERT IGNORE INTO albumtrack (trackid, albumid, composerid) values " .
       "($trackid, $albs, $cid)";

Assuming your IDs are numeric.

Beware of SQL Injections when you inject non-sanitized values you get from your request.

answered Nov 26, 2010 at 23:02

Pablo Santa Cruz

182k33 gold badges250 silver badges300 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

paj Over a year ago

I've actually based it on a tutorial from a PHPSQL book. The original was $sql = "INSERT IGNORE INTO jokecategory SET jokeid=$jid, categoryid=$catID"; But there's probably something else going on too, or it's outdated. Thanks for your correction.

Haluk · Accepted Answer · 2010-11-27 00:19:43Z

0

Beside's Pablo's corrected way of writing the query. I also notice you are trying to insert into two tables at once with:

$sql= "INSERT INTO track, albumtrack"

MySQL does not allow inserting into two tables at once.

answered Nov 27, 2010 at 0:19

Haluk

2,0992 gold badges27 silver badges36 bronze badges

1 Comment

paj Over a year ago

Thanks Haluk. Don't know where I got the idea from. Thanks for setting me straight.

Collectives™ on Stack Overflow

Insert data into SQL tables

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related