PHP codes/SQL Statement to get rid redundancy fields

leikeze · November 3, 2010, 2:05am

I have a SQL query statement using PHP that produces the following result:

NAME FOODS

Ben Pizza
Ben Fries
Ben Choco
Kelly Fries
Chit Pizza

But what I want to view is like this:

NAME FOODS

Ben Pizza
___ Fries
___ Choco
Kelly Fries
Chit Pizza

*Replace the underscores with spaces

Just want to get rid the redundancy. Please help to come up with an SQL statement or PHP codes. Thank you.

AnthonySterling · November 3, 2010, 8:59am

Here’s a quick ‘stab’ at it.


<?php
$array = array(
  array(
    'name'  => 'Ben',
    'food'  => 'Pizza',
  ),
  array(
    'name'  => 'Ben',
    'food'  => 'Fries',
  ),
  array(
    'name'  => 'Ben',
    'food'  => 'Choco',
  ),
  array(
    'name'  => 'Kelly',
    'food'  => 'Fries',
  ),
  array(
    'name'  => 'Chit',
    'food'  => 'Pizza',
  ),
);

class SomethingIterator extends ArrayIterator{
  protected
    $previousName = null;
  
  public function current(){
    $current = parent::current();
    
    if($this->previousName === $current['name']){
      $current['name'] = '';
    }else{
      $this->previousName = $current['name'];
    }
    
    return $current;
  }
}

foreach(new SomethingIterator($array) as $user){
  print_r($user);
}
/*
  Array
  (
      [name] => Ben
      [food] => Pizza
  )
  Array
  (
      [name] => 
      [food] => Fries
  )
  Array
  (
      [name] => 
      [food] => Choco
  )
  Array
  (
      [name] => Kelly
      [food] => Fries
  )
  Array
  (
      [name] => Chit
      [food] => Pizza
  )
*/
?>

leikeze · November 3, 2010, 9:12am

I used a sql statement:

$sql = “SELECT name, foods FROM people”

$result=mysql_query($sql);

and inside the <td>

<td>
<?php
//Header in the .xls file
$_SESSION[‘report_header’]=array(“Name”,“Foods”);

					//Table header
					echo "&lt;table id='box-table-a'&gt;";
					echo "&lt;thead&gt;&lt;tr&gt;&lt;th scope='col'&gt;Name&lt;/th&gt;&lt;th scope='col'&gt;Foods&lt;/th&gt;
					&lt;/tr&gt;&lt;/thead&gt;";
					
					$i=0;
					
					//Generate data from query
					while($row = mysql_fetch_array($result))
					{
						echo "&lt;tbody&gt;&lt;tr&gt;&lt;td&gt;"; 
						echo $_SESSION['report_values'][$i][0]=$row['0']; 
						echo "&lt;/td&gt;&lt;td&gt;";
						echo $_SESSION['report_values'][$i][1]=$row['1']; 
						echo "&lt;/td&gt;&lt;/tr&gt;&lt;/tbody&gt;";
						
						$i++;
					}
					
					echo "&lt;/table&gt;";
					
					//Close database
					mysqli_close($con);
				?&gt;
				&lt;/td&gt;

r937 · November 3, 2010, 9:54am

to accomplish what you want in the php, you must have an ORDER BY clause in your query

leikeze · November 5, 2010, 1:26am

It’ll just rearrange the names but not delete the redundant names in view using PHP.

ORDER BY will just result in:

Ben Pizza
Ben Fries
Ben Coke
Mary Pizza

Not:

Ben Pizza
(space) Fries
(space Coke
Mary Pizza

r937 · November 5, 2010, 1:46am

actually, you ~do~ need ORDER BY

otherwise you could get this –

Ben Pizza
Mary Pizza
Ben Fries
Ben Coke

and that won’t look right after being processed by php –

Ben Pizza
Mary Pizza
Ben Fries
____ Coke

leikeze · November 5, 2010, 2:01am

Yeah. After Order By? What’s next to get rid the redundant?

StarLion · November 5, 2010, 2:42am

the trick isnt to get rid of the redundant inside the query, it’s to ignore the redundant when you’re outputting.
Store the ‘current’ person’s name as a variable, and as you’re walking through the results, only display the name when it changes.

leikeze · November 5, 2010, 6:35am

From what you told, I added some codes:

//Header in the .xls file
$_SESSION['report_header']=array("Name","Foods");

//Table header
echo "<table id='box-table-a'>";
echo "<thead><tr><th scope='col'>Name</th><th scope='col'>Foods</th>
</tr></thead>";

$i=0;

//Generate data from query
while($row = mysql_fetch_array($result))
{
echo "<tbody><tr><td>"; 
$namep = $row['0'];
$_SESSION['report_values'][$i][0]=$namep;
							
if ($namep=$namep) {
echo "";
} else {
	echo $namep;
}
							
echo "</td><td>";
echo $_SESSION['report_values'][$i][1]=$row['1']; 
echo "</td></tr></tbody>";
							
$i++;
}
echo "</table>";

But it can’t seem right because the Name didn’t show at all now.

help

StarLion · November 5, 2010, 12:39pm

if ($namep=$namep) {

does this not strike you as wrong?

if (TheStoredName != TheCurrentRowsName) {
TheStoredName = TheCurrentRowsName;
echo TheCurrentRowsName;
}

leikeze · November 8, 2010, 1:42am

sorry. i’m not good in programming. actually, the most hated subject. haha. kidding! it’s just that i’m not good on it. (: just doing it for a project… T_T sigh XD

leikeze · November 8, 2010, 1:46am

Where will i get the variable TheStoredName and TheCurrentRowsName?
The row[‘name’] will be passed into a variable like $TheStoredName…
How about the TheCurrentRowsName? row[‘name’] too?
May I know the code construction? :x

sowee. really don’t know anything about codings… :sick:

StarLion · November 8, 2010, 12:37pm

TheCurrentRowsName = $row[‘name’]
TheStoredName = another variable; could be anything.

Basically what the code does is that if the stored name is not the same as the current name (IE: Whenever it changes), it outputs the new person’s name, and then sets the stored name as the current one, so that the next time through the loop, if the name hasnt changed, it doesnt print out the name.