PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Sat Sep 26, 2020 10:00 am

All times are UTC - 5 hours




Post new topic Reply to topic  [ 6 posts ] 
Author Message
PostPosted: Fri Aug 15, 2014 3:09 am 
Offline
Forum Newbie

Joined: Thu Aug 14, 2014 5:46 am
Posts: 5
I have been trying to do this myself, but it has been driving me insane and I turn to professionals here for help.

I am a basic web developer for a company that I work for in other capacities. I have a reasonable understanding of HTML and that is about where my expertise ends. I am not typically a programmer, just a simple (extremely) part time designer that uses Muse and Dreamweaver when necessary.

However, recently my company has asked me to accomplish a task for their website. In plain English, they want a large database that exists currently as a CVS file made into a searchable web page. It is 21 columns by approximately 6,700 rows.

To explain what I need a little more technically, here are my ideas and where I have gotten to so far:

1. The company uses godaddy, into which I *believe* I have successfully imported the spreadsheet. I believe it is successful because through godaddy's SQL Control Panel (phpMyAdmin console), I can do the EXACT searches that the company needs, and it spits out the EXACT results that I need.

2. The end result needs to be a .php that I can upload to the website's root folder that can be then inserted into premade pages using:
Syntax: [ Download ] [ Hide ]
<iframe src="SMQ.php" scrolling="yes" width="950" height="800"></iframe>


3. On the .php page, I need to have a way to log in to the SQL server and a simple search box built in that will allow the user to input a very simple search string consisting of no more than 4 numbers and 3 letters at a time. No buttons, no check boxes, just a search box.

3. This query then needs to be output as a nice data table, similar to this:

Image

This in fact is a screenshot of a search I performed out of my SQL database, in phpMyAdmin using the column "Scott" for the search, and the number 226 as the search term. All column names are visible with the exception of the first column, entitled LINEID, made to be the key, and the output should not include the key but have everything else as above.

4. I can see what the simple line of php is that performed this task:
Syntax: [ Download ] [ Hide ]
SELECT * FROM `SMQSQL` WHERE `Scott` = '226' ORDER BY `LINEID` ASC

but I can't figure out how the hell to get this incorporated to a .php search.

To sum it up, I need a .php page written that can connect to a SQL database, perform a data based search, and spit out a clean table when it is done.

I had accomplished this in the past using an import into google docs and using it to perform a search and result display via the following code built into a php called SMQ.php:

Syntax: [ Download ] [ Hide ]
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Example of Google Spreadsheet Data Visualisation</title>
</head>

<body>

<form id="form1" method="post" action ="<?php echo $_SERVER['PHP_SELF']; ?>"> <label>
<input id="search" name="search" type="text" />
</label>
<label>
<input id="Scott #" name="Scott #" type="submit" value="Scott #" />
</label>

<img src="loading.gif" width="16" height="11" />
</form>
<p>
<?php
$search= $_REQUEST['search'];
if ($search > ''){ $search = $search;} else { $search = '';}
?>
<script type="text/javascript" src="http://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load('visualization', '1', {packages: ['table']});
</script>
<script type="text/javascript">
var visualization;
function drawVisualization() {

var query = new google.visualization.Query(
'https://docs.google.com/spreadsheet/ccc?key=0AronCwm9QPefdGpIUllscGgtLUJod2pOazc0bjU0cUE&usp=sharing');

query.setQuery('SELECT A, B, C, D, E, F, G, H, I, J, K, L, M, N, O ,P ,Q ,R ,S ,T WHERE (A) LIKE ("<?php echo $search; ?>") order by A asc label A "Scott #", B "Den", C "Color", D "Cond", E "40", F "60", G "70", H "70J", I "75", J "75J", K "80", L "80J", M "85", N "85J", O "90", P "90J", Q "95", R "95J", S "98", T "98J"');
query.send(handleQueryResponse);
}

function handleQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + '' + response.getDetailedMessage());
return;
}

var data = response.getDataTable();

    visualization = new google.visualization.Table(document.getElementById('table'));
    visualization.draw(data, { page: 'enable', page: 16, pageSize: 16, legend: 'bottom'});
}
google.setOnLoadCallback(drawVisualization);
</script>

 <div id="table"></div>

</div>

</body>
</html>
 


But as you can see, this may not be the most secure thing in the world, plus we want to be able to expand it in the future and not be so simplistic, hence the need to switch to SQL.

Please let me know right away by contacting me at disead@gmail.com if this is something YOU might be able to help with. I'm sure for an experienced programmer, once you have the details from me that you need, it would take maybe 10 minutes to write. I don't have much, but I can pay a little bit for this one time job. If it ends up working out, I may be able to pay more down the line for more advanced options such as being able to do drop-down searches based on the column titled "ISSUE", as well as more things down the line as it grows.

Thank you so much, I hope to hear from someone soon!!!


Top
 Profile  
 
PostPosted: Fri Aug 15, 2014 7:02 am 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6425
Location: Montreal, Canada
I threw this together very quickly and it's completely untested, but maybe it helps?

Syntax: [ Download ] [ Hide ]
<?php

$db = [
        'type'     => 'mysql',
        'host'     => 'localhost',
        'username' => 'jimbob',
        'password' => 'secret',
        'dbname'   => 'my_database',
];

$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']);
        $data = (isset($_POST['search'])) ? getSelectedRows($pdo, $_POST['search']) : getAllRows($pdo);
} 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 your search term">
                        </div>
                        <button type="submit" class="btn btn-primary">Search</button>
                </form>

                <table class="table table-striped">
                        <tr>
                                <th>Issue</th>
                                <th>Scott</th>
                                <th>Den</th>
                                <th>Color</th>
                                <th>Cond</th>
                                <th>70</th>
                                <th>70J</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['Issue']; ?></td>
                                        <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['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>
 

_________________


Top
 Profile  
 
PostPosted: Fri Aug 15, 2014 4:19 pm 
Offline
Briney Mod
User avatar

Joined: Mon Jan 19, 2004 7:11 pm
Posts: 6446
Location: 53.01N x 112.48W
Sounds like your company needs to step up and actually hire someone that can do this. That's not meant as a slight to you - you've explained this isn't really your job. But if they want this done respectably well, they need someone who knows more about web development than just Dreamweaver.

_________________
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.


Top
 Profile  
 
PostPosted: Tue Aug 19, 2014 5:01 am 
Offline
Forum Newbie

Joined: Thu Aug 14, 2014 5:46 am
Posts: 5


Top
 Profile  
 
PostPosted: Tue Aug 19, 2014 6:49 am 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6425
Location: Montreal, Canada
You just need to remove the call to getAllRows().

Replace this line
Syntax: [ Download ] [ Hide ]
$data = (isset($_POST['search'])) ? getSelectedRows($pdo, $_POST['search']) : getAllRows($pdo);

with this
Syntax: [ Download ] [ Hide ]
if (isset($_POST['search'])) {
    $data = getSelectedRows($pdo, $_POST['search']);
}


You may also want to wrap the table itself in
Syntax: [ Download ] [ Hide ]
<?php if (isset($_POST['search'])): ?>
<table>
    ... etc
</table>
<?php endif; ?>

_________________


Top
 Profile  
 
PostPosted: Sat Nov 08, 2014 1:20 am 
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 pages. One php page displays results searched from a sql table named POPTABLE, the other from a table named SMQSQL. The data stored in each table could in fact be stored in one table as the two data sets are very closely related.

My question is this; if I were to combine the tables, or leave the tables in the same database (the current setup), how would I MODIFY my CURRENT php pages to combine them into one? Basically have one user input 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 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 if necessary. 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, one at a time. They are displayed with iframes that point to separate php pages. You can see how similar the data is. Please help if you can! 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!!!!!!!!!!!


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

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 6 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:  
cron
Powered by phpBB® Forum Software © phpBB Group