Looking for someone to do a quick php sql query/display

Looking for volunteers to join your project? Need help with a script but can't afford to pay? Want to offer your services as a volunteer to build up your portfolio? This is the place for you...

Moderator: General Moderators

Post Reply
Disead
Forum Newbie
Posts: 5
Joined: Thu Aug 14, 2014 5:46 am

Looking for someone to do a quick php sql query/display

Post by Disead »

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:

Code: Select all

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

Code: Select all

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:

Code: Select all

<!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!!!
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Looking for someone to do a quick php sql query/display

Post by Celauran »

I threw this together very quickly and it's completely untested, but maybe it helps?

Code: Select all

<?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>
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Looking for someone to do a quick php sql query/display

Post by pickle »

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.
Disead
Forum Newbie
Posts: 5
Joined: Thu Aug 14, 2014 5:46 am

Re: Looking for someone to do a quick php sql query/display

Post by Disead »

Celauran wrote:I threw this together very quickly and it's completely untested, but maybe it helps?

Code: Select all

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

Oh my God, I could hug you to death!!!!!!!!!!! Thank you so, so much!!!! It works perfectly, its beautiful! All I had to do was input my db info! :D:D:D:D:D

The only thing I could ever ask for to finish it is this; what do I need to insert where so that it does not display the entire table before a search is performed???

THANKYOUTHANKYOUTHANKYOUTHANKYOUTHANKYOUTHANKYOUTHANKYOUTHANKYOUTHANKYOUTHANKYOUTHANKYOUTHANKYOUTHANKYOU
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Looking for someone to do a quick php sql query/display

Post by Celauran »

You just need to remove the call to getAllRows().

Replace this line

Code: Select all

$data = (isset($_POST['search'])) ? getSelectedRows($pdo, $_POST['search']) : getAllRows($pdo);
with this

Code: Select all

if (isset($_POST['search'])) {
    $data = getSelectedRows($pdo, $_POST['search']);
}
You may also want to wrap the table itself in

Code: Select all

<?php if (isset($_POST['search'])): ?>
<table>
    ... etc
</table>
<?php endif; ?>
Disead
Forum Newbie
Posts: 5
Joined: Thu Aug 14, 2014 5:46 am

Re: Looking for someone to do a quick php sql query/display

Post 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 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:

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