TalkPHP - Category and order

TalkPHP (http://www.talkphp.com/forums.php)

- Absolute Beginners (http://www.talkphp.com/absolute-beginners/)

- - Category and order (http://www.talkphp.com/absolute-beginners/1667-category-order.html)

Category and order

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 --->

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'm not sure on the question, but would this post by me help you at all?

http://www.talkphp.com/showpost.php?p=5123&postcount=3

Quote:

Originally Posted by Wildhoney (Post 5910)

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

http://www.talkphp.com/showpost.php?p=5123&postcount=3

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

Quote:

Originally Posted by Chaos King (Post 5913)

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

Ill inform you little bit later :)

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.

Quote:

Originally Posted by Chaos King (Post 5886)

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.'