PHP Developers Network
http://forums.devnetwork.net/

Looking for someone to do a quick php sql query/display
http://forums.devnetwork.net/viewtopic.php?f=26&t=140073
Page 1 of 1

Author:  Disead [ Fri Aug 15, 2014 3:09 am ]
Post subject:  Looking for someone to do a quick php sql query/display

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!!!

Author:  Celauran [ Fri Aug 15, 2014 7:02 am ]
Post subject:  Re: Looking for someone to do a quick php sql query/display

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>
 

Author:  pickle [ Fri Aug 15, 2014 4:19 pm ]
Post subject:  Re: Looking for someone to do a quick php sql query/display

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.

Author:  Disead [ Tue Aug 19, 2014 5:01 am ]
Post subject:  Re: Looking for someone to do a quick php sql query/display


Author:  Celauran [ Tue Aug 19, 2014 6:49 am ]
Post subject:  Re: Looking for someone to do a quick php sql query/display

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; ?>

Author:  Disead [ Sat Nov 08, 2014 1:20 am ]
Post subject:  Re: Looking for someone to do a quick php sql query/display

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!!!!!!!!!!!

Page 1 of 1 All times are UTC - 5 hours
Powered by phpBB® Forum Software © phpBB Group
http://www.phpbb.com/