What each value is holding:
$newarray = array (“$(INT)”, “$(URL/STRING)”, “$(TXT/STRING)”, “$(FLOAT)”, “$(CURRENCY)”);
to be sorted by $solditemcount from the highest to the lowest amount. I currently have a while loop that inserts 90 records from deferent tables and generate this particular report. Now I need to display this on screen.
So you can see that I have an array of arrays in $test, which I pass in to usort(). In turn that calls my comparison function called myDateComp(), which does the actual test. In this case I believe $a and $b are the inner arrays that are to be compared, so in this case I compare the "date" element from each, and return either +! or -1 to influence the sort order. Either change the < or the order of the return values to sort in the opposite direction.
Obviously you’d need to modify that code to use a numeric array, so compare by element number instead of by index name.
The thing is that my array doesn’t have key elements. I address this array using array[0], etc… I did reading on usort() and thats when I decided to come here because I really ran into a mental wall.
Hello There! Here is where I’m struglin. I see the function “Doing something” But still unsure whats going on. It still display the data in the same order. When I do this:
I see the data displayed in the table… but it is unorganized. The is under and everything seems to be shifted.
Declaring the function inside the while loop gives me an error. I guess it has to be moved outside the while loop, so I place it at the top before the while is generated.
First question is, where do the different values of $productId and the rest come from? You don’t seem to be retrieving them within the while() loop. Imagining for a second that they are coming in as part of the while() clause, the problem is that, inside the loop, the $test array only has one set of values at a time, and you display them one at a time. What you need to do is build a 2-d array first, sort it, then loop through to display it.
$test = array();
while loop to retrieve data {
$test[] = array ($productId, $productImage, $productName, $solditemcount, $formattedNum);
}
usort($test, "compArray");
foreach ($test as $newtest) {
// display using $newtest as the array name
}
If you’re retrieving the data from a query result set, can’t you sort it in the query?
Thank you for your response. It is several queries being performed from different tables that end in this array.
<?
// There is a nested while loop that retrieve several pieces of the puzzle.
While { // get the Id's needed to make the second search
while { // this is the while loop that storages each instance in the array
$test[] = array ($productId, $productImage, $productName, $solditemcount, $formattedNum);
And here I pretend to sort and echo my result.
}
}
?>
I think I can do the sorting after the while is complete but don’t know how to generate the for loop just yet. I have done it in the past where I use for loop for each instance in the array, but I have no index (don’t seem to be able to grasp that just yet)
I usually stay away from these type of complexity. Arrays seems like alien to me at times. If it was straight from the quesry I would have no issues, but in this occasion I have to use 2 separate tables so I have to glue them togeter through an array so that I can then render the table with the data.
I will go ahead and work as you mentioned in your previous message using the foreach.
Arrays can be a fiddle, especially when you get into arrays within arrays. But if you want to sort it, you’ve no option really but to build an array and sort it once it’s got all the data, or use a temporary table if there will be a lot of information.
Hello! Thank you for helping me out with this issue. The solution was as mentioned before by droopsnoot.
<? PHP
// function and array for the sorting
function compArray($a, $b) {
return ($a[3] < $b[3] ? 1 : -1);
}
$SalesArray = array();
// SQL/CONN/ NESTED WHILE LOOPS {
...
} // WHILE LOOP ENDS
$conn->close();
// Sorting the array using the function
// created at the begining of the script.
usort($SalesArray, "compArray");
foreach ($SalesArray as $newtest) {
// display using $newtest as the array name
echo "
<tr>
<td>$newtest[0]</td>
<td height=\"90px\"><img src=\"../shop/$newtest[1]\" width=\"80px\" height=\"auto\"></td>
<td>$newtest[2]</td>
<td align=\"right\">$newtest[3]</td>
<td align=\"right\">$$newtest[4]</td>
</tr>
";
}
?>
And that gives me the table with the instances ordered from High to low using $solditemcount variable.
Thank you very much and happy holidays to the both of you!
Glad you got it sorted and thanks for confirming it - all too often people look for help and then never return to say whether it was any use.
It would still be interesting to see the queries, in case they can be combined into a single one. Let MySQL do the work for you, if it can. And if it can’t, it often means the database layout could do with some modification, presuming you have the control over it of course.
I believe that if anyone gives you a piece of their time, the least anyone can do is to return the favor and most important is to let those who where helping know the status of your situation. It is only fair, plain and simple, basic communication skills. But not everybody thinks that way and I’m very well aware of that. “but I do what I can to make any interaction better!”
Explaining the table names:
products : This is a table that contains all the products with price, name, description, img url, etc…
order_items : This is a table that only handle processed order and only storages product ID, quantity, etc…
The queries go something like this:
<?
// function and array for the sorting
function compArray($a, $b) {
return ($a[3] < $b[3] ? 1 : -1);
}
$SalesArray = array();
// The first query:
$sql = "SELECT * FROM `products` ORDER BY `id` ASC";
// Then the first WHILE statement fetch_assoc()
while($row = $result->fetch_assoc()) {
$productId = $row["id"];
... // other variables
// Then while inside this loop I need to tap into another table
$sql2 = "SELECT * FROM `order_items` WHERE `product_id` = $productId";
// Then the second WHILE statement fetch_assoc()
while($row2 = $result2->fetch_assoc()) {
$productId = $row2["id"];
... // other variables
} // close while 2
// At this point Insert Into the array
$SalesArray[] = array ($productId, $productImage, $productName, $solditemcount, $formattedNum);
} // close while 1
// Close conection
$conn->close();
// Sort Array
usort($SalesArray, "compArray");
// Print Sorted Array
foreach ($SalesArray as $newtest) {
...
}
?>
As you see, and since I’m not an SQL guru, this was my approach to the given task. Taking in consideration that this will only be seen by a few members and will not be a public “report”, I believe the approach in comparison to my limited knowledge in the subject served its purpose.
OK, so the reason for the two queries is to get the product details (name, image) from the products table, and the order details from the orders table? If that’s the case:
select * from `order_items` inner join `products` on products.id = order_items.product_id
That should return all rows from the order items table, and incorporate the columns from the products table based on the relationship between the product_id column in the order items, and the id column in the products table.
If either of the tables contains the sold item count, then you can just add an ORDER BY clause on the end.
Now, it might be that you only get the ‘sold item count’ by looping through the order_items table for each product and counting up. That makes the query a little more complex, but not impossible. I am also no SQL guru by any means, so I won’t attempt to go there, but I think something like the GROUP BY clause will probably help.
Oh! That looks very interesting. I will go ahead and try to get the query to work that way. Yes, I do require the count to go up inside the loop since the item count can vary per order.
Meaning something like this:
Item 1 sold 3
Item 2 sold 1
Item 3 sold 9
etc…
So as the loop goes, it adds
$i ++;
Then assigns it to the final value
$count = $count + $i
I will see if I can make it work the way you have shown me.
You may want to stop using select * and start explicit naming the columns as select name, price, amount, .... That will help your helpers here to understand which data you want to receive - and you will know what kind of data you really use in the rest of the script. Also you can use tools like SQLFiddle to provide demo data we can rely on and provide further assistance on actual statements.
Another way around is to have SQL calculate the totals for you. For example
select id, productname, productimage,
(select sum(salesqty) from order_items
where order_items.product_id = products.id)
as solditemcount
from products
order by solditemcount desc
This should give a row for each product containing the id, name, image, and a calculated total sales figure presuming the column name is salesqty in that table, naming it solditemcount.
That might give the results the same way around that you had them before and you can just run through them with fetch().
Before I post a more detailed message. So far this is returning the data as needed. very good. Only one thing is missing.
$sql = "
SELECT id, product, image,
(SELECT SUM(nitem) FROM order_items WHERE order_items.product_id = products.id) AS nitem,
FROM products
ORDER BY nitem DESC
";
In the same table as the column ‘nitem’ I have another column named ‘total’ is it possible to get the SUM() of that at the sametime?
$sql = "
(SELECT SUM(nitem) & SUM(total) FROM order_items WHERE order_items.product_id = products.id) AS nitem,
So that at the end I can print the total gross of sale?