Solved

mysql_query ORDER BY most existing rows

Posted on 2006-06-22
4
204 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
Comment Utility
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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now