Forcing Vars from Mysql

[jay7981]

Hey guys/gals,

I have a table,

id

name

class_1

hide

 

and i am listing all names with thier associated class

Class 1

names...

 

Class 2

names... ect

 

the problem i am having is i need to force a users class to "None" if thier hide field is "1"

this is what i have done and its not working ... i have commented the code that is not working, as for the rest it is working great.

<?php
include("./sql-conn.php");
function outputClassList($class1, $users)
{
   if (!$class1) {
    return false;
   }
   $userCount = count($users);
   $output    = "<br>\n<b>{$class1}</b> ({$userCount})";
   $output .= "<br>\n";
   $output .= implode("<br>\n", $users);
   $output .= "<br>\n";
   return $output;
}
$db = mysql_connect($hostname, $username, $password) or die('Failed to connect to database: ' . mysql_error());
mysql_select_db($database);
$query = "SELECT class_1, name, hide FROM class ORDER BY class_1, name ASC" or die('Failed to Query: ' . mysql_error());
$result = mysql_query($query) or die('Failed Getting Results: ' . mysql_error());
$class1 = false;
while ($row = mysql_fetch_assoc($result)) {
   if ($class1 != $row["class_1"]) {
    echo outputClassList($class1, $users);

    //this isn't working
    if ($row['hide'] == "1") {
	    $class1 = "None";
    }
    //end
    $class1 = $row['class_1'];
    $users  = array();
   }
   $users[] = ucfirst($row['name']);
}
//print_r ($users);
echo outputClassList($class1, $users);
mysql_free_result($result);
mysql_close($db);
?>

Edited February 26, 2013 by jay7981

[Jessica]

I'm sure it is working, you're just overwriting the variable immediately after it. 

[jay7981]

i tried this as well and got the same results

 

if ($row['hide'] == "1") {
				    $class1 = "None";
		    }
		    //end
		    $class1 = $row['class_1'];
		    $users  = array();

 

to

 

if ($row['hide'] == "1") {
				    $class1 = "None";
		    }else{
		    //end
		    $class1 = $row['class_1'];
		    }
		    $users  = array();

[Jessica]

Well that's better.

The next problem is you don't use $class1 until the end of the while() loop, so you're only ever going to see the last value of it.

 

Edit: You ALSO are only even getting to that point if the new row's class1 is not the same as the previous row, so you can't hide one that is the same as the previous one.

Edited February 26, 2013 by Jessica

[jay7981]

$class1 is being used at the end of the while because of the function being ran on the loop gathering names based on the class, i dont see how this would impair me forcing a class on users that have a hide of 1.

 

 

**Desired OutPut**

in the db there are 4 classes (Option A, Option B, Option C, None)

and 2 Hide (0 , 1)

 

If john has Option A and Hide of 1 i am wanting him listed with the rest that have None in thier class

but if john has Option A and Hide of 0 he should be with the rest of Option A's

** End Desired OutPut**

 

**What I'm Getting**

The script works fine minus me trying to force a class on someone with hide set to 1

**End What I'm Getting**

Edited February 26, 2013 by jay7981

[Jessica]

If you only ever use it ONE time how can it be used for multiple things?

 

 

I think you need to explain what the desired output is and what you're getting.

[jay7981]

please see edit

[Jessica]

Yeah that was useless.

[Jessica]

Your script can only list ONE class with the total of ALL users, because you call the output function outside of your loop and it only accepts ONE class.

[jay7981]

Your script can only list ONE class with the total of ALL users, because you call the output function outside of your loop and it only accepts ONE class.

 

Ok so how do i fix it to do what i am wanting? obviously i dont know

Edited February 26, 2013 by jay7981

[Jessica]

You haven't adequately described WHAT YOU WANT.

 

Write it out in English, what EXACTLY you want to get.

[jay7981]

Table has

Name Class hide

John Option A 0

bob Option B 0

chris Option C 0

tom None 0

sam Option A 1

 

Output should be

 

Option A

John

 

Option B

bob

 

Option C

chris

 

None

tom

sam

 

Currently Getting

Option A

John

sam

 

Option B

bob

 

Option C

chris

 

None

tom

Edited February 26, 2013 by jay7981

[Jessica]

SELECT IF(hide==1,'none',class_1) as class1, name, hide FROM class ORDER BY IF(hide==1,'none',class_1), name ASC

 

Do the logic in the query, then put all the info in an array instead of one variable. You need to keep the class with the names.

[jay7981]

yea so that didnt work, getting syntax errors ....

Failed Getting Results: 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 '==1,'none',class_1) as class1, name, hide FROM class ORDER BY IF(hide==1,'none',' at line 1

 

anyone else got a solution that is actually going to work ?

Edited February 26, 2013 by jay7981

[Jessica]

See this is what I mean when I say you lot are ungrateful.

 

You could try to fix it. Use = instead of ==.

Or wait for someone else to do it for you and bitch at them for trying.

[jay7981]

anyways your "solution" although broken (== instead of =) i had already corrected and am getting a blank page.

Edited February 26, 2013 by jay7981

[Psycho]

You don't need any of that logic. You can do the whole thing with your query

 

SELECT IF(`hide`=1, 'none', `class_1`) AS `option`,
   GROUP_CONCAT(`name` SEPARATOR ', ') AS `names`
FROM class
GROUP BY `option`
ORDER BY `option`='none', `option`, `name` ASC

 

Using the sample data you showed above the result of that query would be

option   | names
-----------------------
Option A | John
Option B | Bob
Option C | Chris
None	 | Tom, Sam

[jay7981]

ok so i tried that query inplace of mine and am getting a blank page do i need to stop using the function with it? i'm sorry as i do not know much about this type of logic as i am still learning. and i think that is where me and Jess got off on the wrong foot, i did not mean to come across as ungreatful because i am greatful that sites like this exsist to help people like me.

 

what i did was simply changed my query to yours, was there something else i missed or did not do?

[Psycho]

ok so i tried that query inplace of mine and am getting a blank page do i need to stop using the function with it?

 

 . . .

what i did was simply changed my query to yours, was there something else i missed or did not do?

 

Um yeah, I think the reason Jessica got frustrated is that you are not really reading the information provided and are instead filtering it through some preconceived ideas. I specifically stated "You don't need any of that logic" and said you could do the whole thing with your query. So, what do you do? You implement that query using the logic you already have and get unexpected results. I even provided an example of what the database result set would be - I thought that would be enough for you to understand how you could implement it. The least you could have done was to run the query through PHPMyAdmin, or whatever you are using to manage your database, and see that it works before even trying to implement it in your code.

 

Those of us who routinely respond to posts enjoy helping people. We don't get paid to do this. So, when someone receiving our help does not time the time and consideration to really read what is provided or complains about a simple typo in code that we provide it seems the person is not appreciative of our time and effort.

 

So, let me spell it out for you. This should be all you need:

include("./sql-conn.php");
$db = mysql_connect($hostname, $username, $password)
        or die('Failed to connect to database: ' . mysql_error());
mysql_select_db($database);

$query = "SELECT IF(`hide`=1, 'none', `class_1`) AS `option`,
                 GROUP_CONCAT(`name` SEPARATOR ',') AS `names`
          FROM class
          GROUP BY `option`
          ORDER BY `option`='none', `option`, `name` AS";
$result = mysql_query($query) or die('Failed Getting Results: ' . mysql_error());

while ($row = mysql_fetch_assoc($result))
{
    echo "<b>{$row['option']}</b><br>{$row['names']}<br><br>\n";
}

 

However, that will output the names as a comma separated list (due to the GROUP_CONCAT in the query). In the output you want you apparently want a line break after each name. That is a trivial problem to solve which I expect YOU to solve. You can either implement that in the query by changing the string that is used to concatenate the names or you can take the comma separated string of names and implement the logic in the PHP code. I would do the latter.

[jay7981]

Thank  you so much for breaking this down for me and thanks to you i now understan a bit better and that is all i was asking for. You have helped me tremendosly and i have learned alot from this.