Can't get this join to work

EddieFoyJr

This involves three data tables for a postnuke site. One is called nuke_yp_business, the second is nuke_yp_memberships, the other is nuke_yp_assns.

nuke_yp_bussiness has one relevant (to this discusson) data field: busid

nuke_yp_memberships has 4 relevant fields: busid; assnsid; status; years.

nuke_yp_assns has 2 relevant fields: assnsid; name.

For a business identified by $busid, I want all organizations where m.busid = $busid. I want to print a list in 3 columns where: column 1 is the organizations' name (from nuke_yp_assns); column 2 is status (from nuke_yp_memberships); and column 3 is the years (also from nuke_yp_memberships).

Unfortunately, my best shot (see below) isn't workng.

Can somebody help me please?

<?php

$boxstuff = "";
$sql .= "select a.assnid, a.name, m.*";
$sql .= " from $pntable[yp_assns] a, $pntable[yp_membership] m ";
$sql .= " where m.busid = $busid";
$sql .= " and a.assnid = m.assnid";
$result=mysql_query($sql);
while ($memarray = mysql_fetch_array($result)) {
$boxstuff .= "<tr><td width=\"50%\">".ifconstant($memarray[a.name])." </td><td width=\"25%\">".ifconstant($memarray[m.status])."</td><td width=\"25%\">".ifconstant($memarray[m.years])."</td></tr>";

}
$boxstuff .= "";
$row[content] = $boxstuff;
themesidebox($row);
echo "</table> <hr> \n</td></tr>\n";

tomhath

You didn't way what was going wrong, but part of it might be $sql is uninitialized before the first concatenation and you have a couple of unquoted literals. Try it this way:

$sql = "";
$sql .= "select a.assnid, a.name, m.*"; 
$sql .= " from " . $pntable['yp_assns'] . " a, " . $pntable['yp_membership'] . " m "; 
$sql .= " where m.busid = $busid"; 
$sql .= " and a.assnid = m.assnid";

EddieFoyJr

Hey tomhath, thanks for replying.

What I was getting was "Parse error: parse error, unexpected '\"'...on line 3261." That's the one with
$sql .= " from $pntable[yp_assns] a, $pntable[yp_membership] m";

I made the changes you suggested and now I get, " Parse error: parse error, unexpected T_CONSTANT_ENCAPSED_STRING...on line 3259"

That's the first line of your code, the one with
$sql="";

I know this shouldn't be that hard but it's rubber room time at the OK Corral.

Bunkermaster

try this

$sql = "select a.assnid, a.name, m.*"; 
$sql .= " from " . $pntable['yp_assns'] . " a, " . $pntable['yp_membership'] . " m "; 
$sql .= " where m.busid = $busid"; 
$sql .= " and a.assnid = m.assnid";

or this

$sql = "select a.assnid, a.name, m.* 
from " . 
$pntable['yp_assns'] . " a, " . 
$pntable['yp_membership'] . " m 
where m.busid = $busid
and a.assnid = m.assnid";

or even this :

$foo = $pntable['yp_assns'];
$bar = $pntable['yp_membership'];
$sql =<<<ENDSQL
select a.assnid, a.name, m.*
from $foo a, $bar m 
where m.busid = $busidand a.assnid = m.assnid
ENDSQL;

EddieFoyJr

I really do. Why you'd give me the time of day is beyond me but I'm grateful beyond words.

unfortunately,

"this" gives me,
"Parse error: parse error, unexpected T_CONSTANT_ENCAPSED_STRING...on line 3259"

"or this" gives me,
"Parse error: parse error, unexpected T_CONSTANT_ENCAPSED_STRING...on line 3261"

"or even this" gives me,
"Parse error: parse error, unexpected T_VARIABLE...on line 3259"

FYI the first line of code is 3259

Is the sun below the yardarm? I think I need an ale!

EddieFoyJr

Since I was getting nowhere I decided to try something different. The following got me a bit further but still kicked back an error, "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource...on line 3264"

$sql = "";
$sql = " SELECT nuke_yp_membership.*, nuke_yp_assns.assnid, nuke_yp_assns.name";
$sql = " FROM nuke_yp_assns,nuke_yp_membership";
$sql = " WHERE_nuke_yp_membership.busid==$busid";
$sql = " AND nuke_yp_assns.assnid == nuke_yp_membership.assnid";
$result=mysql_query($sql);
while ($memarray = mysql_fetch_array($result)) {
$boxstuff .= "<tr><td>".ifconstant($memarray[nuke_yp_assns.name])." </td><td>".ifconstant($memarray[nuke_yp_membership.status])."</td><td>".ifconstant($memarray[nuke_yp_membership.years])."</td></tr>";

tomhath

That obviously won't work, since $sql will only have the last of the strings assigned to it.

What's above this in your script; it looks like you might have an unclosed quote above it. Repost exactly what you tried, without extra underscores, etc.

EddieFoyJr

I knew there was a reason why I came to you and not the other way around. So let's strike that failed attempt.

This litttle snippet is being hacked into a function the rest of which I can deliver or not as you wish. I'd previously had no problem with this same section of code until I decided it'd be more efficient and much cleaner to build a separate table for the associations. Hence the need to select the associations' names buy assnid.

Here's what I've got:

<?php

$boxstuff = "";
  $sql .= "SELECT m.*, a.assnid, a.name";
  $sql .= " FROM $pntable[yp_assns] a,$pntable[yp_membership] m";
  $sql .= " WHERE (m.busid == $busid)";
  $sql .= " and (a.assnid == m.assnid)";
  $result=mysql_query($sql);
  while ($memarray = mysql_fetch_array($result)) {
    $boxstuff .= "<tr><td><font size=1>".ifconstant($memarray[a.name])."<br></td><td>".ifconstant($memarray[m.status])."</td><td>".ifconstant($memarray[m.years])."</td></tr>"; 
  }
  $boxstuff .= "";
  $row[content] = $boxstuff;
  themesidebox($row);
  echo "</table><br><hr><br>\n</td></tr>\n";

I was originally going to select url from the assns table to make the column with a.name read as a link to the association's home page. I dropped that for the time being and would be happy just to get this working.

EddieFoyJr

Hey Tomhath,

Did I send what you needed?

EddieFoyJr

OK sports fans, before this falls into PHP Builder Hell, I thought I should thank you for your help and share the solution. It was essentially what I had when I came to this board. Everyone was focused on the query but the problem was in the WHILE statement.

<?php

  $boxstuff = "";      

  $sql = "SELECT a.assnid, a.name, m.*";
  $sql .= " FROM _$pntable[yp_assns]_ a, _$pntable[yp_membership]_ m ";
  $sql .= " WHERE_m.busid_=_$busid";
  $sql .= " AND a.assnid = m.assnid";

  $result = mysql_query($sql) or
     die(mysql_error());

  while ($memarray = mysql_fetch_array($result)) {
    $boxstuff .= "<tr><td width=\"50%\"><font size=1>".ifconstant($memarray['name'])."</td>
    <td width=\"25%\">".ifconstant($memarray['status'])."</td>
    <td width=\"25%\">".ifconstant($memarray['years'])."</td></tr>";  
 }

  $boxstuff .= "";
  $row[content] = $boxstuff;
  themesidebox($row);
  echo "</table><br><hr><br>\n</td></tr>\n";