SQL Select Columns

werkkrew · Jan 8, 2003

Im tying to select the data from specific columns as I do with rows all the time.

If I do a mysql_query("SELECT......");
that selects a row

I need to select a column of all the usernames in my username database for a User Manager in my website content management system I am building...any Ideas? I know there is a way, im just going about it wrong.

Namadoor · Jan 9, 2003

You could do something like this...

//First, select the column
$query = "SELECT columnname FROM dbname";
$result = mysql_query($query)

//and then

while ($array = mysql_fetch_array($result)) {
  //add what you want to do with each user here
  //this loop will execute once for every row
}

Hope this helps. '

EDIT: Forgot to add, to use the varable from the column you selected go like this:
$array[columname]

werkkrew · Jan 9, 2003

$sql_fn = mysql_query("SELECT first_name FROM members");
$sql_ln = mysql_query("SELECT last_name FROM members");
$sql_un = mysql_query("SELECT username FROM members");

while($data_fn = mysql_fetch_array($sql_fn)){
$x = 0;
print ($data_fn[$x]);
$x = $x++;
}

So heres what I basically had, and thanks to that last post I fixed an error that got it working.

However, the X doesnt increment, so Im not really sure where in the array I am. And it appears as though X stays set as zero but the values change. What would be the best way to have the value of each row from the column stored for use elsewhere in the script

Namadoor · Jan 9, 2003

The reason X is staying at zero is because you are setting it to zero every time you go through the loop.

So, instead of:

while($data_fn = mysql_fetch_array($sql_fn)){ 
$x = 0; 
print ($data_fn[$x]); 
$x = $x++; 
}

Do this:

$x = 0; 
while($data_fn = mysql_fetch_array($sql_fn)){ 
print ($data_fn[$x]); 
$x = $x++; 
}

Also, if you wanted to do the first_name, last_name, and username all in one loop, do this:

$sql = mysql_query("SELECT first_name, last_name, username FROM members");

$x = 0;

while ($data = mysql_fetch_array($sql))  {
   $first_name[$x] = $data[first_name];
   $last_name[$x] = $data[last_name];
   $username[$x] = $data[username];
   $x++;
}

And then if you had 4 members, you would have set these varibles:

$first_name[0]
$last_name[0]
$username[0]
$first_name[1]
$last_name[1]
$username[1]
$first_name[2]
$last_name[2]
$username[2]
$first_name[3]
$last_name[3]
$username[3]

Hope this helps also.

werkkrew · Jan 9, 2003

Okay, what you said worked, now I need to figure out how to accomplish something as efficiently as possible. I got it work one way but it was clumsy coding. So I took out that, and now I have this up to the point where I need help.

<?php
include 'db.php';
$acess_level = 5;
include 'accessctrl.php';
include 'header.php';


$sql = mysql_query("SELECT first_name, last_name, username FROM members");

$x = 0;

while ($data = mysql_fetch_array($sql))  {
   $f_name[$x] = $data[first_name];
   $l_name[$x] = $data[last_name];
   $u_name[$x] = $data[username];
   $x++;
}

print "<center><font color=\"888888\">";
print "Please Select User For Editing";
print "<br><br>";
print "<form name=\"form1\" method=\"POST\" action=\"user_manager.php\">";
print "<table align=\"center\">";
print "<tr>";
print "<td align=\"left\" valign=\"top\">Choose User to Edit</td>";
print "<td><select name=\"member\" value=\"\">";

Now obviously where I stop here is where I define each option.

The goal of this script is to query the database and allow me or other admins of my site to edit the information of any user.
So I want it to now generate the options in this format

<option value=\"$u_name\">$u_name ($l_name, $f_name)

for each user, it shows their username followed by the first and last name, when you select it puts the username chosen into the variable $member and passes that to the actual user editing form which I have already working to edit your own profile, and Im going to modify it to edit a different profile, update the information, and update the database.

All I really need to know now is whats the best way for me to iterate all 3 arrays efficiently and generate the form options.

Thank you for your help thus far

xblue · Jan 9, 2003

Hi, I'd think it would be most efficient to generate the output right within the while loop, instead of creating arrays first and then looping through them (you don't really need $x, and you would not even need to create the arrays but I left them in there).

<?php
include 'db.php';
$acess_level = 5;
include 'accessctrl.php';
include 'header.php';


$sql = mysql_query("SELECT first_name, last_name, username FROM members");

print "<center><font color=\"888888\">";
print "Please Select User For Editing";
print "<br><br>";
print "<form name=\"form1\" method=\"POST\" action=\"user_manager.php\">";
print "<table align=\"center\">";
print "<tr>";
print "<td align=\"left\" valign=\"top\">Choose User to Edit</td>";
print "<td><select name=\"member\" value=\"\">";

while ($data = mysql_fetch_array($sql))  {
   $f_name[] = $data[first_name];
   $l_name[] = $data[last_name];
   $u_name[] = $data[username];
   print "<option value=\"$u_name\">$u_name ($l_name, $f_name)";
}

werkkrew · Jan 10, 2003

I havent tried it yet, but theres no reason for that not to work.
Good thinking, and thanks a lot

werkkrew · Jan 10, 2003

Well, I got that much of it working thanks to that last post.

I pass the username selected on to the next script which is a form to edit the information with.

In this form I pass several hidden fields containing the original value so that I can query the database and also check for what values were changed but for some reason the values in the hidden fields dont pass on to the next script when submitted.

A) Should I not be using hidden fields to do this, if not, what is the best alternative...

If hidden fields should work what am I doing wrong...

Here is how I define the fields


<?php
$access_level = 5;
include 'accessctrl.php';
include 'header.php';
include 'db.php';

//Check to see if delete checkbox was checked
if (!isset($delete)){
//Pull information from database based on user chosen
$sql = mysql_query("SELECT * FROM members WHERE username='$member'");
while($row = mysql_fetch_array($sql)){
           foreach($row AS $key=>$val){
                    $key = stripslashes($val);
           }

//Set data to simple variables, 3 is used at end to not interfere with
//with the session variables
$first_name3 = $row['first_name'];
$last_name3 = $row['last_name'];
$username3 = $row['username'];
$email_address3 = $row['email_address'];
$user_level3 = $row['user_level'];
$tag3 = $row['tag'];
$info3 = $row['info'];

}
//If delete was checked
}elseif (isset($delete)){
//Delete row that includes that users information
$sql = mysql_query("DELETE FROM members WHERE username='$member' LIMIT 1");
//Check to see if that row still exists after deleted
$check = mysql_query("SELECT * FROM members WHERE username='$member'");
$dblcheck = mysql_num_rows($check);
//If its gone
if ($dblcheck == 0){
	print "<br><br><font color=\"888888\"><b><center>";
    print "$member Was Successfully Removed from the database!";
    print "</b></font></center>";
//If its still there
} else {
    print "<br><br><font color=\"888888\"><b><center>";
    print "Unable to remove $member from database";
    print "</b></font></center>";
}
//Exit Script without entering form
exit();
}


//Start form
?>

<html>
<head>
<title>Edit Member</title>
</head>

<body>
<br>
<br>
<center><font color="888888">Editing Username: <? echo $member; ?></font></center>
<form name="form1" method="POST" action="update_member.php">
  <table width="50%" border="0" cellpadding="4" cellspacing="0" align="center">
    <tr>
      <td width="24%" align="left" valign="top">First Name</td>
      <td width="76%"><input name="first_name2" type="text" id="first_name2" value="<? echo $first_name3; ?>"></td>
    </tr>
    <tr>
      <td align="left" valign="top">Last Name</td>
      <td><input name="last_name2" type="text" id="last_name2" value="<? echo $last_name3; ?>"></td>
    </tr>
    <tr>
      <td align="left" valign="top">Email Address</td>
      <td><input name="email_address2" type="text" id="email_address2" value="<? echo $email_address3; ?>"></td>
    </tr>
    <tr>
      <td align="left" valign="top">Desired Username</td>
      <td><input name="username2" type="text" id="username2" value="<? echo $username3; ?>"></td>
    </tr>
<?
	//Block User Level Assignment from Power User, but not Admin
    if ($user_level = 5){
    print "<tr>";
      print "<td align=\"left\" valign=\"top\">User Access Level</td>";
      print "<td><select name=\"user_level2\" value=\"$user_level3\">";
          print "<option value=\"0\">Band";
          print "<option value=\"1\">Writer";
          print "<option value=\"2\">Artist";
          print "<option value=\"3\">News Poster";
          print "<option value=\"4\">Power User";
          print "<option value=\"5\">Administrator</td>";
    print "</tr>";
    }
?>

<tr>
  <td align="left" valign="top">Band/Artist Name</td>
  <td><input name="tag2" type="text" id="tag2" value="<? echo $tag3; ?>"></td>
</tr>
<tr>
  <td align="left" valign="top">Other Information/Notes:</td>
  <td><textarea name="info2" id="info2"><? echo $info3; ?></textarea></td>
</tr>
<tr>
  <td><input type="hidden" name="last_name3" id="last_name3" value="<? $last_name3 ?>">
      <input type="hidden" name="username3" id="username3" value="<? $username3 ?>">
      <input type="hidden" name="email_address3" id="email_address3" value="<? $email_address3 ?>">
      <input type="hidden" name="first_name3" id="first_name3" value="<? $first_name3 ?>">
      <input type="hidden" name="tag3" id="tag3" value="<? $tag3 ?>">
      <input type="hidden" name="info3" id="info3" value="<? $info3 ?>">
      <input type="hidden" name="user_level3" id="user_level3" value="<? $user_level3 ?>">
  </td>
</tr>
<tr>
  <td align="left" valign="top">&nbsp;</td>
  <td><input type="submit" name="Submit" value="Update"></td>
</tr>
  </table>
</form>
<font color="888888">
<center>
All information will be changed AS SHOWN, if you accidentally degrade your
access level you will not be able to change it back!
</center>
</font>
</body>
</html>

I know this is a lot to look at, but I thought this would work, I cant really put the variables in a session because im already using sessions based on the user that is editing the data based on a DIFFERENT user then himself, so im a little confused as to where I should take this.

Just to clear the script up a bit...

$first_name and the other info variables with NO NUMBER are the session vars

$first_name2 and others are the data being submitted THROUGH the form

and

$first_name3 and others is the data pulled from the database based on the user chosen to edit, so the variables with 2 at the end are the edited values and 3 on the end are the original values.

werkkrew · Jan 10, 2003

If anyone is even reading this anymore

I consulted my book

In order to get a hidden field to actually post a variable you must actually print or echo the variable

or

SQL Select Columns

Wwerkkrew

NNamadoor

Wwerkkrew

NNamadoor

Wwerkkrew

Xxblue

Wwerkkrew

Wwerkkrew

Wwerkkrew