Need help writing 2 queries

[TechMistress]

Hello smarties!

 

I have some code I've written, and it almost works!

 

The problem is, no matter how I log in, the option selects are simply passing $options2 from my query. I'm sure I've got some bad syntax in here. Anyone here see it???

 

The Queries:

$sql="SELECT ID, name, rolename FROM user WHERE rolename = 'Case Administrator'";
$result=mysql_query($sql);

$sql2="SELECT ID, name, rolename FROM user";
$result2=mysql_query($sql2);

$options="";
$options2="";

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

    $ID=$row["ID"];
    $rolename=$row["rolename"];
    $name=$row["name"];
    $options.="<OPTION VALUE=\"$ID\">".$name;
}

while ($row=mysql_fetch_array($result2)) {

    $ID=$row["ID"];
    $rolename=$row["rolename"];
    $name=$row["name"];
    $options2.="<OPTION VALUE=\"$ID\">".$name;
}
?>

 

The form:

<form name="message" action="messageck.php" method="post">
Subject: <input type="text" name="message_subject"><br>
To: <SELECT NAME=message_to>
<?php
if ($user.rolename == "User") {
echo"
<OPTION VALUE=0>Choose</option>
$options</option>
</SELECT>";
} else {
echo"
<OPTION VALUE=0>Choose</option>
$options2</option>
</SELECT>";
}
?>

[Pikachu2000]

From the amount of code you posted, it isn't completely obvious what you're trying to do, but look at the html that's generated in a View ---> Source. I'm betting it probably isn't what you think it should be.

[TechMistress]

Well, the html is fine and it's generating fine. Let me see if I can clarify:

 

I have an if/else statement in my form. It says if I'm a particular user, I should see the query results for $options1 and if I'm else, I should see the query results of $options2.

 

The problem is, no matter how I'm logged in, I'm seeing the results of $options2.

 

So I don't know if this is a query problem or a php problem. Since the html comes out clean, I'm guessing it's not my if/else statement, but a problem with how I'm executing the queries.

[Pikachu2000]

The html for the <select> fields' <option></option> tags can't be coming out right unless there's only one record returned from the query, in which case there would be no need for a loop.

 

In any event, you're going to post more of the code. Include all the relevant code that is used to determine what should be shown to whom, and how that data is gathered.

[TechMistress]

That IS all of the relevant code. The page starts with a session/user id.

 

I call on all of the relevant data in the queries. The $rolename determines which user is online at the moment.

 

The html form is spitting out all of the users in the dropdown, all of the time.  I want it to show just users who match rolename "Case Administers" in the dropdown, if the current session user has a rolename of "User" - else, show all users from the user table.

[Pikachu2000]

Do you have error_reporting = -1 and display_errors = On in your php.ini file?

 

Where do you define the constant called rolename, and what is it's value supposed to be?

[TechMistress]

There are no errors.

 

In the session, I define who I am (my user id). Then in the query I am fetching the rolename from the user table; and in the html form, I am setting an if/else statement saying in $user.rolename equals

 

The only problem is what I stated already, but it's not really an error, it's just not performing correctly - it's not paying attention to my if/else, and just always returning $options2.

[Pikachu2000]

$user.rolename is a variable with a constant concatenated to it. There are errors, you just aren't seeing them because you don't have error reporting set up correctly. There's no reason to run 2 separate queries for that, and there is no way those <select> fields are being echoed as valid markup if there's more than one record returned. Either that, or the code you've posted isn't the actual code you're using. In any event, that code is rather a mess.

[TechMistress]

The html options return perfect - it lists all of the users just fine.  I even tested the form, chose one of the options and the form worked, chose that option and everything.

 

Query 1 returns a list of all users in the database with the rolename of "Case Administrator" and Query 2 returns a list of all users in the database.

 

It's when I tried to lay out both queries to address my if/else statement where something happens - instead of treating the queries separately, it's basically just returning query 2 every thime and ignoring query 1.

[Pikachu2000]

Again, $user.rolename is a variable ($user) with a constant (rolename) concatenated to it. Where is the value of the $user variable assigned, and where is the constant rolename defined?

 

I took the liberty of pasting your code in and setting up a query against it and as I thought, it does not return valid markup. Each <option> tag needs to have it's own closing </option> tag.

[TechMistress]

You'll have to forgive me, because I have never coded in my life - I'm just learning!

 

About the markup, it did return fine, because I caught that </option> thing earlier,so it looks like this:

while ($row=mysql_fetch_array($result2)) {
    $ID=$row["ID"];
    $rolename=$row["rolename"];
    $name=$row["name"];
    $options2.="<OPTION VALUE=\"$ID\">".$name."</OPTION>\n ";

 

As far as the $user, it's set in the first part of my document with the session:

if (!isset($_SESSION["userid"]))
$user = (int) $_SESSION["userid"];

 

I know the user part is working, because I am only seeing MY messages, etc. (this is for a pm system).

 

The 4olename part I thought was set in the queries part, so $user.rolename would be sufficient enough to say if ME, the user had a rolename of ... then do this.

 

 

[Pikachu2000]

You'll have to forgive me, because I have never coded in my life - I'm just learning!

 

I wish I'd known that from the beginning.

 

How about posting the current revision of the code, and a detailed description of what you want to happen with it, under what conditions. Maybe we can get this sorted out.

[TechMistress]

Ok, below is the whole page code. What I want to happen is, if my rolename is "User" (which is just a text title in the db), then the dropdown box I see is options1 (which should be users with a rolename of "Case Administrator"), else, if I am any other rolename, just show all of the users. Right now, it's just always showing all user.

 

<?php
require("./init.php");

if (!isset($_SESSION["userid"]))
{
    $template->assign("loginerror", 0);
    $template->display("login.tpl");
    die();
}
$user = (int) $_SESSION["userid"];

$sql="SELECT ID, name, rolename FROM user WHERE rolename = 'Case Administrator'";
$result=mysql_query($sql);

$options="";
while ($row=mysql_fetch_array($result)) {
    $ID=$row["ID"];
    $rolename=$row["rolename"];
    $name=$row["name"];
    $options.="<OPTION VALUE=\"$ID\">".$name."</OPTION>\n ";
}

$sql2="SELECT ID, name, rolename FROM user";
$result2=mysql_query($sql2);

$options2="";
while ($row=mysql_fetch_array($result2)) {
    $ID=$row["ID"];
    $rolename=$row["rolename"];
    $name=$row["name"];
    $options2.="<OPTION VALUE=\"$ID\">".$name."</OPTION>\n ";
}
?>

<form name="message" action="messageck.php" method="post">
Subject: <input type="text" name="message_subject"><br>
To: <SELECT NAME=message_to>
<?php
if ($user.rolename == "User") {
echo"
<OPTION VALUE=0>Choose</option>
$options
</SELECT>";
} else {
echo"
<OPTION VALUE=0>Choose</option>
$options2
</SELECT>";
}
?>


<br>
Message: <br>
<textarea rows="20" cols="50" name="message_content">
</textarea>
<?php
echo '<input type="hidden" name="message_from" value="'.$user.'"><br>';
?>
<input type="submit" value="Submit">
</form>

[PFMaBiSmAd]

Is there any chance that you have a $_SESSION["rolename"] or similar session variable that holds the rolename for the current logged in member so that it is not necessary to query the user table for it or is it possible to add this to your code that sets the current $_SESSION["userid"] variable when someone logs in?

[PFMaBiSmAd]

FYI - the $user.rolename is probably leftover from copying code from a language that has a data structure (struct), such as C.

[TechMistress]

No, my session just relates my user id - and rolename is simply a text field in the user table. When I'm logged in, I have to problem calling on other fields by using $user.xxx.

 

I DID try using:

 

$user = (int) $_SESSION["userid"];

$rolename = $user.rolename; (before the queries)

 

and then using $rolename in the if/else statement, but it didn't work either.

 

I have been using smarty templates in the software I'm using for this so perhaps the $user.rolename only applies to that.  What is the proper format for an if/else statement like that?

[TechMistress]

Well, I'm getting closer...  Realizing that I was using logic that was probably from smarty, I looked at the code again and realized what you said about rolename being a constant.  I changed my if/else statement to this:

 

<form name="message" action="messageck.php" method="post">
Subject: <input type="text" name="message_subject"><br>
To: <SELECT NAME=message_to>
<?php
if (isset($_SESSION['userid']) && $_SESSION['rolename'] == $row['User']) {
echo"
<OPTION VALUE=0>Choose</option>
$options
</SELECT>";
} else {
echo"
<OPTION VALUE=0>Choose</option>
$options2
</SELECT>";
}
?>

 

Now it always shows $options2, no matter what my rolename - where before it was always showing $options1

[PFMaBiSmAd]

Before you go any further, don't duplicate code for two mutually exclusive choices. The only thing you should be doing conditionally is to form the correct query statement in the $sql variable. All the rest of the code can be greatly simplified.

 

If you actually have a session variable $_SESSION['rolename'], all you would do is -

if($_SESSION["rolename"] == "User"){
$sql="SELECT ID, name, rolename FROM user WHERE rolename = 'Case Administrator' ORDER BY name";
} else {
$sql="SELECT ID, name, rolename FROM user ORDER BY name";
}

// you have the correct query in $sql at this point, just execute it and form the option list from it.

[TechMistress]

Like I said, I don't know anything about coding, so I'm trying to learn as I go. I wouldn't know how to create a new session with rolename (and would only want it on this page anyway). Then, I wouldn't know how to do the while statements using the query codes you gave me. Getting more buried here, I expect!

[TechMistress]

Ok, tried this, but then it just shows the first sql query no matter what.

if((int) $_SESSION["rolename"] == "User"){
$sql="SELECT ID, name, rolename FROM user WHERE rolename = 'Case Administrator' ORDER BY name";
$result=mysql_query($sql);

} else {
$sql="SELECT ID, name, rolename FROM user ORDER BY name";
$result=mysql_query($sql);

}

$options="";
while ($row=mysql_fetch_array($result)) {
    $ID=$row["ID"];
    $rolename=$row["rolename"];
    $name=$row["name"];
    $options.="<OPTION VALUE=\"$ID\">".$name."</OPTION>\n ";
}

[PFMaBiSmAd]

(int) converts the string in $_SESSION["rolename"] to an integer 0 (assuming that the string is not a number) and causes the == comparison to be performed using numbers, which also converts the string 'User' to an integer 0. Since 0 == 0, the comparison is always TRUE.

 

Why do you have (int) in there?

 

You are also still duplicating code. You have two $result=mysql_query($sql); statements. Why not just save the typing and put one of them after the end of the conditional statement?

[TechMistress]

Ugh, OK - I get the (int) part now.

 

I think the problem seems to be that I don't know how to get the current user's rolename from the database.

 

Here's my latest code.

<?php
session_start();
require("./init.php");
$user = (int) $_SESSION["userid"];

if (!isset($_SESSION["userid"]))
{
    $template->assign("loginerror", 0);
    $template->display("login.tpl");
    die();
}

if($user->rolename){
$sql="SELECT ID, name, rolename FROM user WHERE rolename = 'Case Administrator' ORDER BY name";
} else {
$sql="SELECT ID, name, rolename FROM user ORDER BY name";
}
$result=mysql_query($sql);

$options="";
while ($row=mysql_fetch_array($result)) {
    $ID=$row["ID"];
    $rolename=$row["rolename"];
    $name=$row["name"];
    $options.="<OPTION VALUE=\"$ID\">".$name."</OPTION>\n ";
}
?>

<form name="message" action="messageck.php" method="post">
Subject: <input type="text" name="message_subject"><br>
To: <SELECT NAME=message_to>
<?php
echo"
<OPTION VALUE=0>Choose</option>
$options
</SELECT>";
?>

[PFMaBiSmAd]

I'm not exactly sure where you are stuck at with this fundamental task - 1) Forming a query to get the data you want using a related value you already have, 2) executing the query, 3) testing if the query worked or not, 4) testing if the query matched a row or not, or 5) fetching the data that the query returned.

 

Sample logic that demonstrates one possible way to do this -

 

<?php
require("./init.php");

if (!isset($_SESSION["userid"]))
{
    $template->assign("loginerror", 0);
    $template->display("login.tpl");
    die();
}
$user = (int) $_SESSION["userid"];

$query = "SELECT rolename FROM user WHERE ID = $user"; // (#1)
if(!$result = mysql_query($query)){ // (#2)(#3) always check if a query worked or not before attempting to access the result of that query
// the query produced an error of some kind
// your error handling logic would go here...

// log the mysql_error() and other information about the query, page, line number, and the visitor so that you can find and fix the problem...

echo "Fatal error - Could not retrieve your rolename and this page cannot be produced!"; // output some user message
} else {
// the query executed without error
if(!mysql_num_rows($result)){ // (#4) make sure the userid exists or not
	// the userid did not match anything
	// your code to handle this 'unexpected', but possible condition would go here...

	// log the exact $user value and other relevant information so that you can find and fix the problem...

	echo "Fatal error - Your user id ($user) does not exist and this page cannot be produced!"; // output some user message
} else {
	// the query matched a row, fetch the data
	list($rolename) = mysql_fetch_row($result); // (#5)

	// the remainder of your code that is dependent on having the rolename value for the current visitor
	if($rolename == 'User'){
		$sql="SELECT ID, name, rolename FROM user WHERE rolename = 'Case Administrator' ORDER BY name";
	} else {
		$sql="SELECT ID, name, rolename FROM user ORDER BY name";
	}
	$result=mysql_query($sql);

	$options="";
	while ($row=mysql_fetch_array($result)) {
		$ID=$row["ID"];
		$rolename=$row["rolename"];
		$name=$row["name"];
		$options.="<OPTION VALUE=\"$ID\">".$name."</OPTION>\n ";
	}
?>

<form name="message" action="messageck.php" method="post">
Subject: <input type="text" name="message_subject"><br>
To: <SELECT NAME=message_to>
<?php
echo"
<OPTION VALUE=0>Choose</option>
$options
</SELECT>";
?>

the rest of your form...

<?php
// end the logic on this page that is dependent on having the rolename for the current visitor
}
}
?>