misbehaving SQL-driven table...whyyyyy!?

[WhiteRau]

Hey folks!  a two-part problem here:

 

PART 1:

here's a weird problem i bumped into today.  when i leave OUT the 'varTable' section (see the class designations), it generates a table (allbeit messed up for reasons that i'll get into in part 2).  but when i put 'varTable' back in, it generates the first array output just fine, but nothing shows for the table.  (i need the first to be able to display the array)

 

why does that happen?

 

PART 2:

when the table DOES generate, it gives a lot of errors and $i counts up to 7!  whaaaaat!?

 

thanks in advance for your help.  this has me stumped.  i've tried resetting the array, different variable names...nothing worked.  i also included the array output at the very bottom.  seems like a lot of extra data in there to me...and the [ 0 ] is printing oddly here (spaces prevent odd formatting...).

 

WR! 

 

 

 

---

DATABASE TABLE:

 

| id | item    | cost  | use |

--------------------------------

| 0 | basket            | 35    |  1  |

| 1 | bicycle            | 165  |  1  |

| 2 | glass                | 5      |  1  |

| 3 | running shoe    | 156  |  1  |

| 4 | silver              | 300  |  1  |

 

---

PHP CODE:

<?php include("constants.php");

<?php
   $connexion = mysql_connect(DB_SERVER, DB_USER, DB_PASS);
   if(!$connexion) { die("Database Connection Failed: ".mysql_error()); }
   
   $selectDB = mysql_select_db("DB_CHOICE", $connexion);
   if(!$selectDB) { die("Database Selection Failed: ".mysql_error()); }
   
   $query = "SELECT * ";
   $query .= "FROM basic_table";
   
   $results = mysql_query($query, $connexion);
   if(!$results) { die("Database Query Failed: ".mysql_error()); }
?>
<div>
<pre class="varTable">
   <?php
      while($checkTable = mysql_fetch_array($results)) {
         print_r($checkTable);
      }
   ?>
</pre>
</div>

<br><br>
<div id='codeBlock'>
<table class='phpTable' width='500' cellpadding="10">
   <?php   //   generate table header row
      while ($tableRow = mysql_fetch_array($results)) {
         echo "<tr>";
         for($i=0; $i<count($tableRow); $i++) {
            echo "<td>" . 
            $tableRow[$i] .
            "</td>";   
         }
         echo "</tr>\n";
      }
   ?>
</table>

 

---

ARRAY OUPUT:

 

	Array
(
    [0] => 1
    [id] => 1
    [1] => basket
    [item] => basket
    [2] => 35
    [cost] => 35
    [3] => 1
    [usable] => 1
)
Array
(
    [0] => 2
    [id] => 2
    [1] => bicycle
    [item] => bicycle
    [2] => 165
    [cost] => 165
    [3] => 1
    [usable] => 1
)
Array
(
    [0] => 3
    [id] => 3
    [1] => glass
    [item] => glass
    [2] => 5
    [cost] => 5
    [3] => 1
    [usable] => 1
)
Array
(
    [0] => 4
    [id] => 4
    [1] => running shoe
    [item] => running shoe
    [2] => 14
    [cost] => 14
    [3] => 1
    [usable] => 1
)
Array
(
    [0] => 5
    [id] => 5
    [1] => silver
    [item] => silver
    [2] => 300
    [cost] => 300
    [3] => 1
    [usable] => 1
)

[efficacious]

is this a css issue?

 

the arrays look fine to me... seems like your issue is with html/css formatting..

 

 

 

 

this section:

for($i=0; $i<count($tableRow); $i++

 

counts the entire array instead of the number of values. the array looks double like that because it is.. so you can call the value by either:

 

yourVar[integer]

or by

yourVar['DB_FieldName']

[Maq]

In the future, please use

tags around your code.

[WhiteRau]

Maq:  apologies.  i meant to.  forgot. 

 

efficacious:  tried removing all styling... no soap.  same odd problem.

 

as for the values, they call just fine... given that count($tableData) counts the entire array, if i want doubles should i just divide by 2 (to account for the keys)?

 

thank you for your help.  i hope we can figure it out soon!

[mikosiko]

Hey folks!  a two-part problem here:

 

PART 1:

here's a weird problem i bumped into today.  when i leave OUT the 'varTable' section (see the class designations), it generates a table (allbeit messed up for reasons that i'll get into in part 2).  but when i put 'varTable' back in, it generates the first array output just fine, but nothing shows for the table.  (i need the first to be able to display the array)

 

why does that happen?

 

nothing weird with that.... a good read on mysql_fetch_array() is in order.. pay special attention to the "DESCRIPTION:"

 

PART 2:

when the table DOES generate, it gives a lot of errors and $i counts up to 7!  whaaaaat!?

 

 

same answer than before, but now pay attention to the paragraph "RETURN VALUES"

 

finally based on some comments in your code, seems to me that you are tying to print the columns name in the table header row.. is that is case reading about mysql_fetch_fields() should help.

[WhiteRau]

clarity descends.  thank you very much.  looks like i need more diversity in my work! 

 

i'll make what i think are the correct alterations and let you know how it goes. 

 

thanks again for such great help.  and for making me LOOK.  better still.

 

WR!

[WhiteRau]

nope.  still fuzzy.  what i did was change the fetch to assoc (that worked fine) and add a line to reset the pointer using mysql_data_seek  like this:

 

<pre class="varTable">
   <?php
      while($checkTable = mysql_fetch_assoc($results)) {
         print_r($checkTable);
      }
   ?>
</pre>
<?php mysql_data_seek($results, 0); ?>

 

i figured that's all i needed, since the first mysql_fetch_assoc call would leave the pointer at the end.  but now all i get are Undefined Offset # errors for the table output...which makes no sense.  so i'm feeling like a big chunky retard...

[mikosiko]

I will bet that you:

a) replaced the mysql_fetch_array() for mysql_fetch_assoc() in your second while loop too.... or

b) you still having mysql_fetch_array() in that loop but still using the counter incorrectly as somebody else told you some posts ago.

 

easy solution.... read mysql_fetch_row() for the second loop

[mikosiko]

an alternative solution should be write to second loop in this way

 

      
$nmr_fields = mysql_num_fields($results);
while ($tableRow = mysql_fetch_row($results)) {
         echo "<tr>";

         for($i=0; $i<$nmr_fields; $i++) {   /// or use mysql_num_fields($results) directly here instead of $nmr_fields
            echo "<td>". 
            $tableRow[$i] .
            "</td>";   
         }
         echo "</tr>\n";
}

[WhiteRau]

here is what i don't understand: shouldn't the mysql_data_seek reset the pointer so i can reuse the $results in ANY operation? be it fetch_array, fetch_assoc or fetch_row?

 

or should i just be using fetch_row period?  to my mind, these are basically identical loops (which probably should be a function, i know) so why can't i just reset the pointer and move on?

 

this is what is confusing the hell out of me.  maybe i'm going about this all wrong...?  i've seen some of the examples for building HTML tables and the ones that work seem to be so clunky and non-modular: can't break out different parts to individual functions or loops because they're using totally different data contructs.  to my mind, they all add an additional layer of data instead of just working with $results, which contains everything you need already.  i'm just lost when it comes to pointer control on the $results array.

 

thanks for working with me on this.  i appreciate it.

 

WR! 

[mikosiko]

here is what i don't understand: shouldn't the mysql_data_seek reset the pointer so i can reuse the $results in ANY operation?

Yes, that is correct

 

be it fetch_array, fetch_assoc or fetch_row?

Yes, but that doesn't prevent to use them incorrectly.. that is were you are failing to understand the differences among them, and apply the right one (or combination of them) in your code

 

or should i just be using fetch_row period?  to my mind, these are basically identical loops (which probably should be a function, i know) so why can't i just reset the pointer and move on?

what it is important for you is understand the differences/similitudes among mysql_fetch_array, mysql_fetch_assoc and mysql_fetch_row and to know exactly what each one is going to return, and how to use that result in the rest of your code...

 

p.e: in your original code you have this lines (forget the errors in the first loop because you already fixed that using mysql_data_seek):

 

      while ($tableRow = mysql_fetch_array($results)) {
         echo "<tr>";
         for($i=0; $i<count($tableRow); $i++) {

 

 

• and somebody else told you that you were using the count() incorrectly.... why?
  
• if you replaced mysql_fetch_array for mysql_fetch_assoc() in this part of the code it should gave a lot of errors... why?
  
• if you replace mysql_fetch_array for mysql_fetch_row now the code works... why?
  
• if instead of use count($tableRow) you use count(mysql_num_fields($results)) the code works no matter if you use mysql_fetch_array or mysql_fetch_row().... why?
  

 

those are the basic questions that you must answer yourself (testing) to gain the understanding for future.

 

hope this help

 

 

[WhiteRau]

thank you very much!  i really appreciate the point in the right direction.  i wish projects had more time to learn the fundamentals better, eh? 

 

off i go!  hopefully i can find a resource that is a bit clearer than just the manual.  tired brain needs simple right now.

 

WR!