This topic is locked
[SOLVED]

 Summary Page

3/13/2015 11:44:38 PM
PHPRunner General questions
C
cms001 author

I need to create a summary page based on several different tables, but need to show all the information on one page.
What would be the best way to create a page like this? So far I have created a custom php page for this - but it would be nice to have it look like a "list page"
On the summary page I need to calculate several values via mysql:

  1. Total outstanding Jobs
  2. Total jobs not invoiced
  3. Total amount of domains expiring this week
  4. Total turnover this month

    (This I can do - no -problem)
    My question:
  5. Using the custom page - how do I tap into the build-in phpRunner database connection or do I have to specify it like below?

    Currently I am using this:

    // OUR MAIN CONNECTION

    $host="locahost";

    $user="username";

    $pwd="password";

    $port="";

    $sys_dbname="mydqlDB";
    2.My second question is how do I display the result of a mysql count statement?

    $sql = "Select count(*) as domCount from domainnames"
    ****I have tried several options, but cannot get to show the count value**

    $rs=CustomQuery($sql);

    $data=db_fetch_array($rs);
    $row = mysql_fetch_assoc($data);

    // $row = mysql_fetch_field($data['domCount']);

    // $num = $row['domCount'];
    // $rsExists = db_query($strSQLExists,$conn);

    // $data=db_fetch_array($rsExists);

    end*****
    This code is working, I can see the retrieved list of transactions:

    $sql = "select * from domainnames";

    $result = $conn->query($sql);
    if ($result->num_rows > 0) {

    // output data of each row

    while($row = $result->fetch_assoc()) {

    echo "id: " . $row["domainID"]. " - Name: " . $row["domnme"]. " " . $row["type"]. "
    ";

    }

    } else {

    echo "0 results";

    }
    Any assistance will be appreciated.

C
cms001 author 3/14/2015

Got it to work:

Not sure if it 100% correct, but it is working -
answer to question 1:

require_once("include/dbcommon.php");

//** Check if specific record exists ****

global $conn;
answer to question 2

$sql = "select count(*) as domCount from domainnames";

$rs=CustomQuery($sql);

$data=db_fetch_array($rs);
if($data)

{

// set value of count

$_SESSION['TotalDomExp'] = $data['domCount'];

}

else

{

// set to zero

$_SESSION['TotalDomExp'] = 0;

}
****



I need to create a summary page based on several different tables, but need to show all the information on one page.
What would be the best way to create a page like this? So far I have created a custom php page for this - but it would be nice to have it look like a "list page"
On the summary page I need to calculate several values via mysql:

  1. Total outstanding Jobs
  2. Total jobs not invoiced
  3. Total amount of domains expiring this week
  4. Total turnover this month

    (This I can do - no -problem)
    My question:
  5. Using the custom page - how do I tap into the build-in phpRunner database connection or do I have to specify it like below?

    Currently I am using this:

    // OUR MAIN CONNECTION

    $host="locahost";

    $user="username";

    $pwd="password";

    $port="";

    $sys_dbname="mydqlDB";
    2.My second question is how do I display the result of a mysql count statement?

    $sql = "Select count(*) as domCount from domainnames"
    ****I have tried several options, but cannot get to show the count value**

    $rs=CustomQuery($sql);

    $data=db_fetch_array($rs);
    $row = mysql_fetch_assoc($data);

    // $row = mysql_fetch_field($data['domCount']);

    // $num = $row['domCount'];
    // $rsExists = db_query($strSQLExists,$conn);

    // $data=db_fetch_array($rsExists);

    end*****
    This code is working, I can see the retrieved list of transactions:

    $sql = "select * from domainnames";

    $result = $conn->query($sql);
    if ($result->num_rows > 0) {

    // output data of each row

    while($row = $result->fetch_assoc()) {

    echo "id: " . $row["domainID"]. " - Name: " . $row["domnme"]. " " . $row["type"]. "
    ";

    }

    } else {

    echo "0 results";

    }
    Any assistance will be appreciated.

C
cms001 author 3/14/2015

One more question:
in the statement:

$sql = "Select count(*) from tablename";
is this safe or can mysql injections be applied to this format of coding,
or what is the correct way of creating a statement like this.
I have used dal in the add/edit pages, but on the custom page it is still above me - cannot get that to work.

Admin 3/14/2015

It's safe. You only need to worry about SQL injection if you accept input from the end user and need to use it in your SQL query.