Displaying columns with a value

[benji87]

Hi guys,

 

what is the most efficient way to display column names from a database that only have the value '1' in its fields??

 

I cant seem to work it out, could someone give me a code example??

 

Thanks

[taith]

aah yes... i had issues with this before... easiest way is like this...

 

echo '<table>';
$query=mysql_query("SELECT * FROM `table` LIMIT 1");
$row=mysql_fetch_array($query);
echo '<tr>';
foreach($row as $k=>$v){
echo '<td>'.$k.'</td>';
}
echo '</tr>';
$query=mysql_query("SELECT * FROM `table`");
while($row=mysql_fetch_array($query)){
echo '<tr>';
foreach($row as $k=>$v){
  echo '<td>'.$v.'</td>';
}
echo '</tr>';
}
echo '</table>';

not tested... but should work :-)

[benji87]

Ok thanks that kind of worked! trouble is it echos everything. I just want it to display the columns that only have '1' in its field where the session username matches the username in the field, if that makes sense.

 

Plus this code seems to number the columns next to the names.....

[taith]

you just need to limit the queries...

 

$query=mysql_query("SELECT field, name, etc FROM `table` WHERE `field`='1' LIMIT 1");

$query=mysql_query("SELECT field, name, etc FROM `table` WHERE `field`='1'");

[benji87]

Nope its not liking it

 

error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/alns/public_html/plm/userarea/report.php on line 80

 

Warning: Invalid argument supplied for foreach() in /home/alns/public_html/plm/userarea/report.php on line 82

 

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/alns/public_html/plm/userarea/report.php on line 87

 

code:

 

<?
  $username = $_SESSION['username'];
echo '<table border =\"1\">';
$query=mysql_query("SELECT * FROM pl_teach_learn WHERE username = $username AND lesson_planning, objective_led_planning, starters, plenaries, structure_and_pace, questioning, explanations, differentiation, marking, monitoring_progress, group_work, positive_classroom_management, positive_behaviour_management, using_lsas_effectively, gifted_and_talented, speaking_and_listening, ict, engagement_enjoyment ='1' LIMIT 1");
$row=mysql_fetch_array($query);
echo '<tr>';
foreach($row as $k=>$v){
echo '<td>'.$k.'</td>';
}
echo '</tr>';
$query=mysql_query("SELECT * FROM pl_teach_learn WHERE username = $username AND lesson_planning, objective_led_planning, starters, plenaries, structure_and_pace, questioning, explanations, differentiation, marking, monitoring_progress, group_work, positive_classroom_management, positive_behaviour_management, using_lsas_effectively, gifted_and_talented, speaking_and_listening, ict, engagement_enjoyment ='1' LIMIT 1");
while($row=mysql_fetch_array($query)){
echo '<tr>';
foreach($row as $k=>$v){
  echo '<td>'.$v.'</td>';
}
echo '</tr>';
}
echo '</table>';
?>

[taith]

$query=mysql_query("SELECT lesson_planning, objective_led_planning, starters, plenaries, structure_and_pace, questioning, explanations, differentiation, marking, monitoring_progress, group_work, positive_classroom_management, positive_behaviour_management, using_lsas_effectively, gifted_and_talented, speaking_and_listening, ict, engagement_enjoyment FROM pl_teach_learn WHERE username = $username LIMIT 1");

 

[benji87]

I dont want to do that though im trying to get all the columns that have 1 not the username

[per1os]

Thats a weird request benji. I think what you are looking for is something like this:

 

$query=mysql_query("SELECT * FROM pl_teach_learn WHERE username = $username AND (lesson_planning = '1' OR objective_led_planning = '1' OR starters = '1' OR plenaries = '1' OR structure_and_pace = '1' OR questioning = '1'  OR explanations = '1' OR differentiation = '1' OR marking = '1' OR monitoring_progress = '1' OR group_work = '1' OR positive_classroom_management = '1' OR positive_behaviour_management = '1' OR using_lsas_effectively = '1' OR gifted_and_talented = '1' OR speaking_and_listening = '1' OR ict = '1' OR engagement_enjoyment ='1') LIMIT 1") or DIE(mysql_error());

 

Maybe ???

 

[benji87]

Sorry i dont think, i explain myself very well. What i need to do is echo the column names from the table. But i only want to  echo the columns that have the value 1 in an inserted field that also matches the sessions username.

 

What im trying to do is create a sort of questionnaire where the user fills out three forms with check boxes. The value of each box when ticked is '1' so when the form is processed it inserts 1 into all the columns that match the checked boxes along with the username.....

 

does that make sense?

[rcorlew]

Is this the type if inromation you are trying to retreive:

 

[table][tr] [td]User

Answered

$username

$answer

 

If it is then frost's post would work, if not show us a little more of the direction you are trying to go and we can try to help you out more.

[benji87]

No, i want the logged in user to fill out a form then after processing out putting like a report. So say for example:

 

Here is your report based on the choices you made:

 

You picked:

 

Objective led planning  Stuctures and pace  gifted and talented

 

 

those variables are the columns of the table

[rcorlew]

To me, it sounds like you maybe should restructre your databse. It looks like you are asking a series of true and false questions, and want to retreive the true answers from the database.

 

You might want to think about making the structre like this:

 

mysql row = id, username, true_answers

 

Then have your form set up to only pass a variable when you field is checked. The from field would like shomething like this:

 

<input type="radio" name="Lesson Planning" value="1">

 

Then only the fields that are true would be passed to the form processor. Then you would be able to put the options from the form into a string something like this:

 

$string = "$var1 $var2 $var3";

 

Then you would insert those variables into the true_answers column.

 

When you run your sql query to get the usernames true answers you would only have to pull out the string like this:

 

$query = mysql_query("SELECT * FROM table_name WHERE username='$username'");

 

Then you would have a query that would only show true answers.

 

In order to trim out any whitespaces in the string you would just have to do something like this:

 

$mystring = "row[true_answers]":

 

Then just present the true answer like this:

 

$username, you picked $mystring.