Building a table to display from user input: PHP, MySQL
ProgrammingThis forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.
Notices
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
Distribution: FreeBSD, Fedora, RHEL, Ubuntu; OS X, Win; have used Slackware, Mandrake, SuSE, Xandros
Posts: 448
Rep:
Building a table to display from user input: PHP, MySQL
I'm trying to display the results of a MySQL query based on user input. I have two drop-downs that pull distinct values from columns in a table, and on submit, I'd like to display the results based on those pull-down values.
The first part, which gets the pull-down values from the table, works fine:
[html]
<form method='post' action="<?=$_SERVER['PHP_SELF']?>"'>
<table align='center'> <tr>
<td>Select a value from column one:
<select name="column1value">
<?
$col1list = mysql_query("select distinct mytablecolumnone from mytable order by mytablecolumnone")
or die("column one query failed");
while ($record_col1 = mysql_fetch_array($col1list))
{
printf(" <option>%s</option>\n",$record_col1['mytablecolumnone']);
}
?>
</select>
</td>
<td>Select a value from column two:
<select name="column2value">
<?
$col2list = mysql_query("select distinct mytablecolumntwo from mytable order by mytablecolumntwo")
or die("column two query failed");
while ($record_col2 = mysql_fetch_array($col2list))
{
printf(" <option>%s</option>\n",$record_col2['mytablecolumntwo']);
}
?>
</select>
</td>
<td><input type="submit" name="querysubmit" value="View your results"></td>
</tr>
</table>
</form>[/html]It's this next part, displaying the results, that I can't seem to get right:
PHP Code:
<? $num = mysql_numrows($result); // build the query. start with conditions from submitted input: if (isset($_POST['querysubmit'])) { if ($FilterSet) { $result = mysql_query("select * from mytable WHERE id > 0 $FilterOneQuery $FilterTwoQuery order by mytablecolumntwo,mytablecolumnone"); } // otherwise, use the base query, without conditions from imput: else { $result = mysql_query("select * from mytable order by mytablecolumnone,mytablecolumntwo"); }
// get specified column value information from submitted input: $FilterOne = $_GET['column1value']; $FilterTwo = $_GET['column2value']; // sanitize the input (not really needed, but...): $FilterOne = sanitize_filter_input($FilterOne); $FilterTwo = sanitize_filter_input($FilterTwo);
// add input values as conditions to the query: if (!empty($FilterOne)) { $FilterOneQuery = "AND mytablecolumnone =" . $FilterOne; $FilterSet = TRUE; } if (!empty($FilterTwo)) { $FilterTwoQuery = "AND mytablecolumntwo =" . $FilterTwo; $FilterSet = TRUE; }
I highly suggest saving your query as a string and running mysql_query($query); This way, if the query fails you can do a "print $query" to find any syntax errors.
I didn't want to clutter the post by printing the entire source, but I don't suppose there's much left to the body after this, except to close the connection, and the document, at the end:
[html]...
// close connection:
mysql_close(); ?>
</body>
</html>[/html]
Quote:
I highly suggest saving your query as a string and running mysql_query($query); This way, if the query fails you can do a "print $query" to find any syntax errors.
Understood, but how would I do that if the query is changing conditionally, as in the second part where the query is built? And wouldn't this be the same as "print $result" in my case? If I add this after each of the two conditions, and to the print section at the end, I get: "Resource id #5". If I change these to an array instead:
Distribution: FreeBSD, Fedora, RHEL, Ubuntu; OS X, Win; have used Slackware, Mandrake, SuSE, Xandros
Posts: 448
Original Poster
Rep:
So, currently, my updated code is: http://deesto.pastebin.com/f51dc2b03
(please excuse the bad indents in some places; pastebin screws them up at times)
As before, the pull-downs contain column values, and there are no errors on submit, but no results are returned. WHat am I doing wrong?
There is no mysql_connect() and no mysql_select_db() in your code. What you do at the beginning of the file is not 'connecting', but just declaring and initializing some variables.
That is what derzok is poiting at, you don't connect to the mysql server and you don't select a database to use.
Distribution: FreeBSD, Fedora, RHEL, Ubuntu; OS X, Win; have used Slackware, Mandrake, SuSE, Xandros
Posts: 448
Original Poster
Rep:
It looks like I'd omitted a relatively crucial block of code when cutting and pasting:
PHP Code:
// establish connection using variable values: mysql_pconnect("$db_host","$db_user","$db_passwd") or die("Unable to connect to SQL server"); mysql_select_db($db_name) or die("Unable to select database");
// establish query and count resultant rows: // $query = "select * from mytablecolumnone from mytable order by mytablecolumnone"; // $result = mysql_query($query);
This is the code that I'm currently using, and that still has the trouble of not printing results.
Hold on - are you sure that link is the code you're actually using? Do you have an editor/IDE that does syntax highlighting? Because if you did, that file would look like one big syntax error.
Notice that between lines 20 and 22 you never drop out of PHP and back into HTML mode. So your FORM tag is actually being treated as PHP code, which obviously doesn't work. Add that PHP closing tag (?>) in in line 21 and you should be all set.
Distribution: FreeBSD, Fedora, RHEL, Ubuntu; OS X, Win; have used Slackware, Mandrake, SuSE, Xandros
Posts: 448
Original Poster
Rep:
OK, you've got me again: I'd missed another line of code (granted, it's just a '<?', but I suppose that's kind of important!): http://deesto.pastebin.com/f540b827
I believe that's it, and I can assure you that the code does not throw an error, just doesn't return any results (where I have confirmed that the results set != NULL).
Ok just a nudge after a quick look at the code. It is never going to display data if you do not check to see if the submit button is set before your form.
Yes, your code is throwing an error - you're just suppressing it. I created and populated a dummy table, copied your script, and ran it locally. When I clicked the form button, I got this:
Quote:
Fatal error: Call to undefined function mysql_fetch_row_array() in /home/pageer/www/tst.php on line 73
The error says it all: it's either mysql_fetch_row() or mysql_fetch_array(). There is no such function as mysql_fetch_row_array(). You have the same error on line 97 as well.
And after I fixed that, I got:
Quote:
Fatal error: Call to undefined function sanitize_filter_input() in /home/pageer/www/tst.php on line 81
Maybe you defined that in another file, maybe you were trying for just filter_input(). Beats me.
For future reference, the first thing to try is always cranking your error reporting up. You can do this by either setting error_reporting = E_ALL in your php.ini file, or by adding the line"
PHP Code:
error_reporting(E_ALL);
near the top of your script. That will make sure you don't miss any error messages.
Also for future reference, it's extremely bad practice to have database queries and HTML markup. You should be using some kind of template-based approach and keep all the program logic in a different file. You might consider looking into some of the multitude of PHP MVC frameworks out there. And while I'm on the topic, don't use the old mysql extension. Use mysqli or PDO with prepared statements, not the old string-concatenation method of getting data into queries. It's more secure and in some cases can help performance. String-concatenation for queries is evil. Just some things to be aware of, if you weren't already.
Distribution: FreeBSD, Fedora, RHEL, Ubuntu; OS X, Win; have used Slackware, Mandrake, SuSE, Xandros
Posts: 448
Original Poster
Rep:
Thanks michaelk and AdaHacker. First, not sure why, but I never was notified of your recent replies (a few weeks ago). LQ's back-end is usually great at making sure notifications get through, so I wonder what happened there; I just happened to take a look and saw your replies by chance.
Thanks also for the pointers. Yes, I was trying to use some fantastic functions there that didn't yet exist; fixed those. I will also use more verbose error reporting, so I can actually see what's happening instead of just seeing a blank page.
Finally, I'm interested in the frameworks and prepared statements AdaHacker mentioned, so I can improve my code: where can I get some basic information on these?
Finally, I'm interested in the frameworks and prepared statements AdaHacker mentioned, so I can improve my code: where can I get some basic information on these?
Here's the PDO page and a tutorial. Both have sections on prepared statements, along with lots of other stuff. Basically, though, a prepared statement is a pre-compiled SQL statement that takes parameters. The idea is that you pass the database engine a parameterized version of your query, which it checks and compiles, then you bind values to those parameters and actually execute the query.
The benefit here is two-fold. First, if you're going to execute the query more than once (e.g. inserting a bunch of rows into the same table), you can improve performance, because the DB only has to compile the query once, and can then re-use that prepared statement for subsequent executions. Second, it guards against SQL-injection attack because you don't have to worry about manually escaping your data - by the time the data is bound, the query is already compiled and it's too late to inject SQL into it.
As for PHP frameworks, there are literally dozens of them out there to choose from. Here's one list of them. They vary in scope and style, from Symfony, which is inspired by Ruby's Rails framework, to Prado, which is reminiscent of ASP.NET, and nearly everything in between. Regardless of approach, the general purpose of these is to provide a platform on which to build your applications - basically, they take care of the infrastructure code, taking some of the "grunt work" out of building your app.
What I was getting at with the mention of frameworks was the use of the MVC (Model, View, Controller) pattern in many of them. The key key concept in MVC is separation of concerns - the display stuff goes in one file, the database stuff in another, and the business logic that ties them together in a third. The code you posted, on the other hand, was doing database queries right next to HTML, which can quickly become unmaintainable as your application grows. Many frameworks bake this separation of concerns into the way you develop your program, i.e. they enforce good application architecture, to a certain extent. Many of them also have handy features like database abstraction and ORM (Object-Relational Mapping) layers, templating systems, built-in caching, and so forth. But the real point is that they might be worth looking into just to give you an idea of proper application architecture.
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.