Filtering php table from SQL

Question

cscheck 0 Newbie Poster

12 Years Ago

Hello all,

Correct me if I mis-post or should have posted this somewhere else. This is my first thread here so bear with me. I have had a lot of luck just browsing other issues to get my problems solved in the past, but this time I am stumped. More so for my lack of knowledge than anything.

I built an SQL db, and a few php pages to interact with that database. You can create a new record, edit an existing record, or delete a record from various php pages. I can provide code for the new, edit, and delete php pages if required.

My main view page code is as follows.

<html>
<head>
        <title>View Licenses</title>
</head>
<body>
<?php
/* 
        VIEW.PHP
        Displays all data from 'sw_lic' table
*/

        // connect to the database
        include('connect-db.php');

        // get results from database
        $result = mysql_query("SELECT * FROM sw_lic ORDER by dept, username") 
                or die(mysql_error());  

        // display data in table
        echo "<table border='1' cellpadding='10'>";
        echo "<tr> <th>Username</th> <th>System Name</th> <th>Dept.</th> <th>Software Name</th> <th>Software Ver.</th> <th>LIC Key</th> <th></th> <th></th></tr>";

        // loop through results of database query, displaying them in the table
        while($row = mysql_fetch_array( $result )) {

                // echo out the contents of each row into a table
                echo "<tr>";

                echo '<td>' . $row['username'] . '</td>';
                echo '<td>' . $row['sysname'] . '</td>';
                echo '<td>' . $row['dept'] . '</td>';
                echo '<td>' . $row['sw_name'] . '</td>';
                echo '<td>' . $row['sw_ver'] . '</td>';
                echo '<td>' . $row['sw_key'] . '</td>';
                echo '<td><a href="edit.php?sw_id=' . $row['sw_id'] . '">Edit</a></td>';
                echo '<td><a href="delete.php?sw_id=' . $row['sw_id'] . '">Delete</a></td>';
                echo "</tr>"; 

        } 
        echo "</table>";
?>



</body>

<p><INPUT TYPE="BUTTON" VALUE="Add New Record"onClick="location.href='new.php'"></p>
<p><input TYPE="button" VALUE="Print" onClick="window.open('NB_sw_lic/view.php')"></p>
</html>

Next I have built a <select> field to populate based on the dept field values in the db, and then group by them to slim down the <options> shown...(code for that)

<?php

$sql = "SELECT dept, sw_id FROM sw_lic GROUP by dept";
$result = mysql_query($sql);

echo "<select name='dept_filter' id='dept_filter' onchange='this.form.submit()'>";
while ($row = mysql_fetch_array($result)) {
    echo "<option value='" . $row['dept'] . "'>" . $row['dept'] . "</option>";
}
echo "</select>";

?>
<p><INPUT TYPE="BUTTON" VALUE="Filter" onClick="SELECT * FROM sw_lic WHERE dept = $_POST['dept_filter']"></p>

I want to place the previous code at the end of the document. I can place the code with no issues at all, but it just doesn't fuction. I am a complete novice at coding and most of this code was used from other forums and open source coding sites.

I want to be able to select my option from the <select> field, and then have it run a SELECT command on the db, then take the results to a new page called view.php. Can anyone elaborate on what I might search for in order to accomplish my goal, or maybe give me some coding examples as to how I could accomplish this?

Thanks so much in advance!!

php sql

3 Contributors
7 Replies
2K Views
2 Days Discussion Span
Latest Post 12 Years Ago Latest Post by cscheck

pritaeas 2,194 ¯\_(ツ)_/¯

12 Years Ago

onClick="SELECT * FROM sw_lic WHERE dept = $_POST['dept_filter']">

onClick is a Javascript event (on the client). You cannot put a query there, because that needs PHP code (on the server). Explain what you want to achieve with the dropdown.

pritaeas 2,194 ¯\_(ツ)_/¯

12 Years Ago

Instead of the onClick, put a form around the select and submit button, with form action pointing to a PHP script that handles the display of the selected department.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

cscheck 0 Newbie Poster · Answer 1 · 2012-07-17T16:24:56+00:00

Hey pritaeas, thanks for the response.

First, I would like a drop down field to pull options from the 'dept' field in the MySQL table.

When users select a department from the drop down I would like to show a table that is comprised of only to records that match that dept value that they selected in the drop down.

This table can be shown on a separate page, or nested in the same page, it really doesn't matter to me how its structured as I can change those things around a bit if needed.

Currently the 'edit' link and 'add new record' button call separate .php files. I'm game for whatever can be done to implement this function.

Thanks so much, again!

cscheck 0 Newbie Poster · Answer 2 · 2012-07-17T16:32:17+00:00

I will work in that direction. I might need help passing that variable but will report back once I have written the script and adjust the code, and I run into problems.

Thanks again.

CarterLangley 0 Newbie Poster · Answer 3 · 2012-07-18T13:23:04+00:00

pritaeas, can't he do a database search for the value he wants, and then put that value in a session variable, open a new page and using the session value, do what has to be done?

cscheck 0 Newbie Poster · Answer 4 · 2012-07-18T15:30:15+00:00

Well, I have once again confused the daylights out of myself. :) Here is what I have so far.

I have added the following code to the bottom of the view.php page.

    $sql = "SELECT DISTINCT dept FROM sw_lic WHERE dept != ''";
    $result2 = mysql_query($sql);
    echo "<form action='$GLOBALS[dept]/filter_view.php' method='POST'>";
    echo "<select name='dept'>";
    while($nt=mysql_fetch_array($result2))
    {
    echo "<option value=$nt[dept]>$nt[dept]</option>";
    }
    echo "</select>";

My confusion is how I actually pass the selected 'dept' to the filter_view.php page.

Here is my filter_view.php page...

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
        <title>Filtered View</title>
</head>
<body>
<?php
/* 
        filter_view.PHP
        Displays data from 'sw_lic' table that matches dropdown selection
        from view.php in a table...well, its supposed to anyway. :)
*/

        // connect to the database
        include('connect-db.php');

        // get results from database
        $result = mysql_query("SELECT * FROM sw_lic WHERE dept = .$nt. ORDER by username") 
                or die(mysql_error());  

        // display data in table
        echo "<p><b>View All</b> | <a href='view_paginated.php?page=1'>View Paginated</a></p>";
        echo "<table border='1' cellpadding='10' background-color='#000066' color='#FFFFFF'>";
        echo "<tr> <th>Username</th> <th>System Name</th> <th>Dept.</th> <th>Software Name</th> <th>Software Ver.</th> <th>LIC Key</th> <th></th> <th></th></tr>";

        // loop through results of database query, displaying them in the table
        while($row = mysql_fetch_array( $result )) {

                // echo out the contents of each row into a table
                echo "<tr>";

                echo '<td>' . $row['username'] . '</td>';
                echo '<td>' . $row['sysname'] . '</td>';
                echo '<td>' . $row['dept'] . '</td>';
                echo '<td>' . $row['sw_name'] . '</td>';
                echo '<td>' . $row['sw_ver'] . '</td>';
                echo '<td>' . $row['sw_key'] . '</td>';
                echo '<td><a href="edit.php?sw_id=' . $row['sw_id'] . '">Edit</a></td>';
                echo '<td><a href="delete.php?sw_id=' . $row['sw_id'] . '">Delete</a></td>';
                echo "</tr>"; 
        } 

        // close table>
        echo "</table>";
?>
<p><input TYPE="button" VALUE="Print" onClick="window.print();"></p>

</body>
</html>

I know I have probably butchered this but I am a noob. Remember, I'm self taught, so be gentle. :P lol

Thanks for any help in advance!

cscheck 0 Newbie Poster · Answer 5 · 2012-07-20T13:00:58+00:00

cscheck 0 Newbie Poster

12 Years Ago

any help plz?