LinuxQuestions.org - Building a table to display from user input: PHP, MySQL

- Programming (https://www.linuxquestions.org/questions/programming-9/)

- - Building a table to display from user input: PHP, MySQL (https://www.linuxquestions.org/questions/programming-9/building-a-table-to-display-from-user-input-php-mysql-643523/)

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

    // print results:
    print "Displaying $num records.<br/>";  
    print "<table width=200 border=1>\n";
    // print table columns as headings:
    for ($i = 0; $i < mysql_num_fields($result); $i++) {
      print '<th>' . mysql_field_name($result, $i) . '</th>';
      }
    // print table rows:
    while($getrow = mysql_fetch_array($result))
      {
          print "<tr>\n";
          foreach ($getrow as $field)
          print "\t<td>$field</td>\n";
          print "</tr>\n";
      }
    print "</table>\n";
    }

No errors are returned on submit, but othing is ever output (just goes back to the form), so I assume I'm not calling for the data correctly.

What am I doing wrong? Any suggestions for improvement, please?

Where are you connecting to the database?

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.

Hello, and thanks.

Quote:

Originally Posted by derzok (Post 3159220)

Where are you connecting to the database?

At the very beginning of the document BODY:

PHP Code:




<?
$db_host = 'localhost';
$db_user = 'myuser'; 
$db_passwd = 'mypass';
$db_name = 'mydatabase';
...

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:

PHP Code:




$resultarray = mysql_fetch_row_array($result);
print $resultarray[0];

... I get the same as before: no output.

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.

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

Sorry about that. :o The code as a whole is updated here:
http://deesto.pastebin.com/f5edfa884

This is the code that I'm currently using, and that still has the trouble of not printing results.

Quote:

Originally Posted by deesto (Post 3160570)

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.

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.

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?

Quote:

Originally Posted by deesto (Post 3181603)

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.