Page 1 of 1

I need help combining 2 php queries into 1!

Posted: Thu Dec 18, 2014 3:44 pm
by Disead
Hey guys! Thanks again for all your previous help, especially Celauran who got me a working, up and running .php page for my company ^_^

Now, as always, the journey continues and I need a little help again. I have posted the working .php pages that I use below. There are as of now, 2 different php queries, shown stacked on top of each other on one web page using two separate iframes. One php page displays results searched from a sql table named POPTABLE, the other from a table named SMQSQL.

My question is this; how would I MODIFY my CURRENT php queries to combine them into one? Basically have one user input/query/search button to search two tables and display results from both, either in the same array or in separate ones? Currently, each php and results array is displayed separately in different iframe panes on one page, and the user has to input a query into separate search boxes and hit separate search buttons to get results, one table at a time.

Ideally, I would very much like to be able to have several radio buttons and a single search bar. The radio buttons would allow the user to search the SMQ (SMQSQL table), the POP (POPTABLE table) or both at the same time and list the data concurrently in the same output table, but can remain in separate tables/arrays if that is easier. Would this be possible? I have included a screenshot with results displayed in the two separate tables; to get results in both, the search term "300" had to be put into BOTH boxes separately, one at a time. They are displayed with iframes that point to separate php pages stored on my website. You can see how similar the data is. THANK YOU!!!

Image

Here is my php:

Code: Select all

<?php

$db = [
        'type'     => 'mysql',
        'host'     => 'localhost',
        'username' => '******************',
        'password' => '****************',
        'dbname'   => 'SMQ',
];

$dsn = "{$db['type']}:hostname={$db['host']};dbname={$db['dbname']}";

function getAllRows(PDO $pdo) {
        $query = "SELECT * FROM `POPTABLE` ORDER BY `LINEID` ASC";
        $result = $pdo->query($query);

        return $result ? $result->fetchAll(PDO::FETCH_ASSOC) : [];
}

function getSelectedRows(PDO $pdo, $search) {
        $query = "SELECT * FROM `POPTABLE` WHERE `Scott` = :search ORDER BY `LINEID` ASC";
        $stmt = $pdo->prepare($query);
        $exec = $stmt->execute([':search' => $search]);

        $results = [];
        if ($exec && $stmt->rowCount()) {
                $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
        }

        return $results;
}

$data = [];
$error;
try {
        $pdo  = new PDO($dsn, $db['username'], $db['password']);
        if (isset($_POST['search'])) {
    $data = getSelectedRows($pdo, $_POST['search']);
}
} catch (Exception $e) {
        $error = $e->getMessage();
}

?>

<!DOCTYPE html>
<html>
        <head>
                <meta charset="utf-8">
                <link rel="stylesheet" href="//maxcdn.bootstrapcdn.com/bootstrap/3.2.0/css/bootstrap.min.css">
        </head>
        <body>
                <?php if ($error): ?>
                <div class="row">
                        <p class="alert alert-danger col-md-8 col-md-offset-2"><?= $error; ?></p>
                </div>
                <?php endif; ?>

                <form role="form" method="post" action="" class="form col-md-6 col-md-offset-3">
                        <div class="form-group col-xs-10">
                                <input type="text" name="search" class="form-control" placeholder="Enter Scott Catalog Number Here">
                        </div>
                        <button type="submit" class="btn btn-primary">Search</button>
                </form>

                <br><br>
                <p>
                
                      <table class="table table-striped">
                        <tr>
                               
                                <th>Scott</th>
                                <th>Denom</th>
                                <th>Cond</th>
                                <th>Total</th>
                                <th>50</th>
                                <th>70</th>
                                <th>70J</th>
                                <th>75</th>
                                <th>75J</th>
                                <th>80</th>
                                <th>80J</th>
                                <th>85</th>
                                <th>85J</th>
                                <th>90</th>
                                <th>90J</th>
                                <th>95</th>
                                <th>95J</th>
                                <th>98</th>
                                <th>98J</th>
                                <th>100</th>
                                <th>100J</th>
                        </tr>

                        <?php if (empty($data)): ?>
                        <tr>
                                <td colspan="21" class="text-center">No results to display</td>
                        </tr>
                        <?php else: ?>
                                <?php foreach ($data as $row): ?>
                                <tr>
                                        
                                        <td><?= $row['Scott']; ?></td>
                                        <td><?= $row['Denom']; ?></td>
                                        <td><?= $row['Cond']; ?></td>
                                        <td><?= $row['Total']; ?></td>
                                        <td><?= $row['50']; ?></td>
                                        <td><?= $row['70']; ?></td>
                                        <td><?= $row['70J']; ?></td>
                                        <td><?= $row['75']; ?></td>
                                        <td><?= $row['75J']; ?></td>
                                        <td><?= $row['80']; ?></td>
                                        <td><?= $row['80J']; ?></td>
                                        <td><?= $row['85']; ?></td>
                                        <td><?= $row['85J']; ?></td>
                                        <td><?= $row['90']; ?></td>
                                        <td><?= $row['90J']; ?></td>
                                        <td><?= $row['95']; ?></td>
                                        <td><?= $row['95J']; ?></td>
                                        <td><?= $row['98']; ?></td>
                                        <td><?= $row['98J']; ?></td>
                                        <td><?= $row['100']; ?></td>
                                        <td><?= $row['100J']; ?></td>
                                </tr>
                                <?php endforeach; ?>
                        <?php endif; ?>
                </table>
        </body>
</html>
 
That was the POPTABLE php. It generates the second array.

Here is the next:

Code: Select all

<?php

$db = [
        'type'     => 'mysql',
        'host'     => 'localhost',
        'username' => '****************************',
        'password' => '***************',
        'dbname'   => 'SMQ',
];

$dsn = "{$db['type']}:hostname={$db['host']};dbname={$db['dbname']}";

function getAllRows(PDO $pdo) {
        $query = "SELECT * FROM `SMQSQL` ORDER BY `LINEID` ASC";
        $result = $pdo->query($query);

        return $result ? $result->fetchAll(PDO::FETCH_ASSOC) : [];
}

function getSelectedRows(PDO $pdo, $search) {
        $query = "SELECT * FROM `SMQSQL` WHERE `Scott` = :search ORDER BY `LINEID` ASC";
        $stmt = $pdo->prepare($query);
        $exec = $stmt->execute([':search' => $search]);

        $results = [];
        if ($exec && $stmt->rowCount()) {
                $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
        }

        return $results;
}

$data = [];
$error;
try {
        $pdo  = new PDO($dsn, $db['username'], $db['password']);
        if (isset($_POST['search'])) {
    $data = getSelectedRows($pdo, $_POST['search']);
}
} catch (Exception $e) {
        $error = $e->getMessage();
}

?>

<!DOCTYPE html>
<html>
        <head>
                <meta charset="utf-8">
                <link rel="stylesheet" href="//maxcdn.bootstrapcdn.com/bootstrap/3.2.0/css/bootstrap.min.css">
        </head>
        <body>
                <?php if ($error): ?>
                <div class="row">
                        <p class="alert alert-danger col-md-8 col-md-offset-2"><?= $error; ?></p>
                </div>
                <?php endif; ?>

                <form role="form" method="post" action="" class="form col-md-6 col-md-offset-3">
                        <div class="form-group col-xs-10">
                                <input type="text" name="search" class="form-control" placeholder="Enter Scott Catalog Number Here">
                        </div>
                        <button type="submit" class="btn btn-primary">Search</button>
                </form>

                <br><br>
                <p>
                
                      <table class="table table-striped">
                        <tr>
                               
                                <th>Scott</th>
                                <th>Den</th>
                                <th>Color</th>
                                <th>Cond</th>
                                <th>70</th>
                                <th>70J</th>
                                <th>75</th>
                                <th>75J</th>
                                <th>80</th>
                                <th>80J</th>
                                <th>85</th>
                                <th>85J</th>
                                <th>90</th>
                                <th>90J</th>
                                <th>95</th>
                                <th>95J</th>
                                <th>98</th>
                                <th>98J</th>
                                <th>100</th>
                        </tr>

                        <?php if (empty($data)): ?>
                        <tr>
                                <td colspan="21" class="text-center">No results to display</td>
                        </tr>
                        <?php else: ?>
                                <?php foreach ($data as $row): ?>
                                <tr>
                                        
                                        <td><?= $row['Scott']; ?></td>
                                        <td><?= $row['Den']; ?></td>
                                        <td><?= $row['Color']; ?></td>
                                        <td><?= $row['Cond']; ?></td>
                                        <td><?= $row['70']; ?></td>
                                        <td><?= $row['70J']; ?></td>
                                        <td><?= $row['75']; ?></td>
                                        <td><?= $row['75J']; ?></td>
                                        <td><?= $row['80']; ?></td>
                                        <td><?= $row['80J']; ?></td>
                                        <td><?= $row['85']; ?></td>
                                        <td><?= $row['85J']; ?></td>
                                        <td><?= $row['90']; ?></td>
                                        <td><?= $row['90J']; ?></td>
                                        <td><?= $row['95']; ?></td>
                                        <td><?= $row['95J']; ?></td>
                                        <td><?= $row['98']; ?></td>
                                        <td><?= $row['98J']; ?></td>
                                        <td><?= $row['100']; ?></td>
                                </tr>
                                <?php endforeach; ?>
                        <?php endif; ?>
                </table>
        </body>
</html>
 
That is the SMQSQL table. It produces the top array.

Hopefully I was clear enough to understand what is going on; if not, I am more than happy to answer any questions!!!

Thank you!!!

PS While I am at it, how would I add a dropdown box that would have choices like "set 1, set 2, set 3 etc." that when clicked would display a set of results that have a corresponding ID in the table? THANKS A MILLION!!!!!!!!!!!

Re: I need help combining 2 php queries into 1!

Posted: Thu Dec 18, 2014 8:36 pm
by requinix
Moving this to the normal forum since this isn't so hard to do...

Quick question, first: how would the two tables be combined? They both have a number of columns in common but there are a few unique to each table.

Putting the two tables on the same page (and not combining them) is mostly a matter of copying and pasting code from one file into the right place in the other file. Would have to get rid of the function, though, but you could just not use the function and put its code inline with the script.

Re: I need help combining 2 php queries into 1!

Posted: Thu Dec 18, 2014 11:20 pm
by Christopher
Have you just tried JOINing both tables to see what you get? E.g., SELECT * FROM POPTABLE JOIN SMQSQL. It would take a minute to try. MySQL may just sort it out for you.

I am wondering why you don't just make the schema of POPTABLE and SMQSQL the same? The differences are the name of the Den/Denom column and the columns 50 and 100J. It might require some small code/query changes but should be easy to do. Why are there two almost identical tables anyway?