PHP and MySQL Table Joins

[Ralph Freshour]

I am having a hard time with joins - my following code displays:

..member_name .gender

instead of the actual data - I've been reading through my PHP and
MySQL manuals - the MySQL manual tells me how to form the syntax but
it is always shown in MySQL interactive mode and not using PHP code so
I have to try and figure it out in PHP (I've already opened mysql and
selected the database):

$php_SQL = "SELECT basics.member_n ame, personal.gender FROM basics,
personal WHERE basics.member_n ame = personal.member _name";
$php_resultID = mysql_query($ph p_SQL, $php_linkID);
while ($php_row = mysql_fetch_row ($php_resultID) )
{
print "$php_row->basics.member_ name
$php_row->personal.gende r<BR>";
}

[Nikolai Chuvakhin]

Re: PHP and MySQL Table Joins

Ralph Freshour <ralph@primemai l.com> wrote in message
news:<nr85lvcse ms709s8bpgf1vd1 rq6ll71uif@4ax. com>...[color=blue]
>
> I am having a hard time with joins - my following code displays:
>
> .member_name .gender
>
> instead of the actual data -
>
> $php_SQL = "SELECT basics.member_n ame, personal.gender FROM basics,
> personal WHERE basics.member_n ame = personal.member _name";
> $php_resultID = mysql_query($ph p_SQL, $php_linkID);
> while ($php_row = mysql_fetch_row ($php_resultID) )
> {
> print "$php_row->basics.member_ name
> $php_row->personal.gende r<BR>";
> }[/color]

Of course. Variable names (and object fields are variables) cannot
contain periods. Try aliasing; on an unrelated topic, use JOIN rather
than WHERE to link the two tables:

$php_SQL = 'SELECT basics.member_n ame AS member_name, ' .
'personal.gende r AS gender ' .
'FROM basics LEFT JOIN personal ' .
'ON basics.member_n ame = personal.member _name';
$php_resultID = mysql_query($ph p_SQL, $php_linkID);
while ($php_row = mysql_fetch_row ($php_resultID) ) {
echo $php_row->member_name, $php_row->gender, '<BR>';
}

Also, joining on [an unindexed?] text field is a performance drag;
consider joining on an indexed ID field if you have one. (If you
don't, you probably should.)

Cheers,
NC

[Mark Hewitt]

Re: PHP and MySQL Table Joins



"Ralph Freshour" <ralph@primemai l.com> wrote in message
news:nr85lvcsem s709s8bpgf1vd1r q6ll71uif@4ax.c om...[color=blue]
> I am having a hard time with joins - my following code displays:
>
> .member_name .gender[/color]

[snip]
[color=blue]
>
> $php_SQL = "SELECT basics.member_n ame, personal.gender FROM basics,
> personal WHERE basics.member_n ame = personal.member _name";
> $php_resultID = mysql_query($ph p_SQL, $php_linkID);
> while ($php_row = mysql_fetch_row ($php_resultID) )
> {
> print "$php_row->basics.member_ name
> $php_row->personal.gende r<BR>";
> }
>[/color]

I rarely (read never!) use the object syntax, but try printing without the
qualifying table name,
I'm sure this should work:

while ($php_row = mysql_fetch_row ($php_resultID) )
{
print "$php_row[member_name] $php_row[gender]<BR>";
}

then quite likely:

while ($php_row = mysql_fetch_row ($php_resultID) )
{
print "$php_row->member_name $php_row->gender<BR>";
}

would work too?

Thanks
Mark
---------------------------------------------------------------------------
Windows, Linux and Internet Development Consultant
Email: corporate@scrip tsmiths.com
Web: http://www.scriptsmiths.com
---------------------------------------------------------------------------

[Ralph Freshour]

Re: PHP and MySQL Table Joins

I think I've got the 3rd table included ok - I say I think because
while this specific query is returning 5 records - that is correct in
that is how many are less than 30 days old so that seems to be working
- however, the other fields such as age, filename are displaying as
the same data - from record one - member_name is the only unique data
probably from the GROUP BY clause - each member has a different age in
the personal table yet the first record age col in personal is 28 and
all 5 records display 28 for age - how do I get each member's age from
the personal table to display???

$php_SQL = "SELECT ".
"basics.account _creation_date AS account_creatio n_date, ".
"basics.member_ name AS member_name, ".
"basics.display ed_member_name AS displayed_membe r_name, ".
"photos.filenam e1 AS filename, ".
"personal.a ge AS age ".
"FROM basics, personal, photos ".
"WHERE account_creatio n_date >= DATE_SUB(NOW(), INTERVAL 30
DAY) ".
"GROUP BY member_name";

$php_resultID = mysql_query($ph p_SQL, $php_linkID);
while ($php_row = mysql_fetch_obj ect($php_result ID))
{
print "<TR>";
print "<TD>";
print $php_row->filename . ", ".
$php_row->displayed_memb er_name . ", ".
$php_row->age . ", ".
"<BR>";
print "</TD>";
print "</TR>";
}





On 1 Sep 2003 08:46:24 -0700, matejl@volja.ne t (lazo) wrote:
[color=blue]
>Ralph Freshour <ralph@primemai l.com> wrote in message news:<nr85lvcse ms709s8bpgf1vd1 rq6ll71uif@4ax. com>...[color=green]
>> I am having a hard time with joins - my following code displays:
>>
>> .member_name .gender
>>
>> instead of the actual data - I've been reading through my PHP and
>> MySQL manuals - the MySQL manual tells me how to form the syntax but
>> it is always shown in MySQL interactive mode and not using PHP code so
>> I have to try and figure it out in PHP (I've already opened mysql and
>> selected the database):
>>
>> $php_SQL = "SELECT basics.member_n ame, personal.gender FROM basics,
>> personal WHERE basics.member_n ame = personal.member _name";
>> $php_resultID = mysql_query($ph p_SQL, $php_linkID);
>> while ($php_row = mysql_fetch_row ($php_resultID) )
>> {
>> print "$php_row->basics.member_ name
>> $php_row->personal.gende r<BR>";
>> }[/color]
>
>Hi,
>
>when I use joins, I always add alias (SELECT basics.member_n ame as
>alias_1, personal.gender as alias_2 FROM ...), then to display data I
>use ...
>
>while ($php_row = mysql_fetch_arr ay($php_resultI D))
> {
> echo '$php_row[alias_1]';
> echo '<br>';
> echo '$php_row[alias_2]';
> echo '<br>';
> echo '<br>';
> }[/color]