Left Join and result formating

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
wescrock
Forum Commoner
Posts: 31
Joined: Wed Sep 10, 2008 10:31 am
Location: Fresno, CA

Left Join and result formating

Post by wescrock »

Hello again,

I have a 2 table set up to handle authors for citations...
Author:
author_id
first_name
middle_name
last_name

Author_link:
link_id
citation_id
author_id

What needs to happen is, using a citation ID, i need to return the names of the authors in a specific format depending on how many authors are present for that particular citation.

for 1 author: last_name, first name middle_name
for 2 authors: last_name, first_name middle_name 'and' first_name(2) middle_name(2) last_name(2)
for 3 or more: last_name, first_name middle_name, first middle last ... 'and' first(n) middle(n) last(n)

my code is:

Code: Select all

                $result_author=@mysql_query("SELECT al.id_author_, a.first_name, a.middle_name, a.last_name 
                    FROM wfbiblio.author_link al
                    LEFT JOIN wfbiblio.author a on al.id_author_= a.id_author
                    WHERE id_citation_='$id_citation_'");
                        while ($row = mysql_fetch_assoc($result_author)){
                            $first_name=$row['first_name'];
                            $middle_name=$row['middle_name'];
                            $last_name=$row['last_name'];
                            
                            $authors = $last_name . ", " . $first_name . " " . $middle_name . "/" . $authors;
                        }
I haven't tried to do it yet because i have no idea what to look for in this... I'm sure it uses something that counts the results returned by the query, then creates the string? idk.

if you could help, i'll give you three virtual high fives and my sincerest gratitude.

thanks,
Wes
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Left Join and result formating

Post by requinix »

Code: Select all

$authors = array();
$row = first row of results;
$authors[] = "last, first middle";
 
while ($row = next row of results) {
    $authors[] = "first middle last";
}
 
$last = array_pop($authors);
if (the $authors array is empty) {
    $authorstr = $last;
} else {
    $authorstr = implode(", ", $authors) . " and " . $last;
}
wescrock
Forum Commoner
Posts: 31
Joined: Wed Sep 10, 2008 10:31 am
Location: Fresno, CA

Re: Left Join and result formating

Post by wescrock »

I'll try that. Thanks.

Does it just go into the while loop?

Thanks,
Wes
wescrock
Forum Commoner
Posts: 31
Joined: Wed Sep 10, 2008 10:31 am
Location: Fresno, CA

Re: Left Join and result formating

Post by wescrock »

tasairis wrote:

Code: Select all

$authors = array();
$row = first row of results;
$authors[] = "last, first middle";
 
while ($row = next row of results) {
    $authors[] = "first middle last";
}
 
$last = array_pop($authors);
if (the $authors array is empty) {
    $authorstr = $last;
} else {
    $authorstr = implode(", ", $authors) . " and " . $last;
}
I have taken a look at this, and I am a little confused as to what I need to do where and what methods I need to use.
Thanks,
Wes
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Left Join and result formating

Post by requinix »

wescrock wrote:I have taken a look at this, and I am a little confused as to what I need to do where and what methods I need to use.
Ah, see, that's what you're supposed to find out ;) It's why I posted pseudocode and not PHP.

Think about it and go looking in the manual for help.
wescrock
Forum Commoner
Posts: 31
Joined: Wed Sep 10, 2008 10:31 am
Location: Fresno, CA

Re: Left Join and result formating

Post by wescrock »

so, while looking at this, I don't really know what i should be doing.

for the places that say "last, first middle" and 'first middle last" I just do:

Code: Select all

$authors[] = $last . ", " .  $first . " " $middle;
but does something need to go in: $authors = array();

and im not certain how to facilitate: $row = first row of results;

thanks,
Wes
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Left Join and result formating

Post by requinix »

wescrock wrote:but does something need to go in: $authors = array();
Go in? The [] in $authors[] means that whatever's to the right of the equals sign gets appended to the $authors array.
wescrock wrote:and im not certain how to facilitate: $row = first row of results;
Use one of the mysql_fetch_ functions - except this time it's not in some loop. You're just getting the first row at that moment.
wescrock
Forum Commoner
Posts: 31
Joined: Wed Sep 10, 2008 10:31 am
Location: Fresno, CA

Re: Left Join and result formating

Post by wescrock »

this is where I am with it (and no, i havent been working non stop at it... lol):

Code: Select all

           <?php 
                $result_authors = @mysql_query("SELECT author_link.id_author_,
                    author.first_name, author.middle_name, author.last_name
                        FROM wfbiblio.author_link al
                        LEFT JOIN wfbiblio.author au
                        ON al.id_author_ = au.id_author_");
                     
                while ($row=mysql_fetch_assoc($result_authors)) {
                    $first_name = $row['first_name'];
                    $middle_name = $row['middle_name'];
                    $last_name = $row['last_name'];
 
                    $authors = array();
 
                    $row=mysql_fetch_row($result_authors);
                    $authors[] = $last_name . ", " . $first_name . " " . $middle_name;
                     
                    while ($row=mysql_fetch_row($result_authors)) {
                        $authors[] = $first_name . " " . $middle_name . " " . $last_name;
                    }
                     
                    $last=array_pop($authors);
                    if (empty($authors)) {
                        $authorstr = $last;
                    } else {
                        $authorstr = implode(", ", $authors) . " and " . $last;
                    }               
                }
            ?>
i am getting an error saying:
<br />
<b>Warning</b>: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in <b>/var/www/world_fair/manage/edit/edit_book.php</b> on line <b>100</b><br />
any ideas? thanks,
Wes
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Left Join and result formating

Post by requinix »

If you ever get an error message like that it means there was a problem with your query.

Before the while loop put

Code: Select all

if (!$result_authors) die(mysql_error());
and see what the error message has to say.
wescrock
Forum Commoner
Posts: 31
Joined: Wed Sep 10, 2008 10:31 am
Location: Fresno, CA

Re: Left Join and result formating

Post by wescrock »

O.k. I figured it out but the results still do not display. I just get a blank output.

I put echo $authorstr; and i just get a series of commas.
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: Left Join and result formating

Post by aceconcepts »

try using array as opposed to assoc
wescrock
Forum Commoner
Posts: 31
Joined: Wed Sep 10, 2008 10:31 am
Location: Fresno, CA

Re: Left Join and result formating

Post by wescrock »

that returns:
Crockett, Wes Allen, Wes Allen Crockett, Wes Allen Crockett, Wes Allen Crockett, Wes Allen Crockett, Wes Allen Crockett and Wes Allen Crockett
there are only 2 authors, the one above that keeps getting repeated and 1 other., so that doesnt work.
Post Reply