Issue
I am having trouble trying to output a mysql search in the way that I want. I cannot find an answer to my specific question anywhere yet with lots of searching and am hoping someone on this forum would be willing to help! I see it logically as a two step process.
First, I want to execute a mysql search where the search criteria selects for table rows where a specific column has data:
SELECT *
FROM `mytable`
WHERE column3 = 'yes'
This gives a subset of my table with only the rows containing 'yes' in column3.
Now, for the second step I would like to remove all columns from this subset table that do not contain any information (i.e., are null). I'm sure this is easy for a pro, but trying to cut the 'null columns' is stumping me and does not seem to be answered online yet. Your help would be greatly appreciated!
Solution
After further digging, I have finally come to realize that while you can easily filter for certain rows of data using mysql, filtering for certain columns of data dynamically is a different matter. I had assumed that columns could be filtered like rows. I found an answer to a similar question by bluish in 2011 that describes this: https://stackoverflow.com/a/4748682/5033595
Now that I understand the concept better, I still need more information to construct the code that will accomplish what is described by bluish in the link. I will edit this answer if I come up with this myself. Conversely, I would appreciate it if anyone else would be willing to contribute an answer with the code.
Ok, sorry for the delay and thanks to everyone for your advice. After sleuthing the internet forums and trying different options, I finally discovered a solution. In this solution, the key is using array_filter() to identify if data is contained in an array, or if it is empty. This works for 1-dimensionnal arrays, so I first moved every column of mysql data into its own 1-dimensionnal array and used the array_filter() as a tool to decide which columns will be published in the HTML table. The code and output follows. Enjoy...
CODE>>>>>>>>>>>>>>>>>>>>>>>>>>
<?php
//STEP 1: Making and confirming a connection to the Database>>>>>>>>>>>>>>>>>>>>>>>>>>
echo "STEP 1: MAKING A CONNECTION TO THE MYSQL DATABASE...<BR><BR>";
//make connection
$db = mysql_connect("localhost","root","urpassword");
if (!$db) {
die("Database connection failed miserably: " . mysql_error());
} echo "Connected successfully<BR>";
//select database
$db_select = mysql_select_db("mydatabase",$db);
if (!$db_select) {
die("Database selection also failed miserably: " . mysql_error());
} echo "Database selected successfully<BR>";
//query
$result = mysql_query("SELECT * FROM employees2", $db);
if (!$result) {
die("Database query failed: " . mysql_error());
} echo "Database queried successfully<BR>";
//<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
//STEP 2 OUTPUTTING THE MYSQL SEARCH OF THE WHOLE DATABASE INCLUDING EMPTY COLUMN>>>>>>>>>>>>>>>>>>>>>>>>>>>>
?>
<HTML>
<HEAD>
<TITLE>Only showing table columns containing data</TITLE>
</HEAD>
<BODY>
<BR><p>STEP 2: PLACING THE MYSQL TABLE INTO AN HTML TABLE</P><BR>
<TABLE width="600" border="1" cellpadding="1" cellspacing="1">
<TR>
<TH>Name</TH>
<TH>Age</TH>
<TH>Position</TH>
<TH>Height</TH>
<TH>Salary</TH>
</TR>
<?php
while ($row=mysql_fetch_assoc($result)) {
echo "<TR>";
echo "<TD>".$row['Name']."</TD>";
echo "<TD>".$row['Age']."</TD>";
echo "<TD>".$row['Position']."</TD>";
echo "<TD>".$row['Height']."</TD>";
echo "<TD>".$row['Salary']."</TD>";
echo "</TR>";
} //end while
?>
</TABLE>
<!-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< -->
<?PHP
//STEP 3 Put mysql database into 2 dimension array: I didn't know my mysql >search wasn't in an array format already!!>>>>>>>>>>>>>
echo "<BR><BR>STEP 3: CONVERTING THE MYSQL TABLE INTO A 2-DIMENSIONNAL ARRAY<BR><BR>";
$query = mysql_query("SELECT * FROM employees2", $db);
$results = array();
while($line = mysql_fetch_assoc($query)){
$results[] = $line;
}
print_r($results);
//<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
//STEP 4 - Fetching each column as its own array>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
echo "<BR><BR><BR>STEP 4: FETCHING EACH ARRAY COLUMN AS ITS OWN ARRAY";
$query = mysql_query("SELECT * FROM employees2", $db);
$results = array();
while($line = mysql_fetch_assoc($query)){
$results[] = $line;
$Name = array_column($results, 'Name');
$Age = array_column($results, 'Age');
$Position = array_column($results, 'Position');
$Height = array_column($results, 'Height');
$Salary = array_column($results, 'Salary');
}
echo "<BR><BR>";
print_r($Name);
echo "<br>";
print_r($Age);
echo "<br>";
print_r($Position);
echo "<br>";
print_r($Height);
echo "<br>";
print_r($Salary);
echo "<br>";
//<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
//STEP 5 - Identifying if individual column arrays contain data>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
echo "<BR><BR>STEP 5: CONFIRMING THAT YOU CAN IDENTIFY IF A COLUMN SEPARATED INTO ITS OWN ARRAY IS EMPTY<BR><BR>";
$query = mysql_query("SELECT * FROM employees2", $db);
$results = array();
while($line = mysql_fetch_assoc($query)){
$results[] = $line;
$Name = array_column($results, 'Name');
$Age = array_column($results, 'Age');
$Position = array_column($results, 'Position');
$Height = array_column($results, 'Height');
$Salary = array_column($results, 'Salary');
}
if(array_filter($Name)) {
echo "contains at least one non-empty element<BR>";} else echo "is empty<BR>";
if(array_filter($Age)) {
echo "contains at least one non-empty element<BR>";} else echo "is empty<BR>";
if(array_filter($Position)) {
echo "contains at least one non-empty element<BR>";} else echo "is empty<BR>";
if(array_filter($Height)) {
echo "contains at least one non-empty element<BR>";} else echo "is empty<BR>";
if(array_filter($Salary)) {
echo "contains at least one non-empty element<BR>";} else echo "is empty<BR>";
//<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
?>
<!-- STEP 6 - Creating table that only displays columns with data>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> -->
<BR><p>STEP 6: CREATING AN HTML TABLE THAT ONLY DISPLAYS COLUMNS WITH DATA</P><BR>
<TABLE width="500" border="1" cellpadding="1" cellspacing="1">
<?php
$query = mysql_query("SELECT * FROM employees2", $db);
$results = array();
while($line = mysql_fetch_assoc($query)){
$results[] = $line;
$Name = array_column($results, 'Name');
$Age = array_column($results, 'Age');
$Position = array_column($results, 'Position');
$Height = array_column($results, 'Height');
$Salary = array_column($results, 'Salary');
}
echo "<TR>";
if(array_filter($Name)) {echo "<TH>Name</TH>";}
if(array_filter($Age)){echo "<TH>Age</TH>";}
if(array_filter($Position)){echo "<TH>Position</TH>";}
if(array_filter($Height)){echo "<TH>Height</TH>";}
if(array_filter($Salary)){echo "<TH>Salary</TH>";}
echo "</TR>";
$query = mysql_query("SELECT * FROM employees2", $db);
while($line = mysql_fetch_assoc($query)){
echo "<TR>";
if(array_filter($Name)) {echo "<TD>".$line['Name']."</TD>";}
if(array_filter($Age)) {echo "<TD>".$line['Age']."</TD>";}
if(array_filter($Position)) {echo "<TD>".$line['Position']."</TD>";}
if(array_filter($Height)) {echo "<TD>".$line['Height']."</TD>";}
if(array_filter($Salary)) {echo "<TD>".$line['Salary']."</TD>";}
echo "</TR>";
}
?>
</TABLE>
<BR><BR><BR><BR>
</BODY>
</HTML>
<?php
//closing mysql database
mysql_close($db);
?>
CODE<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
OUTPUT>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
STEP 1: MAKING A CONNECTION TO THE MYSQL DATABASE...
Connected successfully
Database selected successfully
Database queried successfully
STEP 2: PLACING THE MYSQL TABLE INTO AN HTML TABLE
Name Age Position Height Salary
Chris Mogan 25 Assistant Programmer 170000
Emily Grant 25 Accountant 120000
James Daniel 27 Lead Programmer 210000
Jessica Pam 28 Group Head 250000
Mike Pablo 23 Head Designer 200000
Nick Meyers 24 Assistant Designer 150000
STEP 3: CONVERTING THE MYSQL TABLE INTO A 2-DIMENSIONNAL ARRAY
Array ( [0] => Array ( [Name] => Chris Mogan [Age] => 25 [Position] => Assistant Programmer [Height] => [Salary] => 170000 ) [1] => Array ( [Name] => Emily Grant [Age] => 25 [Position] => Accountant [Height] => [Salary] => 120000 ) [2] => Array ( [Name] => James Daniel [Age] => 27 [Position] => Lead Programmer [Height] => [Salary] => 210000 ) [3] => Array ( [Name] => Jessica Pam [Age] => 28 [Position] => Group Head [Height] => [Salary] => 250000 ) [4] => Array ( [Name] => Mike Pablo [Age] => 23 [Position] => Head Designer [Height] => [Salary] => 200000 ) [5] => Array ( [Name] => Nick Meyers [Age] => 24 [Position] => Assistant Designer [Height] => [Salary] => 150000 ) )
STEP 4: FETCHING EACH ARRAY COLUMN AS ITS OWN ARRAY
Array ( [0] => Chris Mogan [1] => Emily Grant [2] => James Daniel [3] => Jessica Pam [4] => Mike Pablo [5] => Nick Meyers )
Array ( [0] => 25 [1] => 25 [2] => 27 [3] => 28 [4] => 23 [5] => 24 )
Array ( [0] => Assistant Programmer [1] => Accountant [2] => Lead Programmer [3] => Group Head [4] => Head Designer [5] => Assistant Designer )
Array ( [0] => [1] => [2] => [3] => [4] => [5] => )
Array ( [0] => 170000 [1] => 120000 [2] => 210000 [3] => 250000 [4] => 200000 [5] => 150000 )
STEP 5: CONFIRMING THAT YOU CAN IDENTIFY IF A COLUMN SEPARATED INTO ITS OWN ARRAY IS EMPTY
contains at least one non-empty element
contains at least one non-empty element
contains at least one non-empty element
is empty
contains at least one non-empty element
STEP 6: CREATING AN HTML TABLE THAT ONLY DISPLAYS COLUMNS WITH DATA
Name Age Position Salary
Chris Mogan 25 Assistant Programmer 170000
Emily Grant 25 Accountant 120000
James Daniel 27 Lead Programmer 210000
Jessica Pam 28 Group Head 250000
Mike Pablo 23 Head Designer 200000
Nick Meyers 24 Assistant Designer 150000
OUTPUT<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Answered By - Shane
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.