Steve_NI Posted October 12, 2013 Share Posted October 12, 2013 (edited) 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"; } Edited October 12, 2013 by Steve_NI Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted October 12, 2013 Share Posted October 12, 2013 (edited) $Accname needs to be wrapped in quotes in the second query $mysqli->query("SELECT `Credit`, `Debit`, `Catagory` FROM `transactions WHERE `Accname`='$Accname'"); Edited October 12, 2013 by Ch0cu3r Quote Link to comment Share on other sites More sharing options...
Steve_NI Posted October 12, 2013 Author Share Posted October 12, 2013 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. Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted October 12, 2013 Share Posted October 12, 2013 (edited) 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; } Edited October 12, 2013 by Ch0cu3r Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted October 13, 2013 Share Posted October 13, 2013 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. Quote Link to comment Share on other sites More sharing options...
Steve_NI Posted October 13, 2013 Author Share Posted October 13, 2013 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? Quote Link to comment Share on other sites More sharing options...
Steve_NI Posted October 13, 2013 Author Share Posted October 13, 2013 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` = ?") Quote Link to comment Share on other sites More sharing options...
Steve_NI Posted October 13, 2013 Author Share Posted October 13, 2013 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 8QUERY 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. Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted October 13, 2013 Share Posted October 13, 2013 (edited) I think mac_gver meant to use a join query like this SELECT t.Credit, t.Debit, t.Catagory, # get the Credit, Debit and Category from transactions table a.Accname # get the Accname from the Accounts table FROM transactions t LEFT JOIN Account a WHERE a.Accname = t.Accname # Join Account table where the Accname in Accounts table matches the Accname in transactions table WHERE a.accID = ? # Only select the Account that matches the accID from $_GET['id'] Your code would then be $id = $_GET['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 = ?'); $stmt->bind_param("i", $id); if($stmt->execute()) { $stmt->bind_result($cred, $deb, $cat, $Accname); $stmt->fetch(); if ($stmt->num_rows > 0) { echo "Search worked"; echo "<p><b>$Accname</b>: Credit: $cred, Debit: $debit, Category: $cat</p>"; } else { echo "Search didnt work"; } } else { echo "QUERY ERROR: ".$mysqli->error; } Edited October 13, 2013 by Ch0cu3r Quote Link to comment Share on other sites More sharing options...
Steve_NI Posted October 13, 2013 Author Share Posted October 13, 2013 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! Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted October 13, 2013 Share Posted October 13, 2013 (edited) My join query most probably has an error in it add echo $mysqli->error; after the $mysqli->prepare(); Edited October 13, 2013 by Ch0cu3r Quote Link to comment Share on other sites More sharing options...
Steve_NI Posted October 13, 2013 Author Share Posted October 13, 2013 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! Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted October 13, 2013 Share Posted October 13, 2013 Add $stmt->store_result(); before if ($stmt->num_rows > 0) Also change echo "Search worked"; echo "<p><b>$Accname</b>: Credit: $cred, Debit: $debit, Category: $cat</p>"; to $stmt->bind_result($cred, $deb, $cat, $Accname); // bind fields to variales while($stmt->fetch()) // loop over the results, binding fields to variables above { echo "<p><b>$Accname</b>: Credit: $cred, Debit: $deb, Category: $cat</p>"; } Quote Link to comment Share on other sites More sharing options...
Steve_NI Posted October 13, 2013 Author Share Posted October 13, 2013 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; } ?> Quote Link to comment Share on other sites More sharing options...
Solution Ch0cu3r Posted October 13, 2013 Solution Share Posted October 13, 2013 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 Quote Link to comment Share on other sites More sharing options...
Steve_NI Posted October 13, 2013 Author Share Posted October 13, 2013 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. Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted October 13, 2013 Share Posted October 13, 2013 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. That warning is coming from your text editor not PHP. Your editor doesn't realise you have already defined it in connect-To-db.php. You can safely ignore the warning. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.