help with insert statement

[wikedawsum]

I'm trying to make a website to track all of the movies I have. I have a page that displays the movies by category with title and a description if I choose to put one. I'm having trouble with setting up the form to add a new movie. Here is the code I have so far:

Form to add new movies:
[code]<form name='add_movie' action='add_movie.php' method='post'>
<table width=250 cellpadding=2 cellspacing=0 bgcolor='#cccccc'>
<tr>
<td><p>Title</p></td>
<td><input type='text' name='title'  size=30 maxlength=255></td>
</tr>
<tr>
<td><p>Description</p></td>
<td><input type='text' name='description'  size=30 maxlength=255></td>
</tr>
<tr>
<td><p>Category</p></td>
<td><input type='text' name='category'  size=30 maxlength=255></td>
</tr>
<tr>
<td><input type='submit' value='Add Movie'></td>
</tr>
</table>
</form>[/code]


and add_movie.php:
[code]<?php
$conn = mysql_connect("localhost", "brandi", "bc5106")
  or die($msg_no_connect);
mysql_select_db("movies")
  or die(mysql_error());
  $res = mysql_query("INSERT INTO dvd (title, description, category) VALUES ($_POST['title'], $_POST['description'], $_POST['category'];");
  if (mysql_affected_rows($res) > 0) {
  echo 'Your movie was successfully added.'
  }
  else {
  echo 'This movie already exists..'
  }
?>[/code]

I want to be able to add the movie if it doesn't exist, or tell me that it's already there if the movie does exist. Can anyone help me with this code?

Thank you!!

[Psycho]

<?php
<?php
$conn = mysql_connect("localhost", "brandi", "bc5106")
  or die($msg_no_connect);
mysql_select_db("movies")
  or die(mysql_error());

$sql = "SELECT * FROM dvd WHERE title = '{$_POST['title']}':;
$res = mysql_query($sql);

if (mysql_num_rows($res)) {
  echo 'This movie already exists..'
} else {
  $sql = "INSERT INTO dvd (title, description, category) VALUES ($_POST['title'], $_POST['description'], $_POST['category'];"
  $res = mysql_query($sql);
  if (mysql_affected_rows($res) > 0) {
      echo 'Your movie was successfully added.'
  } else {
      echo 'The insert failed.'
  }
}
?>
?>

[wikedawsum]

Thanks for the help. All I got was a blank page after clicking submit.

[wikedawsum]

I see what the code is supposed to be doing, I'm just not sure why it's not returning any information.. even an error would help! Anyone have any other suggestions?

Thanks!

[Psycho]

Then add some debugging code. Does this produce any errors?

[code]<?php
$conn = mysql_connect("localhost", "brandi", "bc5106") or die($msg_no_connect);

mysql_select_db("movies") or die(mysql_error());

$sql = "SELECT * FROM dvd WHERE title = '".$_POST['title']"'";
$res = mysql_query($sql) or die($sql."<br><br>".mysql_error());

if (mysql_num_rows($res)) {
  echo "This movie already exists.";
} else {
  $sql = "INSERT INTO dvd (title, description, category) VALUES ($_POST['title'], $_POST['description'], $_POST['category'];"
  $res = mysql_query($sql) of die($sql."<br><br>".mysql_error());
  if (mysql_affected_rows($res) > 0) {
      echo "Your movie was successfully added.";
  } else {
      echo "The insert failed.";
  }
}
?>[/code]

[Philip]

Taken from the code above (with debugging.) The problem was a semicolon (;) inside the "
[code]<?php
$conn = mysql_connect("localhost", "brandi", "bc5106") or die($msg_no_connect);

mysql_select_db("movies") or die(mysql_error());

$sql = "SELECT * FROM dvd WHERE title = '".$_POST['title']"'";
$res = mysql_query($sql) or die($sql."<br><br>".mysql_error());

if (mysql_num_rows($res)) {
  echo "This movie already exists.";
} else {
  $sql = "INSERT INTO dvd (title, description, category) VALUES ($_POST['title'], $_POST['description'], $_POST['category']";
  $res = mysql_query($sql) of die($sql."<br><br>".mysql_error());
  if (mysql_affected_rows($res) > 0) {
      echo "Your movie was successfully added.";
  } else {
      echo "The insert failed.";
  }
}
?>[/code]

[wikedawsum]

Thank you both for the help. Unfortunately I'm still getting a blank page..  >:(

[Philip]

Hmm... okay, try this:

[code]<?php
$conn = mysql_connect("localhost", "brandi", "bc5106") or die(mysql_error());

mysql_select_db("movies") or die(mysql_error());

$sql = "SELECT * FROM dvd WHERE title = '".$_POST['title']"'";
$res = mysql_query($sql) or die($sql."<br><br>".mysql_error());

if (mysql_num_rows($res) > 0) {
  echo "This movie already exists.";
} else {
  $sql = "INSERT INTO dvd (title, description, category) VALUES ($_POST['title'], $_POST['description'], $_POST['category']";
  $res = mysql_query($sql) of die($sql."<br><br>".mysql_error());
  if (mysql_affected_rows($res) > 0) {
      echo "Your movie was successfully added.";
  } else {
      echo "The insert failed.";
  }
}
?>
[/code]

[wikedawsum]

Still nothing.. very frustrating!

[Philip]

I see it:

THe line with this:
$sql = "SELECT * FROM dvd WHERE title = '".$_POST['title']"'";

Should be:
$sql = "SELECT * FROM dvd WHERE title = '".$_POST['title']."'";

[wikedawsum]

Changed to correct code... still nothing!

[Philip]

Also,
$sql = "INSERT INTO dvd (title, description, category) VALUES ($_POST['title'], $_POST['description'], $_POST['category']";
should be

$sql = "INSERT INTO dvd (title, description, category) VALUES (".$_POST['title'].", ".$_POST['description'].", ".$_POST['category'].")";

[wikedawsum]

Finally got something back!

INSERT INTO dvd (title, description, category) VALUES (Shall We Dance, , romance)

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'We Dance, , romance)' at line 1

Now I just need to find where the error is. Thank you!!

[Philip]

Try adding single quotes around it

VALUES ('".$_POST['title']."', '".$_POST['description']."', '".$_POST['category']."')

[wikedawsum]

Thank you, KingPhillip! I'm finally able to insert the movies into my db. One more quick question if you don't mind.. when I click add movie I keep getting "the insert failed", but when I got back to my main page that displays the movies, it shows that it is there. Any idea why that may be happening? It's this part of the code:

[code]if (mysql_num_rows($res) > 0) {
  echo "This movie already exists.";
} else {
  $sql = "INSERT INTO dvd (title, description, category) VALUES ('".$_POST['title']."', '".$_POST['description']."', '".$_POST['category']."')";
  $res = mysql_query($sql) or die($sql."<br><br>".mysql_error());
  if (mysql_affected_rows($res) > 0) {
      echo "Your movie was successfully added.";
  } else {
      echo "The insert failed.";
  }[/code]

Thanks again for all your help. I have a better understanding of using insert statements now.  :)

[Philip]

Normally, I do
[code]<?php

if(!$res) {
echo "The insert failed!";
} else {
echo "The movie was added!";
}
?>[/code]

[wikedawsum]

That worked perfectly!! Thank you!!