need some help here on custom query

Worker Joined: Dec 22, 2004 Posts: 135

I have a website where members can tell me where their offices are located (multiple). I'm trying to create some code that will list their offices in a table. I created a table called "offices" which contains address, city, zip (etc) plus user_id. Tell me why this code isn't working. I think it must have something to do with the DB query that's highlighted. Sorry, I am a noob. PS- the table works, no problems there, problem is getting the correct info from the user that's logged in)

//begin white area with table of current offices
echo "$userinfo[username],"._OFFICEID.""
." ";
$result = $db->sql_query("select u.name, o.offname, o.add1, o.add2, o.city, o.state, o.zip, o.phone, o.fax FROM nuke_users u, offices o, WHERE u.username = '$check' and o.user_id='$check'");

$num = $db->sql_numrows($result);
$i=0;
print "<table width=80% border=0>";
while($row=mysql_fetch_assoc($result)) {
if($i==3)
{
echo "</tr> <tr>";
$i=0;
}
echo "<td>"."".$row[name].""." ".$row[offname]." ".$row[add1].$row[add2]." ".$row[city].", ".$row[state]." ".$row[zip]." ".$row[phone]." Phone"." ".$row[fax]." Fax"." "." "."</td>";
$i++;
}

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

You are still not doing what you were instructed in this post http://www.ravenphpscripts.com/postx6889-0-0.html . You have to to cross-reference u.SOMETHING=o.SOMETHING

Posted: Wed Oct 05, 2005 10:41 pm

d*** ur fast
I thought I was by having AND in there.
How's this:

$result = $db->sql_query("select u.name, o.offname, o.add1, o.add2, o.city, o.state, o.zip, o.phone, o.fax FROM nuke_users u, offices o, WHERE u.username = '$check' =o.user_id='$check'");

Posted: Wed Oct 05, 2005 10:45 pm

Write it like I explained Smack

- something like

Code:

WHERE u.username = '$check' and u.username = o.user_id

Posted: Thu Oct 06, 2005 6:54 am

I understand what you're asking me to do, but there is a common denominator - "check". It should only bring back info from the current user, correct? i'm so lost. And how can a username=user_id?

Posted: Thu Oct 06, 2005 7:30 am

Quote:

And how can a username=user_id

You said the 2 were equal in your post above

Quote:

WHERE u.username = '$check' and o.user_id='$check

It's a Syllogism - Simple algebra.
If A=C and B=C then A=C which is what you said here:
WHERE u.username = '$check' and o.user_id='$check'

If both are equal to $check then they have to equal each other.

Posted: Thu Oct 06, 2005 8:39 am

Ahh, I was thinking $check contained the user_id and username so I could use it for both. how about this way:

Code:

select 


 u.name, 


 u.user_id,


 o.offname,


 o.user_id, 


 o.add1, 


 o.add2, 


 o.city, 


 o.state, 


 o.zip, 


 o.phone, 


 o.fax 


FROM 


 nuke_users u


INNER JOIN 


 offices o 


ON 


 u.user_id=o.user_id  


WHERE 


u.username= '$check'

Posted: Thu Oct 06, 2005 8:47 am

Why so complicated? Why not just

Code:

select u.name, u.user_id, o.offname, o.add1, o.add2, o.city, o.state, o.zip, o.phone, o.fax


FROM  nuke_users u, office o


WHERE u.user_id = '$check' and  u.user_id=o.user_id

which is what I've been saying all along Laughing

Posted: Thu Oct 06, 2005 8:50 am

if i knew anything about syntax i'd be done with this last year Smile

I know what I want, I just need to put it in correct syntax. Ok, latest code is this: (it echos the words in the table, but none of the DB results)

Code:

   $check = $cookie[1];


    $check2 = $cookie[2];


   $sql = "select u.name, u.user_id, o.user_id, o.offname, o.add1, o.add2, o.city, o.state, o.zip, o.phone, o.fax 


FROM  nuke_users u, office o 


WHERE u.user_id = '$check' and  u.user_id=o.user_id";


    $result = $db->sql_query($sql);


    $row = $db->sql_fetchrow($result);


   //begin white area with table of current offices


   echo "<b>$userinfo[username],"._OFFICEID."</b>"


       ."<br><br>";   


   $num = $db->sql_numrows($result);


$i=0; 


print "<table width=80% border=0>";


while($row=mysql_fetch_assoc($result))   { 


   if($i==3) 


      { 


      echo "</tr> <tr>"; 


      $i=0;   


      } 


   echo "<td>"."<b>".$row[name]."</b>"."<br>".$row[offname]."<br>".$row[add1].$row[add2]."<br>".$row[city].", ".$row[state]." ".$row[zip]."<br>".$row[phone]." Phone"."<br>".$row[fax]." Fax"."<br>"."<br>"."</td>"; 


   $i++; 


   }

Posted: Thu Oct 06, 2005 8:56 am

Take this query
select u.name, u.user_id, o.user_id, o.offname, o.add1, o.add2, o.city, o.state, o.zip, o.phone, o.fax
FROM nuke_users u, office o
WHERE u.user_id = '$check' and u.user_id=o.user_id and substitute the real value for $check. Then copy and paste it into the SQL window in phpMyAdmin and see what the result is.

Posted: Thu Oct 06, 2005 9:01 am

After I fixed "office" to "offices" (hoping that was the problem), the SQL query gave me correct results! I uploaded it to the server and still only get the table with no DB results, must be something else. This is entire code:

Code:

function edithome() {


    global $user, $userinfo, $Default_Theme, $cookie, $db, $broadcast_msg, $user_news, $storyhome, $module_name, $name, $offname, $add1, $add2, $city, $state, $zip, $phone, $fax;


    cookiedecode($user);


    getusrinfo($user);


   $check = $cookie[1];


    $check2 = $cookie[2];


   $sql = "select u.name, u.user_id, o.user_id, o.offname, o.add1, o.add2, o.city, o.state, o.zip, o.phone, o.fax 


FROM  nuke_users u, offices o 


WHERE u.user_id = '$check' and  u.user_id=o.user_id";


    $result = $db->sql_query($sql);


    $row = $db->sql_fetchrow($result);





    if ((is_user($user)) AND (strtolower($userinfo['username']) == strtolower($cookie[1])) AND ($userinfo['user_password'] == $cookie[2])) {


    include("header.php");


    OpenTable();


    echo "<center><font class=\"title\"><b>"._HOMECONFIG."</b></font></center>";


    CloseTable();


    echo "<br>";


    OpenTable();


    nav();


    CloseTable();


    echo "<br>";


    if(empty($userinfo['theme'])) {


        $userinfo['theme'] = $Default_Theme;


    }


   //begin white area with table of current offices


   echo "<b>$userinfo[username],"._OFFICEID."</b>"


       ."<br><br>";   


   $num = $db->sql_numrows($result);


$i=0; 


print "<table width=80% border=0>";


while($row=mysql_fetch_assoc($result))   { 


   if($i==3) 


      { 


      echo "</tr> <tr>"; 


      $i=0;   


      } 


   echo "<td>"."<b>".$row[name]."</b>"."<br>".$row[offname]."<br>".$row[add1].$row[add2]."<br>".$row[city].", ".$row[state]." ".$row[zip]."<br>".$row[phone]." Phone"."<br>".$row[fax]." Fax"."<br>"."<br>"."</td>"; 


   $i++; 


   }

Posted: Thu Oct 06, 2005 9:33 am

Try this

Code:

function edithome() {


   global $user, $userinfo, $Default_Theme, $cookie, $db, $broadcast_msg, $user_news, $storyhome, $module_name, $name, $offname, $add1, $add2, $city, $state, $zip, $phone, $fax;


   cookiedecode($user);


   getusrinfo($user);


   $check = $cookie[1];


   $check2 = $cookie[2];


   $sql = "select u.name, u.user_id, o.user_id, o.offname, o.add1, o.add2, o.city, o.state, o.zip, o.phone, o.fax 


      FROM  nuke_users u, offices o 


      WHERE u.user_id = '$check' and  u.user_id=o.user_id";


   $result = $db->sql_query($sql);


//   $row = $db->sql_fetchrow($result);





   if (is_user($user) AND strtolower($userinfo['username']) == strtolower($check) AND $userinfo['user_password'] == $check2) {


      include("header.php");


      OpenTable();


      echo "<center><font class=\"title\"><b>"._HOMECONFIG."</b></font></center>";


      CloseTable();


      echo "<br>";


      OpenTable();


      nav();


      CloseTable();


      echo "<br>";


      if(empty($userinfo['theme'])) {


        $userinfo['theme'] = $Default_Theme;


      }


      //begin white area with table of current offices


      echo "<b>$userinfo[username],"._OFFICEID."</b>"


       ."<br><br>";   


      $num = $db->sql_numrows($result);


      $i=0;


      print "<table width=80% border=0>";


      while($row=mysql_fetch_assoc($result))   {


         if($i==3) {


            echo "</tr> <tr>";


            $i=0;   


         }


         echo "<td>"."<b>".$row[name]."</b>"."<br>".$row[offname]."<br>".$row[add1].$row[add2]."<br>".$row[city].", ".$row[state]." ".$row[zip]."<br>".$row[phone]." Phone"."<br>".$row[fax]." Fax"."<br>"."<br>"."</td>";


         $i++;


      }


   }


}

Posted: Thu Oct 06, 2005 9:43 am

gives me a blank page, I tried just putting // in fron of the $row code and still just the table came up

Posted: Thu Oct 06, 2005 10:04 am

Try that code again. The code you posted was missing some closing brackets. I added them to my code above.

Posted: Thu Oct 06, 2005 10:47 am

that code gives a blank page, the old and new code. sry, I appreciate all your help though. You're in a race with DevShed by the way Smile

Posted: Thu Oct 06, 2005 11:59 am

Look at your error log.

Posted: Thu Oct 06, 2005 12:05 pm

For debugging, modify this

Code:

      while($row=mysql_fetch_assoc($result))   {


         if($i==3) {


            echo "</tr> <tr>";


            $i=0;   


         }


         echo "<td>"."<b>".$row[name]."</b>"."<br>".$row[offname]."<br>".$row[add1].$row[add2]."<br>".$row[city].", ".$row[state]." ".$row[zip]."<br>".$row[phone]." Phone"."<br>".$row[fax]." Fax"."<br>"."<br>"."</td>";


         $i++;


      }


   }

to

Code:

      while($row=mysql_fetch_assoc($result))   {


         die('Inner Loop');


         if($i==3) {


            echo "</tr> <tr>";


            $i=0;   


         }


         echo "<td>"."<b>".$row[name]."</b>"."<br>".$row[offname]."<br>".$row[add1].$row[add2]."<br>".$row[city].", ".$row[state]." ".$row[zip]."<br>".$row[phone]." Phone"."<br>".$row[fax]." Fax"."<br>"."<br>"."</td>";


         $i++;


      }


   }


   else die('Outer Loop');

and report back.

Posted: Thu Oct 06, 2005 2:01 pm

blank page, my error logging was disabled and it will be enabled in 24 hrs per my host.

Posted: Thu Oct 06, 2005 2:35 pm

24 hours? GEESH! What a joke Laughing

- Get Raven Web Hosting

Posted: Thu Oct 06, 2005 2:39 pm

Try changing
while($row=mysql_fetch_assoc($result)) {
to

while($row=$db->sql_fetchrow($result)) {

Posted: Thu Oct 06, 2005 2:46 pm

that little change caused the blank screen again, I also can't put any "}" after the last one in the array or I get a blank page.

Code:

function edithome() { 


     global $user, $userinfo, $Default_Theme, $cookie, $db, $broadcast_msg, $user_news, $storyhome, $module_name, $name, $offname, $add1, $add2, $city, $state, $zip, $phone, $fax; 


     cookiedecode($user); 


     getusrinfo($user); 


     $check = $cookie[1]; 


     $check2 = $cookie[2]; 


     $sql = "select u.name, u.user_id, o.user_id, o.offname, o.add1, o.add2, o.city, o.state, o.zip, o.phone, o.fax  


          FROM  nuke_users u, offices o  


          WHERE u.user_id = '$check' and  u.user_id=o.user_id"; 


     $result = $db->sql_query($sql);


if (is_user($user) AND strtolower($userinfo['username']) == strtolower($check) AND $userinfo['user_password'] == $check2) { 


          include("header.php"); 


          OpenTable(); 


          echo "<center>"._HOMECONFIG."</center>"; 


          CloseTable(); 


          echo "<br>"; 


          OpenTable(); 


          nav(); 


          CloseTable(); 


          echo "<br>"; 


          if(empty($userinfo['theme'])) { 


            $userinfo['theme'] = $Default_Theme; 


          }


//begin white area with table of current offices 


          echo "$userinfo[username],"._OFFICEID."" 


           ."<br><br>";    


          $i=0; 


          print "<table width=80% border=0>"; 


          while($row=$db->sql_fetchrow($result))   { 


               die('Inner Loop'); 


               if($i==3) { 


                    echo "</tr> <tr>"; 


                    $i=0;    


               }


echo "<td>"."".$row[name].""."<br>".$row[offname]."<br>".$row[add1].$row[add2]."<br>".$row[city].", ".$row[state]." ".$row[zip]."<br>".$row[phone]." Phone"."<br>".$row[fax]." Fax"."<br>"."<br>"."</td>";


               $i++;


          }


     }


     else die('Outer Loop');


}     


     


    OpenTable();


    echo "<form action=\"modules.php?name=$module_name\" method=\"post\">";


    if ($user_news == 1) {


   echo "<b>"._NEWSINHOME."</b> "._MAX127." "


       ."<input type=\"text\" name=\"storynum\" size=\"4\" maxlength=\"3\" value=\"".$userinfo['storynum']."\">"


       ."<br><br>";


    } else {


   echo "<input type=\"hidden\" name=\"storynum\" value=\"$storyhome\">";


    }


    if ($userinfo['ublockon']==1) {


        $sel = "checked";


    } else { $sel = ""; }


    if ($broadcast_msg == 1) {


   if ($userinfo['broadcast'] == 1) {


       $sel1 = "checked";


       $sel2 = "";


   } elseif ($userinfo['broadcast'] == 0) {


       $sel1 = "";


       $sel2 = "checked";


   }


   echo "<b>"._MESSAGEACTIVATE."</b><br><input type=\"radio\" name=\"location\" value=\"0\" $sel1> "


   ._FW." <br><input type=\"radio\" name=\"location\" value=\"0\" $sel2>"


   ._NW."<br><input type=\"radio\" name=\"location\" value=\"1\" $sel1> "


   ._N." <br><input type=\"radio\" name=\"location\" value=\"1\" $sel1> "


   ._S." <br><input type=\"radio\" name=\"location\" value=\"1\" $sel1> "


   ._E." <br><input type=\"radio\" name=\"location\" value=\"1\" $sel1> "


   ._CH." <br><input type=\"radio\" name=\"location\" value=\"1\" $sel1> "


   ._ML." <br><input type=\"radio\" name=\"location\" value=\"1\" $sel1> "


   ._RF." <br><br>";


    } else {


   echo "<input type=\"hidden\" name=\"broadcast\" value=\"1\">";


    }


    echo "<input type=\"checkbox\" name=\"ublockon\" $sel>"


   ." <b>"._ACTIVATEPERSONAL."</b>"


   ."<br>"._CHECKTHISOPTION.""


   ."<br>"._YOUCANUSEHTML."<br>"


   ."<textarea cols=\"55\" rows=\"5\" name=\"ublock\">".$userinfo['ublock']."</textarea>"


   ."<br><br>"


   ."<input type=\"hidden\" name=\"username\" value=\"".$userinfo['username']."\">"


   ."<input type=\"hidden\" name=\"user_id\" value=\"".intval($userinfo['user_id'])."\">"


   ."<input type=\"hidden\" name=\"op\" value=\"savehome\">"


   ."<input type=\"submit\" value=\""._SAVECHANGES."\">"


   ."</form>";


    CloseTable();


    include("footer.php");


    } else {


   main($user);


    }


}

Posted: Thu Oct 06, 2005 9:34 pm

Resolved. We found a misplaced comma Laughing