[Resolved] Using foreach &arrays w/ query on multiple ta

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
blepoxp
Forum Newbie
Posts: 16
Joined: Tue Feb 13, 2007 12:13 pm

[Resolved] Using foreach &arrays w/ query on multiple ta

Post by blepoxp »

I am having a hard time getting php to give me what I want from the database.
It appears that my mssql query is working fine, but that I may be having problems with the array in relation to calling a specific column from a specific table.

Here's the code followed by more explanation:

Code: Select all

<?php (Database connections ommited)
mssql_select_db($database, $dbconnect);
$query = "SELECT TOP 10 Res.ResourceID, Res.Title, Res.ShortBlurb, Res.Created, Cat.Title, Doc.Title, Doc.Location ";
$query .= "FROM DA_Resource Res, DA_Category Cat, DA_Document Doc ";
$query .= "WHERE Res.ResourceID = Doc.ResourceID ";
$query .= "AND Cat.CategoryID = Res.CategoryID ";
$result = mssql_query( $query, $dbconnect );

while ($line = mssql_fetch_assoc($result))
        {
            $return[] = $line;
        }

$output = "<html><body><ul>";

foreach ($return as $line) 
{ $output .= "<li>$line[Title]</li>
			   <li>$line[Location]</li>";}

$output .="</ul></body></ul>";
echo $output;

?>
This returns the following list a bulleted list with 10 titles from the table, "DA_Document". My problem is, when I start trying to use an alias within the foreach, I get errors. eg:

Code: Select all

<li>$line[Res.Title]</li>
Does this make sense? Would somebody be able to help me?
Thanks.
Last edited by blepoxp on Tue Feb 13, 2007 1:59 pm, edited 1 time in total.
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post by nathanr »

Code: Select all

<?php (Database connections ommited)
mssql_select_db($database, $dbconnect);
$query = "SELECT TOP 10 Res.ResourceID, Res.Title, Res.ShortBlurb, Res.Created, Cat.Title AS cat_title, Doc.Title AS doc_title, Doc.Location ";
$query .= "FROM DA_Resource Res, DA_Category Cat, DA_Document Doc ";
$query .= "WHERE Res.ResourceID = Doc.ResourceID ";
$query .= "AND Cat.CategoryID = Res.CategoryID ";
$result = mssql_query( $query, $dbconnect );

while ($line = mssql_fetch_assoc($result))
        {
            $return[] = $line;
        }

$output = '<html><body><ul>';

foreach ($return as $line)
{ $output .= '<li>'.$line['doc_title'].'</li>
                           <li>'.$line['Location'].'</li>';}

$output .='</ul></body></ul>';
echo $output;

?>
Try using the SELECT blah AS some_blah method, I've changed your code above to use this method
blepoxp
Forum Newbie
Posts: 16
Joined: Tue Feb 13, 2007 12:13 pm

Resolved. Thanks.

Post by blepoxp »

Worked like a charm.
Muchos gracias!
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post by nathanr »

no problem, and thank god as I never test what I write in ehre <foolish me>
Post Reply