Trouble returning MySQL results

[The Letter E]

Hey Everybody,

 

I am writing a SUPER SIMPLE script and for some reason I cannot figure this issue out. I guess I'm too close to the situation and have spent too many hours staring at this script.

 

Here's the problem: I am running a basic SQL query through php that should return multiple rows of data and instead returns the first row multiple times.

 

I'm not sure what the problem is, but I'm sure YOU can help!

 

<?php

  //Get Invoice Rows
  $sql = 'SELECT * FROM timecard WHERE INVOICE_ID=\'1000\'';
  $result = mysql_query($sql);
  $rows = mysql_fetch_array($result);
  $num = mysql_num_rows($result);

  //Build Current Invoice
  $i=0;
  $invoice = '<table class="invoice" cellspacing="0" cellpadding="0">';                   
  $invoice .= '<tr class="heading"><td>#</td><td>Invoice</td><td>Date</td><td>Time In</td><td>Time Out</td><td>Hours</td><td>$/Hr</td><td>Sub Total</td></tr>';
  while($i < $num){
    if( $i%2 )
    {
      $eo = 'odd';
    }
    else
    {
      $eo = 'even';
    }
    $invoice .= '<tr id="invoiceRow" class="'.$eo.'"><td>'.$rows[0].'</td><td>PHG'.$rows[1].'</td><td>'.$rows[2].', '.$rows[4].' '.$rows[3].', '.$rows[5].'</td><td>'.$rows[6].'</td><td>'.$rows[7].'</td><td>'.$rows[8].'</td><td>'.$rows[9].'</td><td>'.$rows[10].'</td></tr>';
    $runningTotal[$i] = $rows[10];
    $i++;
  }
  //Get Total
  $total = array_sum($runningTotal);
  $invoice .= '<tr><td colspan="7" style="background-color: #000000; color: #ffffff; font-weight: bold; padding-left: 5px;">Total</td><td align="right" style="background-color: #333333; font-weight: bold; color: #FFFFFF; padding-right: 5px;">'.$total.'</td>';
  $invoice .= '</table>';
  
  echo $invoice;

?>

 

Much thanks in advance for anyone that is able to resolve this problem, even just a try is nice!!

 

Thank You,

 

E

[litebearer]

perhaps...

//Get Invoice Rows
  $sql = 'SELECT * FROM timecard WHERE INVOICE_ID=\'1000\'';
  $result = mysql_query($sql);
  while($row = mysql_fetch_array($result)) {
    /* echo out whatever you want */
  }

[The Letter E]

That is still returning row 1 multiple times. I'm thinking this is going to need some outside the box thinking. I write scripts like this all the time, and this does not happen. I'm afraid that there may be a problem with my DB or PHP.INI...?? Is there anything, besides that at a procedural level, that could be causing this problem??

[The Letter E]

Here's a sample of the table:

 

ID INVOICE_ID WEEKDAY MONTH_DAY    MONTH YEAR START_TIME END_TIME HOURS_WORKED DPH SUB_TOTAL

1 1000 Mon         1                 Jan         2011 500p         700a         14                         12 168

2 1000 Tue         2                 Jan         2011 400p         700p         3                         12 36

3 1000 Wed 3                 Jan         2011 400p         700p         3                         12 36

 

I tried this code as well:

  $sql = 'SELECT * FROM timecard WHERE INVOICE_ID=\'1000\'';
  $result = mysql_query($sql);
  $rows = mysql_fetch_array($result);
  $num = mysql_num_rows($result);
  
  foreach($rows as $row){
    echo '<pre>';
    print_r($row);
    echo '</pre>';
    echo '<br>';
  }

 

That didn't work either...

 

I'm getting some weird arrays returned.

 

Here's what I got:

1

1

1000

1000

Mon

Mon

1

1

Jan

Jan

2011

2011

500p

500p

700a

700a

14

14

12

12

168

168

 

All of those values are from row one and they are being returned two at a time for each column. I have never seen this happen before.

 

Any additional ideas/help is appreciated.

 

Thank You all,

 

E

[litebearer]

Try this...

<?php
//Get Invoice Rows
$what_invoice_id = 1000;
$sql = "SELECT * FROM timecard WHERE INVOICE_ID='$what_invoice_id''';
$result = mysql_query($sql);

//Build Current Invoice
$i=0;
$invoice = '<table class="invoice" cellspacing="0" cellpadding="0">';                   
$invoice .= '<tr class="heading"><td>#</td><td>Invoice</td><td>Date</td><td>Time In</td><td>Time Out</td><td>Hours</td><td>$/Hr</td><td>Sub Total</td></tr>';
while($row=mysql_fecth_array($result)) {
    if( $i%2 ){
      $eo = 'odd';
    }else{
      $eo = 'even';
    }
    $invoice .= '<tr id="invoiceRow" class="'.$eo.'"><td>'.$rows[0].'</td><td>PHG'.$rows[1].'</td><td>'.$rows[2].', '.$rows[4].' '.$rows[3].', '.$rows[5].'</td><td>'.$rows[6].'</td><td>'.$rows[7].'</td><td>'.$rows[8].'</td><td>'.$rows[9].'</td><td>'.$rows[10].'</td></tr>';
    $runningTotal[$i] = $rows[10];
    $i++;
}
  //Get Total
  $total = array_sum($runningTotal);
  $invoice .= '<tr><td colspan="7" style="background-color: #000000; color: #ffffff; font-weight: bold; padding-left: 5px;">Total</td><td align="right" style="background-color: #333333; font-weight: bold; color: #FFFFFF; padding-right: 5px;">'.$total.'</td>';
  $invoice .= '</table>';
  
  echo $invoice;

?>

[litebearer]

BTW, when you do this...

$rows = mysql_fetch_array($result);

you are only getting ONE row back. the WHILE causes you to loop thru multiple results

[The Letter E]

that did the trick!

 

Array

(

    [0] => 1

    [iD] => 1

    [1] => 1000

    [iNVOICE_ID] => 1000

    [2] => Mon

    [WEEKDAY] => Mon

    [3] => 1

    [MONTH_DAY] => 1

    [4] => Jan

    [MONTH] => Jan

    [5] => 2011

    [YEAR] => 2011

    [6] => 500p

    [sTART_TIME] => 500p

    [7] => 700a

    [END_TIME] => 700a

    [8] => 14

    [HOURS_WORKED] => 14

    [9] => 12

    [DPH] => 12

    [10] => 168

    [sUB_TOTAL] => 168

)

 

Array

(

    [0] => 2

    [iD] => 2

    [1] => 1000

    [iNVOICE_ID] => 1000

    [2] => Tue

    [WEEKDAY] => Tue

    [3] => 2

    [MONTH_DAY] => 2

    [4] => Jan

    [MONTH] => Jan

    [5] => 2011

    [YEAR] => 2011

    [6] => 400p

    [sTART_TIME] => 400p

    [7] => 700p

    [END_TIME] => 700p

    [8] => 3

    [HOURS_WORKED] => 3

    [9] => 12

    [DPH] => 12

    [10] => 36

    [sUB_TOTAL] => 36

)

 

Array

(

    [0] => 3

    [iD] => 3

    [1] => 1000

    [iNVOICE_ID] => 1000

    [2] => Wed

    [WEEKDAY] => Wed

    [3] => 3

    [MONTH_DAY] => 3

    [4] => Jan

    [MONTH] => Jan

    [5] => 2011

    [YEAR] => 2011

    [6] => 400p

    [sTART_TIME] => 400p

    [7] => 700p

    [END_TIME] => 700p

    [8] => 3

    [HOURS_WORKED] => 3

    [9] => 12

    [DPH] => 12

    [10] => 36

    [sUB_TOTAL] => 36

)

 

 

Thank you litebearer!!

[The Letter E]

BTW, when you do this...

$rows = mysql_fetch_array($result);

you are only getting ONE row back. the WHILE causes you to loop thru multiple results

 

Ahhhh... I thought it returned an array that contained nested arrays of each row. This helps a lot! Thank you again, I hate being stuck on little problems like this. Definitely not worth the sleep I lost. haha

 

I'm glad there are great people like you that don't mind sharing your time and knowledge with the community!

 

Happy Holidays

 

E