Prepared statements

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

Edited October 12, 2013 by Steve_NI

[Ch0cu3r]

$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

[Steve_NI]

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.

[Ch0cu3r]

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

[mac_gyver]

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.

[Steve_NI]

 

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?

[Steve_NI]

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

[Steve_NI]

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.

[Ch0cu3r]

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

[Steve_NI]

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!

[Ch0cu3r]

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

Edited October 13, 2013 by Ch0cu3r

[Steve_NI]

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!

[Ch0cu3r]

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

[Steve_NI]

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

[Ch0cu3r]

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

[Steve_NI]

 

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.

[Ch0cu3r]

 

 

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.