mysql_query in a while loop

Question

I'm trying to execute I have an html form in a page of this sort :

Name: <input type="text" id="namebox" value="" name="fields[]" /> <br />
Position: <input type="text" id="positionbox" value="" name="fields[]" /> <br />

<input type="hidden" name="createcard">     
<input type="submit" value="Create">

.. and 3 other fields. I'm passing these 5 form fields by POST to a file process.php which has the following function to insert the array elements into a mysql DB.

if(isset($_POST['createcard'])){
    $this->procCreateCardtheme1();
..
..

    function procCreateCardtheme1(){
    global $session;

            $cardData = $_POST['fields'];
            $username=$session->username;
            $sno=1;
            echo count($cardData);
            while($sno < count($cardData))
            {
             $v=$cardData[$sno];
             echo $v;
             mysql_query("INSERT INTO carddata VALUES ('$username', $sno, '$v', 1)");
             $sno++;
            }

Now, the echo statement above returns the expected output, that is the five or so fields. But the mysql_query only executes once. It just stores the first entry in the DB, and nothing else. Even re-submitting the form does nothing at all. It's just the one entry that is stored in the DB. Any ideas?

not a good idea to execute a query in a loop. form the query in the loop and execute it outside. — zerodin
– zerodin, Commented Oct 25, 2010 at 20:28
Change to mysql_query("INSERT INTO carddata VALUES ('$username', $sno, '$v', 1)") or die(mysql_error()) and tell us what the output is. Plus, be sure to escape user input data for safety! — Jason Durbin
– Jason Durbin, Commented Oct 25, 2010 at 20:30
Yes. I will add mysql_real_escape_string() to the user data. :) Thanks ;) — 0xff0000
– 0xff0000, Commented Oct 25, 2010 at 20:33
Also, you do validation on the $username, even as it comes from $session, you never know when some other page screws up and stores a bad user name in the session. — David Mårtensson
– David Mårtensson, Commented Oct 25, 2010 at 20:40

Mark Byers · Accepted Answer · 2010-10-25 20:31:24Z

2

Do you have a unique constraint on username in the carddata table? This will cause the second insert to fail.

To debug this you should add some error checking to your program:

mysql_query("INSERT INTO carddata VALUES ('$username', $sno, '$v', 1)")
    or trigger_error(mysql_error());

You might also need to use mysql_real_escape_string to avoid syntax errors or possible SQL injection vulnerabilities if the string data can contain quotes.

edited Oct 25, 2010 at 20:31

answered Oct 25, 2010 at 20:26

Mark Byers

843k202 gold badges1.6k silver badges1.5k bronze badges

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

4 Comments

0xff0000 Over a year ago

I want it to be the same username. I just need the array values to be changing. i.e. $cardData[0], $cardData[1] and so on..

Mark Byers Over a year ago

@Hardik Ruparel: Can you post the output of SHOW CREATE TABLE carddata in your question?

Felix Kling Over a year ago

@Hardik Ruparel: You initialize $sno with 1. I think you should initialize it with 0.

0xff0000 Over a year ago

@Felix. Yep. I noticed that too. Thanks :)

Kashif · Accepted Answer · 2019-06-23 12:24:21Z

0

Single Loop iteration issue occurs when you have issue with variable for query ($query) and Result Object ($result).

Try different name for variable inside the WHILE Loop or debug the variable inside the loop.

answered Jun 23, 2019 at 12:24

Kashif

382 bronze badges

Collectives™ on Stack Overflow

mysql_query in a while loop

2 Answers 2

4 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Related