PHP - Querying Table Results

My db is called localDB, and the table in that db is called MonthlySales. I want to display the table in a table in php. But to be able to filter the table by the column year, so if 2000 is selected only the values with 2000 are shown. Is there also a better way to populate the drop down menu?

My script looks like:

Code: [Select]
<form action='<?php echo $_SERVER['PHP_SELF']; ?>' method='post' name='form_filter' >
     
    <select name="value">
        <option value="all">All</option>
        <option value="2000">2000</option>
        <option value="2001">2001</option>
    </select>
     
    <input type='submit' value = 'Filter'>

</form>

   
    <?php
    $link = mysql_connect('localhost', 'root', 'root');
    if (!$link) {
    die('Could not connect: ' . mysql_error());
    }

 $db_selected = mysql_select_db('localDB', $link);
    if (!$db_selected) {
        die (mysql_error());
    }

// process form when posted 
if(isset($_POST['value'])) 
{ 
if($_POST['value'] == '2000') 
{  
$query = "SELECT * FROM MonthlySales WHERE Year='2000'";   
}   
elseif($_POST['value'] == '2001') 
{   
$query = "SELECT * FROM MonthlySales WHERE Year='2001'";   
} 
else 
{   
$query = "SELECT * FROM MonthlySales";   
}   
$sql = mysql_query($query);   
 
while ($row = mysql_fetch_array($query))
{  
$Id = $row["Id"];  
$ProductCode = $row["ProductCode"];  
$Month = $row["Month"];  
$Year = $row["Year"]; 
$SalesVolume = $row["SalesVolume"]; 
 
echo "<tr>";
   echo "<td>" . $row['Id'] . "</td>";
   echo "<td>" . $row['ProductCode'] . "</td>";
echo "<td>" . $row['Month'] . "</td>";
echo "<td>" . $row['Year'] . "</td>";
echo "<td>" . $row['SalesVoulme'] . "</td>";
   echo "</tr>";
} 
mysql_close($con);  
} 
?>

Does anybody know how to show the below php results in a table format?

<?php

if(isset($_POST['submit'])){
if(isset($_GET['go'])){

$fname = $_POST['fname'];
$lname = $_POST['lname'];
$skill = $_POST['skill'];
   
//connect  to the database
$db=mysql_connect  ("127.0.0.1", "root",  "") or die ('I cannot connect to the database  because: ' . mysql_error());
   
//select  the database to use
 $mydb=mysql_select_db("resource matrix");
   

//query  the database table
$sql="SELECT DISTINCT First_Name, Last_Name, l.Resource_ID FROM ((resource l inner join resource_skill ln on l.Resource_ID = ln.Resource_ID) inner join skill n on ln.Skill_ID = n.Skill_ID) WHERE First_Name LIKE '$fname' OR Last_Name LIKE '$lname' OR Skill_Name LIKE '$skill'";
   
//run  the query against the mysql query function
$result=mysql_query($sql);
   
//create  while loop and loop through result set
while($row=mysql_fetch_array($result)){
$First_Name  =$row['First_Name'];
$Last_Name=$row['Last_Name'];
$Resource_ID=$row['Resource_ID'];
   
//display the result of the array
echo "<ul>\n";
echo "<li>" . "<a  href=\"a.php?id=$Resource_ID\">"   .$First_Name . " " . $Last_Name .  "</a></li>\n";
echo "</ul>";
}
}
else{
echo  "<p>Please enter a search query</p>";

}
}

Hi guys,

I am working with an old script at the moment, there is one page which just will not populate the table results.
I have tried running multiple debugging commands but the only one it flags is the line displaying Quote

last;

saying it's not a used function.
If I comment out this line, no errors are produced but the results do not enter the table.
Can anyone shed some light on this please, I've spent hours and hours and banging my head against a brick wall would probably be more constructive right now.

Many thanks indeed for any help or advice.

<?php

mysql_connect("localhost", "$db_user","$db_upwd") or die ("Error - Could not connect: " . mysql_error()); 
mysql_select_db("$database");

$query="select host,count(*) from badc_mis_prog group by host";
$result = mysql_query($query) or die ("Error - Query: $query" . mysql_error());

$count=0;
$hosts=array();
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
    $html_hlname=$row[0];
    $html_hlname=preg_replace("/</","&lt;",$html_hlname);
    $html_hlname=preg_replace("/>/","&gt;",$html_hlname);
            array_push($hosts, $html_hlname,$row[1],0);
    $count++;
        }

$query="select host,count(*) from badc_mis_prog where reported=1 group by host";
$result = mysql_query($query) or die ("Error - Query: $query" . mysql_error());
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
    $html_hlname=$row[0];
    $html_hlname=preg_replace("/</","&lt;",$html_hlname);
    $html_hlname=preg_replace("/>/","&gt;",$html_hlname);
            for ($i=0; $i<($count*3); $i+=3) {
        if ($hosts[$i] == $html_hlname) {
    $hosts[($i+2)]=$row[1];
   last;
}
    }
        }

for ($i=0 ; $i<(($count-1)*3); $i+=3){
    for ($j=$i+3 ; $j<($count*3); $j+=3){
        if ($hosts[($i+1)] < $hosts[($j+1)]){
            $temp=array();
    $temp[0]=$hosts[$i];
    $temp[1]=$hosts[($i+1)];
    $temp[2]=$hosts[($i+2)];
    $hosts[$i]=$hosts[$j];
    $hosts[($i+1)]=$hosts[($j+1)];
    $hosts[($i+2)]=$hosts[($j+2)];
    $hosts[$j]=$temp[0];
    $hosts[($j+1)]=$temp[1];
    $hosts[($j+2)]=$temp[2];
                }
    }
}
print "<br><br><br><center><table border=\"1\">\n";
print "<tr><td>Host Name</td><td>Hosted</td><td>Reported</td><td>Ratio H/R</td></tr>\n";

for ($i=0; $i<($count*3); $i+=3) {
   if ($hosts[($i+1)]<15){ break;}
           printf ("<tr><td>&nbsp; %s &nbsp;</td><td> %d </td><td> %d </td><td>%.1f %%</td></tr>\n",$hosts[$i],$hosts[($i+1)],$hosts[($i+2)],(($hosts[($i+2)]/$hosts[($i+1)])*100));
}

print "</table></center>\n";


?>

Hi,
I'm quite new to this and I'm trying to get this to line up in a table with 3 columns (unlimited rows)
I have searched and tried but I'm not having any luck.

Can any one help?
Thank you

Code: [Select]
<?php 
echo '<div class="resultados_sub_cat">';

foreach($this->subcats as $key => $subcat) {

$subcat->link = JRoute::_('index.php?option=classcliff&view=list&catid='.$subcat->id."&Itemid=".$this->Itemid);
if ($key != 0)
echo ' - ';
echo '<a href="'.$subcat->link.'">'.$subcat->name.'</a>';

} 
?>

Ive gotten some results user selcts check box on first page
The php page will say which brackets it falls between example price is between 100-300 say

I dont seem to be able to populate a table with the data in the database:S

Code: [Select]
$row_number= 0;

while ($row = mysql_fetch_array($result3))
    {
If  (($row["price"] = $price_low) && ($row["price"] <= $price_high)) //price
If (($row["storage"] = $storage_low) && ($row["storage"] <= $storage_high)) //storage
if (($row["Processor "] = $processor_low) && ($row["Processor"] <= $processor_high))  //Processor
{ $row_number++;

?>

<tr>

<td align="center"><?php print $row["Computer_Price"]; ?> </td>
<td align="center"><?php print $row["Computer_Storage"]; ?> </td>
<td align="center"><?php print $row["Computer_ProcessorSpeed"]; ?> </td>

</tr>
</table>
Thats wat im usign atm the price_low and price_high are what sets the low and high price for the search

  i think this is more of a HTML problem, but it is in my PHP code so i will ask anyway!
if i have the search code:


 if (count($error) < 1) {
      $searchSQL = "SELECT id, placing, racedate, horseid FROM race WHERE ";
     
      // grab the search types.
      $types = array();
      $types[] = isset($_GET['placing'])?"`placing` LIKE '%{$searchTermDB}%'":'';
      $types[] = isset($_GET['racedate'])?"`racedate` LIKE '%{$searchTermDB}%'":'';
      $types[] = isset($_GET['horseid'])?"`horseid` LIKE '%{$searchTermDB}%'":'';
     
      $types = array_filter($types, "removeEmpty"); // removes any item that was empty (not checked)
     
      if (count($types) < 1)
         $types[] = "`placing` LIKE '%{$searchTermDB}%'"; // use the body as a default search if none are checked
     
          $andOr = isset($_GET['matchall'])?'AND':'OR';
      $searchSQL .= implode(" {$andOr} ", $types) . " GROUP BY `horseid` ORDER BY `racedate`"; // order by title.

      $searchResult = mysql_query($searchSQL) or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");
     
      if (mysql_num_rows($searchResult) < 1) {
         $error[] = "The search term provided {$searchTerms} yielded no results.";
      }else {
         $results = array(); // the result array
         $i = 1;
echo '<table border="1"><tr><th>Num</th><th>Placing</th><th>Race Date</th><th>Horse ID</th></tr>';
while ($row = mysql_fetch_assoc($searchResult))
{
   echo "<tr><td>$i</td>
                    <td>{$row['placing']}</td>
                    <td><a href=\"viewrace.php?date=" . urlencode($row['racedate']) . "\">{$row['racedate']}</a></td>
                    <td>{$row['horseid']}</td></tr>";
   $i++;
}
echo '</table>';

how do i get the 'horseid' to appear at the header of the table rather than in a column?

thank you for all the brilliant help this site provides!!

Hi

I have the following code:

Code: [Select]
$result = mysql_query("SELECT * FROM xbox_games order by gameid");
$row = mysql_fetch_assoc($result);
$id = $row["gameid"];
$title = $row["gametitle"];
$cover = $row["cover"];

                                <?php echo $title;?>

Which displays only the first result from my database.

How can i change this to display all the results either as a list, or in a table?

Thanks

I have been trying to work out how to get my results into a 3 column layout using css and not using tables in any way.

I found the code for tables:

echo '<table>'; 

$counter = 0; 
$cells_per_row = 3; 
while($row=mysql_fetch_array($result)) 
{ 
    $counter++; 
    if(($counter % $cells_per_row) == 1)  { echo '<tr>'; } 
    echo '<td>' . (whatever you echo from your $row array) . '</td>'; 
    if(($counter %  $cells_per_row) == 0) { echo '</tr>'; } 
} 
// just in case we haven't closed the last row 
// this would happen if our result set isn't divisible by $cells_per_row 
if(($counter % $cells_per_row) != 0) { echo '</tr>'; } 

echo '</table>'; 


How Can I adapt this or how should I use divs here?
I am fine with the css code, just need to work out how to get the 3 column layout correct in the loop.

Hi Guys.

I have some code which displays the rows of a database table in a html table on my webpage:

Code: [Select]
<?php//to display image from source$dir = "360_covers";echo '<table>';echo '<tr><th>Title</th><th>Cover</th><th>comment</th></tr>';$result = mysql_query("SELECT * FROM xbox_games order by gametitle");while ($row = mysql_fetch_assoc($result)) {    echo "<tr><td>{$row['gametitle']}</td><td><img src=\"$dir/{$row['cover']}\" width='38' height='38'></td><td>{$row['cover']}</td></tr>";}//close out tableecho '</table>';?>

What i want to do now is add a form, possibly at the end of each row of the html table, which will allow a session user to update that record.

Can anybody advise on the best way to do this?

How would I force a certain table format and then populate it with the SQL results in their respective columns and rows?

i.e.

Code: [Select]
+------------------+----------+----------+----------+
|     | Col 1    | Col 2    | Col 3    |
+-------------------+----------+----------+----------+
| row 1 lab data    | 16777216 |   | 573      |
+-------------------+----------+----------+----------+
| row 2 lab data    | 23454235 | 87247247 | 65743    |
+-------------------+----------+----------+----------+
| row 3 lab data    | 16777216 | 47832364 |          |
+-------------------+----------+----------+----------+
Currently I'm just using a while loop to populate it horizontally, but that doesn't work for row 1.

Code: [Select]
<?PHP
while($row = mysql_fetch_array($result)
{
?> blah blah blah html here <?PHP echo $row[0]; ?> more html <?PHP echo $row[1]; ?>
and so forth
<?PHP } ?>

It ends up producing this:

Code: [Select]

+------------------+----------+----------+----------+
|     | Col 1    | Col 2    | Col 3    |
+-------------------+----------+----------+----------+
| row 1 lab data    | 16777216 | 573   |      |
+-------------------+----------+----------+----------+
| row 2 lab data    | 23454235 | 87247247 | 65743    |
+-------------------+----------+----------+----------+
| row 3 lab data    | 16777216 | 47832364 |          |
+-------------------+----------+----------+----------+

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=312470.0

Hi everyone, I'm having real problems trying to retrieve database records in a 3 column layout, I got there eventually with a huge amount of help.

Unfortunately the code will only display records that are divisable by by three; for example for a database table that has 45 records there is no problem, as all records can be displayed in a 3 column layout. However if the table contains 47 records it won't display the 2 odd records.  My php skills are limited so I need all the help I can get.

I'm a bit desperate to sort this out for a project I'm doing, any help would be greatly appreciated - here is the php code:


$total = count($records);
  $nocol = 3;
  $norows = $total / $nocol;

for ($i=1; $i <= $norows; $i++) {
    $cell = 0;
    echo "<tr>";
    for($col=1; $col <= $nocol; $col++) {
       echo "<td>";
       if
       ($col == 1) {
       $cell += $i;
       echo '<strong class="navtext">'.$records[$cell - 1]['ret_name'].'</strong><br />';
       echo $records[$cell - 1]['ret_add1'].'<br />';
       echo $records[$cell - 1]['ret_add2'].'<br />';
       echo $records[$cell - 1]['ret_town'].'<br />';
       echo $records[$cell - 1]['ret_county'].'<br />';
       echo $records[$cell - 1]['ret_pcode'].'<br />';
       echo $records[$cell - 1]['ret_phone'].'<br />';
       echo $records[$cell - 1]['ret_email'].'<br />';
       echo $records[$cell - 1]['ret_web'].'<br />';
       } else {
        $cell += $norows;
       echo '<strong class="navtext">'.$records[$cell - 1]['ret_name'].'</strong><br />';
       echo $records[$cell - 1]['ret_add1'].'<br />';
       echo $records[$cell - 1]['ret_add2'].'<br />';
       echo $records[$cell - 1]['ret_town'].'<br />';
       echo $records[$cell - 1]['ret_county'].'<br />';
       echo $records[$cell - 1]['ret_pcode'].'<br />';
       echo $records[$cell - 1]['ret_phone'].'<br />';
       echo $records[$cell - 1]['ret_email'].'<br />';
       echo $records[$cell - 1]['ret_web'].'<br />';
       
       }
       echo"</td>";
    }
      echo"</tr>";
} 


I'm also trying to paginate the results, is this actually possible when using a three column layout?  I look forward to any suggestions.

//First I'm assigning a $variable ($emailzipmatch) to query a database table called(repzipcodes) and having it pull and display 1 to 3 records based on matching up a customer's zip code (RepZipCode = $CustomerZipMatch) with 1 to 3 other people (GROUP BY RepId HAVING COUNT(1) <= 3") that want that customer's information from that particular zip code. <This code works fine>

// CODE WORKS BELOW
Code: [Select]
<?php
    $emailzipmatch = mysql_query("SELECT * FROM repzipcodes WHERE RepZipCode = $CustomerZipMatch GROUP BY RepId HAVING COUNT(1) <= 3") or die(mysql_error());

    $recipients = array();
    while($row = mysql_fetch_array($emailzipmatch)) 
    { 
    $recipients[] = $row['RepEmail'];

    echo "Agent's Email Address: ";
 
    echo 'font color="#FF7600"',$row['RepEmail'], '/font';
 
    echo '<br />'; 

    echo "Rep's ID: "; 
    echo '<br />';
    echo 'font color="#FF7600"',$row['RepId'], '/font';
    echo '<br />';
    echo 'hr align="left" width="50%" size="2" /';
    }


//MY PROBLEM BELOW

// For the NEXT step of the process above I would take $row['RepEmail'] and $row['RepId'] which can have 1 to 3 results and assign the 1 to 3 results a new $variable so it can be inserted into a different db table so I can track the results of the query ($emailzipmatch = ) from the top of the page: ie..

    <New Variable>   <Listed from above>
    $SentRepId 0   = RepId (results from above echo area)
    $SentRepId 1   = RepId (results from above echo area)
    $SentRepId 2   = RepId (results from above echo area)


// Below I'd like to insert the above results into a new database

    $?Variable??? = mysql_query("INSERT INTO sentemail 
   (SentRepId0, SentRepId1, SentRepId2,SentDateTime
   ) VALUES (
   '$_SESSION[RepId]', // ?????
   '$_SESSION[RepId]', // ?????
   '$_SESSION[RepId]', // ?????
    NOW()
     )") or die(mysql_error());

Thank ahead of time for any help you guys can give me. Please respond with ANY question if my coding or request isn't clear or if I've been confusing due to my lack of experience with PHP and MySQL.


MOD EDIT: code tags added.

Hello everyone !

I am currently developing a module for my site that will allow me to classify each solution to help a disability by type.
For example, here's an example:

Type : Visual disability
Solution : Add screen readers

For this, I have a database in which these types and solutions are found. In this database, the solutions of one type or another are not in a defined order. They can be put randomly, when a user adds one.

I need to display, in a table, these Types first (as a title), then the solutions corresponding to this type.

I manage to generate a display thanks to a foreach loop, but my solutions are all mixed up and therefore not clearly sorted.

This is how it is currently displayed

Visual disability

    Audio-described cut-scenes

    Highlighted path to follow

    Sensitivity settings for all the controls

    Screen readers on menus

    Slow down the game speed

But I would need it to look like this:

Visual disability

    Audio-described cut-scenes

    Highlighted path to follow

    Screen readers on menus

    Slow down the game speed

Physical disability

    Sensitivity settings for all the controls

In my example above, only the solution "Sensitivity settings for all the controls" is part of the "Physical disability" type. The rest is part of the "Visual disability" type.

Here is the code I currently use to retrieve my information from my database and display it. It's a Wordpress.

 

/*====== Accessibility Options ======*/

function cloux_accessibility_options() {
    if ( function_exists( 'rwmb_meta' ) ) {
        $output = "";
        $accessibility_options = rwmb_meta( 'accessibility-options' );
        $accessibility_options_status = rwmb_meta( 'accessibility-options-status' );
        if( $accessibility_options_status == "1" ) {
            if( !empty( $accessibility_options ) ) {
                $output .= '<div class="accessibility-options widget-box">';
                    $output .= cloux_title( $title = esc_html__( 'Accessibility Options', 'cloux' ), $style = "style-4", $align = "left", $colored_title = '' );
                    $output .= '<ul>';
                        $output .= '<li class="title visual_disability">' . esc_html__( 'Visual disability', 'cloux' ) . '</li>';
                    $output .= '</ul>';

                        foreach ( $accessibility_options as $accessibility_options ) {
                            if( !empty( $accessibility_options ) ) {
                                $accessibility_name =  isset( $accessibility_options['accessibility-options-name'] ) ? $accessibility_options['accessibility-options-name'] : '';
                                $accessibility_type =  isset( $accessibility_options['accessibility-type-name'] ) ? $accessibility_options['accessibility-type-name'] : '';
                                $accessibility_type = get_term( $accessibility_type, 'accessibility' );
                                $accessibility_type_name = $accessibility_type->name;
                                $availability =  isset( $accessibility_options['availability'] ) ? $accessibility_options['availability'] : '';

                            if ( $accessibility_type_name == "Visual disability" ){
                                if( !empty( $accessibility_name ) or !empty( $availability )) {
                        $output .= '<ul>';
                                    $output .= '<li class="item accessibility_name">';
                                        if( !empty( $accessibility_name ) ) {
                                            $accessibility_name = get_term( $accessibility_name, 'accessibility' );
                                            if( ( !empty( $accessibility_name ) ) ) {
                                                $output .= '<a href="' . get_term_link( $accessibility_name ) . '">' . esc_attr( $accessibility_name->name ) . '</a>';
                                            }
                                        }
                                    $output .= '</li>';
                                    $output .= '<li class="item visual_disability status">';
                                        if( $availability == "1" ) {
                                            $output .= '<i class="fas fa-check" aria-hidden="true"></i>';
                                        } else {
                                            $output .= '<i class="fas fa-times" aria-hidden="true"></i>';
                                        }
                                    $output .= '</li>';
                                }
                            }
                            
                            
                            if ( $accessibility_type_name == "Motor/Physical disability" ){
                                if( !empty( $accessibility_name ) or !empty( $availability )) {
                        $output .= '<ul>';
                                    $output .= '<li class="item accessibility_name">';
                                        if( !empty( $accessibility_name ) ) {
                                            $accessibility_name = get_term( $accessibility_name, 'accessibility' );
                                            if( ( !empty( $accessibility_name ) ) ) {
                                                $output .= '<a href="' . get_term_link( $accessibility_name ) . '">' . esc_attr( $accessibility_name->name ) . '</a>';
                                            }
                                        }
                                    $output .= '</li>';
                                    $output .= '<li class="item visual_disability status">';
                                        if( $availability == "1" ) {
                                            $output .= '<i class="fas fa-check" aria-hidden="true"></i>';
                                        } else {
                                            $output .= '<i class="fas fa-times" aria-hidden="true"></i>';
                                        }
                                    $output .= '</li>';
                                }
                            }
                            
                            
                        $output .= '</ul>';
                        }
                    }
                $output .= '</div>';
            }
        }
        return $output;
    }
}

Thank you in advance for all the help you can give me!

 

Edited January 13 by Sagaroth

Hello, 
I know this should be pretty simple to figure out, but everything I try is giving me absolutely no results. 
I have a mysql query selecting columns from my database and returning results. I have the results printing out right now, so I can see that this part is working. 

All I want to do is take the results and put them into a table to display on my page. Basically, take what's in the database table and copy it to a table I can put on the web. 

FYI I am using sourcerer so the "connect" code is taken care of for me in the "JFactory" bit of code. Here is the first part of my code, selecting the information from the database. 

{source} 
<?php 
$db = JFactory::getDbo(); 
$query = $db -> getQuery(true); 

$query -> SELECT($db -> quoteName(array('first_dept_name', 'last_name', 'dept', 'position', 'phone_num'))); 
$query -> FROM ($db -> quoteName('#__custom_contacts')); 
$query -> ORDER ('first_dept_name DESC'); 
$query -> WHERE ($db -> quoteName('contact_category')."=".$db -> quote('YTown Employees')); 
$db -> setQuery($query); 
$results = $db -> loadObjectList(); 

print_r($results); 

Here is where I am trying to print the results into a table. I got this code directly from a PHP book, but I am getting nothing at all returned back to me. I get table headers, but no data. 

<?php 

echo "<table><tr><th>Name</th><th>Department</th></tr>"; 

while ($row = mysqli_fetch_array ($result)){ 

echo "<tr>"; 

echo "<td>".$row['last_name']."</td>"; 

echo "<td>".$row['dept']."</td>"; 

echo "</tr>"; 

} 

echo "</table>"; 

?> 
{/source}