Jump to content

Prepared statements


Steve_NI
Go to solution Solved by Ch0cu3r,

Recommended Posts

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 by Steve_NI
Link to comment
Share on other sites

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 by Ch0cu3r
Link to comment
Share on other sites

 

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Ch0cu3r
Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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>";
        }
Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • Solution

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
Link to comment
Share on other sites

 

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.

Link to comment
Share on other sites

 

 

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.