0

I am trying to debug a weird issue. I have a query which joins several tables to pull out various information to populate some emails that are sent out. Whenever I try to generate some test emails to verify the code is working, one of the emails is produced without any data in it due to this query issue.

Here is the function which runs the query:

function getSData($id,$uid)
{
    global $sql;

    echo "PID: ".$id." | UID: ".$uid."</br>";

    if ($pd_stmt = $sql->prepare("select t1.Name,t2.SDate,t2.DCode,t3.Quantity,t2.Location,t3.Cost from seminar_events t2 inner join seminars t1 on t2.SID = t1.ID inner join cart_list t3 on t2.ID = t3.PID where t2.ID = ? and t3.CID = ?;"))
    {
        if ($pd_stmt->bind_param('ii',$id,$uid))
        {
            if ($pd_stmt->execute())
            {
                $pd_stmt->bind_result($sname,$sdate,$dcode,$qty,$slocale,$cost);
                $pd_stmt->fetch();

                echo "Array:";
                print_r(array("Name"=>$sname,
                             "SDate"=>$sdate,
                             "DCode"=>$dcode,
                             "Quantity"=>$qty,
                             "Local"=>$slocale,
                             "Cost"=>number_format((float)$cost)));
            } else{
                echo $sql->error;
            }
        } else{
            echo $sql->error;
        }
    } else{
        echo $sql->error;
    }
}

Here is the PHP output:

103 //from an earlier echo checking the value of $pID just before this function is called and is passed as the first arg

PID: 103 | UID: 24

Array:

If I take this query and the values above and substitute them in for the ? parameters in the query and run it in PHPMyAdmin I get the records I expected.

Shadow
  • 33,525
  • 10
  • 51
  • 64
Geowil
  • 624
  • 1
  • 12
  • 36
  • I haven't coded in php for a few years now, but I think you supply one to many values: `$pd_stmt->bind_param('ii',$id,$uid)`. Perhaps try removing the `'ii'`? – Renier Jan 06 '17 at 07:12
  • Not enough `?`s in your `$sql->prepare()`... and remove the `;` at the end of that String argument. – StackSlave Jan 06 '17 at 07:35
  • I cannot see anyithing obviously incorrect with the code ;-/ Debugging: I would look at the integer versions of the input strings... `var_dump("PID: ", $id, (int) $id, "UID: ", $uid, (int) $uid);` as they may have invalid characters in them. I would also use 'general logging' and see what the mysql engine is actually getting from PHP. – Ryan Vincent Jan 06 '17 at 10:59
  • @Renier No, the argument list is correct. The first arg is a list of types for the arguments that follow it in the order of the argument list. I am substituting in two integers thus a type list of ii. – Geowil Jan 06 '17 at 16:38
  • @PHPglue there are two arguments being substituted into the query, I am passing two parameters to bind_param. Which params are missing from the bind? – Geowil Jan 06 '17 at 16:39
  • @RyanVincent Thanks, I will try those when I get home from work today. – Geowil Jan 06 '17 at 16:40
  • this https://stackoverflow.com/questions/45388357/mysql-query-working-in-phpmyadmin-but-not-in-php might work for you – Irshad Babar Jan 24 '20 at 19:55

1 Answers1

0

If you'll write which SQL AL you're using it'll helpful, as it does not seem to be PDO or Mysqli. But what I can suppose - it is similar to the both, and then you should have to see, which parameters the method bind_param takes. In PDO there is the parameter, the variable and the type of the variable. Please check the reference for this method and see, if there is a bind_value method, which will be better here or if the execute method could not get input params like in http://php.net/manual/de/pdostatement.execute.php

Lukas
  • 201
  • 1
  • 4
  • It is prepared and parameterized Mysqli. bind_param takes at least two arguments. The first is a list of the var types to follow in the argument list, the rest are the variables which will be substituted into the query for the ?s in the order that they appear. – Geowil Jan 06 '17 at 16:36