PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Fri Nov 16, 2018 1:33 am

All times are UTC - 5 hours




Post new topic Reply to topic  [ 3 posts ] 
Author Message
PostPosted: Thu Dec 18, 2014 4:44 pm 
Offline
Forum Newbie

Joined: Thu Aug 14, 2014 5:46 am
Posts: 5
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:
Syntax: [ Download ] [ Hide ]
<?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:
Syntax: [ Download ] [ Hide ]
<?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!!!!!!!!!!!


Last edited by requinix on Thu Dec 18, 2014 6:44 pm, edited 1 time in total.
missing a quote that messed up highlighting


Top
 Profile  
 
PostPosted: Thu Dec 18, 2014 9:36 pm 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6617
Location: WA, USA
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.


Top
 Profile  
 
PostPosted: Fri Dec 19, 2014 12:20 am 
Offline
Site Administrator
User avatar

Joined: Wed Aug 25, 2004 7:54 pm
Posts: 13583
Location: New York, NY, US
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?

_________________
(#10850)


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 3 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 7 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group