PHP MySQL ORDER BY help needed

[bhutastuta]

Tweet

Hi

I have a few tables set up in mysql. One for mobile (makes, name, model popularity etc) and another for the offers (tariff details, network etc.) and a few others not so relevant for this.

I want to display results on my homepage, which is first ordered by model popularity (which is found in the mobiles table) and then by deal popularity (which is found in offers table)

so far this is what I have but it only orders by model popularity and not deal popularity. I thought my method would work :blush but it don't!

The code:

$pmcontent = mysql_query("select SQL_NO_CACHE * from mobiles ORDER BY Model_Popularity DESC LIMIT 4")or die(mysql_error());
    
    while($pm=mysql_fetch_array($pmcontent)){
    
    $pminfo = mysql_query("select SQL_NO_CACHE * from offers WHERE Model_ID = '".$pm['Model_ID']."' ORDER BY Deal_Popularity2 DESC");

$pminf = mysql_fetch_assoc($pminfo);
    $pmbrand = $pm['Model_Make'];
    $pmmodel = $pm['Model_Name'];
    $pmmins = $pminf['Tariff_FreeMins'];
    $pmminstype = $pminf['Tariff_FreeMinsType'];
    $pmtexts = $pminf['Tariff_FreeTxts'];
    $pmfreegift = $pminf['Offer_FreeGift'];
    $pmnetwork = $pminf['Network_Name'];
    $pmtarifflength = $pminf['Tariff_ContractLength'];
    $pmtariff = $pminf['Tariff_Name'];
    $pmprice = $pminf['Offer_MonthlyCost'];
    $pmoffer = $pminf['Offer_OfferText'];
    $pmofferlink = $pminf['Offer_Link'];

    mysql_free_result($pminfo);

//and then some other irrelevant coding

    }
//echo bla

mysql_free_result($maincontent); 

Asim

[bhutastuta]

Anyone? I'm sure this is possible, I just can't figure it out...

[jamie07051975]

Not really understanding what you are trying to do but you may want to user an inner join to link the two tables and then order by one field and then another. MySQL cache is a good thing by the way and I would not try to disable it.

Post the columns of the tables and just explain what you are trying to do and it shoul dbe be pretty easy.

[bhutastuta]

Structure is as follows-:

2 tables-:

Offers
Mobiles

The columns within Offers that I am using-:
Model_ID
Tariff_FreeMins
Tarif_FreeTxts
Deal_Popularity

and so on but to point out the one that I am using to link the two tables is Model_ID

The columns within Mobiles that I am using-:
Model_ID
Model_Make
Model_Name
Model_Popularity

Right,

What I want to do is to display 4 deals (or any number) which is ordered by model popularity first and then select the most popular deal (using Deal_Popularity) data from the offers table which match up with the model.

As a result I should have 4 different phones in order of the model popularity alongside the actual offers that are the most popular with that phone.

E.G Nokia 5800 is the most popular mobile phone, so this should be my first phone displayed. The most popular nokia 5800 deal is say on t-mobile with 700 mins and unlmited texts, so the first to be displayed should be the nokia 5800 with the above deal.

The reason I am not joining the table is because if I order it as suggested then the result will be 4 of the same mobile phones but with the deal details showing in order of popularity.

I am looking for the most popular phone with its associated most popular deal. then the 2nd most popular phone with its associated most popular deal and so on...

I hope this makes it a little bit more clear.

I think the sql_no_cache has worked better for the site rather than enabling the cache.