Page 1 of 1

While look insert statement not working...

Posted: Wed Aug 20, 2008 3:58 am
by tta013
Dear all,

I am having a problem with my feedback form. The form allows clients to click on a create a review link, I want clients to fill out the questionnaire for the clubs that they have joined. (There are about 10 different clubs). So here is the code:


Code:

Code: Select all

<div>
        <?php
 
        if ($_REQUEST['action'] == 'create') {
          create_review($_REQUEST['id']);
                  }
        ?>
</div> 
---
 
On the another page, I have the following bit:
 

Code: Select all

function create_review($id) {
  $club_id = array('A' => 1, 'B' => 2, 'C' => 3, 'D' => 4, 'E' => 5, 'F' => 6, 'G' => 7, 'H' => 9, 'I' => 10, 'J' => 11, 'K' => 12);
  $sql  = "INSERT INTO Review (booking_id, contact_id, start_date, pause_date, finish_date, stage, client_info) VALUES ";
//at this point when I check in the Review table, all the necessary information were found.
  $sql .= " (" . $id . ", " . $_SESSION['contact_id'] . ", CURDATE(), NULL, NULL, 0, '" . mysql_real_escape_string($_SERVER['HTTP_USER_AGENT']) . "')";
  $result = mysql_query($sql) or die(mysql_error());
  $sql = "SELECT activity FROM Activities WHERE booking_id = " . $id . " AND activity IN ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l')";
// at this point when i run a query on the database, i found two activities [b]b[/b] and [b]f[/b].
  $result = mysql_query($sql) or die(mysql_error());
//this is where i think not working
  if (mysql_num_rows($result) > 0) {
    while ($row = mysql_fetch_assoc($result)) {
        $sql = "INSERT INTO Club_Review (booking_id, club_id) VALUES (" . $id . ", " . $club_id[strtoupper($row['activity'])] . ")";
        $result = mysql_query($sql) or die(mysql_error());
    }
  }
// at this piont, when i check Club_Review table, I can only see one booking_id and  club_id instead of two rows with same booking id and different club ids.
  $sql = "INSERT INTO Review_Response (booking_id) VALUES (" . $id . ")";
  $result = mysql_query($sql) or die(mysql_error());
}
----

The problem i have is when a client has booked more than one clubs, the program will only INSERT one club into Club_Review instead of all the clubs the client has booked. I was wondering if someone can help me on this please...

I must admit I am a PHP newbie.

Thanks in advance.

Regards,

Re: While look insert statement not working...

Posted: Wed Aug 20, 2008 2:48 pm
by nowaydown1
Welcome to the forum! You're overwriting the $result resource for your while loop with the query you're running inside the loop:

Code: Select all

 
  while ($row = mysql_fetch_assoc($result)) {
         $sql = "INSERT INTO Club_Review (booking_id, club_id) VALUES (" . $id . ", " . $club_id[strtoupper($row['activity'])] . ")";
         $result = mysql_query($sql) or die(mysql_error());
   }
 
Should be more like:

Code: Select all

 
  while ($row = mysql_fetch_assoc($result)) {
         $sql = "INSERT INTO Club_Review (booking_id, club_id) VALUES (" . $id . ", " . $club_id[strtoupper($row['activity'])] . ")";
         $insertResult = mysql_query($sql) or die(mysql_error());
   }