Help a beginner

Im not sure if I understand what you mean by that, but this is the info I have so farAttachment 4771 using the following script

Code:

---

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

---

That pic is showing out of 24xxx from the account ID column. What I need from my pop up datepicker is that I select the dates in which I want to see new accounts added, the sql query then shows data according to the two dates selected from the datepicker, thats where I am getting stuck.

If there other info needed please ask.

try something like so:

Code:

---

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
WHERE
        vtiger_crmentity.createdtime >= STR_TO_DATE($startDateTime, '%Y-%m-%d %H:%i:%s')
AND
        vtiger_crmentity.createdtime <= STR_TO_DATE($endDateTime, '%Y-%m-%d %H:%i:%s')
ORDER BY
        vtiger_crmentity.createdtime

---

where $startDateTime and $endDateTime are determined from the values submitted by the form.

Thanks traq

That doesnt seem to work though.

Is there some sort of tutorial that I can go through on how to setup a form and then how to get the data to use that as the parameters?

I find alot on the web about it but only being half scripts that people post up I dont know what to put where and how it links up?

Quote:

---

Originally Posted by Dirty Harry

That doesnt seem to work though.

---

What do you mean?

• what did you try?
• what happened?
• what problems did you encounter?
• what did you try to do to solve them (or, at least, what do you think is causing the problem)?

give us something to work with. That query was almost guaranteed not to work as a "copy-paste" - I don't know what your form looks like, or how you plan to process the submitted data, or anything.

Quote:

---

Originally Posted by Dirty Harry

Is there some sort of tutorial that I can go through on how to setup a form and then how to get the data to use that as the parameters?

---

I'm sure there are. But it's a very simple concept:

HTML Code:

---

<form action="send form to this page" method="post">
    <label>Start Date:  <input type="datetime" name="start"></label>
    <label>End Date:  <input type="datetime" name="end"></label>
    <input type="submit" name="searchsubmit" value="Submit Search">
</form>

---

PHP Code:

---

<?php
// if form was submitted
if( isset( $_GET['searchsubmit'] ) ){

    // the start time (if any) is in `$_GET['start']`
    // check that $start is a valid date/time (strtotime() will work for now, but has a limited date range)
    //    (using the DateTime class would be better)
    // if so, sanitize it (if you're using mysql, the function is mysql_real_escape_string())
    //    (but remember my earlier caution AGAINST ext_mysql)
    // and put it into your query.
    
    // same for end time.

}

---

Quote:

---

Originally Posted by Dirty Harry

I find alot on the web about it but only being half scripts that people post up I dont know what to put where and how it links up?

---

For the most part, webdev forums -like this one- have members who are excited and knowledgeable about web stuff. But it's not their *job*. They do it because they enjoy it, and because they enjoy helping others learn. Because of this, most advice you will get will be examples, and not finished, "cut-n-paste" code. You're expected to do some work on your own.

Now, you may be doing the work, but if you don't share that with us, we have no way of knowing. So, share with us! Be part of the process! Just saying "that didn't work" and posting your code doesn't really encourage any further advice. See my list at the top of this post.

If you want to learn how to do it, then this is the way to go about it. I'll help you every step of the way, as long as I can, and I know others here will as well - as long as they don't feel like they're doing all the work for you. My advice is that, if you want someone to give you a finished product, then you should hire them to give you a finished product. :)

Sorry about that, you right, I should have otherwise you cant see what I did wrong.
I dont want someone to do it for me no, I want to learn as I go along and I really appreciate the time you taking to help me.
What I meant by not knowing how to put it together is that I'm not sure what values to put and where to put it, but let me post my 2 pages of script and let me learn!

Right, I started off with this one script I copied which is a pie chart which I dont want, I only used it as it was the closest code to what I needed(this page is called test2.php):

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;







}
  </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();
  });
  </script>



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

  <form action="test_connectdb.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="date"/>

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





</html>

---

So either I start a complete new thing, or we go through it and trim out whats not needed. Please I dont want anything done for me, I just want to understand what and why.

That code once date is picked then goes to the following page which is the test_connectdb.php page.

Code:

---

<html>
  <body>

    Data selected between <?php echo $_POST["startdate"]; ?><br />
    and <?php echo $_POST["enddate"]; ?> is as follows:
   
     

<?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
WHERE vtiger_crmentity.createdtime >= STR_TO_DATE($startDate, '%Y-%m-%d %H:%i:%s')
AND vtiger_crmentity.createdtime <= STR_TO_DATE($endDate, '%Y-%m-%d %H:%i:%s')
ORDER BY vtiger_crmentity.createdtime");

echo "<table border='5'>
 <tr>
 <th>Account ID</th>
 <th>Account Number</th>
 <th>CRMID</th>
 <th>Createdtime</th>
 </tr>";
 while($row = mysql_fetch($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);
 ?>


  </body>
</html>

---

All it shows is the dates I selected and the headings for the columns, there is no data.

I am actually so excited and amped to get this to work as this is my first proper 'report' and I am enjoying it, just hit a dead-end and cannot complete it. So I will work with you as much as I can and give you whatever info I can so we can get this to work. I have learnt so much already and cannot wait to learn even more.

Thank you once again for your time and effort.

Harry

Quote:

---

Originally Posted by Dirty Harry

So either I start a complete new thing, or we go through it and trim out whats not needed.

---

I'd recommend the "starting fresh" route.

The pop-up date picker can be added next without much trouble; for now, it's not strictly necessary.
Let's start with just the essentials of submitting the form, processing it, querying the database, and displaying the results:
(untested; I don't have your DB)

PHP Code:

---

<?php
// let's rearrange the general flow of your script:
//    do all the PHP first; output the HTML when you're done
//    (and the function definitions are at the end, out-of-the-way).
// I'm also using the mysqli class instead of the (outdated) mysql extension

// in this example, we'll handle everything from the same script.

## Basics ##

// where we are.
const this_script_URL = '/URL/to/this/page.php';
// database credentials.
const DBhost = 'your-database-hostname';
const DBuser = 'your-database-username';
const DBpass = 'your-database-password';
const DBname = 'database-we-should-use';

## Logic ##
// this is where we decide what to do and tell PHP to do it.

if( empty( $_POST['submit'] ) ){
    // the form was not submitted.  Display the form.
    $OUTPUT = showForm();
}else{
    // the form was submitted.  get the dates requested.
    if( ($dates = processForm()) && $dates !== false ){
        // we got valid dates from the form submission. 
        // query the database (make sure we've connected okay first)
        if( ($DB = connectToDB()) && !mysqli_connect_error() ){
            $result = queryDatabase( $DB,$dates );
            // and format the results as HTML to be output.
            $OUTPUT = showResults( $result,$dates );
            // close the database connection
            $DB->close();
        }else{
            // could not connect to the database.
            $OUTPUT = showError( '...could not connect to the database.<br>'.mysqli_connect_error() );
        }
    }else{
        // the form submission was not valid.
        $OUTPUT = showError( '...the form submission was not valid.' );
    }
}
## All Done! ##

// not all servers specify UTF-8 by default (punk servers!)
//    the implication here is that *you* should be taking it upon yourself to use UTF-8 *everywhere* :)
header( "Content-Type: text/html; charset=utf-8" );
// send the HTML page and end PHP execution
exit( showPage( $OUTPUT ) );

## Function Definitions ##

// $error is any error message you wish to show (leave it empty if there are no errors).
// returns the HTML markup for your form.
function showForm( $error=false ){
    // the form submits to itself.
    $action = this_script_URL;
    // this is your form.
    $_form = <<< HTML
    <form action="$action" method="post">
        <p>Start Date: <input type="datetime" name="startdate"></p>
        <p>End Date: <input type="datetime" name="enddate"></p>
        <p><input type="submit" value="Search Dates"></p>
    </form>
HTML
;
    // if there's an error message, add it.
    if( $error ){
        $_form = '<p class="error">'.$error.'</p>'.$_form;
    }
    // return it.
    return $_form;
}

// validates and sanitizes the form submission.
// returns timestamps for start and end date/times if successful; false means that something is wrong.
// *NOTE: using strtotime() is quick and easy to demonstrate, but because of the way the function works,
//    date/times *before* Jan 1., 1970 *might not work* (depending on your server),
//    and date/times *before* Fri, 13 Dec 1901 20:45:54 OR *after* Tue, 19 Jan 2038 03:14:07 *DEFINITELY won't work.*
//    Probably not a dealbreaker right now, but other methods (e.g., the DateTime class) are more [past|future]proof.
function processForm(){
    // both startdate and enddate are required
    if( !empty( $_POST['startdate'] ) && !empty( $_POST['enddate'] ) ){
        // convert start and end to unix timestamps (this is just a quick way to validate them as dates)
        $start = strtotime( $_POST['startdate'] );
        $end = strtotime( $_POST['enddate'] );
        // if $start or $end are false, that means they were not valid dates* (see note above!!!)
        if( $start !== false && $end !== false ){
            return array( $start,$end );
        }
    }
    // if we get this far, something went wrong
    return false;
}

// returns a connection to your DB, using mysqli
function connectToDB(){
    return new mysqli( DBhost,DBuser,DBpass,DBname );
}

// $DB is the database connection object (we already made sure it was successful in the Logic portion of the code)
// $dates is an array holding the start and end dates
// returns the result of the database query
function queryDatabase( $DB,$dates ){
    // get the start & end dates
    list( $start,$end ) = $dates;
    // prepare SQL query
    // note I'm now using the mysql function FROM_UNIXTIME() because we're now passing a unix timestamp
    //    (they don't need to be escaped (sanitized) because unix timestamps have numeric characters only)
    $SQL = "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
        WHERE vtiger_crmentity.createdtime >= FROM_UNIXTIME('$start', '%Y-%m-%d %H:%i:%s')
        AND vtiger_crmentity.createdtime <= FROM_UNIXTIME('$end', '%Y-%m-%d %H:%i:%s')
        ORDER BY vtiger_crmentity.createdtime";
    // query the database
    $result = $DB->query( $SQL );
    // return the result
    return $result;
}

// $result is the results of the database query
// returns the results formatted as HTML
function showResults( $result,$dates ){
    // get the start and end dates
    list( $start,$end ) = $dates;
    // what if there was an error?
    if( $result === false ){
        return showError( '...the database query failed.<br>'.$DB->error() );
    }
    // what if there were no results?
    if( $result->num_rows === 0 ){
        return showError( '...no records found between '.date( 'Y-m-d H:i:s',$start ).' and '.date( 'Y-m-d H:i:s',$end ) );
    }
    // loop through results and build rows for your table.
    while( $r = $result->fetch_row() ){
        // get each field
        list( $accountID,$accountNo,$crmID,$created ) = $r;
        // use htmlspecialchars to make sure results are displayed as text
        $accountID = htmlspecialchars( $accountID );
        $accountNo = htmlspecialchars( $accountNo );
        $crmID = htmlspecialchars( $crmID );
        $created = htmlspecialchars( $created );
        // assign the new table row to an array of rows
        $row[] = "<tr><td>$accountID</td><td>$accountNo</td><td>$crmID</td><td>$created</td></tr>";
    }
    // make the array of rows into one long string
    $rows = implode( "\n",$row );
    // this is your table.
    $table = <<< HTML
    <table><tbody>
        <tr><th>Account ID</th><th>Account Number</th><th>CRMID</th><th>Time Created</th></tr>
        $rows
    </tbody></table>
HTML
;
    // return the table.
    return $table;
}

// $message is the error message to show
// returns the error message, along with the form so the user can try again.
function showError( $message='' ){
    // was there a message?
    if( empty( $message ) || !is_string( $message ) ){
        $message = '...something weird happened.';
    }else{
        // make safe for display
        $message = htmlspecialchars( $message );
    }
    // return error message with form
    return showForm( $message );
}

function showPage( $content ){
    // this is your whole page.
    $_html = <<< HTML
<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="UTF-8">
        <title>Search By Dates</title>
    </head>
    <body>
        <h1>Search by Dates</h1>
        <div id="output">$content&nbsp;</div>
    </body>
</html>
HTML
;
    // all done
    return $_html;
}
?>

---

Quote:

---

Originally Posted by Dirty Harry

<?php
$con = mysql_connect("localhost:*****","****","*******");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

---

Just as a matter of practise, I wouldn't post the password of your databases online, even if they are local host.

also, sorry; I overlooked establishing the database connection!

I've added this function to the function definitions in my post above:

PHP Code:

---

// returns a connection to your DB, using mysqli
function connectToDB(){
    return new mysqli( DBhost,DBuser,DBpass,DBname );
} 


---

Wow, that makes a hell of a lot more sense. Now atleast I sort of see how it works together. However, I have put in my db details, when I load the page, it is blank, nothing happens. What do I fix?

Why so bernie? I will keep it in mind for next time though.