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_name, personal.gender FROM basics,
personal WHERE basics.member_name = personal.member_name";
$php_resultID = mysql_query($php_SQL, $php_linkID);
while ($php_row = mysql_fetch_row($php_resultID))
{
print "$php_row->basics.member_name
$php_row->personal.gender<BR>";
}

[Nikolai Chuvakhin]

Ralph Freshour <ra***@primemail.com> wrote in message
news:<nr********************************@4ax.com>. ..

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_name, personal.gender FROM basics,
personal WHERE basics.member_name = personal.member_name";
$php_resultID = mysql_query($php_SQL, $php_linkID);
while ($php_row = mysql_fetch_row($php_resultID))
{
print "$php_row->basics.member_name
$php_row->personal.gender<BR>";
}

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_name AS member_name, ' .
'personal.gender AS gender ' .
'FROM basics LEFT JOIN personal ' .
'ON basics.member_name = personal.member_name';
$php_resultID = mysql_query($php_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]

"Ralph Freshour" <ra***@primemail.com> wrote in message
news:nr********************************@4ax.com...

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

.member_name .gender
[snip]

$php_SQL = "SELECT basics.member_name, personal.gender FROM basics,
personal WHERE basics.member_name = personal.member_name";
$php_resultID = mysql_query($php_SQL, $php_linkID);
while ($php_row = mysql_fetch_row($php_resultID))
{
print "$php_row->basics.member_name
$php_row->personal.gender<BR>";
}

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: co*******@scriptsmiths.com
Web: http://www.scriptsmiths.com
---------------------------------------------------------------------------

[Ralph Freshour]

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_creation_date, ".
"basics.member_name AS member_name, ".
"basics.displayed_member_name AS displayed_member_name, ".
"photos.filename1 AS filename, ".
"personal.age AS age ".
"FROM basics, personal, photos ".
"WHERE account_creation_date >= DATE_SUB(NOW(), INTERVAL 30
DAY) ".
"GROUP BY member_name";

$php_resultID = mysql_query($php_SQL, $php_linkID);
while ($php_row = mysql_fetch_object($php_resultID))
{
print "<TR>";
print "<TD>";
print $php_row->filename . ", ".
$php_row->displayed_member_name . ", ".
$php_row->age . ", ".
"<BR>";
print "</TD>";
print "</TR>";
}

On 1 Sep 2003 08:46:24 -0700, ma****@volja.net (lazo) wrote:

Ralph Freshour <ra***@primemail.com> wrote in message news:<nr********************************@4ax.com>. ..

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_name, personal.gender FROM basics,
personal WHERE basics.member_name = personal.member_name";
$php_resultID = mysql_query($php_SQL, $php_linkID);
while ($php_row = mysql_fetch_row($php_resultID))
{
print "$php_row->basics.member_name
$php_row->personal.gender<BR>";
}

Hi,

when I use joins, I always add alias (SELECT basics.member_name as
alias_1, personal.gender as alias_2 FROM ...), then to display data I
use ...

while ($php_row = mysql_fetch_array($php_resultID))
{
echo '$php_row[alias_1]';
echo '<br>';
echo '$php_row[alias_2]';
echo '<br>';
echo '<br>';
}