mysqli query with mysql function inside

Question

I'm trying to produce a passfrase on the database with a function I found somewere. If i use the sql query directly on the database it produces a string as desired, but in my php page it gives the error:

"Fatal error: Uncaught Error: Call to a member function fetch_array() on boolean"

$query_rsCaptchaID = "DELIMITER $$
DROP FUNCTION IF EXISTS `randomPasswordGenerator` $$
CREATE FUNCTION `randomPasswordGenerator`(
  ) RETURNS varchar(64) CHARSET utf8
BEGIN
  DECLARE charCount TINYINT(1) DEFAULT 0;
  DECLARE charDiceRoll TINYINT(2);
  DECLARE randomChar CHAR(1);
  DECLARE randomPassword CHAR(64) DEFAULT '';
  REPEAT
    SET charCount = charCount + 1;
    SET charDiceRoll = 1 + FLOOR(RAND() * 94);
    IF (charDiceRoll <= 32)
    THEN
      SET randomChar = ELT(charDiceRoll,
      '`', '~', '!', '@', '#', '$', '%', '^',
      '&', '*', '(', ')', '-', '=', '_', '+',
      '[', ']', '{', '}', '\\', '/', '|', '?',
      ';', ':', '\'', '\"', ',', '.', '<', '>');
    ELSEIF (charDiceRoll >= 33)
      AND (charDiceRoll <= 68)
    THEN
      SET charDiceRoll = charDiceRoll - 33;
      SET randomChar = CONV(
        charDiceRoll,
        10, 36);
    ELSE
      SET charDiceRoll = charDiceRoll - 59;
      SET randomChar = LOWER(
        CONV(
          charDiceRoll,
          10, 36)
      );
    END IF;
    SET randomPassword = CONCAT(randomPassword, randomChar);
  UNTIL (charCount = 64)
  END REPEAT;
  RETURN randomPassword;
END $$
DELIMITER ;
SELECT randomPasswordGenerator() AS captchaID;";
$rsCaptchaID = mysqli_query($db,$query_rsCaptchaID);
$row_rsCaptchaID = $rsCaptchaID->fetch_array();

Any ideas anyone? I´m quite new on MySQLi, and not english speaking, sorry for errors.

`is_a` function has been deprecated for a long time (PHP 5.0). Use `instanceof` operator instead. — BeetleJuice, Jul 26 '16 at 13:48
I took the way thru php: `code`function random_str($length,$keyspace = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ`~!@#$%^&*()-=_+[]{}\\/|?;:\'\",.<>') { $str = ''; $max = mb_strlen($keyspace, '8bit') - 1; if ($max < 1) { throw new Exception('$keyspace must be at least two characters long'); } for ($i = 0; $i < $length; ++$i) { $str .= $keyspace[random_int(0, $max)]; } return $str; } $colname_captchaID = random_str(64); — David Sundberg, Jul 26 '16 at 14:38

score 0 · Answer 1 · edited Jul 26 '16 at 13:18

0

That error occurs because your query fails. Between the last two lines you posted, you need to do some error checking:

$rsCaptchaID = mysqli_query($db,$query_rsCaptchaID);
if(!$rsCaptchaID) die(mysqli_error($db); // <- will show you the SQL error
$row_rsCaptchaID = $rsCaptchaID->fetch_array();

edited Jul 26 '16 at 13:18

RiggsFolly

93,638
21
103
149

answered Jul 26 '16 at 13:14

BeetleJuice

39,516
19
105
165

I always think its better to do `if($rsCaptchaID === false)` as `$rsCaptchaID` just might look false because its not a scalar variable. But I guess its just a personal choise – RiggsFolly Jul 26 '16 at 13:20
@RiggsFolly I also tend to use `=== false` in my own code to build good habits. In this instance though there is no possibility of this failing because `mysqli_query` can only return `true`, `false`, or a resource (http://php.net/manual/en/mysqli.query.php). Only a legitimate error can reach `die` – BeetleJuice Jul 26 '16 at 13:34
My point is that when its a resource, it might just possibly look like a false! – RiggsFolly Jul 26 '16 at 13:36
I don't think so @RiggsFolly . Objects with properties are truthy. Even empty objects unless your PHP is a decade old. See this fiddle https://3v4l.org/7XTco. For the docs, see the section `Converting to boolean`: http://php.net/manual/en/language.types.boolean.php – BeetleJuice Jul 26 '16 at 13:45
Interesting reading – RiggsFolly Jul 26 '16 at 13:53
Thanks @BeetleJuice! I think I´ll give it up as a query and do the passfrase in php insted... – David Sundberg Jul 26 '16 at 14:13
@DavidSundberg I think that's a good idea. I appreciate the thanks: upvote and/or selecting the answer would be even better. Happy coding. – BeetleJuice Jul 26 '16 at 15:29

mysqli query with mysql function inside

1 Answers1