Category and order

Jonnee · 12-08-2007, 09:19 AM

Hello all!
I'm new here, yea this looks like i'm just fishing info and leaving.. Well it doesn't have to be so.
Ok what ever.

My problem is this:

I have an ad website under developement and it has multiple gategories where same ad can be. But the order is always different. So how could this have been done?

I have idea like this in mysql tables:

-- Categories --
id
category (the name like sport, horses, dogs...)
order (helds numbers wich will be the ids of ads.)

-- Ads --
id
name
url
image
width
height
colspan
category

My function will use this -> Mainos('category'); So it will print all ads in order wich is told in category table in order row of the category. And it will start new row allways after colspan total = 3

I hope someone understood what I asked and is able to help me :)

Thank you.

Regards, Jonne

Chaos King · 12-08-2007, 01:16 PM

I honestly did not understand one thing that you said...

[edit]
Ahh, nevermind. I see what you mean now.

Well I am not exactly sure how to do this in one query, but you would just run a query to fetch the category, and then use another query to fetch the ads from that category.

PHP Code:


			
$fetch = mysql_query ( sprintf ( "SELECT order FROM category WHERE category = '%s' LIMIT 1", $categoryName ) );

$row = mysql_fetch_assoc ( $row );
$categoryOrder = $row['order'];

$categoryOrder = implode ( ',', $categoryOrder );

$fetch = mysql_query ( sprintf ( "SELECT * FROM ads WHERE id IN ( %s )", $categoryOrder ) );

while ( $row = mysql_fetch_assoc ( $fetch ) )
{

print_r ( $row );

}

I think that will work, try it and let me know. :)

Wildhoney · 12-08-2007, 02:54 PM

I'm not sure on the question, but would this post by me help you at all?

Category System Tutorial

Chaos King · 12-08-2007, 02:56 PM

Quote:

Originally Posted by Wildhoney

I'm not sure on the question, but would this post by me help you at all?

Category System Tutorial

Wildhoney, here is a "translation".

Category Table:
categoryID
categoryName
categoryAdsOrder

Ads Table:
adID
adCategoryID
adField
adField1

He wants to select all the ads from a specific category and list them in the order defined in categoryAdsOrder

Jonnee · 12-09-2007, 02:11 PM

Quote:

Originally Posted by Chaos King

Wildhoney, here is a "translation".

Category Table:
categoryID
categoryName
categoryAdsOrder

Ads Table:
adID
adCategoryID
adField
adField1

He wants to select all the ads from a specific category and list them in the order defined in categoryAdsOrder

Yes just like that.
I'll try Chaoses way.

Ill inform you little bit later :)

Wildhoney · 12-09-2007, 03:12 PM

You could do this all in one query if your table were normalised. Try reading the post I posted a link to above - and have a lookup table.

Jonnee · 12-23-2007, 09:58 PM

Quote:

Originally Posted by Chaos King

I honestly did not understand one thing that you said...

[edit]
Ahh, nevermind. I see what you mean now.

Well I am not exactly sure how to do this in one query, but you would just run a query to fetch the category, and then use another query to fetch the ads from that category.

PHP Code:


			
$fetch = mysql_query ( sprintf ( "SELECT order FROM category WHERE category = '%s' LIMIT 1", $categoryName ) );



$row = mysql_fetch_assoc ( $row );

$categoryOrder = $row['order'];



$categoryOrder = implode ( ',', $categoryOrder );



$fetch = mysql_query ( sprintf ( "SELECT * FROM ads WHERE id IN ( %s )", $categoryOrder ) );



while ( $row = mysql_fetch_assoc ( $fetch ) )

{



print_r ( $row );



}

I think that will work, try it and let me know. :)

I am sorry for long unactivity on this topic. I have my own reasons for that.

I tested now your way to do it and well firstly it gave me 3 errors, about first mysql_fetc_assoc, well repaired that.
Also it gave error that implode has bad arguments and it also yelled me about the last mysql_fetch_assoc function.

PHP Code:


			
$q = mysql_query ( sprintf ( "SELECT jarjestys FROM kategoria WHERE nimi = '%s' LIMIT 1", $kategoria ) );

    $rivi = mysql_fetch_assoc ($q);

    $jarjestys = $rivi['jarjestys'];

    

    $jarjestys = implode("," , $jarjestys);

    

    $fetch = mysql_query ( sprintf ( "SELECT * FROM mainos WHERE id IN ( %s )", $jarjestys ) );

    

    while ( $row = mysql_fetch_assoc ( $fetch ) )

    {

    $nimi = $row['nimi'];

    $colspan = $row['colspan'];

    $url = $row['url'];

    $height = $row['height'];

    $width = $row['width'];

    $output = '

            <td colspan="'.$colspan.'" id="taulukko">

                <center>

                <a href="'.$url.'" target=_blank>

                    <img src="'.$image.'" width="'.$width.'" height="'.$height.'" border="0">

                </a> 

                </center>

            </td>        

        ';

        if($colspan == 1) {

            $mainos_numero ++;

        } elseif($colspan == 2) { 

            $mainos_numero += 2;

        } elseif($colspan == 3) { 

            $mainos_numero += 3;

        } else {

            exit(' Error in AD'.$name.'. Please input colspan.');    

        }

        

        if($mainos_numero == 4){

            $newrow = true;

            $mainos_numero = 0;

        } else {

            $newrow = false;

        }

        

        if($newrow == true) {

            echo "</tr>\n";

            echo "<tr>";

            print_r ($output);

        } else {

            print_r ($output);

        }

  

     }

I also tried the single quote method, I really didn't get it...

I tried it like this ->

Code:

SELECT    
  ad.name
  ad.url
  ad.height
  ...
FROM    
  category
LEFT JOIN    
  ads_categories
ON    
  ads_categories.category_id = category.id
LEFT JOIN    
  ads
ON    
  ad.id = ads_categories.ad_id
WHERE    
  category.name = '.$category.'