Jump to content

Forcing Vars from Mysql


jay7981
Go to solution Solved by Psycho,

Recommended Posts

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 by jay7981
Link to comment
Share on other sites

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();

Link to comment
Share on other sites

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 by Jessica
Link to comment
Share on other sites

$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 by jay7981
Link to comment
Share on other sites

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 by jay7981
Link to comment
Share on other sites

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 by jay7981
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by jay7981
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

  • Solution

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.