PHP/MYSQL: Only able to insert values into first row

I'm wondering if this necessarily calls for an array and/or loop, and what solutions might solve this issue. As a learning exercise, I'm trying to insert two of five variable into two successive rows in MY SQL. I set up a simple table with one column in SQL called test. My first "INSERT INTO table VALUES ( '$Word1' )"; statement successfully inserts the value into the first row. Similar/almost identical subsequent code with $Word2 does not add the value to SQL. I'm imagining I have to somehow advance to the next row, but I'm completely lost as to how to accomplish this. I scoured the forums, my开发者_运维知识库 PHP book, and w3Schools in vain.

/*retrieve user input from separate HTML input form */
/* and initializes variables */  
$Word1 = $_POST["Word1"];
$Word2 = $_POST["Word2"];
$Word3 = $_POST["Word3"];
$Word4 = $_POST["Word4"];
$Word5 = $_POST["Word5"];

//select db
mysql_select_db("madlibs", $con);  

//insert user input for word 1 into SQL
$sql = "INSERT INTO test (MadWords)
VALUES
('$Word1')";
if (!mysql_query($sql,$con))
{
  die('Error: ' . mysql_error());
}

//word 2      
/* ***THIS CODE AND MANY VARIATIONS OF IT FAIL TO ENTER $WORD2 INTO SQL*/
"INSERT INTO test (MadWords)
VALUES
('$Word2')";

if (!mysql_query($sql2,$con))    
  /*I've cut this if statement in other debugging runs with the same result*/
{
  die('Error: ' . mysql_error());
}  

echo "1 record added";

---

it could be the duplication of primary key that prevents to insert second values in the table.

---

You never declared your $sql2 variable. It's executing a null query instead of the one you want it to execute.

//word 2      
/* ***THIS CODE AND MANY VARIATIONS OF IT FAIL TO ENTER $WORD2 INTO SQL*/
$sql2 = "INSERT INTO test (MadWords)
VALUES
('$Word2')";

if (!mysql_query($sql2,$con))    
  /*I've cut this if statement in other debugging runs with the same result*/
{
  die('Error: ' . mysql_error());
} 

That should be the fix unless there is code that is relevant that you didn't post.

To answer your comment, you could do something like:

foreach ($_POST as $value) {
  $sql = "Insert into test (madwords) values('$value')";
  mysql_query($sql,$con);
}

---

your problem is with PRIMERY Key if you create one column as id which is primery key this query works fine

---

Assuming you're using the same query for both iterations, the most likely reason why the second query would fail is that the MadWords field is your primary key, or otherwise doesn't allow duplicates.

• Do the Word1 and Word2 fields have the same value?
• What do you get when you run EXPLAIN test in MySQL?

---

mysql> create database madlibs;
Query OK, 1 row affected (0.03 sec)

mysql> use madlibs;
Database changed

mysql> create table test(MadWords varchar(200));
Query OK, 0 rows affected (0.01 sec)        

mysql> select * from test;
Empty set (0.00 sec)

mysql> insert into test(MadWords) values ('foo');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----------+
| MadWords |
+----------+
| foo      |   
+----------+
1 row in set (0.00 sec)

mysql> insert into test(MadWords) values ('bar');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----------+
| MadWords |
+----------+
| foo      |   
| bar      |   
+----------+
2 rows in set (0.00 sec)

seems ok.

Your input might be messed up. In the supplied example, you didn't actually set the $sql2 variable. Try using the queries directly on the database without involving PHP, either through the command line or PHPMyAdmin. If you're ever having trouble with your database and you're not sure if it's in the application layer or the database layer, print out all of your database queries literally. That is, instead of using mysql_query, echo out the query you were going to run. Does it look how you expect? Try running those queries you get against the database on their own.

If you have a table for each MadLibs story, there should be a column for each word. That way an entire submission is one row. There should be an ID column to identify that row; then you can rely on just that ID to get out all of the data for that whole submission with a select * from test where id = 12345; or whatever.