Echo Out MySQL Data into a Table

[Drummin]

I am glad you got a solution that will work for you. Now that I see some table fields I would like to offer this sample page.

1. One of the first things I am doing is checking if GET['vs'] is set,
   as GET['vs'] might not be, which would throw an error otherwise.
2. I then am querying the table and building an array of acceptable
   shortnames. This not only is used to check submitted data before
   using in the query but can also be used to build selection forms
   (showing 2 variations at the bottom).
3. I then validate the shortnames array and build a searchnames array
   with only qualified matches.
4. I build the query and build a data array with the results using the
   shortname as primary key so that each can be placed in their
   respective column. I am also keeping a running count for each
   shortname group in the data array.
5. I do a quick foreach loop to build a count array and then max() to
   find the number of loops need to display all rows.
6. Keeping php query loops out of html I build the display above output with data
   being placed into the correct shortname column.

Anyway it gives you something to compare to what you came up with.

<?php
$shortnames = (isset($_GET['vs']) ? $_GET['vs'] : '');
include_once('connect.php');

$connection = mysqli_connect($host,$user,$password) or die();
mysqli_select_db($connection,$db);

//Build list of acceptable shortnames
$acceptableshortnames = array();
$sql1 = "SELECT DISTINCT shortname FROM compare_wearables ORDER BY name";
$query = mysqli_query($connection, $sql1) or die(mysqli_error($connection));
while($row = mysqli_fetch_assoc($query)) {
    $acceptableshortnames[] = $row['shortname'];
}

//Validate shortnames against acceptable shortnames to searchnames array
if(isset($shortnames) && is_array($shortnames)):
    $searchnames = array();
    foreach($shortnames as $shortname):
        if(in_array($shortname,$acceptableshortnames)):
            $searchnames[] = $shortname; 
        endif;
    endforeach;
endif;


$sql = "SELECT name,shortname,activity_calories,price FROM compare_wearables ";
$qArr = array();

//Build field value array
if(isset($searchnames) && !empty($searchnames)):
    foreach($searchnames as $searchname):
        $qArr[] = "shortname = '".$searchname."'";
    endforeach;
endif;

//IF we have search values add to sql
if(!empty($qArr)):
    $sql .= ' WHERE ';
    $sql .= implode(' OR ', $qArr);
endif;

// Add order by
$sql .= " ORDER BY name";

//echo $sql . '<br/>';

$query = mysqli_query($connection, $sql) or die(mysqli_error($db));

//Build data array with shortname as primary key
$data = array();
while($spec = mysqli_fetch_assoc($query)) {
    $data[$spec['shortname']][] = $spec;
    !isset($data[$spec['shortname']]['cnt']) ? $data[$spec['shortname']]['cnt'] = 1 : $data[$spec['shortname']]['cnt']++;
}

mysqli_close($connection);

//Get counts for each shortname array
$counts = array();
foreach($data as $shortname => $arr):
    $counts[] = $arr['cnt'];
endforeach;

//max number of rows
$max = max($counts);    


$display = "";

if(isset($searchnames) && !empty($searchnames)){
    $display .= '<table>
    <tr>'."\r";
    
    foreach($searchnames as $searchname):
        $display .= '<th colspan="3">' . $searchname . '</th>'."\r";        
    endforeach;
    
    $display .= '</tr>'."\r";
    
    for($i=0;$i<$max;$i++):
        $display .= '<tr>'."\r";
            foreach($searchnames as $searchname):
            
                if(array_key_exists($searchname,$data) && array_key_exists($i,$data[$searchname])){
                    $display .= '<td>' . $data[$searchname][$i]['name'] . '</td>
                    <td>' . $data[$searchname][$i]['activity_calories'] . '</td>
                    <td>' . $data[$searchname][$i]['price'] . '</td>'."\r";                
                }else{
                    for($r=1; $r<=3; $r++):
                        $display .= '<td>&nbsp;</td>'."\r";
                    endfor;            
                }
                             
            endforeach;    
        $display .= '</tr>'."\r";
    endfor;    
    $display .= '</table>'."\r";

}else{
    $display .= 'Please select a name'."\r";
}
?>
<html>
<head>
<title>Sample</title>
</head>
<body>

<?php
if(isset($display)){ echo $display; }
?>

<!-- Sample select form using $acceptableshortnames array -->
<form method="get" action="">
<select name="vs[]" multiple="multiple">
<option value="">Select</option>
<?php
foreach($acceptableshortnames as $acceptname):
?>
<option value="<?php echo $acceptname;?>"><?php echo $acceptname;?></option>
<?php
endforeach;
?>
</select><br />
<input type="submit" name="compare" value="Compare" />
</form>

               
<!-- Sample checkbox form using $acceptableshortnames array -->
<form method="get" action="">
<?php
foreach($acceptableshortnames as $acceptname):
?>
<input type="checkbox" name="vs[]" value="<?php echo $acceptname;?>" />&nbsp;<?php echo $acceptname;?><br />
<?php
endforeach;
?>
<input type="submit" name="compare" value="Compare" />
</form>
</body>
</html>

[geiger]

Thanks for this. I used your code as guidance and came up with the following:

[code]<?php

// Build array of selections
$getSelections=array();
if($_SERVER[‘QUERY_STRING’]!=“”) {
$params = explode(‘&’, $_SERVER[‘QUERY_STRING’]);
foreach ($params as $param) {
$name_value = explode(‘=’, $param);
$name = $name_value[0];
$value = $name_value[1];
array_push($getSelections, $name_value[1]);
}
}

// Connect to db
include_once(‘includes/connect.php’);
$connection = mysqli_connect($host, $user, $password, $db) or die();
mysqli_select_db($connection, $db);

// Build list of acceptable shortnames
$acceptableShortnames = array();
$acceptableShortnames_SQL = mysqli_query($connection, “SELECT DISTINCT shortname FROM compare_wearables ORDER BY name”) or die(mysqli_error($connection));
while($getShortnames = mysqli_fetch_assoc($acceptableShortnames_SQL)) {
$acceptableShortnames = $getShortnames[‘shortname’];
}

// Validate selections against acceptable shortnames
$validSelections = array();
foreach($getSelections as $selection) {
if(in_array($selection, $acceptableShortnames)) {
$validSelections = $selection;
}
}

// If no valid selections, pick some randomly
if(count($validSelections) < 1) {
$randomSelections = array_rand($acceptableShortnames, 3);
$validSelections = array();
foreach($randomSelections as $randomSelection) {
$validSelections = $acceptableShortnames[$randomSelection];
}
}

// Get specs on valid selections
$validSelectionsStr = implode(“‘,’”, $validSelections);
$specs_SQL = mysqli_query($connection, “SELECT * FROM compare_wearables WHERE shortname in('”.$validSelectionsStr.“') ORDER BY price DESC”) or die(mysqli_error($db));

// Build data array
$specs = array();
while($item = mysqli_fetch_assoc($specs_SQL)) {
$fields = array_keys($item); // How do I make this more efficient?
foreach($fields as $field) {
$specs[$field] = $item[$field];
}
}

// Close connection
mysqli_close($connection);

?>[/code]

[Drummin]

@geiger

$fields = array_keys($item); // How do I make this more efficient?
foreach($fields as $field) {
$specs[$field] = $item[$field];
}

IMHO, Instead of using the fields array here, use the shortname field from the database to build/sort the array as I did.

while($spec = mysqli_fetch_assoc($query)) {
    $data[$spec['shortname']][] = $spec;

etc.

[geiger]

I would consider this, but my script works very well at the moment, and building the array based on shortnames rather than fields would necessitate me rewriting a lot of code for little to no gain. Thanks, though!