Quick variable question

[jay7981]

hey guys/gals,

im trying to write a php script and it works fine as i have it, but i need to set it to where it pulls the var from mysql table that it is displayed differeantly.

 

i.e.

if $var==abcdefg then echo bob

if $var==hijkl then echo jim

ect....

 

how can i do this?

[salathe]

Can you not just do that, but with PHP syntax?

 

if ($var == 'abcdefg') { echo 'bob'; }
if ($var == 'hijkl')   { echo 'jim'; }

[trq]

You just demonstrated the basics of it. Its really not clear where exactly you are stuck.

[jay7981]

ok i have a table in mysql that has authid, name, and dept

and a second table that has auth, name, password, access, and flags

 

now the 2 tables may not have the same amount of users.

what i have so far is a script that pulls all info from the first table and displays in a html table just fine. what i am trying to do is add a new colomn that is called access and have it pull the access levels from the field in the second table that matches the first tables authid. the data in the access field looks like this "abcdef" ect .... each access level i want to show as a name like admin, programmer, analyst, ect. there are about 8-10 different access levels i need to code for. 

 

example

 

Table 1 $member_table

123456789 BOB infosec

324641486 KIM CustSvc

897653215 DAN Janitor

 

Table 2 $access_table

123456789 BOB {MD5 pass} abcdefj

324641486 KIM {MD5 pass} acfgijkl

 

Output needs to look like

      ID                Name            Dept          Access

123456789        BOB            infosec        Admin

324641486        KIM            CustSvc      CustSvc

 

 

<div align="center">
<?php
include 'config.php';
include 'access.php';

$db = mysql_connect ($hostname, $username, $password) or die ('Failed to connect to database: ' . mysql_error());
mysql_select_db($database);

$query = "SELECT * FROM $member_table";
$result = mysql_query($query) or die ('Failed to query ' . mysql_error());

?>
<table width="100%" border="3" cellspacing="0" cellpadding="0">
<tr>
  <td width="100%" align="center" colspan="5" bgcolor="#252525"><font size="+3"><strong>Members</strong></font></td>
</tr>
<tr>
  <td width="10%" bgcolor="#252525" align="center"><font size="+2"><strong>SteamID</strong></font></td>
  <td width="10%" bgcolor="#252525" align="center"><font size="+2"><strong>Rank</strong></font></td>
  <td width="10%" bgcolor="#252525" align="center"><font size="+2"><strong>Name</strong></font></td>
  <td width="10%" bgcolor="#252525" align="center"><font size="+2"><strong>Access</strong></font></td>
  <td width="10%" bgcolor="#252525" align="center"><font size="+2"><strong>Control</strong></font></td>
</tr>
<?php
while ($row = mysql_fetch_assoc($result)) 
{
	echo "<tr>";
	$auth = $row['authid'];
	echo "<td width=\"10%\" bgcolor=\"#252525\"><strong>$auth</strong></td>";
	$rank = $ranks[$row['rank']];
	echo "<td width=\"10%\" bgcolor=\"#252525\"><strong>$rank</strong></td>";
	$name = $row['name'];
	echo "<td width=\"10%\" bgcolor=\"#252525\"><strong>$name</strong></td>";
	echo "<td width=\"10%\" bgcolor=\"#252525\"><table width=50% border=\"0\">
  <tr>
    <td width=\"25%\"><form action=\"edit.php?auth=$auth\" method=\"post\">
        <input type=\"submit\" value=\"Edit\">
      </form></td>
    <td width=\"25%\"><form action=\"delete.php?auth=$auth\" method=\"post\">
        <input name=\"submit\" type=\"submit\" value=\"Delete\" />
      </form></td>
    <td width=\"25%\" colspan=\"2\"><form action=\"admin_add2.php?auth=$auth&name=$name\" method=\"post\">
        <select name=\"access\">
          <option selected>Select Level</option>
          <option value=\"abcdefghijklmnopqrstu\">Leader/CoLeader</option>
          <option value=\"bcdefijmnopqrstu\">Upper Admin</option>
          <option value=\"bcefijnprstu\">Mid Admin</option>
          <option value=\"cfu\">Recruit Admin</option>
          <option value=\"u\">Member</option>
        </select>
        <input type=\"submit\" value=\"Set Permissions\">
      </form></td>
  </tr>
</table></td>";
	echo "</tr>";
}

echo "</table>";

mysql_free_result($result);
mysql_close($db);
?>
</div>

[jay7981]

any ideas on how to do this ?

 

any help would be greatly appreciated.

 

[salathe]

Use a JOIN in your SQL statement to pull back the access information for each user.

[jay7981]

I have done that and it works to a point, its only listing the people that actually are in the 2nd table. i need it to list them even if hey are not in the second table and are in the first.

 

table1(member_table) has the following rows:

authid(primary key)

rank

name

 

table2(admin_table) has the following rows:

auth(primary key) is also the same as authid from table1

name 

password(not displayed in output) 

access 

flags (not displayed in output)

 

 

Output is

ID                Name            Dept          Access

123456789        BOB            infosec        Admin

324641486        KIM            CustSvc      CustSvc

 

I need

ID                Name            Dept          Access

123456789        BOB            infosec        Admin

324641486        KIM            CustSvc      CustSvc

897653215        DAN            Janitor

 

even though DAN is in the first and not the second i still need him in the output. here is the new code::

<?php
include 'config.php';
include 'access.php';

$db = mysql_connect ($hostname, $username, $password) or die ('Failed to connect to database: ' . mysql_error());
mysql_select_db($database);

$query = "SELECT * FROM $member_table , $admin_table WHERE authid = auth AND rank >0";
$result = mysql_query($query) or die ('Failed to query ' . mysql_error());

?>
<table border="1" bordercolor="#999999" cellspacing="1" cellpadding="2">
<tr>
  <td colspan="5">Members</td>
</tr>
<tr>
  <td>SteamID</td>
  <td>Rank</td>
  <td>Flags</td>
  <td>Name</td>
  <td>Control</td>
  <td>Admin</td>
</tr>
<?php
while ($row = mysql_fetch_assoc($result)) 
{
	$auth = $row['authid'];
	$rank = $ranks[$row['rank']];
	$flags = $row['access'];
	$name = $row['name']; ?>
<tr>
<td><?php echo "$auth" ;?></td>
<td><?php echo "$rank" ;?></td>
<td><?php echo "$flags" ;?></td>
<td><?php echo "$name" ;?></td>
<td><table>
  <tr>
    <td><form action="edit.php?auth=<?php echo "$auth" ;?>" method="post">
        <input type="submit" value="Edit">
      </form></td>
    <td><form action="delete.php?auth=<?php echo "$auth" ;?>" method="post">
        <input name="submit" type="submit" value="Delete" />
      </form></td>
  </tr>
</table>
<td>
<table>
<tr>
<td><form action="admin_add2.php?auth=<?php echo "$auth" ;?>&name=<?php echo "$name" ;?>" method="post">
        <select name="access">
  		  <option selected>Select Level</option>
	  <option value="abcdefghijklmnopqrstu">Leader/CoLeader</option>
          <option value="bcdefijmnopqrstu">Upper Admin</option>
          <option value="bcefijnprstu">Mid Admin</option>
          <option value="cfu">Recruit Admin</option>
          <option value="u">Member</option>
        </select>
        <input type="submit" value="Set">
      </form></td>
</td>
<tr>
</table>
<?php 
}
?>
</table>
<?php
mysql_free_result($result);
mysql_close($db);
?>

 

 

[salathe]

You'll be wanting a LEFT JOIN.

[jay7981]

could you explain a bit more as i am new to joins.

[salathe]

The link that I gave you earlier should cover the basics: http://dev.mysql.com/doc/refman/5.4/en/join.html

[jay7981]

ok i tried to follow the examples and i am now getting errors i know i dont have this string syntaxed correctly could you please help me format it. Thanks.

 

ERROR:

Failed to query You have an error in your SQL syntax; check the manual that corresponds to your 
MySQL server version for the right syntax to use near 'LEFT JOIN admins ON clan_members.authid=admins.auth' at line 1

QUERY:

$query = "SELECT $member_table.*,$admin_table.access LEFT JOIN $admin_table ON $member_table.authid=$admin_table.auth";
$result = mysql_query($query) or die ('Failed to query ' . mysql_error());

[jay7981]

sorry for double post, i was able to get a working version of the code in dbforge query builder, however im not sure of how to format it for php.  and thanks for all teh help so far i am learning a lot from these forums and greatly appreciate all the help.

 

in my config.php i have:

$member_table = "clan_members";

$admin_table = "admins";

 

here is the query builder code:

SELECT
  clan_members.*, admins.access
FROM
  bioamx.clan_members
  LEFT OUTER JOIN bioamx.admins ON clan_members.authid = admins.auth