Some logic help please...

[woodsonoversoul]

I have a pretty simple "read database and display results" type script, except the script doesn't associate the data properly. I'm assuming it's a logic problem ( I'm probably missing a priming read somewhere), and I was hoping you guys could take a look at it. All help is GREATLY appreciated.

 

The Data:

sale_id	 category   location           price    time	
42 	    toy 	   Best Buy         20.00   2008-01-02 11:57:27
40 	    test 	   somewhere      2.30    2007-09-23 20:13:09
39 	    test 	   all over 	       207.56 2007-09-23 19:54:35
41 	    test 	   Yo mamma\'s 	207.56 	2007-09-26 11:39:32

 

The Script:

<?php

// query to get records ordered by time
    $query = "SELECT * FROM purchase ORDER BY time DESC";
    

// execute query
if ($mysqli->query($query)) {
    // print number of affected rows
    $resultRows = $mysqli->affected_rows;
    }
else {
    // print error message
    echo "Sorry, we're temporaraly having database issues concerning: $query. ".$mysqli->error;
    }
    
    // execute query
if ($result = $mysqli->query($query)) {
    // see if any rows were returned
    if ($result->num_rows > 0) {
        // yes
        // print them one after another
	// while running through the output add prices
        $tempTot = 0;
        echo $row[0];

        echo "<table cellpadding=10 border=1>";
        
        // Create a variable containing the current date and display
        $nowTime = time();
        echo date("D, M dS", $nowTime);
        
        echo "

            <table id='displaytable' summary='A cronological listing of user/'s spending.'>
                <tr>
                <th scope='column'>Time</th>
                <th scope='column'>Category</th>
                <th scope='column'>Location</th>
                <th scope='column'>Amount</th>
                </tr>
                </table>"
                ;
        

?>
<?php
  
                
?>                


        <?php  //begin while statement
        
        /* To set alternating colors for the rows
           set a new variable $rowNum to zero
           and evaluate to see if it's even or odd.
           Update at end of While loop. */
           
        // Initiate $rowNum
        $rowNum = 0;
        
        while($row = $result->fetch_array()) {      
        

        
        //Print message if there are no transactions on this day
        if ($tranNumPerDay == 0){
          // Get rid of this line break and configure this w/ css (1-12)
          echo "No Transactions Today";
          echo "<br>";
          };
          
        
        // Check to see if this transaction was entered today. If not display transaction date
        if (date("m/d/y",(strtotime($row[5]))) !== date("m/d/y",$nowTime)) {
                echo date("D, M dS", (strtotime($row[5])));
                echo "<br>";
                $nowTime = (strtotime($row[5]));
                
                $tranNumPerDay = 0;
                
                echo "

            <table id='displaytable' summary='A cronological listing of user/'s spending.'>
                <tr>
                <th scope='column'>Time</th>
                <th scope='column'>Category</th>
                <th scope='column'>Location</th>
                <th scope='column'>Amount</th>
                </tr>"
                ;
                }
        
        // Evaluate $rowNum to see if this row is even or odd
        if($rowNum%2 == 0){
          echo "<tr class='odd'>";
          }
          else
            echo "<tr>";
        
        
        // print them one after another
        
        echo "
        <td><a href=".$_SERVER['PHP_SELF']."?time=".$row[5].">".date("g:i a", (strtotime($row[5])))."</td>
        <td><a href=".$_SERVER['PHP_SELF']."?category=".$row[2].">".$row[2]."</td>
    	<td><a href=".$_SERVER['PHP_SELF']."?location=".$row[3].">".$row[3]."</td>
      <td><a href=#>$".$row[4]."</td>
      <td class='extras'>more</td>
      <td class='extras'><a href='deleteRow.com'>x</a></td>
      </tr>";
      
    
      
      // Update $rowNum
      $rowNum++;
      $tranNumPerDay++;
    	
    	}
    	echo "</table>";
    	}
    	}

    	?>
    	

<?php
// free result set memory
    $result->close();
    
    // close connection
$mysqli->close();

?>

 

and the output is:

 

 

You can see how the dates are lumped together at the top and how the dates don't correspond the the actual data. Why?

[woodsonoversoul]

I hate to bump my own stuff, but I don't know where else to go for help

[craygo]

your output is not there???

[ohdang888]

i'm not a php expert, but i think i my know what you're trying to do:

 

if you are only pulling out 1 item at a time, try this:

 

$result = mysql_query("SELECT * FROM purchase ORDER BY time DESC") or die(mysql_error());
$data = mysql_fetch_array($result);

$sale_id = $data['sale_id'];

$sale_id = $data['THIS PART IS WHERE THE COLUMN NAME IS];

 

 

 

 

OR!!!!! are you having a problem matching up the correct item with the correct price and time, etc. ?

 

if so: you can do this:

$result = mysql_query("SELECT * FROM purchase ORDER BY time DESC") or die(mysql_error());
while($data = mysql_fetch_assoc($result)){       
  
  echo $data['sale_id'];
   echo $data['price'];

}

and keep all the echo-ing of the data inside the  {  }

[woodsonoversoul]

Thanks ohdang and craygo. I don't know why the output (a snapshot of the page isn't showing, it did when I previewed the post). I'll try ohdangs suggestions later tonight.

[woodsonoversoul]

Hey sorry, here's my output. See how the dates don't match up with the data table above?

 

<a href="http://picasaweb.google.com/woodson.dan/BackPack/photo#5173929834464207282"><img src="http://lh3.google.com/woodson.dan/R819xZQI4bI/AAAAAAAAAII/e-kesJ6MLSg/s800/Screenshot-Spindex.com%20%7C%20Main%20Display%20-%20Mozilla%20Firefox.png" /></a>

[sqlnoob]

ah yes I see

 

the date is one row too high

[sqlnoob]

I think it's because the rowumber for the date is wrong

 

or it could be that you echo it too early

 

anyway try to numrow it -1 instead of 0, maybe that helps.

 

I'm a noob, so don't count your hopes up on my help. Afterall I could be wrong.