Steve_NI

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

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.

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?

Thats perfect Barand, once again many thanks for your help.

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?

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.

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?

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!!!

Ignore my last post I was being a bit of a tube. Sorted now, many thanks for your help!

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?

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?

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

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?

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!

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'; } } ?>

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.

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

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!

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!

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.

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` = ?")

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?

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.

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"; }