Multiple For Loops to Display Data

[obrienkev]

Hi mike,

Get this variable in url:
?client=Abbey+National%2FInscape+UK&subfund=not_set&director=not_set&contact=not_set&submit=Continue

Still get blank page.

Any ideas?

Heres my code on the following page that checks fields, etc.:

  $client = $_POST['client'];
  
  // Connect to Database
  $conn = db_connect();
  if (!$conn)
       return 'Could not connect to Database;';
   
  // Perform Query
 $query="select * from client as c, director as d, contact as co, subfund as s where c.client = '$client' and c.client_ID=s.client_ID";
  
  if(isset($_POST['subfund']!= 'not_set')) {
  $subfund = $_POST['subfund'];
  $query .=" and s.subfund='$subfund'";
  }
  
  if(isset($_POST['director']!= 'not_set')) {
  $director = $_POST['director'];
  $query .=" and d.director='$director' and d.director_ID=s.director_ID";
  }
  
  if(isset($_POST['contact']!= 'not_set')) {
  $contact = $_POST['contact'];
  $query .=" and co.name='$contact' and co.contact_ID=s.contact_ID";
  }
  
  $result = mysql_query($query) or die(mysql_error());
  
  $num_results = mysql_num_rows($result);
  
  echo '<p><span class="header">Director Selected: '.$director.'</span></p>';
  echo '<table width="600" border="1" cellspacing="2" cellpadding="0">';
  
  for ($i=0; $i<$num_results; $i++)
  {
           $row = mysql_fetch_array($result);
          echo '<tr><td>Client:</td><td>'.$row['client'].'</td></tr>';
         echo '<tr><td>Entity: </td><td>'.$row['entity'].'</td></tr>';
         echo '<tr><td>Subfund: </td><td>'.$row['subfund'].'</td></tr>';
  }
  
  echo '</table>'; 

and heres my select menus:

 $select_echo_1 = "<option value=\"not_set\">Select Client</option>";
 $select_echo_2 = "<option value=\"not_set\">Select Subfund</option>";
 $select_echo_3 = "<option value=\"not_set\">Select Director</option>";
 $select_echo_4 = "<option value=\"not_set\">Select Name</option>";
 
 while ($row = mysql_fetch_array($result))
 {
    $select_echo_1 .= "<option value='" . $row["client"] . "'>" . $row["client"] . "</option>";
      $select_echo_2 .= "<option value='" . $row["subfund"] . "'>" . $row["subfund"] . "</option>";
    $select_echo_3 .= "<option value='" . $row["director"] . "'>" . $row["director"] . "</option>";
      $select_echo_4 .= "<option value='" . $row["name"] . "'>" . $row["name"] . "</option>";
 }
 
 echo("<select name='client'>$select_echo_1</select><br /><br />");
 echo("<select name='subfund'>$select_echo_2</select><br /><br />");
 echo("<select name='director'>$select_echo_3</select><br /><br />");
 echo("<select name='contact'>$select_echo_4</select><br /><br />"); 

[spikeZ]

You need to remove the isset part....
Also because the form method has changed, so must the variable _POST...

<?
$client = $_GET['client'];

// Connect to Database
$conn = db_connect();
if (!$conn)
     return 'Could not connect to Database;';
  
// Perform Query
$query="select * from client as c, director as d, contact as co, subfund as s where c.client = '$client' and c.client_ID=s.client_ID";

if($_GET['subfund'] != 'not_set')) {
$subfund = $_GET['subfund'];
$query .=" and s.subfund='$subfund'";
}

if($_GET['director'] != 'not_set')) {
$director = $_GET['director'];
$query .=" and d.director='$director' and d.director_ID=s.director_ID";
}

if($_GET['contact'] != 'not_set')) {
$contact = $_GET['contact'];
$query .=" and co.name='$contact' and co.contact_ID=s.contact_ID";
}
#################################################
# echo the query to check it is what you expect #
echo $query;
#################################################
$result = mysql_query($query) or die(mysql_error());

$num_results = mysql_num_rows($result);

echo '<p><span class="header">Director Selected: '.$director.'</span></p>';
echo '<table width="600" border="1" cellspacing="2" cellpadding="0">';

for ($i=0; $i<$num_results; $i++)
{
         $row = mysql_fetch_array($result);
         echo '<tr><td>Client:</td><td>'.$row['client'].'</td></tr>';
        echo '<tr><td>Entity: </td><td>'.$row['entity'].'</td></tr>';
        echo '<tr><td>Subfund: </td><td>'.$row['subfund'].'</td></tr>';
}

echo '</table>'; 
?>

I have also put in an echo for the query to check that it is doing what you want.

Cheers

Mike

[obrienkev]

Thanks Mike. However, if I do not select one of the selectmenus the results bring up all the info from the table related to the unselected menu.

Why is this? Is it my query that's wrong??

e.g. If the contact select menu is not chosen then the results page displays all the results from the contact database.

 $query="select * from client as c, director as d, contact as co, subfund as s where c.client = '$client' and c.client_ID=s.client_ID";
 
 if($_GET['subfund']!= 'not_set') {
 $subfund = $_GET['subfund'];
 $query .=" and s.subfund='$subfund'";
 }
 
 if($_GET['director']!= 'not_set') {
 $director = $_GET['director'];
 $query .=" and d.director='$director' and d.director_ID=s.director_ID";
 }
 
 if($_GET['contact']!= 'not_set') {
 $contact = $_GET['contact'];
 $query .=" and co.name='$contact' and co.contact_ID=s.contact_ID";
 } 

[spikeZ]

So if you dont select any menu, you get the full database of results?
or do you mean if you don't select one of the items you get all the results from that item?

[spikeZ]

try this....

<?
$client = $_GET['client'];

// Connect to Database
$conn = db_connect();
if (!$conn)
     return 'Could not connect to Database;';
  
// Perform Query
$query="select * from client as c, director as d, contact as co, subfund as s where c.client = '$client' and c.client_ID=s.client_ID";
if((!$_GET['subfund']) && (!$_GET['director']) && (!$_GET['contact'])) {
echo 'No selection made, please go back and pick something.';
exit();
}
if($_GET['subfund'] != 'not_set')) {
$subfund = $_GET['subfund'];
$query .=" and s.subfund='$subfund'";
}

if($_GET['director'] != 'not_set')) {
$director = $_GET['director'];
$query .=" and d.director='$director' and d.director_ID=s.director_ID";
}

if($_GET['contact'] != 'not_set')) {
$contact = $_GET['contact'];
$query .=" and co.name='$contact' and co.contact_ID=s.contact_ID";
}
#################################################
# echo the query to check it is what you expect #
echo $query;
#################################################
$result = mysql_query($query) or die(mysql_error());

$num_results = mysql_num_rows($result);

echo '<p><span class="header">Director Selected: '.$director.'</span></p>';
echo '<table width="600" border="1" cellspacing="2" cellpadding="0">';

for ($i=0; $i<$num_results; $i++)
{
         $row = mysql_fetch_array($result);
         echo '<tr><td>Client:</td><td>'.$row['client'].'</td></tr>';
        echo '<tr><td>Entity: </td><td>'.$row['entity'].'</td></tr>';
        echo '<tr><td>Subfund: </td><td>'.$row['subfund'].'</td></tr>';
}

echo '</table>'; 
?>

[obrienkev]

Yeah. If I don't select one of the items I get the full results from that item.

If I don't select any menu at all I get no results, which is correct.

How do I fix this??


This check doesn't work because each menu has a value of 'not_set' anyway.

 if((!$_GET['subfund']) && (!$_GET['director']) && (!$_GET['contact'])) 
 {
 echo 'No selection made, please go back and pick something.';
 exit();
 } 

[spikeZ]

nuts, it's Friday then!

if(($_GET['subfund'] == 'not_set') && ($_GET['director'] == 'not_set') && ($_GET['contact'] == 'not_set')) 

[obrienkev]

Still brings up all results from contact table.

Checking if filled in won't do me as the user should be allowed to select one if s/he likes.

Any more ideas Mike? Thanks for your help.

[spikeZ]

OK, heres a plan....
take out the not_set from the option value and leave it blank.
use this code..

if(isset($_POST['subfund'])) {
  $subfund = $_POST['subfund'];
  $query .=" and s.subfund='$subfund'";
  } 

That should now not pass a value.
(you can also remove the line from post #32)

Mike

[obrienkev]

Hi SpikeZ. Back again. Thanks so much for your help to date.

I have changed my code a little.

The page with the select menus now has thios code:

<form action="two.php" name="form" method="post">
  <span class="header">Select:</span><br /><br />
  
  <select name="directors">
  <option value="none">Select Director</option>
  <?php
  while ($row = mysql_fetch_array($result))
  {
  echo "<option value='" . $row["director"] . "'>" . $row["director"] . "</option>";
  }
  ?>
  </select>
  
  <select name="clients">
  <option value="none">Select Client</option>
  <?php
  while ($row = mysql_fetch_array($result2))
  {
  echo "<option value='" . $row["client"] . "'>" . $row["client"] . "</option>";
  }
  ?>
  </select>

How do I make each menu have a possible 'not selected' option, which would mean the menu is omitted from the query?

I know I'm backtracking a little here but I could really do with your help again.

Basically here's my plan:

1/ The user selects from each select menu. Will be 4 or 5 select menus. If s/he doesn't select a menu then that menu should be omitted from the query.

2/ Each selection of the menus should bring up the desired results from the database tables.

So for example the user selects from director and client menus. The results would then produce all the clients relevant to the director selected.

Again I would really appreciate your help.

Thanks.

Kevin.

[spikeZ]

Morning Kevin,
If I understand correctly, go back to the ideas in post #30 and use the not_set option
<option value="not_set"> </option>

along with the code...

if($_GET['subfund'] != 'not_set')) { 
$subfund = $_GET['subfund']; 
$query .=" and s.subfund='$subfund'"; 
} 

There might be a way of supressing the form element before it is sent using javascript but generally the form sends whatever is on the page.

Mike

[obrienkev]

This is what I have tried so far but always get the 'Please select client' error message.

Why is this?

$query="select * from client as c, director as d, contact as co, subfund as s where c.client = '$client' and c.client_ID=s.client_ID"; 
 
 
 if (!$client || $client="none")
 {
       echo 'Please select client';
      exit;
 }
 
 if($director!="none") 
 {
 $query .=" and d.director='$director' and d.director_ID=s.director_ID";
 }
 
 $result = mysql_query($query) or die(mysql_error());
 $num_results = mysql_num_rows($result); 

[spikeZ]

because what you are asking there is:
if there is no client submitted OR the client field =none, display the message.
If you want, PM me the page.

Mike

[obrienkev]

Yup. That's right. The code from the previous page is in post #35. I have <option value="none">Select Client</option>

Even when I select a client I still get the error message.

[obrienkev]

Hey SpikeZ. Seems to work now.
Changes to

if (!$client || $client=="none")
 {
       echo 'Please select client';
       exit;
 } 

Problem now is that for any selection the results page brings up the same results repeatedly. Here is my code:

$query="select * from client as c, director as d, contact as co, subfund as s where c.client = '$client' and c.client_ID=s.client_ID"; 
 
 
 if($director!="none") 
 {
 $query .=" and d.director='$director' and d.director_ID=s.director_ID";
 }
 
 $result = mysql_query($query) or die(mysql_error());
 $num_results = mysql_num_rows($result);
 
 echo '<p><span class="header">Director Selected: '.$director.'</span></p>';
 echo '<table width="600" border="1" cellspacing="2" cellpadding="0">';
 
 for ($i=0; $i<$num_results; $i++)
 {
          $row = mysql_fetch_array($result);
          echo '<tr><td>Client:</td><td>'.$row['client'].'</td></tr>';
         echo '<tr><td>Entity: </td><td>'.$row['entity'].'</td></tr>';
         echo '<tr><td>Director: </td><td>'.$row['director'].'</td></tr>';         
 }
 
 echo '</table>'; 

[spikeZ]

It's because the query is incorrect.
The query itself is (with the selection )

select * from client as c, director as d, contact as co, subfund as s where c.client = '$client' and c.client_ID=s.client_ID and d.director='$director' and d.director_ID=s.director_ID 

whereas it should be using a WHERE clause in the final part, so your last part should read

$query .=" WHERE d.director='$director' and d.director_ID=s.director_ID"; 

Mike

[obrienkev]

Mike,

Get SQL syntax error using my code where I changed and to where. Any ideas??

$query="select * from client as c, director as d, contact as co, subfund as s where c.client = '$client' and c.client_ID=s.client_ID"; 
 
 
 if($director!="none") 
 {
 $query .=" WHERE d.director='$director' and d.director_ID=s.director_ID";
 } 

[spikeZ]

Sorry Kevin, my fault - didn't read the query fully.
You have two where clauses - one for clients and one for directors.
Change back to and....

Mike

[obrienkev]

So do you know how I can solve this Mike??

[spikeZ]

PM'd

[obrienkev]

Hey Mike,
Posted my problem on database forum. changed code to

 $query="select * 
   from subfund as s
 inner
   join director as d
 	on s.director_ID = d.director_ID
 inner
   join client as c
 	on s.client_ID = c.client_ID
  where c.client = '$client'";

Seems to work anyway. Thanks for your help!

[spikeZ]

Believe it or not, that is something along the lines of what I was going to say!!!!

Glad it's finally sorted.

Mike