Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

mysql_query ORDER BY most existing rows

Posted on 2006-06-22
4
Medium Priority
?
216 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 8

Accepted Solution

by:
Autogard earned 600 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 600 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

670 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