PHP/MSQL help needed...

[fubarur]

Ok, I'm new and trying to learn all this...

 

I have a page that is querying for a certain table and whats inside to display an article on a page. (it works fine)

Now I need to query another table to find out where the placement type for this article is. (this is my problem)

 

The new table is:

article_placement

fields inside are

article_id

placement_id

 

The code I have is:

 

^<?php

^ob_start();

 

^{require_once('Ambiance/Environment/co.fubsa.php');}

^{Environment::InitializeVDDB(Environment::dsn());}

 

^{Environment::LoadForms(array('ShoppingCart'));}

 

^{require_once('HTTP.php');}

 

^{// Grab article id}

^{$id = $_REQUEST['article-id'];}

 

^{// Validate article id}

^{if(!Ambiance_HTML_Form_Validation::test($id, '0123456789', true, 10)):}

        ^{HTTP::redirect(Environment::webroot() . '/index.php', true);}

^endif;

 

^{// prepare product sql statement}

^{$sql = 'SELECT id, type_id, title FROM article ' .}

                ^{'LEFT JOIN article_access_level ON article.id = article_access_level.article_id ' .}

                ^{'WHERE article.id = ' . $id;}

 

^{if($_REQUEST['command'] != 'preview'):}

        ^{$sql .= ' AND article_access_level.access_level_id = 1';}

^endif;

 

^{// Select product info}

^{$article = $db->getRowEx($sql);}

 

^{if(!is_array($article)):}

        ^{HTTP::redirect(Environment::webroot() . '/index.php', true);}

^endif;

 

^{require_once('Ambiance/Modules/VDFileAccess.php');}

 

^{// Document path}

^{$article_path = '/var/www/html/articles/' . $id . '/';}

^?>

 

Any ideas?

[fubarur]

To make it a bit more clear this might help?

 

$sql = 'SELECT id, type_id, title FROM article ' .
                'LEFT JOIN article_access_level ON article.id = article_access_level.article_id ' .
                'WHERE article.id = ' . $id;

 

This finds the article # and displays the article info on the page

 

I now want to find what type of article it is

 

Table: placement_type

 

Within the fields

Article_id This is the Article ID from above query

placement_id  This tells what type of article that Article ID is

 

So I want to match the Article ID with the type, does that make sense?

 

I'm starting to confuse even myself 

 

Thanks for any help....

[fubarur]

I have yet to figure this one out....  ???

 

Please someone save me from destruction 

[monk.e.boy]

 

You need to look up whether you want a LEFT, or RIGHT join.

 

I would guess you want:

 

SELECT id, type_id, title, placement_type.title as new_title
FROM article 
JOIN article_access_level ON article.id = article_access_level.article_id
JOIN placement_type ON placement_type.id = article.type_id
WHERE article.id = blah_blah_asdas

 

Take note of the JOINs and the "placement_type.title as new_title"

 

Does this help?

 

monk.e.boy

 

[fubarur]

I will try it out, will this replace my current "$sql ="

[monk.e.boy]

no, just read it aloud and your computer will figure it out  ;D ;D

 

monk.e.boy

[fubarur]

Nope, I can't get it to work  :'(