While loop not working...

[tta013]

Dear all,

 

I have the follwoing code:

 

$club_id = array('CLASS1' => 1, 'CLASS2' => 2, 'CLASS3' => 3);

$sql = "SELECT activity FROM Activities WHERE booking_id = " . $id . " AND activity IN ('class1', 'class2', 'class3')";

 

  $result = mysql_query($sql) or die(mysql_error());

  if (mysql_num_rows($result) > 0) {

    while ($row = mysql_fetch_assoc($result)) {

  $sql = "INSERT INTO Activity_Review (booking_id, club_id) VALUES (" . $id . ", " . $club_id[$row['activity']] . ")";

  $result = mysql_query($sql) or die(mysql_error());

}

  }

 

  $sql = "INSERT INTO Activity_Response (booking_id) VALUES (" . $id . ")";

  $result = mysql_query($sql) or die(mysql_error());

}

  }

 

----

 

For some reasons, the code will only insert to activity_review only one row even if there is more than one row.

 

Can someone give me an advise as to what might be the problem is?

 

Regards,

[MasterACE14]

try changing this part...

  if (mysql_num_rows($result) > 0) {
    while ($row = mysql_fetch_assoc($result)) {
     $sql = "INSERT INTO Activity_Review (booking_id, club_id) VALUES (" . $id . ", " . $club_id[$row['activity']] . ")";
     $result = mysql_query($sql) or die(mysql_error());
   }

 

to this:

  if (mysql_num_rows($result) > 0) {
     $i = 1;
    while (mysql_num_rows($result) < $i) {
     $sql = "INSERT INTO Activity_Review (booking_id, club_id) VALUES (" . $id . ", " . $club_id[$row['activity']] . ")";
     $result = mysql_query($sql) or die(mysql_error());
  $i++;
   }

[mbeals]

actually, a valid query that returns 0 rows will cause mysql_fetch_assoc to return 0,

 

so you can just do:

 

<?php
$club_id = array('CLASS1' => 1, 'CLASS2' => 2, 'CLASS3' => 3);
$sql = "SELECT activity FROM Activities WHERE booking_id = " . $id . " AND activity IN ('class1', 'class2', 'class3')";

$result = mysql_query($sql) or die(mysql_error());

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

$sql = "INSERT INTO Activity_Response (booking_id) VALUES (" . $id . ")";
mysql_query($sql) or die(mysql_error());
?>

 

You also had some extra end braces that I cleaned up.  Not sure if they were part of a bigger code block or if you meant them to be there...but for this specific block, they shouldn't be.

That code will generate and execute an sql statement for each row returned by the top query.  If you are still having issues, double check the first sql statement is producing the results you are expecting and then throw an 'echo "$sql<br>"; into the while loop to take a look at what sql statements are being executed.

 

1. verify the results and number of results returned by the first query

2. verify you don't have any primary key conflicts (although that should pop an error)

 

[tta013]

This didn't work I am afraid. The data is no longer appending to the activity_respond table..

 

actually, a valid query that returns 0 rows will cause mysql_fetch_assoc to return 0,

 

so you can just do:

 

<?php
$club_id = array('CLASS1' => 1, 'CLASS2' => 2, 'CLASS3' => 3);
$sql = "SELECT activity FROM Activities WHERE booking_id = " . $id . " AND activity IN ('class1', 'class2', 'class3')";

$result = mysql_query($sql) or die(mysql_error());

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

$sql = "INSERT INTO Activity_Response (booking_id) VALUES (" . $id . ")";
mysql_query($sql) or die(mysql_error());
?>

 

You also had some extra end braces that I cleaned up.  Not sure if they were part of a bigger code block or if you meant them to be there...but for this specific block, they shouldn't be.

That code will generate and execute an sql statement for each row returned by the top query.  If you are still having issues, double check the first sql statement is producing the results you are expecting and then throw an 'echo "$sql<br>"; into the while loop to take a look at what sql statements are being executed.

 

1. verify the results and number of results returned by the first query

2. verify you don't have any primary key conflicts (although that should pop an error)

 

[tta013]

HI there,

 

Sorry, I tried as you suggested but it is not evening inserting booking Id and club_id into the activity review anymore. Previously it was adding one record....

 

I was wondering what is not clicking in the process...

 

 

try changing this part...

  if (mysql_num_rows($result) > 0) {
    while ($row = mysql_fetch_assoc($result)) {
     $sql = "INSERT INTO Activity_Review (booking_id, club_id) VALUES (" . $id . ", " . $club_id[$row['activity']] . ")";
     $result = mysql_query($sql) or die(mysql_error());
   }

 

to this:

  if (mysql_num_rows($result) > 0) {
     $i = 1;
    while (mysql_num_rows($result) < $i) {
     $sql = "INSERT INTO Activity_Review (booking_id, club_id) VALUES (" . $id . ", " . $club_id[$row['activity']] . ")";
     $result = mysql_query($sql) or die(mysql_error());
  $i++;
   }

[mbeals]

echo your queries

[tta013]

Here it is,

 

 

function create_review($id) {

  $club_id = array('CLASS1' => 1, 'CLASSB' => 2, 'CLASSTOP' => 3);

  $sql  = "INSERT INTO Review (booking_id, contact_id) VALUES ";

  $sql .= " (" . $id . ", " . $_SESSION['contact_id'] . " . mysql_real_escape_string($_SERVER['HTTP_USER_AGENT']) . "')";

  $result = mysql_query($sql) or die(mysql_error());

 

  $sql = "SELECT activity FROM Booked_Activities WHERE booking_id = " . $id . " AND activity IN ('CLASS1', 'CLASSB', 'CLASSTOP')";

  $result = mysql_query($sql) or die(mysql_error());

  if (mysql_num_rows($result) > 0) {

//this is the bit that's not working

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

}

  }

 

  $sql = "INSERT INTO Review_Response (booking_id) VALUES (" . $id . ")";

  $result = mysql_query($sql) or die(mysql_error());

}

 

 

 

echo your queries

[kenrbnsn]

That is your code, not the queries that are generated. BTW, when you post code to this forum, please put it between

tags.

 

"echo your queries" means to put

<?php
echo $sql . '<br>';
?>

into your code and show us the results.

 

Ken