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

Question

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";

have you assigned second query in $sql2 variable and also check for the primary key in table? — Shakti Singh
– Shakti Singh, Commented Dec 3, 2010 at 6:06
Thank you for the warning. I'm aware of the concept and was unconcerned with preventing sql injection for this project, as i know it would remain local. — Bodhidarma
– Bodhidarma, Commented Dec 3, 2010 at 11:49

Adeel · Accepted Answer · 2010-12-03 06:11:56Z

2

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

edited Dec 3, 2010 at 6:11

answered Dec 3, 2010 at 6:05

Adeel

6151 gold badge6 silver badges16 bronze badges

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

1 Comment

Bodhidarma Over a year ago

When I defined the second query as $sql2 it fixed the problem, but by "duplication of the primary key" do you mean using test (MadWords) in two INSERT statements? Is this something to be avoided? I'm wondering because the code works despite this duplication now.

Brains1994 · Accepted Answer · 2010-12-03 06:59:14Z

1

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);
}

edited Dec 3, 2010 at 6:59

answered Dec 3, 2010 at 6:15

Brains1994

913 bronze badges

2 Comments

Bodhidarma Over a year ago

Thanks. That fixed it. Stylistically, including this if statement after every insertion isn't so appealing to me. Is there a cleaner way of achieving the same ends?

Brains1994 Over a year ago

You could do a for loop using the $_POST array. EDIT: Added some sample code to my original post for your problem.

Bhanu Prakash Pandey · Accepted Answer · 2010-12-03 06:11:34Z

0

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

answered Dec 3, 2010 at 6:11

Bhanu Prakash Pandey

3,7851 gold badge26 silver badges16 bronze badges

Comments

anon · Accepted Answer · 2010-12-03 06:12:24Z

0

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?

answered Dec 3, 2010 at 6:12

anon

Comments

jorelli · Accepted Answer · 2010-12-03 06:53:24Z

0

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.

answered Dec 3, 2010 at 6:53

jorelli

8,4164 gold badges38 silver badges35 bronze badges

4 Comments

Bodhidarma Over a year ago

Do I know you from somewhere?

Bodhidarma Over a year ago

I'd vote this up if i could. I'm pretty sure I'll be able to have a madlibs app by by saturday.

jorelli Over a year ago

that's most unlikely, as I have no physical form.

jorelli Over a year ago

oh and don't use CamelCase table names; SQL is case-insensitive.

Collectives™ on Stack Overflow

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

5 Answers 5

1 Comment

2 Comments

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

2 Comments

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related