Please help a newb! PHP form issues

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Flashart
Forum Commoner
Posts: 71
Joined: Tue Oct 06, 2009 12:12 pm

Please help a newb! PHP form issues

Post by Flashart »

HI I hope someone can help me with the following page. What i am trying to do is create a page that queries a db and then displays the results in a table within the same page. A lot of tutorials I have seen on the web have a html page with the form that then goes to a php page with the result. I want to stay on the page (top part where the form is stay static if you see what i mean) with the results dynamically loaded below. Here is the code I have so far.

Code: Select all

 
<html>
<head>
<title>DB Query Page</title>
<META NAME="ROBOTS" CONTENT="NOINDEX, NOFOLLOW">
 
</head>
 
 
<body>
 
 
<div id="form">
 
<form action="<?php echo $PHP_SELF;?>" method="POST">
Adgroup:    <input type="text" name="$adgroup" /> 
Date From:  <input type="text" name="from_date" /> 
Date To:    <input type="text" name="to_date" />
            <input type="submit" value="Apply" name="query" />  
</form>     
 
</div>
 
 
<?php
//File that holds connection parameters
include 'connection.php';
 
//Create short variable names
$adgroup = $_POST['adgroup'];
 
$query = "select * from adgroup_data where adgroup='%$adgroup%'";
 
 
$result = mysql_query($query) or die (mysql_error());
 
 
 
echo "<table cellpadding=3 id=table>";
    //echo '<th>date</th>';
    echo '<th>Account</th>';
    //echo '<th>customer_id</th>';
    echo '<th>Adgroup</th>';
    echo '<th>Impressions</th>';
    echo '<th>Clicks</th>';
    echo '<th>CTR</th>';
    echo '<th>CPC</th>';
    echo '<th>CPM</th>';
    echo '<th>Cost</th>';
    echo '<th>Avg_Position</th>';
    echo '<th>Conversions</th>';    
    echo '<th>Conversions_mpc</th>';
 
while($row = mysql_fetch_array($result, MYSQL_ASSOC)) 
 
{
   
   //"<td>{$row['date']}</td>".
    echo "<tr><td>{$row['account']}</td>";
   //"<td>{$row['customer_id']}</td>".
    echo "<td>{$row['adgroup']}</td>";
    echo "<td>{$row['impressions']}</td>";
    echo "<td>{$row['clicks']}</td>";
    echo "<td>{$row['ctr']}</td>";
    echo "<td>{$row['cpc']}</td>";
    echo "<td>{$row['cpm']}</td>";
    echo "<td>{$row['cost']}</td>";
    echo "<td>{$row['avg_position']}</td>";
    echo "<td>{$row['conversions']}</td>";
    echo"<td>{$row['conversions_mpc']}</td>
    </tr>";
}
?>
</body>
</html>
 

I am very new (2 days and counting!) to php so there are probably lots of errors here. However I am now at the "can't see wood for trees" stage of development and I would be extremely grateful for any help. When I click "apply" it reloads the page (presumably because of the action within the form) but with no data.

I realise that is an obvious action (set it to load itself therefore any data the query retrieved will be lost) but I can't figure out how to make the page keep the parameters entered within the adgroup text box, and load the results in the table below.

Any help would be greatly appreciated.
Kind regards
Peter
Flashart
Forum Commoner
Posts: 71
Joined: Tue Oct 06, 2009 12:12 pm

Re: Please help a newb! PHP form issues

Post by Flashart »

Ok so i changed the select statement to

Code: Select all

"select * from adgroup_data where adgroup like '%".$adgroup."%' ";
and by golly it works.

However when I initially load the page, it goes and loads ALL the database results! I can't figure out why!

When I enter an adgroup it retrieves it fine, however when I add further fields like

Code: Select all

"select * from adgroup_data where adgroup like '%".$adgroup."%' AND date>= '%".date_from."%' AND date <= '%".date_to."%' ";
it stops working. So close but so far!

Can anyone help?!
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Please help a newb! PHP form issues

Post by califdon »

Welcome to the forum and to PHP/MySQL scripting!

My first guess is that you're having trouble with dates, which are quite difficult to deal with in queries. The first thing you have to consider is how they are stored in the table. Usually the best practice is to store anything that represents a date as a Date or Datetime data type. This gives you a lot of flexibility in manipulating date values, but you must understand that Dates are stored as numeric values, not strings, so you can't use wildcards in SQL statements. Here's a good explanation of dates in PHP: http://www.bigroom.co.uk/blog/dates-in-php-and-mysql.

Assuming that your variables $date_from and $date_to are coming from a manually entered form, they will be strings, so you will have to convert them to a format that MySQL recognizes (and converts to the Datetime numeric format). So you have to decide how to ask the user to input a date: will you use some date-picker javascript code so the user will open a small calendar and click on the desired date (which assures you that the format will be correct), or ask the user to select (or input) 3 fields (month, day, year), or just give the user a blank field and hope that they will enter in a particular format? This is what makes date handling somewhat tedious. But, one way or another, you must determine the month, day, and year, then convert to the format that MySQL recognizes (by default, YYYY-MM-DD), in order to use in queries. And you cannot use the wildcard, %.

Hope that gets you started.
Flashart
Forum Commoner
Posts: 71
Joined: Tue Oct 06, 2009 12:12 pm

Re: Please help a newb! PHP form issues

Post by Flashart »

I got rid of the % wildcard in the dates and it works so thank you very much!

You are correct in that it's in a form. The user would enter the date in a YYYY-MM-DD format (i'm not sure how I would convert a DD-MM-YYYY to the correct mysql syntax on the fly), the date is also stored as YYYY-MM-DD format in the db.

I would like to implement a calendar but have been unable to locate a nice looking javascript calendar. And I am unsure how I would implement that!
One thing, how would I script the current page so that when the page loads with the data, the parameters entered remain in the text boxes?

I am very grateful for your help.

Regards
Peter
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Please help a newb! PHP form issues

Post by califdon »

Flashart wrote:I got rid of the % wildcard in the dates and it works so thank you very much!

You are correct in that it's in a form. The user would enter the date in a YYYY-MM-DD format (i'm not sure how I would convert a DD-MM-YYYY to the correct mysql syntax on the fly), the date is also stored as YYYY-MM-DD format in the db.

I would like to implement a calendar but have been unable to locate a nice looking javascript calendar. And I am unsure how I would implement that!
One thing, how would I script the current page so that when the page loads with the data, the parameters entered remain in the text boxes?

I am very grateful for your help.

Regards
Peter
Never mind the format, what is the data type of the date fields? It will either be CHAR, VARCHAR, DATE, DATETIME, etc. Format is entirely separate from the way the data is stored. The required code will change radically, depending on the data type.

Converting is typically done in PHP with the use of substring() and other string functions (explode() is often useful). You would probably benefit from reading the PHP manual on string functions. MySQL has some similar functions, but I generally find it easier to write and later understand, if I do all the converting and formatting in PHP and create variables which then are used in the SQL statement (or process the results of a SQL statement), rather than trying to incorporate that into the SQL.

The way you would implement a javascript calendar is that you would use the javascript code to create a pop-up window or possibly have it visible on the page (the problem with the latter is that, to make it large enough to be practical, it occupies a lot of space on the page). Then the $_POST variable would be taken from the javascript object, instead of an HTML <input> tag, for example. Each one is different, so you would have to read the documentation for the javascript object. I know that there are some pretty clunky javascript solutions available, but if you search, you can find some usable ones. Here are a few that I just looked up, that might be useful:
http://www.nsftools.com/tips/DatePickerTest.htm
http://www.thefreecountry.com/javascrip ... cker.shtml
http://www.blueshoes.org/en/javascript/datepicker/
http://www.javascriptkit.com/script/scr ... ndar.shtml
http://www.bigwebmaster.com/JavaScript/ ... Calendars/

The advantages of using a date-picker are several: it is easy for the user, it looks professional, and it reduces the opportunity for users to enter invalid dates or screw up the formatting.

But you still have the issue of how you are storing the dates. Again, for maximum utility, usually you should be using a data type of Date or Datetime, for which MySQL uses a default input format of YYYY-MM-DD, so you would configure any of the date-picker examples above to produce that output format.
Flashart
Forum Commoner
Posts: 71
Joined: Tue Oct 06, 2009 12:12 pm

Re: Please help a newb! PHP form issues

Post by Flashart »

The date type is DATE.

Thank you for the javascript date picker links. I shall also have a read through on string functions. I am keen on finding out on how to keep what the user enters stay in the text boxes (page loads data, text boxes clear themselves) though I guess I could ECHO the entered parameters somewhere at the top.

Thank you very much for your help.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Please help a newb! PHP form issues

Post by califdon »

OK, that's good. With a DATE data type, you can format it in every imaginable way when you output it, using the date() function.

The way to open a page with data already in <input> elements of a form is to use PHP to put that variable data in the value= attribute of the <input> element. The logic goes like this:

You have a page that contains a form with some <input> elements. The user enters data and submits the form to a PHP script that reads the data, perhaps updates a database, then returns a page with the same form, but this time you want some of the previous values to appear immediately in the form. Right? So, in your PHP script that returns the page, you need something like this:

Code: Select all

...
mysql_connect($host,$usr,$pwd) or die(mysql_error());
...
$cust_num = mysql_real_escape_string($_POST['custnum']);
...
// process data from form, now return new page with form
...
echo "<form ....>";
echo "<input type='text' name='custnum' value='$cust_num'>";
...
The reason for the mysql_real_escape_string() function is to reduce the hazard from malicious user input, known as "sql injection." The reason to put the mysql_connect() function before the mysql_real_escape_string() function is that it only works when the script has connected to a mysql server.

The other way to do this is to use Ajax, which exchanges data between the browser and the server without loading a new page, so you don't have to do anything to retain user data, there's no new page load, and it is a smooth operation, no blinking page. It requires a little Javascript, but in some situations it can be a very nice solution. Ajax is, as you may know, is a technique for using a particular object defined in Javascript, the XMLHttpRequest object. Some references:
http://www.yourhtmlsource.com/javascript/ajax.html
http://www.w3schools.com/PHP/php_ajax_database.asp
Flashart
Forum Commoner
Posts: 71
Joined: Tue Oct 06, 2009 12:12 pm

Re: Please help a newb! PHP form issues

Post by Flashart »

That's extremely helpful, thank you. I shall try your suggestion and see how I get on, and then when I have taken a brave pill I shall take a stab at Ajax!

I noticed in the example you provided you code the form using the echo statement. I have coded the form using HTML. Is there an advantage to using explicit PHP instead of a HTML/PHP combination?

I have had a quick look online for a PHP form tutorial (I couldn't see how I would add a label for an input field for example) but they all seem to be about building the form in HTML and displaying the results in PHP?

I appreciate your help so far and I certainly don't expect you to hold my proverbial hand throughout my development! Sometimes it's a lot easier to ask someone who knows than flicking through lots of pages whether real or virtual!
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Please help a newb! PHP form issues

Post by califdon »

I never mind offering guidance to someone who obviously wants to learn to do it themselves. I don't spend much time on those who just want someone to write their code for them.

The difference between creating a form with simple HTML and creating the HTML with PHP, using echo (or print, essentially the same), is that without PHP, there is no way to insert variables, such as the data that you want to remain in the form. If you know the default value that you want to appear in an <input> element at the time you write the HTML code, you can just hard-code it in the HTML:

Code: Select all

<input type='text' name='shippingmode' value='Ground'>
which shows 'Ground' as the default, but allows the user to overwrite it. But if you want the default to be some value that will be known only when the script is executed, you have to use a server-side language such as PHP (or VBScript, if it's on a Windows server). That's what server-side scripts do, enable you to make a dynamic script, where some elements cannot be known at the time the script is written.

There are 2 ways you can implement this. Which one is easier depends on how much dynamic content there is, compared with the static content. If there's a lot of dynamic content, it's often easier to use echo (or print) to send the whole form, like this:

Code: Select all

...
<?php
...
  echo "<form name='myform' method='post' action='myactionscript.php'>";
  echo "Last Name: <input type='text' name='lastname' value='$lname'><br>";
  echo "First Name: <input type='text' name='firstname' value='$fname'><br>";
  ...
?>
...
 
because you can just insert PHP variables in a double-quoted string, like that.

Or if there's only a few variables, it may be easier to just use HTML until you get to the spot where you need to insert a variable, like this:

Code: Select all

...
<form name='myform' method='post' action='myactionscript.php'>
<input type='text' name='thisdate' value=" <?php echo date('m/j/Y'); ?> "><br>
<input type='text' name='ordernum'><br>
...
Do you see that, in the second example, it's plain HTML until the <?php tag that just inserts the current date, formatted as month/day/year?

As to Ajax, it's not all that scary. You should have some familiarity with Javascript, but it only requires a dozen or so lines of code in the <head> section, and a short PHP script on the server to respond to the XMLHttpRequest. If you've never touched Javascript at all, it might be wise to get a little practice doing that before attempting Ajax.
Post Reply