Page 1 of 1

Left Join and result formating

Posted: Mon Oct 20, 2008 5:18 pm
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

Re: Left Join and result formating

Posted: Mon Oct 20, 2008 7:16 pm
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;
}

Re: Left Join and result formating

Posted: Tue Oct 21, 2008 9:08 am
by wescrock
I'll try that. Thanks.

Does it just go into the while loop?

Thanks,
Wes

Re: Left Join and result formating

Posted: Tue Oct 21, 2008 10:34 am
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

Re: Left Join and result formating

Posted: Tue Oct 21, 2008 3:04 pm
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.

Re: Left Join and result formating

Posted: Tue Oct 28, 2008 2:35 pm
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

Re: Left Join and result formating

Posted: Tue Oct 28, 2008 2:50 pm
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.

Re: Left Join and result formating

Posted: Mon Nov 03, 2008 4:55 pm
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

Re: Left Join and result formating

Posted: Mon Nov 03, 2008 5:25 pm
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.

Re: Left Join and result formating

Posted: Tue Nov 04, 2008 9:58 am
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.

Re: Left Join and result formating

Posted: Tue Nov 04, 2008 10:18 am
by aceconcepts
try using array as opposed to assoc

Re: Left Join and result formating

Posted: Tue Nov 04, 2008 10:44 am
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.