Help a beginner

**Dirty Harry** · 09-20-2012, 10:10 AM

Howzit

I have been browsing the site and found quite a few helpful scripts which I played around with, however I have not been able to find what I need.

I am trying to write a report which I think includes more than just PHP.

I need to build a report where a user can select two dates,so one of those fancy calendar pop up thingys, and once done, will need to reference a mysql database which will then show those records between those dates.

Eg, I choose between 10-08-2012 and 20-09-2012,when I click submit or search it will then search the respective 'datecolumn' in table a,match up 'id_column' in table a to 'id_column' in table b and show the 'account_number' from table b which corresponds to the date/s selected from the date prompts mentioned earlier.

If I am not making sense please say so.

If I need to build more than one page I will do so,I have been struggling with this for a week now,might be easy for others but being inexperienced I would know where to start.

I would imagine I need to use more than one language here.

Any help will be greatly appreciated.

Cheers
Harry

**keyboard** · 09-20-2012, 10:13 AM

So, what are you actually asking for help with?

**Dirty Harry** · 09-20-2012, 10:19 AM

I need help to get the scripting correct for it to work and to get it together. All I have at the moment is a script that references mysql and that gives me all the data from the fields selected which is what I mentioned in my original post. But I highly doubt that that script will be at all usefull.

I have an idea of how it should work but dont know how to put it together and how to get each one to link up and reference the right inf

**bernie1227** · 09-20-2012, 10:26 AM

I'd assume that you would just get javascript calendar, and then the content of each thing would be whatever in the database had the corresponding date.

**Dirty Harry** · 09-20-2012, 10:34 AM

Yeah that's how I thought it to work, but I don't know how to put it together,that's what I'm struggling with. There are javascript calendars on google which work,but I need two to choose from,so a 'start date' and an 'end date'.Then from those values choose respective information from the database.

**traq** · 09-21-2012, 02:12 AM

CLIENT (user):
1. pick a start date and end date, using a pop-up calendar (as bernie said, javascript).
2. send the selected dates to the server (hinthint, you need a <form> now).

SERVER (website):
1. check for form submission.
2. read submitted values.
3. validate that they *are* dates, and in the proper format.
4. connect to database.
5. query database.
6. retrieve resultset.
7. parse resultset and write html to display results.
8. send html result to client.

CLIENT:
1. read results; possibly send another request.

beyond that,

Your question is unclear.
Please provide more information, and be as specific as possible.

What do you want to accomplish?
What have you already tried?
What problems did you encounter?

Also, please be sure to share all relevant code.

**Dirty Harry** · 09-21-2012, 07:04 AM

Yeas thats it traq.

ok to answer the questions,
What I want to accomplish is for a user to put in selected dates from calendar to query the database and whatever fields or information that is between those two dates are what needs to be showed on the client's side. So if a new client is added to the database and is within the dates selected, then that is what should show up.

I have tried to get forms etc but as I said,I dont know how to put it all together.

I would like someone to help me and say,eg,you need a form which does this,in the form this links to this part of the query db script,as said in original post,I am still very much a beginner. This is exciting for me but I'm getting stuck. All I have been able to do from a php page I put together was show all the field relating to eachother. Now i would like some assistance in being able to do as mentioned above.

The code I have at the moment is as follows

Code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
  <head>

  <link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/themes/base/jquery-ui.css" rel="stylesheet" type="text/css"/>
 <script type="text/javascript" src="http://code.jquery.com/jquery-1.6.2.min.js"></script>
  <script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/jquery-ui.min.js"></script>
  <meta http-equiv="content-type" content="text/html; charset=utf-8"/>
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript"
  src="https://www.google.com/jsapi?autoload={'modules':[{'name':'visualization','version':'1','packages':['corechart','table','piechart']}]}">
</script>

 <script type="text/javascript">


google.setOnLoadCallback(pieChart);


function pieChart() {
      var startdate  = "";
      var enddate  =  "";
         if ($("#datepicker").hasClass('hasDatepicker')) {
            startdate  = $("#datepicker").datepicker('getDate');
             }
           if ($("#datepicker2").hasClass('hasDatepicker')) {
           enddate  = $("#datepicker2").datepicker('getDate');
            }

      var pieJsonData = $.ajax({
          url: "overall_ban_pos_pie_date.php?startdate=" + startdate + "&amp;enddate=" + enddate,
          dataType:"json",
          async: false
          }).responseText;

var pieData = new google.visualization.DataTable(pieJsonData);


var pieChartWrapper = new google.visualization.ChartWrapper({
          'chartType': 'PieChart',
          'containerId': 'pie_div',
      'dataTable':pieData,
          'options': {
    chartArea:{left:10,top:40,height:200,width:360},
    width:300, 
    height:260,
    title: "Neutral Percentage", 
    titleTextStyle:{fontSize:12},
    tooltip:{showColorCode:true},
    legend:{textStyle:{fontSize: 10},position:'left'},
    pieSliceTextStyle:{fontSize: 10}
          }
        });



pieChartWrapper.draw();


}
  </script>

  <script type="text/javascript">

  $(document).ready(function() {
    $("#datepicker").datepicker({dateFormat: "yy-mm-dd"});
  });

  $(document).ready(function() {
    $("#datepicker2").datepicker({dateFormat: "yy-mm-dd"});
  });


  $("#pieChart").click(function(e) {
    e.preventDefault();
     pieChart();
  });
  </script>



</head>
<body style="font-size:62.5%;">

  <form action="overall_ban_pos_pie_date.php" method="post">

Start Date: <input type="text" name="startdate" id="datepicker"/>
End Date: <input type="text" name="enddate" id="datepicker2"/>

<input type="submit"  id="pieChart"/>

</form>
<div id="pie_div"></div>     
</body>




<?php	 


 $con = mysql_connect("localhost:33307","USER","PASS");
 if (!$con)
   {
   die('Could not connect: ' . mysql_error());
   }

mysql_select_db("leonetcrm", $con);
 
$result = mysql_query("SELECT vtiger_account.accountid, vtiger_crmentity.crmid, vtiger_account.account_no, vtiger_crmentity.createdtime
FROM leonetcrm.vtiger_account
INNER JOIN leonetcrm.vtiger_crmentity
ON vtiger_account.accountid=vtiger_crmentity.crmid");
echo "<table border='5'>
 <tr>
 <th>Account ID</th>
 <th>Account Number</th>
 <th>CRMID</th>
 <th>Createdtime</th>
 </tr>";
 while($row = mysql_fetch_array($result))
   {
   echo "<tr>";
   echo "<td>" . $row['accountid'] . "</td>";
   echo "<td>" . $row['account_no'] . "</td>";
   echo "<td>" . $row['crmid'] . "</td>";
    echo "<td>" . $row['createdtime'] . "</td>";
   echo "</tr>";
   }
 echo "</table>";
 
mysql_close($con);
 ?> 

</html>

I copied this from the web,I need to take the piechart out,just want normal table format. Once I can get that sorted I think it will work.

Thanks for taking the time to assist.

Cheers
Harry

**traq** · 09-21-2012, 08:28 PM

If you haven't gotten too far along, I would suggest making some basic changes to how you organize things - it will help in the long run:

1. DO NOT use the mysql_* functions. They are outdated and not recommended for new projects.

Use the mysqli extension instead. If you're interested, here's more information on choosing which extension to use.

2. AVOID (at nearly any cost) switching back and forth between php code and regular HTML. Likewise, AVOID using echo or other functions that send output to the browser until your script is complete. Things will be a lot less confusing in the long run if you keep them separated.

Instead, put _all_ of your php code a the top of your script (storing any generated HTML markup in a variable), and output it when you are done. For example:

PHP Code:


<?php /* BAD example */
$DB = new mysqli( host,user,pass,name );
if( $query = $DB->query( "SELECT `col1`,`col2` FROM `table` WHERE `col1`='something'" ) ){
    echo "<table>
                 <tr><th>Column One</th><th>Column Two</th></tr>";
    while( $row = $result->fetch_assoc() ){
        echo "<tr><td>{$result['col1']}</td><td>{$result['col2']}</td></tr>";
    }
    echo "</table>";
}?>

PHP Code:


<?php
/* GOOD example */
// this results in the same output, but the script is separate from the output.
// makes it easier to debug, maintain, and make changes in the future.
$DB = new mysqli( host,user,pass,name );
if( $query = $DB->query( "SELECT `col1`,`col2` FROM `table` WHERE `col1`='something'" ) ){
    while( $row = $result->fetch_assoc() ){
        $tablerow = "<tr><td>{$result['col1']}</td><td>{$result['col2']}</td></tr>";
    }
    $table = "<table>
                 <tr><th>Column One</th><th>Column Two</th></tr>";
    $table .= implode( "\n",$tablerow );
    $table .= "</table>";
}
// all done
echo $table;
?>

**Dirty Harry** · 09-25-2012, 06:58 AM

Thanks traq

I see what you saying. I'm not that far with the coding. I will change it to what you suggested and see what it does.

I still however am not too sure on how to get my report to work properly, firstly the coding as well as the layout, I dont know how to plan it so not sure where to start.

Ok

I have split it in two, the form one on page and the sql script on another,only problem is with the sql select script it just shows the whole range and not my start date to end date...

Any help with that please.