array function help needed.

[Ujj]

Hi, this is my second post after getting loads of support and help in my first post.

I have a situation like this below

$k==0;
$sql = "select title,price,date  from item_detail where itemID in ($items) and groupID=$gid";
		$rs = mysql_query($sql) or die(mysql_error());
		while($row = mysql_fetch_row($rs)){	
                                
			$title[$k]= $row[0]);
			$price[$k]= $row[1];
			$date= $row[2];

			$k++;
		}

 

lets say it gives me a output like this

$title[0]=water; $title[1]=milk; $title[2]=coke; ....
$price[0]=1,2,3,4,5;  $price[1]=6,7,8,9,10; $price[2] = 11,12,13,14,15; .....
$date = 1344345095,1344345096,1344345099,1344345087,1344345010; .....

 

how can i print this values in the format as below.

['date',      'water ', 'milk', 'coke', '....'],
['1344345095' , '1',    '6',   '11','.....],
['1344345096' , '2',    '7',   '12', '.....],
['1344345099' , '3',    '8',   '13', '.....],
['1344345087' , '4',    '9',   '14','.....],
['1344345010' , '5',    '10',   '15','....]

 

any help would be greatly appreciated

 

 

 

[Jessica]

Do you want it in an HTML table or just plain text output like that? You should really restructure your arrays but given the basic idea you have now, try this:

 

<?php
echo '<table><tr>';
foreach($title AS $key=>$text){
echo "<th>$text</th>";
}
echo "</tr>";
foreach($date AS $key=>$d){ //This assumes you make $date a proper array.
echo '<tr>';
echo '<td>'.$d.'</td>';
foreach($title AS $tk=>$text){
	echo '<td>'.$price[$tk][$k].'</td>'; //You'll also need to make your price a proper array.
}
echo '</tr>';
}
echo '</table>';
?>

[Christian F.]

Your database structure is making things a bit harder than what they need to be, as what you have here is a classical foreign key relation.

If you separate the date and price to a table of their own, using the item_id as the foreign key, things will become a whole lot easier.

[Ujj]

Do you want it in an HTML table or just plain text output like that? You should really restructure your arrays but given the basic idea you have now, try this:

 

<?php
echo '<table><tr>';
foreach($title AS $key=>$text){
echo "<th>$text</th>";
}
echo "</tr>";
foreach($date AS $key=>$d){ //This assumes you make $date a proper array.
echo '<tr>';
echo '<td>'.$d.'</td>';
foreach($title AS $tk=>$text){
	echo '<td>'.$price[$tk][$k].'</td>'; //You'll also need to make your price a proper array.
}
echo '</tr>';
}
echo '</table>';
?>

 

Hi jesirose,

thank you very much for the reply, i just want a plain text same as in my question, the $date will be same for all itemID, so i didn't use array for it.

[Jessica]

Think about what you wrote originally, and what you just wrote.

 

(Off topic: 5,000th post? Time for a break.)

[Ujj]

Think about what you wrote originally, and what you just wrote.

 

(Off topic: 5,000th post? Time for a break.)

 

what i wrote here?i just need a plain text formatted in the same way as they are in my question.

[Ujj]

Your database structure is making things a bit harder than what they need to be, as what you have here is a classical foreign key relation.

If you separate the date and price to a table of their own, using the item_id as the foreign key, things will become a whole lot easier.

 

thanks ChristianF,

 

even if I separate the date and price to their own table i need to store the price and date in comma separated format as they keeps growing..

[Christian F.]

Ujj: What you want to do, is to first and foremost restructure your database. When that's done, you can extract the data and build an array that looks like this:

$items = array ($date => array ($item => $price, $item => $price),....)

 

That'll make your efforts a whole lot easier!

As for the formatting, either use HTML tables or look at padding with sprintf ().

 

Added:: No, no! You do not want to store a comma-delimited list in the database, that defeats the purpose of the database in the first place. What you want to do, is to add new rows to the "date-price" table, for each time there's a new price update.

You are welcome for the help, but please do take the time to properly read and test out what we're telling you.

[Ujj]

thanks again ChristianF,

 

my scripts stores the price every time it runs and user can set it up the frequency how often they want to update, in that case if i store date and price for each item in new row every time it runs, i can't imagine how long the rows in that table will go .. 

[Christian F.]

You can fit a whole lot more rows in a table, than lines into a varchar (or even text field). Trust me when I say that the size of the table is your least concern.

Not only that, but you'll find that as the dataset grows your method will just give you more and more headaches. That includes performance issues. Plus, storing the data in rows, as you're meant to do, makes it trivial to perform maintenance to clean out old and unneeded data.

[jcbones]

Ujj, in an effort to understand why we are suggesting to restructure your database, please take the time to watch and understand

.  IMHO, databases should extend to at least the 3rd Normal Form.

[Ujj]

thank you very much ChristianF and jcbones  for these suggestions, i will apply them to my database.

[Christian F.]

You're welcome, glad we could help.

[Ujj]

Ujj: What you want to do, is to first and foremost restructure your database. When that's done, you can extract the data and build an array that looks like this:

$items = array ($date => array ($item => $price, $item => $price),....)

 

That'll make your efforts a whole lot easier!

As for the formatting, either use HTML tables or look at padding with sprintf ().

 

hi ChristianF,

 

i changed my database structure as suggested by you and storing those value in new rows every time it runs the query,

 

now my table (item_history) looks like this

 

 

based on this table how can i create an array like you have suggested before, another table (item_detail) has title associated with each itemID. lets say for itemID '110096692315' in first row of  the image, has title 'my first telephone', in table item_detail, and this item(110096692315) three updated price in the above table.

 

my target is to get plain output like this:

 

$data = [date,            title_a, title_b, title_c,.........],
[1345035323,17.89,18.99,17.25,5.23....],
[1345035323,16.85,18.99,17.24,5.20....],......

 

help please.. been trying all day 

 

 

[Christian F.]

Presuming you're fetching all of the necessary data with a JOIN, and ORDERED BY `date` plus any constraints or sorting you'd like:

$data = array ();

// Fetch and loop all results.
while ($row = $res->fetch_array ()) {
    // Check if we have a new date array.
    if (!isset ($data[$row[$date]]) {
        // Define it.
        $data[$row[$date] = array ();
    }

    // Add item & price to current date array.
    $data[$row[$date]][$row[$title]] = $row[$price];
}

That'll give you an array as described in my previous post, to get one similar to the one you described in your last post just stuff the result from $db->fetch_array () into $data directly.

However, to output a table with the item names being the headers, then I recommend using the structure I've posted. That way you can use the keys from the first array result to build the headers, before you start to print out the prices and dates themselves.

[Ujj]

Thnaks ChristianF

 

i tried all sort of foreach loop to print the result array but  no joy..

$sql = "SELECT ih.itemID,title,ih.price,soldQty,ih.date FROM item_detail as id LEFT JOIN item_update AS ih ON id.itemID=ih.itemID  WHERE ih.itemID IN ($items) AND groupID=$gid ORDER BY ih.date";
		$rs = mysql_query($sql) or die(mysql_error());

		$data = array ();

		// Fetch and loop all results.
		while ($row = mysql_fetch_array($rs)) {
			// Check if we have a new date array.
			if (!isset ($data[$row[$date]])) {
				// Define it.
				$data[$row[$date]] = array ();
			}
						// Add item & price to current date array.
			$data[$row[$date]][$row[$title]] = $row[$price];
		}

		foreach ($data as $v1) {
			foreach ($v1 as $v2) {
				echo "First Attempt:\n$v2\n";
			}
		}


		echo "Second Attempt:\n";
		foreach ($data as $k => $v) {
			echo "\$data[$k] => $v.\n";
		}
		$len = sizeof($data);
		for ($row = 0; $row < $len; $row++)
		{
			echo "<b>The row number $row";


			for ($col = 0; $col < 3; $col++)
			{
				echo $data[$row][$col];
			}


		}
			echo "Third Attempt:\n";
				foreach ($data as $mydata) {
					foreach ($mydata as $k=>$t) {
					  echo "$k: $t", "\n";
					}
					echo "\n";
			  }

 

and the out put i get is

 

First Attempt: Second Attempt: $data[] => Array. The row number 0Third Attempt: :

 

help again please..

[jazzman1]

Have you tried dumping the content of $row?

I can not understand if (!isset ($data[$row[$date] , variable inside in variable inside in other one 

Put this code inside the while loop and post the result: 

while ($row = mysql_fetch_array($rs)) {
echo '<pre>'.print_r($row, true).'</pre>'; exit; 
// Check if we have a new date array.

[Ujj]

this is what i got as  output

 

Array
(
    [0] => 110096692315
    [itemID] => 110096692315
    [1] => paper pager paager paper pitty
    [title] => paper pager paager paper pitty
    [2] => 17.89
    [price] => 17.89
    [3] => 0
    [soldQty] => 0
    [4] => 1345120513
    [date] => 1345120513
)

[jazzman1]

Now, how you'd like to be your sorting?

[Ujj]

in the following way please 

$data =
[date,            title_a, title_b, title_c,.........],
[date_value1,  price_of_a, price_of_b, price_of_c.....],
[date_value2,  price_of_a, price_of_b, price_of_c.....],
[date_value3,  price_of_a, price_of_b, price_of_c.....],
...........

 

 

 

 

[jazzman1]

date, title_a, title_b, title_c are names of the tables ?

[Ujj]

No they are title of products from another table associated with each itemID

 

Array
(
    [0] => 110096692315
    [itemID] => 110096692315
    [1] => paper pager paager paper pitty
    [title] => paper pager paager paper pitty
    [2] => 17.89
    [price] => 17.89
    [3] => 0
    [soldQty] => 0
    [4] => 1345120513
    [date] => 1345120513
)

 

 

[Christian F.]

Feeling nice, so I've finished the code for you.

 

Notice that I've simplified the patten a bit, and used two different methods of making an array of unknown length into table cells. I recommend using the latter method, as it's cleaner and slightly more efficient.

 

// Get data from database.

$data = array ();
$titles = array ();

// First structure the data for easy printing.
while ($row = $db->fetch_array ()) {
if (!isset ($data[$row['date']])) {
	$data[$row['date']] = array ();
}

// Save the data
$data[$row['date']][] = $row["price"];

// Store the titles in a separate array, for easier printing.
$titles[] = $row['title'];
}

// Start the table, and print out the headers.
$output = "<table>\n\t<tr>\n\t\t<th>Date</th>\n";
foreach ($titles as $title) {
$output .= "\t\t<th>$title</th>\n";
} 
$output .= "\t</tr>\n";

// Loop through the data, and add to output.
foreach ($data as $date => $prices) {
$output .= "\<tr>\n\t\t<td>$date</td>\n\t\t<td>";

// Create HTML table cells out of prices.
$output .= implode ("</td>\n\t\t<td>", $prices);

// Close last cell and row.
$output .= "</td>\n\t</tr>";
}

// Close table.
$output .= "</table>\n";

[jazzman1]

No they are title of products from another table associated with each itemID

 

Array
(
    [0] => 110096692315
    [itemID] => 110096692315
    [1] => paper pager paager paper pitty
    [title] => paper pager paager paper pitty
    [2] => 17.89
    [price] => 17.89
    [3] => 0
    [soldQty] => 0
    [4] => 1345120513
    [date] => 1345120513
)

 

And..... where they are in this particular output ?

 

[jcbones]

Ujj, if I could offer another suggestion.

 

In the future, store you dates as a DATETIME or TIMESTAMP column type.  This is because MySQL has VAST amounts of functions just for dates, and times.  Which would make reports a breeze to produce.