Solved

mysql_query ORDER BY most existing rows

Posted on 2006-06-22
4
207 Views
Last Modified: 2013-12-12
I am administrating a little webshop built in php/mysql where users can choose from a list of items and send a request about the item they like.
Everything works fine.  But now I want to have this content list sorted after the number of the most requested items (all requests are stored in table2)

details:
I have the two existing sql tables ("content" and "request_log")

table1 "content"
==========
this table contains a long list of content offered for requests from the net. It contains the following columns: "unique_id", "name", "description", ....

table1 looks like: for example:
"767","porsche911","red, good condition fast"
"893","mercedes","silver 5 years old"
"613","lexus","gold 5 doors"



table2 "request_log"
=============
the second table is used as a request table. The rows are updated dynamically after any request of the content of table1.
It contains the following columns "unique_id", "date", "requestemail", ... (unique_id of table2 corresponds with unique_id of table1).

table2 looks like: for example:
"893","23.06.2006","wanttobuymercedes@yahoo.com"
"893","21.06.2006","requestformercedes@yahoo.com"
"893","18.12.2005","mercedesrequest@hotmail.com"
"767","23.07.2005","like_porsche@gmx.com"


Normaly table1 is shown and sorted by random.
with this cmd: $sql = mysql_query(" SELECT * FROM $tabname ORDER BY rand() ");
Now I want to sort table1 after number of requests. The result should be a sorted list of the most requested content.

output should look like this:
1.) The car mercedes with ID 893 was requested 3 times
2.) The car porsche911 with ID 767 was requested 1 time
3.) The car lexus with ID 613 was requested 0 time

I am a php newbie. php.net and EE could not help me so far. And please explain your code. ;-)

Hope someone can help me. Many thanks in advance

Mathias



0
Comment
Question by:mfuerlinger
4 Comments
 
LVL 8

Accepted Solution

by:
Autogard earned 150 total points
ID: 16965227
I would suggest a table redesign.  What I would do is add a column to table 1 called "number_of_requests" and then every time that item is requested I would increment the value in table1.  This kind of design would violate normalization in the database (meaning that we are putting information in a field that could be received from another portion of the database), but in this case I would trade that for simplicity and speed.

----------------code-----------------
// Get our information from the database sorted in reverse order (largest number of requests to smallest)
$result = mysql_query("SELECT * FROM table1 ORDER BY number_of_requests DESC");
// The current print out index
$myIndex = 1;
// Loop through each row we got from the database
while($row = mysql_fetch_assoc($result))
{
    // If there was just one request we just want to print out "time"
    if($row["number_of_requests"] == 1)
    {
        $printTimes = "time";
    }
    // Otherwise we print out "times"
    else
    {
        $printTimes = "times";
    }
    // Now print out the car with the most requests
    print "$myIndex.) The car " . $row["name"] . " with ID " . $row["unique_id"] . " was requested " . $row["number_of_requests"] . " $printTimes.<br>";
    // Increment index
    $myIndex += 1;
}
--------------------------------------

With the way your table2 is designed right now you would have to loop through every item in table1 and then find out how many times that has been requested by checking table2.  That is a lot of work and would be very slow.

Somebody else may know a better way of doing it while keeping the same design...  There is probably a way to do it by doing a more complicated query, but I can't think of a way right now....

--------code----------
$printArray = array();
$result = mysql_query("SELECT * FROM table1");
while($row = mysql_fetch_assoc($result))
{
    $result2 = mysql_query("SELECT COUNT(*) as number_of_requests FROM table2 WHERE unique_id = " . $row["unique_id"] . "");
    if($row["number_of_requests"] == 1)
    {
        $printTimes = "time";
    }
    else
    {
        $printTimes = "times";
    }
    // I am attempting to add an element to the printArray array with the key being the number of times it was requested and the value being the string we want to print out -- you may have to check syntax here
    $printArray = array_merge($printArray, array($row["number_of_requests"] => " The car " . $row["name"] . " with ID " . $row["unique_id"] . " was requested " . $row["number_of_requests"] . " $printTimes."));
}
// Now sort the array by key in descending order
krsort($printArray);
// Now print out
$myIndex = 1;
foreach($printArray as $item)
{
    print "$myIndex.)$item<br>";
    $myIndex += 1;
}
-------------------------------
0
 
LVL 16

Assisted Solution

by:dr_dedo
dr_dedo earned 150 total points
ID: 16965457
try this SQL, it should list all cars you got with the number each wasrequested
SELECT c.name, count(c.name) as x FROM content c , request_log r where c.unique_id = r.unique_id group by c.name order by x desc;

please note that if you plan to use such a query frequently, then it is adviced to use Autogard advice reagarding storing the number of requests into content table. cometimes, over normalization of tables reduce its performance. if you run that query infrequently, then keep it this way
0

Featured Post

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question