Jump to content

Steve_NI

Members
 View Profile  See their activity

  • Posts

    25

  • Joined

  • Last visited

 Content Type 

Posts posted by Steve_NI

    Date differences

    in PHP Coding Help

    Posted 

    $objToday = new DateTime(date('Y-m-d'));  #  create datetime object for today
$objStart = new DateTime($goalDate);          # create datetime object for goal date

$objDuration = $objStart->diff($objToday, true);     # create a DateInterval object using the diff method of our $objStart datetime object passing in $objToday.

echo 'Days since start date: ' . $objDuration->format('d');    #  call the format method of our DateInterval object to say we just want the number of FULL (whole) DAYS  since the start

    Appreciate your efforts I tried that but it printed out Days since start date d !

    Date differences

    in PHP Coding Help

    Posted

    $goalDate is the string..  $start is the DateTime object

     

    So    

     

    $interval = date_diff($start,$end);

    Thanks for your help

    When I try:

    $today = date('Y-m-d');
$start = date_create($goalDate);

$interval = date_diff($start,$today);

    I get the error : date_diff() expects parameter 2 to be DateTime, string given

     

    But when I then make the second parameter a string:

    $start = date_create($goalDate);
$end = date_create($today);

$interval = date_diff($start,$end);

    I get the error: Object of class DateInterval could not be converted to string

     

    I'm getting myself very confused all I want is a number of days between the two dates that I could divide by 7 to work out the number of weeks between the them :confused:

    Date differences

    in PHP Coding Help

    Posted

    You need to initialise your dates with the DateTime object before you use date_diff()

     

    See examples here

    http://www.php.net/manual/en/datetime.diff.php

     I followed the examples with my code:

    $start = date_create($goalDate);
$end = date_create($today);
$interval = date_diff($goalDate,$today);

    I still get the same error as before : date_diff() expects parameter 1 to be DateTime

    The examples seem to use hard coded dates, but mine will depend on what the user inserts and also will differ dependent upon the current date.

    Date differences

    in PHP Coding Help

    Posted

    I am trying to work the date difference between two dates

    I have a form that a user completes and in that form they provide an end date.

    In my php I convert this to a unix date:

    $stamp = strtotime($_POST['endldate']);
    $endDate = date('Y-m-d', $stamp);

     

    I then want todays date:

    $today = date('Y-m-d');

     

    I then want to get the difference in days between the two.

    I have tried $difference = datediff($endDate,$today)

     

    But this returns an error = date_diff() expects parameter 1 to be DateTime

     

     

    What i want to do is then divide the difference by 7 to get the number of weeks

     

    How do I get around the error?

    Numerous addition and subtraction elements in one query

    in MySQL Help

    Posted

    Sorry, one final issue. I had to change around the code slightly to take into consideration that the balance on the credit card wil be a negative etc

    So it looks like:

    Select Sum(
               (Select SUM(Balance) from account) +
               (Select sum(
                            (select sum(Balance) from credit_card) +
                            (select sum(-Amount) FROM bills where Date > $today)
                          )
               )
    ) as Total

    But if I have a balance on the bank accounts of 742, a balance on credit cards of -948 and bills still to come out before the end of the month at 189.88 the resultant query produces a total of -405.88000000000001.

    How do I get it rounded down to just the two decimal places?

    Numerous addition and subtraction elements in one query

    in MySQL Help

    Posted

     

    Don't leave a space between the SUM and the (

     

    ie SUM( and not SUM (

     

    Another solution would be

    SELECT SUM(total)
FROM
    (
    SELECT SUM(balance) as total FROM account
    UNION
    SELECT SUM(-balance) as total FROM credit_card
    UNION
    SELECT SUM(-amount) as total FROM bills WHERE date > 12
    ) as tots

    Thats its sorted now! Cheers Barand, who would have known a space would cause so much chaos (apart from you obviously lol!) Thanks for your help guys.

    Numerous addition and subtraction elements in one query

    in MySQL Help

    Posted

    The quotes in that message are surrounding the part of the SQL that it had a problem with -- Line 1 refers to line 1 of the query (it's all on one line, so that is not helpful).

     

    Your query appears to be missing a closing parenthesis at the end. So the quotes in the error are referring to the nothing it found when looking for that closing parenthesis.

     

    Here is what your query looks like - I split it out so the groupings are more visible:

    Select Sum (
               (Select SUM(Balance) from account) -
               (Select sum(
                            (select sum(Balance) from credit_card) - 
                            (select sum(Amount) FROM bills where Date > 12)
                          )
               )

    Many thanks for coming back to me.

    I have fixed the missing parentheses but when I run the query now I get

    #1630 - FUNCTION finance_checker.Sum does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual

    In this case finance_checker is the name of the datavase containing all the tables. Any ideas how can the SUM function not work for a MySQL database?

    Numerous addition and subtraction elements in one query

    in MySQL Help

    Posted

    Following the guidelines I have a the following query

    Select Sum ((Select SUM(Balance) from account)-(Select sum((select sum(Balance) from credit_card) - (select sum(Amount) FROM bills where Date > 12)))

    I want to tally up the total users have in their bank accounts(the database will only have one user) so if they have two accounts one with a balance of 1000 and the other with a balance of 500 their total is 1500. From this I want to deduct their credit card balance plus a total of any bills left to be paid before the end of the month. So if its the 12th today and they have a dd for electricity for 50 on the 15th and a dd for gas on the 25th for 100 thats 150, and their current credit card bill is 600, that means (600 +150)= 750. This is taken away from their account total (1500-750) thus our user has 750 to do him the rest of the month.

     

    Here is the table structure

    CREATE TABLE `bills` (
 `BillID` int(11) NOT NULL AUTO_INCREMENT,
 `Payee` varchar(100) NOT NULL,
 `Amount` double(6,2) NOT NULL,
 `Accname` varchar(50) NOT NULL,
 `Date` int(11) NOT NULL,
 PRIMARY KEY (`BillID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1

CREATE TABLE `account` (
 `accID` int(11) NOT NULL AUTO_INCREMENT,
 `Bankname` varchar(50) NOT NULL,
 `Accname` varchar(50) NOT NULL,
 `Sortcode` int(11) NOT NULL,
 `Accnum` varchar( NOT NULL,
 `Balance` double NOT NULL,
 `PasswordHint` varchar(100) NOT NULL,
 `OverdraftLimit` double NOT NULL,
 PRIMARY KEY (`accID`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=latin1

CREATE TABLE `credit_card` (
 `CreditCardID` int(11) NOT NULL AUTO_INCREMENT,
 `ProviderName` varchar(50) NOT NULL,
 `AccountNumber` int(11) NOT NULL,
 `PasswordHint` varchar(50) NOT NULL,
 `CreditLimit` int(11) NOT NULL,
 `InterestRate` double NOT NULL,
 `PaymentDay` int(11) NOT NULL,
 `Accname` varchar(50) NOT NULL,
 `Balance` int(11) NOT NULL,
 PRIMARY KEY (`CreditCardID`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1

    On running the query I get

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

    But I have no quotes on line 1!

     

    I have tried running the two queries individually. So I can total the balances and I can add the bills to the credit card bills its only when I try to put the two together does it fall down.

     

    Apologies if this is too long I have tried to stick by the rules in the sticky!!!

    Dropdown selection query

    in PHP Coding Help

    Posted

     

    1. The option values should be 7, 28 and 180 and not the text descriptions

    <option value='7'>Seven</option>

    2. You need to test if the $_GET data exists (ie has the form been submitted)

    $days = isset($_GET['days']) ? intval($_GET['days']) : 7;    // sets seven as the default

    Again thanks Barand

    I have changed the option values in the form to make them integers 1,7,28 & 100

    <form  action="data.php" method="get">
               <select name="days">
                  <option value="1">One Day</option>
                  <option value="7">Seven Days</option>
                  <option value="28">Twenty Eight days</option>
                  <option value="180">One Hundred and Eighty Days</option>
            </select>
            <input type="submit" value="Submit">
               
    </form>

    And in the data.php form I am running the test you suggest

    $days = isset($_GET['days']) ? intval($_GET['days']):7;

echo "Number of days = ".$days;

    Each time however it echoes out the days is equal to 0. I cannot understand why it is not getting the value submitted from the dropdown menu. Should days at the very least be 7 if that is what we put the default value to be in the ternary operator?

    Dropdown selection query

    in PHP Coding Help

    Posted

     

    If $days is the value selected from your dropdown, then your query will be like

    $days = intval($_GET['days']);
$query = "SELECT account, amount, date
    FROM transaction
    WHERE date BETWEEN CURDATE() - INTERVAL $days DAY AND CURDATE()";

    Thanks Barand

    Here is my HTML code

    <html>
    <head>
        <title>
            Dropdown
        </title>
    </head>
    <body>
        <form  action="data.php" method="get">
               <select name="days">
                  <option value="One">1</option>
                  <option value="Seven">7</option>
                  <option value="TwentyEight">28</option>
                  <option value="OneHundredEighty">180</option>
            </select>
            <input type="submit" value="Submit">
               
    </form>
    </body>
</html>

    But when i put the following into php

    $days = $_GET['days'];

    I get an undefined index error. Can you see where i have gone wrong?

    Dropdown selection query

    in PHP Coding Help

    Posted

    I have a mysql database that contains account transactions amount, account and Date

    I want to give the user a dropdown option where they have a number of options over which time frame they can see the data

    i.e they can see transactions over the last 1, 7, 28 days

     

    I know the mysql query I need to write to search the database for each option but how do I link the two together?

    i.e. the user selects the 7 day option, this runs the mysql query and I can produce a table with that data

    or if the user selects the 28 day option this runs a different mysql query and I can reproduce a table with that data.

     

    Can anyone give me some pointers

     

    Many thanks

    PHP and Dropdown boxes with MySQL

    in PHP Coding Help

    Posted

    Ok I have managed to code it so that I can loop through the database and it has given me my drop down box populated with unique references from the database.

    Here is the code:

    $server = 'localhost';
$user='root';
$pass='';
$db = 'finance_checker';

$mysqli = mysqli_connect($server, $user, $pass, $db);

$query = $mysqli->query("SELECT distinct `catagory` FROM `transactions`");

while($array[]= $query->fetch_object());

array_pop($array);

?>

        
        <h3>Payments Made by Catagory</h3>
        <select name="the_name">
            <?php foreach ($array as $option): ?>
            <option value="<?php echo $option->Transaction; ?>"><?php echo $option -> catagory;?></option>
            <?php endforeach; ?>
        </select>
        
        <?php
        $query-> close();
        ?>

    This now gives me a dropdown that looks something like:

    Food

    Clothes

    Entertainment

    Petrol

     

    Now what I would like is for my user to be able to click on one of these dropdown options lets say food and this then will link to a search which will for example "Select `payee`, `amount`, `date` From `transactions` Where `catagory` = Food" and then produce a new table of information of all food transactions ie:

    Payee   Amount   Date

    Tesco     20.45      26/10/2013

    Boots      12.32      25/10/2013

     

    How would I go about linking the dropdown options back to the database?

    PHP and Dropdown boxes with MySQL

    in PHP Coding Help

    Posted

    Hi

     

    I'm looking for some help.

    I am looking to query a mysql database and get distinct elements from a particular column and have the result set populate a dropdown box.

    After that I am looking for the ability of a user to click on one of those drop down options and from that it will produce all rows in the table that have that element in it.

     

    I'm new to php but the site I am developing is using PHP.

     

    First thing I need to know is that if this is even possible to do and if so would anyone have any tutorials that would guide me?

     

    thanks!

    Validating a registration form

    in PHP Coding Help

    Posted

    I am preparing a form form for a new user to register to join a site. I am trying to put some validation in to the user input to ensusre passwords are correct size, and they reverify it etc before it is submitted to the database.

    I keep getting the same error though when I run the page that I have undefined index. When I actually enter data and hit submit these errors disappear and the appropriate error messages are given to the user.

    I know its something simple I am forgetting to do but I cannot fathom it out, would someone with more experience be able to point out my error?

    <?php
function user_exists($username){
$server = 'localhost';
$user='root';
$password='';
$db = 'finance_checker';

$mysqli = mysqli_connect($server, $user, $password, $db);

if(mysqli_connect_errno($mysqli)){
    echo "Failed to connect to MySQL".mysqli_connect_error();
}
$res = $mysqli->query("SELECT * FROM `users` WHERE `UserName` = '$username'");

return ($res->num_rows>0);
$res->close();
}

?>

<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Registration</title>
        <link rel="stylesheet" type="text/css" href="style.css"/>
    </head>
    <body>
        <h1>Registration</h1>
        <form action="registration.php" method="post">
            <ul id="register">
                <li> Username : *<br />
                    <input type ="text" name="username"/>
                </li>
                <li> Password : *<br />
                    <input type ="password" name="password"/>
                </li>
                <li>
                    Re-Confirm Password : *<br/>
                    <input type="password" name="password2"/>
                </li>
                <li> First Name: *<br />
                    <input type ="text" name="firstname"/>
                </li>
                <li> Last Name : *<br />
                    <input type ="text" name="lastname"/>
                </li>
              
                <li> Email : *<br />
                    <input type ="text" name="email"/>
                </li>
                
                    <input type="submit" value="Register"/>
                     
            </ul>
    </body>
</html>

<?php

if(isset($_POST)){
if(empty($_POST)==false){
    $req_fields=array('username','password','password2','firstname','lastname','email');
    
    foreach ($_POST as $key=>$value){
        if(empty($value)&& in_array($key, $req_fields)===true){
            echo 'Please complete all fields to register!';
            break 1;
        }
        
    }
}
//If there are no errors 

    if(user_exists($_POST['username'])==true){
        echo 'Cannot use the username '.$_POST['username'].' it has already been taken!<br />';
    }
    if(preg_match("/\\s/", $_POST['username'])){
        echo "Your username must not contain any spaces!";
    }
    //Make sure the password is of sufficient length
    
    if((strlen($_POST['password']<6))||(strlen($_POST['password']>12))){
       echo 'Password must be at least 6 characters long and no more than 12.<br />';
        }
    if($_POST['password']!=$_POST['password2']){
        echo 'Passwords do not match. Please try again! <br />';
    }
    if(!(filter_var($_POST['email'], FILTER_VALIDATE_EMAIL))){
        echo 'Email address is not valid.Please enter a valid email address';
    }
    }

?>

    Prepared statements

    in PHP Coding Help

    Posted

     

    Get rid of $stmt->fetch(); before $stmt->store_result();

     

    Also is this code being ran in a function? If it is then don't use global $mysqli; to get the mysqli object. You should pass that object to your function as an argument

    function myFunc($mysqli) {  // mysqli object passed as argument
   $mysqli->query( ... );
   ... etc ...
}

$mtsqli = new mysqi( ... );  // create mysqli object
myFunc($mysqli);             // pass mysqli object to function

    That is is working now, you sir are a gentleman and a scholar, thanks very much with all your help and patience with this. Thanks so much! Does this site do a rating on user helpfulness in order that I can add to yours?

     

    PS its not a function but I keep getting a yellow trianglewarning that the variable seems to be unitialized, I googled that and that was one piece of advice I picked up, clearly I picked it up incorrectly.

    Prepared statements

    in PHP Coding Help

    Posted

    Still no luck

    Here is what the code looks like at present, I am echoing out the id that I am getting and it is definitely there but when I run the query it still tells me the that the search didnt work but I know there is data in the database that meets the search criteria.

    <?php
//connect to database
    include ('connect-To-db.php');
        $id = $_GET['id'];
        global $mysqli;
        echo "$id";
       $stmt = $mysqli->prepare("SELECT t.Credit, t.Debit, t.Catagory,a.Accname
           FROM transactions as t LEFT JOIN account as a ON a.Accname=t.Accname
           WHERE a.accID=?");
       echo $mysqli->error;

       $stmt->bind_param("i", $id);
         if($stmt->execute()){
           
            $stmt->fetch();
            $stmt->store_result();
                if ($stmt->num_rows>0){
                     $stmt->bind_result($cred, $deb, $cat, $Accname); 
                        while($stmt->fetch())                              
                            {
                                echo "<p><b>$Accname</b>: Credit: $cred, Debit: $deb, Category: $cat</p>";
                             }
                }
                else{
                    echo "Search didnt work";
                }
            }
                
                else {
                    echo "QUERY ERROR: ".$mysqli->error;
                }
?>

    Prepared statements

    in PHP Coding Help

    Posted

    My join query most probably has an error in it add echo $mysqli->error; after the $mysqli->prepare();

    Getting their slowly but surely I had a couple of backticks in that I shouldnt have. All errors have now gone.

    However on running the query it echos "Search didnt Work" but I know the account and transaction databases, there are 9 transactions where the a.Accname = t.Accname. What other breaks could I put in to flesh out what is preventing this code from running? Once again thanks for your patience with this on a sunday!

    Prepared statements

    in PHP Coding Help

    Posted

    I tried that word for word Ch0cu3r and still get a  Fatal error: Call to a member function bind_param() on a non-object on line 10

    Line 10 is the $stmt-> bind_param("i",$id); line of code

    I've googled it for advice and it refers to scope but I cannot see how that is the case here.

    Thanks for your patience with this!

    Prepared statements

    in PHP Coding Help

    Posted

    Mac_gyver, I tried your advice and used this code:

    $id = $_GET['id'];
       if($stmt = $mysqli->prepare("SELECT `Credit`, `Debit`, `Catagory` FROM `transactions` WHERE `Accname`='$Accname' IN SELECT `Accname` FROM `Account` WHERE `accID`=?"))
                {
       
            $stmt->bind_param("i", $id);
            $stmt->execute();
            $stmt->bind_result($cred, $deb, $cat,$Accname);
            
            while($stmt->fetch()){
                if ($stmt->num_rows>0){
                    echo "Search worked";
                }
                else {
                    echo "Search didnt work";
                }
            }
                }
                else {
                    echo "QUERY ERROR: ".$mysqli->error;
                }

    This produces the error: Notice: Undefined variable: Accname in on line 8
    QUERY ERRORYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT `Accname` FROM `Account` WHERE `accID`=?' at line 1. I need this to work but i'll be honest I'm not too sure of what is causing the errors.

    Prepared statements

    in PHP Coding Help

    Posted

    your second query is missing a back-tick, producing a sql syntax error.

     

    however, you should never run a select query inside of a loop, and your two queries are related. just run one JOIN'ed, prepared query and be done with it.

    Thanks mac_gyver, just so I am clear are you suggesting I run a prepared query along the lines of ("SELECT `Credit`,`Debit`, `Catagory` FROM `tranasctions` WHERE `Accname` in SELECT `Accname` FROM `Account` WHERE `accID` = ?")

    Prepared statements

    in PHP Coding Help

    Posted

     

    Check that the query is not returning an error

     

    Change 

    $query = $mysqli->query("SELECT `Credit`, `Debit`, `Catagory` FROM `transactions WHERE `Accname`=$Accname");
              
                if($query->num_rows>0){
                 echo "Yes";
                    }
                else{
                 echo "No";
                    }

    to

    // if query return true
if($query = $mysqli->query("SELECT `Credit`, `Debit`, `Catagory` FROM `transactions WHERE `Accname`='$Accname'"))
{
    // see how many rows where returned
    if($query->num_rows>0) {
      echo "Yes";
    }
    else{
      echo "No";
    }
}
// query returned false, due to an error
else {
  // get error
  echo 'Query Error: ' . $mysqli->error;
  exit;
}

    Hi Ch0cu3r, thanks for that, I tried your advice and I get the error message Trying to get a project of a non object and the line the error refers to is the line that states if($query->num_rows>0).

    I looked for some information on that and one thought was to put in $query->store_result(), however when I do this it then produces the error Call to a member function store_result() on a non-object and then refers to the $query->store_result() line of code. I can only presume that there is a problem with $query as a non-object, but I'll be honest I dont really understand what this means and how I can rectify it. Would you have had any experience of this?

    Prepared statements

    in PHP Coding Help

    Posted

    Many thanks for coming back to me on that.

    I have amended the query but I now have an error saying Trying to get property of non object and it refers me to the line if($query->num_rows>0)

    I'm not really getting what the problem could be.

    Prepared statements

    in PHP Coding Help

    Posted · Edited by Steve_NI

    I have a question re a project I'm doing in php/mysql.
    I am designing a site where users can record their financial accounts (bank account/credit cards etc) and the transactions and their type that they undertake on a monthly basis on each one of them.
    What I want is that the user selects a particular account and using GET I can take the account id, and use that to get all transactions associated with that account name from a different table in the database.
    I get the error trying to get the property of non-object and I'm unsure where I have gone wrong. Ive put some echo comments in previously and can see the GET is working and the initial statement works and I can retrieve the Accname using the ID. It appears to be the next part where I take that Accname that I have got and try to use it to get data from another table that I run into problems.

     

    Here is my code, apologies if its ugly I am really only learning this and piecing together bits and pieces from various online tutorials.

    <?php
//connect to database
    include ('connect-To-db.php');
    
  
        $id = $_GET['id'];
       if($stmt = $mysqli->prepare("SELECT `Accname` FROM `Account` WHERE `accID`=?")){
            $stmt->bind_param("i", $id);
            $stmt->execute();
           
 $stmt->bind_result($Accname);
            
            while($stmt->fetch()){
             $query = $mysqli->query("SELECT `Credit`, `Debit`, `Catagory` FROM `transactions WHERE `Accname`=$Accname");
              
                if($query->num_rows>0){
                 echo "Yes";
                    }
                else{
                 echo "No";
                    }
             }
          
       else
       {
           echo "ERROR";
       }
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.