LinuxQuestions.org
View the Most Wanted LQ Wiki articles.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 05-20-2008, 10:02 AM   #1
deesto
Member
 
Registered: May 2002
Location: NY, USA
Distribution: FreeBSD, Fedora, RHEL, Ubuntu; OS X, Win; have used Slackware, Mandrake, SuSE, Xandros
Posts: 448

Rep: Reputation: 31
Question 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?
 
Old 05-20-2008, 10:21 AM   #2
derzok
Member
 
Registered: Aug 2004
Location: Ohio
Distribution: Debian, Slackware
Posts: 58

Rep: Reputation: 15
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.
 
Old 05-20-2008, 10:46 AM   #3
deesto
Member
 
Registered: May 2002
Location: NY, USA
Distribution: FreeBSD, Fedora, RHEL, Ubuntu; OS X, Win; have used Slackware, Mandrake, SuSE, Xandros
Posts: 448

Original Poster
Rep: Reputation: 31
Question

Hello, and thanks.
Quote:
Originally Posted by derzok View Post
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.
 
Old 05-21-2008, 07:56 AM   #4
deesto
Member
 
Registered: May 2002
Location: NY, USA
Distribution: FreeBSD, Fedora, RHEL, Ubuntu; OS X, Win; have used Slackware, Mandrake, SuSE, Xandros
Posts: 448

Original Poster
Rep: Reputation: 31
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?
 
Old 05-21-2008, 09:44 AM   #5
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Rep: Reputation: 282Reputation: 282Reputation: 282
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.
 
Old 05-21-2008, 01:30 PM   #6
deesto
Member
 
Registered: May 2002
Location: NY, USA
Distribution: FreeBSD, Fedora, RHEL, Ubuntu; OS X, Win; have used Slackware, Mandrake, SuSE, Xandros
Posts: 448

Original Poster
Rep: Reputation: 31
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. 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.
 
Old 05-21-2008, 02:58 PM   #7
AdaHacker
Member
 
Registered: Oct 2001
Location: Brockport, NY
Distribution: Kubuntu
Posts: 384

Rep: Reputation: 31
Quote:
Originally Posted by deesto View Post
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.
 
Old 05-21-2008, 03:11 PM   #8
deesto
Member
 
Registered: May 2002
Location: NY, USA
Distribution: FreeBSD, Fedora, RHEL, Ubuntu; OS X, Win; have used Slackware, Mandrake, SuSE, Xandros
Posts: 448

Original Poster
Rep: Reputation: 31
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).
 
Old 05-21-2008, 03:50 PM   #9
michaelk
Moderator
 
Registered: Aug 2002
Posts: 11,894

Rep: Reputation: 746Reputation: 746Reputation: 746Reputation: 746Reputation: 746Reputation: 746Reputation: 746
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.
 
Old 05-21-2008, 06:50 PM   #10
AdaHacker
Member
 
Registered: Oct 2001
Location: Brockport, NY
Distribution: Kubuntu
Posts: 384

Rep: Reputation: 31
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.
 
Old 06-11-2008, 10:10 AM   #11
deesto
Member
 
Registered: May 2002
Location: NY, USA
Distribution: FreeBSD, Fedora, RHEL, Ubuntu; OS X, Win; have used Slackware, Mandrake, SuSE, Xandros
Posts: 448

Original Poster
Rep: Reputation: 31
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?
 
Old 06-11-2008, 05:25 PM   #12
AdaHacker
Member
 
Registered: Oct 2001
Location: Brockport, NY
Distribution: Kubuntu
Posts: 384

Rep: Reputation: 31
Quote:
Originally Posted by deesto View Post
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.

Last edited by AdaHacker; 06-11-2008 at 05:28 PM.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Recover a file saved in a mysql table via php upload prabhatsoni Linux - Software 0 07-13-2006 07:49 AM
mysql user table corruption creolophus Linux - Software 1 04-23-2006 08:12 PM
PHP/MySQL table question newuser455 Programming 10 11-03-2005 04:50 AM
Problem inserting data into a mysql table using PHP Rockgod2099 Programming 13 08-03-2005 12:27 AM
mysql 'user' table read-only fuelinjection Linux - General 2 06-13-2004 09:05 AM


All times are GMT -5. The time now is 05:19 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration