[SOLVED] Queries inside a loop

[padams]

Can you create queries inside a loop? Probably in a 'do while' loop.

 

The problem I've hit is that I have a web app for a sports team. On one page we enter how many tries(touchdowns) they scored, and then this number of drop-down menus appears on the next page, filled with all the possible players. The user then selects the players who scored and submits the form.

 

This action should then create a new record in the 'tries' table for each try scored (in other words, for each drop-down menu).

 

I've got it so that I can create one try automatically using the code below, but is there a way I can loop this so it repeats for every try? I've named them try1, try2, etc.

 

$createtry_sql = sprintf("INSERT into tries (tryOpponent, tryTeam, trySeason, tryPlayer) VALUES ('%s', '%s', '%s', '%s')",

$_SESSION['creatematch']['opposition'],

$_SESSION['creatematch']['ottersteam'],

$_SESSION['creatematch']['season'],

$_POST['try1']

);

$createtry_query = mysql_query($createtry_sql) or die(mysql_error());

[Ken2k7]

You can just do something like this:

 

<?php

foreach ($_POST as $p){
    if ($p != "Submit"){
        // sql queries
    }
}

[padams]

Very close! Unfortunately it just entered the same try multiple times. It's because I had $_POST['try1'] for one of the values, rather than 'try2', 'try3', etc. How would I alter this so that the value inside the POST will auto-increment?

 

$createtry_sql = sprintf("INSERT into tries (tryOpponent, tryTeam, trySeason, tryPlayer) VALUES ('%s', '%s', '%s', '%s')",

  $_SESSION['creatematch']['opposition'],

  $_SESSION['creatematch']['ottersteam'],

  $_SESSION['creatematch']['season'],

  $_POST['try1']  <-------------------------this line here is the problem!!

  );

$createtry_query = mysql_query($createtry_sql) or die(mysql_error());

[teng84]

$x=$_POST['try1'] ;<--- put this outside the loop

 

 

 

$++;

$createtry_sql = sprintf("INSERT into tries (tryOpponent, tryTeam, trySeason, tryPlayer) VALUES ('%s', '%s', '%s', '%s')",

  $_SESSION['creatematch']['opposition'],

  $_SESSION['creatematch']['ottersteam'],

  $_SESSION['creatematch']['season'],

  $x <-------------------------this line here is the problem!!

  );

 

 

is that what you mean?

[padams]

I tried $_POST['try'.$p] and it kind of worked. It puts the correct tries in the table, but also puts one extra record in it, with nothing in the tryPlayer field. Any idea why that would be?

 

foreach ($_POST as $p){

    if ($p != "Submit"){

        // sql queries

 

$createtry_sql = sprintf("INSERT into tries (tryOpponent, tryTeam, trySeason, tryPlayer) VALUES ('%s', '%s', '%s', '%s')",

$_SESSION['creatematch']['opposition'],

$_SESSION['creatematch']['ottersteam'],

$_SESSION['creatematch']['season'],

$_POST['try'.$p]              <---------------------line I changed

);

$createtry_query = mysql_query($createtry_sql) or die(mysql_error());

[teng84]

i guess your code is correct when it add an additional record why?

foreach ($_POST as $p){ <--look

 

isnt it the submit button is part of that post? so iguess that cause the script to add one extra record right?

[padams]

That makes sense. Is there any way to have the foreach ignore the last element of an array? If it helps, this element will always have an empty value for the player scoring the try. ( $_POST['try'.$p]  would return nothing).

[teng84]

count the post and add an increment inside your loop

 

 

inside the loop have something like this

 

 

$counter =1;

foreach ($_POST as $p){

    if ($p != "Submit"){

        // sql queries

 

$counter++;

if (count($POST) == $counter)//<--added

break;

 

$createtry_sql = sprintf("INSERT into tries (tryOpponent, tryTeam, trySeason, tryPlayer) VALUES ('%s', '%s', '%s', '%s')",

  $_SESSION['creatematch']['opposition'],

  $_SESSION['creatematch']['ottersteam'],

  $_SESSION['creatematch']['season'],

  $_POST['try'.$p]              <---------------------line I changed

  );

 

 

note not tested but that should work

[padams]

Thanks again for the help. It still inserted the extra record in the database, but I thought of a workaround. Not pretty, but it seems to be working! I'm now running an extra query that deletes any records in the tries table where the tryPlayer field is blank.

 

$removetry = "DELETE from tries where tryPlayer = ''";

$removetry_query = mysql_query($removetry) or die(mysql_error());

 

Thanks again!

[ToonMariner]

you shoudl NEVER execute queries in a loop that could be large - many hosts limit you to 50 or 100 queries per page (may sound alot but if you don't know how many queries you are going to execute you could hit problems).

 

Multiple inserts can be perfromed with one query (it is MUCH more efficient).

 

 

now one thing that will REALLY help you out here is instead of having lots of inputs called try1, try2 etc. have the input called try[1], try[2] and so on - this will let you loop through the correct array like so

<?php
$qry = INSERT into tries (tryOpponent, tryTeam, trySeason, tryPlayer) VALUES ";
$counter =1;
foreach ($_POST['try'] as $key => $val){
$qry .= "('" . $_SESSION['creatematch']['opposition'] . "',
              '" . $_SESSION['creatematch']['ottersteam'] . "',
              '" . $_SESSION['creatematch']['season'] . "',
              $val   );";
}

$qry = substr($qry,0,-1);

$qry = mysql_query($qry);
?>

 

try that one query and a nice neat way of managing them all.

[padams]

I tried that and it didn't create any records at all in the table. No errors, just no tries. Anything you can spot?

 

$qry = "INSERT into tries (tryOpponent, tryTeam, trySeason, tryPlayer) VALUES ";

$counter =1;

foreach ($_POST['try'] as $key => $val){

$qry .= "('" . $_SESSION['creatematch']['opposition'] . "',

              '" . $_SESSION['creatematch']['ottersteam'] . "',

              '" . $_SESSION['creatematch']['season'] . "',

              $val  );";

}

 

$qry = substr($qry,0,-1);    <-----------I don't understand what is happening here.

 

$qry = mysql_query($qry);

[ToonMariner]

$qry = substr($qry,0,-1);    <-----------I don't understand what is happening here.

- removes the last ;.......... which shoudl be ,!!!!

 

my bad

 

$qry = "INSERT into tries (tryOpponent, tryTeam, trySeason, tryPlayer) VALUES ";

$counter =1;

foreach ($_POST['try'] as $key => $val){

$qry .= "('" . $_SESSION['creatematch']['opposition'] . "',

              '" . $_SESSION['creatematch']['ottersteam'] . "',

              '" . $_SESSION['creatematch']['season'] . "',

              $val  ),";

}

 

$qry = substr($qry,0,-1);    <-----------I don't understand what is happening here.

echo $qry;

// this will echo qry so you can copy and paste into phpmyadmin to check validity of query.

 

$qry = mysql_query($qry);

 

 

[padams]

INSERT into tries (tryOpponent, tryTeam, trySeason, tryPlayer) VALUES ('PASH', 'Mens', '2007', 1 );('PASH', 'Mens', '2007', 1 );('PASH', 'Mens', '2007', 16 );('PASH', 'Mens', '2007', 16 )

 

This didn't create any new records either, and then I tried pasting the query into phpmyadmin and it gave me an error message, saying there was a problem with my SQL syntax near ('PASH', 'Mens', '2007', 1);

 

Is this because the number doesn't have quote marks around it?

[ToonMariner]

did you see my point about swapping the ';' in teh query string for ',' ????

 

foreach ($_POST['try'] as $key => $val){

$qry .= "('" . $_SESSION['creatematch']['opposition'] . "',

              '" . $_SESSION['creatematch']['ottersteam'] . "',

              '" . $_SESSION['creatematch']['season'] . "',

              $val   ),";

}

 

[padams]

Genius! It works, at last! I can't thank you enough, I've been struggling with this for quite a while. Your suggestion has made for an elegant solution and I really appreciate the help.