[SOLVED] multiple while with mysql link.

[synking]

Hey guys i am attempting to create a table from a mysql database. The table gets three columns with different amount of rows depending on the search query.

 

I am trying to create a select box in the <thead> area of the table. i am using this code

 

<?php
                    while ($row = mysql_fetch_array($result)) {
                        echo('
                            <option value="'.$row["city"].'">'.$row["city"].'</option>');
                    }?>

but what happens is it creates only one select box (I have it trying to create it under each column.) and it does not fill out the data for the table just the one select box under the city column. I am attempting to create this select field for a filtering function in the search query. I have not got that far as the select field seems to end the results. The information in the table is filled out the same way with a while statement to echo new rows/columns.

 

Not sure why this is not working any help would be great. It may help to know that i am using only one mysql query.

 

 

 

 

[vladn]

use print_r($DB_RESULT_VARIABLE_HERE) to debug your script.

[synking]

ok not sure where i should put that. After the first select or before. as all what will print is the array from the database. which i know all the fields are correct. maybe if you give me more info as how that will help me.

[Psycho]

I think you need to provide a little more information. Are you trying to create multiple instances of the same select field? Or are you trying to create different select lists from the same query?

 

If it is one of the above, then the answer is probably the same. You should start by separating your logic from the actual display/content. I think your problem stems from you trying to create the lists in-line with the HTML. By separating the logic and display it would be easier to implement.

 

Anyway, I would create variables for the option lists and then echo them later in the code as needed.

 

Here's a quick example of what I mean.

 

PHP Logic

<?php
$stateOptions = '';
$cityOptions = '';
while ($row = mysql_fetch_array($result))
{
    $stateOptions .= "<option value="{$row['state']}">{$row['state']}</option>";
    $cityOptions .= "<option value="{$row['city']}">{$row['city']}</option>";
}
?>

 

Display Code

Select State:
<select name="state"><?php echo $stateOptions; ?></select><br />

Select Cities:
<select name="city1"><?php echo $cityOptions; ?></select>
<select name="city2"><?php echo $cityOptions; ?></select>

[synking]

Ok thanks for the info but i am getting this error

 

Parse error: syntax error, unexpected '{' in /home/jking/public_html/test/mailtest/access_result.php on line 6

 

and if i remove the { i get unexpected varialbe. if you can give me any more help it would be great.  I am using it inline with the page but if you can tell me what more info i can give i will.  anymore help is good.

[Psycho]

I am not going to write your code for you (unless you want to pay me). As stated in my sig

I do not always test the code I provide, so there may be some syntax errors.

 

However, I did notice that the code posted above lost the escaping of the quote marks (a bug on this forum).

 

The code I provided was simply an example (hence the use of the word 'example' beofre the code). It is up to you to adapt for your purposes. If you want help, then provide the code that is in error with specific details.

[synking]

Yes thanks for the help and sorry for not checking it i have it working the way you suggested but it still only echo the option list an nothing else i hope this may help.

 

This is the page that gets called by a form.

 

<?PHP
error_reporting (E_ALL ^ E_NOTICE);


require_once("../../includes/DbConnector.php");
require_once('page_array.php');


$connector = new DbConnector();

if(isset($_POST['state'])) {
   $state = $_POST['state'];
}
if(isset($_POST['npa'])) {
   $npa = $_POST['npa'];
}



$result = $connector->query("SELECT * FROM accessnumbers where state='$state' AND npa='$npa' ORDER BY city ASC");
$numres = $connector->fetchRow($result);
  if ($numres == 0) {
       include_once('no_result.php');
  }elseif (!$result) {
       echo('<p class="error">Error from SQL Query: ' .$connector->getSqlError() . '</p>');

   }else{
       include_once('access_result.php');
}
mysql_free_result($result);

?>

 

and when successful it calls this page

<?php
$stateOptions = '';
$cityOptions = '';
while ($row = mysql_fetch_array($result))
{
    $stateOptions .= "<option value=\"{$row['state']}\">{$row['state']}</option>\n";
    $cityOptions .= "<option value=\"{$row['city']}\">{$row['city']}</option>\n";
}
?>
<html>
<head>
   <title>Result of Access Number search</title>
    <style type="text/css" media="screen">
      @import "css.php";
    </style>
</head>
<body>
<table id="dbresult">
   <thead>
    <tr>
     <th>City</th>
     <th>State</th>
     <th>Access Number</th>
     <th>Carrier</th>
    </tr>
    <tr>
     <th><form id="filter_city" action="access_check.php" onSubmit="return valid(this)" method="get" name="filter_city">
          Select State:
     <select name="filter_city" id="filter_select" size="1">
               <?PHP
                   echo ($cityOptions);
             ?>
     </th>
     <th>
    </tr>
   </thead>
   <tbody>
<?php
   while ($row = $connector->fetchArray($result)) {
     echo ('
    <tr>
     <td>'.$row["city"].'</td>
     <td>'.$row["state"].'</td>
     <td>'.$row["npa"].'-'.$row["nxx"].'-'.$row["localnum"].'</td>
     <td>'.$row["carrier"].'</td>
    </tr>');
   }?>
  </tbody>
</table>
</body>
</html>

 

The issue is that the table never gets created in the result page it only shows the option box and the <th> fields before that is called.

[Psycho]

You are apparently using a database extraction class:

$result = $connector->query("SELECT * FROM accessnumbers where state='$state' AND npa='$npa' ORDER BY city ASC");
$numres = $connector->fetchRow($result);

By the basis of the "$connector->" statements.

 

So, I don't think you can use the regular mysql_fetch_array() statement in the results page. I *think* you should be using the fetchRow() method of the class. But, as I don't know what the class is I can't say for sure.

[synking]

thanks but the connector class returns the mysql equivalent code where

 

$connector->fecthArray($result)
returns mysql_fecth_array($result)

And it is a home built class that i use to make sure i am sending the right request.

 

And thanks for the info so far but it still does not seem to work.

[Psycho]

OK, I see the problem now. But before I get to the real problem, I do notice that you run this line before you load the page to display the results:

$numres = $connector->fetchRow($result);

 

I would think that that line would "use up" the first record in the result set, not sure why you have it there. Anyway, the problem is that you are trying to loop through the result set twice - once for the city/state list and again for other data. You either need to reset the result set back to the first record OR pull all the data in one pass (my preference).

 

I also notice that you don't seem to be using the state list. you have a label to select the state, but you are actually using the city list. I did not correct any of that:

 

<?php
$stateOptions = '';
$cityOptions  = '';
$resultHTML   = '';
while ($row = mysql_fetch_array($result))
{
    $stateOptions .= "<option value=\"{$row['state']}\">{$row['state']}</option>\n";
    $cityOptions  .= "<option value=\"{$row['city']}\">{$row['city']}</option>\n";
    $resultHTML   .= "<tr>";
    $resultHTML   .= "<td>{$row['city']}</td>";
    $resultHTML   .= "<td>{$row['state']}</td>";
    $resultHTML   .= "<td>{$row['npa']}-{$row['nxx']}-{$row['localnum']}</td>";
    $resultHTML   .= "<td>{$row['carrier']}</td>";
    $resultHTML   .= "</tr>\n";
}
?>
<html>
<head>
   <title>Result of Access Number search</title>
    <style type="text/css" media="screen">
      @import "css.php";
    </style>
</head>
<body>
<table id="dbresult">
   <thead>
    <tr>
     <th>City</th>
     <th>State</th>
     <th>Access Number</th>
     <th>Carrier</th>
    </tr>
    <tr>
     <th><form id="filter_city" action="access_check.php" onSubmit="return valid(this)" method="get" name="filter_city">
          Select State:
     <select name="filter_city" id="filter_select" size="1">
         <?php echo ($cityOptions); ?>
     </th>
     <th>
    </tr>
   </thead>
   <tbody>
   <?php echo $resultHTML; ?>
   </tbody>
</table>
</body>
</html>

 

 

[synking]

Thanks for the assistence and the line

 

$numres = $connector->fetchRow($result);

 

Is there to see if the result has any rows but i have found a better way to implement that. Thanks so much for the help on this one though.