Page 1 of 1

Using Variable Variable in MySQL fetch_assoc()

Posted: Tue Aug 04, 2009 8:21 am
by etangman
Through the following code I am attempting to display row header and row information from a database. The only thing displayed on the page however are the first and last for loops. I believe the problem is in the nested for loop where I am using $temp in the fetch_assoc() as $row. I know this is something simple, but I am unable to find the exact problem in other forums. Any help would be much appreciated.

Problem area?

Code: Select all

 
for ($k=1; $k < 37; $k++) {
  $temp = "h".$k;
  $$temp.="<td>".$row[$temp]."</td>";
  }
 
Full code:

Code: Select all

 
  $num_rows = $db->affected_rows;
 
  $rdrow="<tr><td width=\"100\">Record Date</td>";
 
  $temp = "h";
  $$temp = "<tr>";
 
      for ($i=1; $i < 37; $i++) {
      $temp = "h".$i;
      $$temp = "<tr><td>H".$i."</td>";
      }
 
  $comp="<tr><td>Comp</td>";
  $mill="<tr><td>Mill</td>";
  $proc="<tr><td>Processing</td>";
  $ebrow="<tr><td>Entered By</td>";
 
      for ($i=1; $i < $num_fields; $i++) {
 
      $row = $result->fetch_assoc();
 
      $rdrow.="<td width=\"100\">".$row['recorddate']."</td>";
 
        for ($k=1; $k < 37; $k++) {
        $temp = "h".$k;
        $$temp.="<td>".$row[$temp]."</td>";
        }
 
      $comp.="<td>".$row['comp']."</td>";
      $mill.="<td>".$row['mill']."</td>";
      $proc.="<td>".$row['processing']."</td>";
      $ebrow.="<td>".$row['enteredby']."</td>";
      }
 
  $db->close();
 
  echo $rdrow.="</tr>";
 
      for ($i=1; $i < 37; $i++) {
 
      $temp = "h".$i;
      echo $$temp.="</tr>";
      }
 
  echo $comp.="</tr>";
  echo $mill.="</tr>";
  echo $proc.="</tr>";
  echo $ebrow.="</tr>";
 
Result:

Code: Select all

 
3 record(s) selected.
Record Date
H1
H2
H3
H4
H5
H6
H7
H8
H9
H10
H11
H12
H13
H14
H15
H16
H17
H18
H19
H20
H21
H22
H23
H24
H25
H26
H27
H28
H29
H30
H31
H32
H33
H34
H35
H36
Comp
Mill
Processing
Entered By
 

Re: Using Variable Variable in MySQL fetch_assoc()

Posted: Tue Aug 04, 2009 8:35 am
by onion2k
I can't see anything wrong with the code... but it does raise the question of your database structure. Do you really have 36 fields called h1 through to h36? Is there a good reason for that?

Re: Using Variable Variable in MySQL fetch_assoc()

Posted: Tue Aug 04, 2009 8:52 am
by towerspl
It seems random to me as to why you would use variable variables, the code doesnt look as though it would actually work and doesnt need the use of variable variables

Re: Using Variable Variable in MySQL fetch_assoc()

Posted: Tue Aug 04, 2009 12:19 pm
by etangman
onion2k wrote:I can't see anything wrong with the code... but it does raise the question of your database structure. Do you really have 36 fields called h1 through to h36? Is there a good reason for that?
towerspl wrote:It seems random to me as to why you would use variable variables, the code doesnt look as though it would actually work and doesnt need the use of variable variables
The fields represent house numbers, the reason I am using variable variables is because the next set involves 200 houses. I don't want to write 1000 lines of code for something that simple. If the code I specified in the problem area looks correct then why does the page not display the record data? Especially this:

Code: Select all

$$temp.="<td>".$row[$temp]."</td>";
It seems like the MySQL engine doesn't accept the $temp variable and just ignores the request.

Re: Using Variable Variable in MySQL fetch_assoc()

Posted: Tue Aug 04, 2009 2:15 pm
by peterjwest
I agree with the other two, you don't need to use variable variables, you would be much better off using an array.

You would also do yourself a favour separating the database interaction and html formatting code and creating a function or two. The following code should do exactly the same as your code:

Code: Select all

 
//Database interaction
$records = array();
$recordItem = 1;
while ($row = $result->fetch_assoc()) {
  $records[$recordItem] = $row;
  $recordItem ++;
}
$db->close();
  
//HTML output
printRow($records, 'recorddate', 'Record Date', 100);
for ($houseNumber = 1; $houseNumber <= 36; $houseNumber++) { 
  printRow($records, 'h'.$houseNumber, 'H'.$houseNumber);
}
$fields = array('comp'=>'Comp','mill'=>'Mill','processing'=>'Processing','enteredby'=>'Entered By');
foreach($fields as $field => $fieldName) {
  printRow($records, $field, $fieldName);
}
  
function printRow($records, $field, $fieldName, $width = NULL) {
  if ($width) $width = ' width="'.$width.'"';
  echo '<tr>';
  echo '<td'.$width.'>'.$fieldName.'</td>';
  foreach($records as $record) {
    echo '<td>'.$record[$field].'</td>';
  }
  echo '</tr>';
}
 
BTW I've also switched your double quotes to single quotes because you don't have to escape html double quotes.
It seems like the MySQL engine doesn't accept the $temp variable and just ignores the request.
Actually at this point the MySQL engine is not involved, $row[] is a standard php array and will therefore accept $$temp so long as it is valid, but you're still better off using an array.

Re: Using Variable Variable in MySQL fetch_assoc()

Posted: Wed Aug 05, 2009 1:30 pm
by etangman
Wow, awesome! I clearly have a lot to learn still, but your help is much appreciated. Thank you all!