PHP SQL statement doesn't work but works directly from phpmyadmin

Question

I've seen a lot of questions on this, however I haven't found the answer specific to mine. I have a PHP where users input a ticket number, the information will get stored in one database, however I want to check a different database and table to check if that number is in the table. The input is a string, and I've tried intval($ticket) but still doesn't work.

    $ticketQry = mysqli_prepare($con,"select count(*) from <database2>.mantis_bug_table where id =?");
    mysqli_stmt_bind_param($ticketQry,'i',intval($Ticket));
    mysqli_execute($ticketQry);
    var_dump($ticketQry);

    Result:
    object(mysqli_stmt)#7 (10) { ["affected_rows"]=> int(-1) ["insert_id"]=> int(0) ["num_rows"]=> int(0) ["param_count"]=> int(1) ["field_count"]=> int(1) ["errno"]=> int(0) ["error"]=> string(0) "" ["error_list"]=> array(0) { } ["sqlstate"]=> string(5) "00000" ["id"]=> int(6) }

I found that affected_rows int(-1) means there is something wrong with the query, but I cannot for the life of me figure it out.

I tried adding a mysqli_select_db() statement to switch between the two databases and yes I removed from the SQL statement when I did this, but that didn't work.

Now if I go in phpmyadmin and put in the same parameter and sql statement, it works just fine. I did try binding it as a string as well.

If I run this in phpmyadmin, the result is 1. So again I know this is working from phpmyadmin, but not my PHP script.

$con has the db info in it so not sure why you are needing to use — Maxqueue, Apr 02 '20 at 19:41
Do you really have `<>` around the database name? That's not valid MySQL syntax. — Barmar, Apr 02 '20 at 19:42
@Maxqueue, because I guess I should say I'm connecting to the localhost, and then using mysqli_select_db(), — dcary, Apr 02 '20 at 19:55
@Barmar, no I don't have <> around the database name, I would get an error, my result shows now errors, it's just having an issue with running the query, not a SQL syntax error. — dcary, Apr 02 '20 at 19:56
[mysqli\_fetch\_assoc() expects parameter / Call to a member function bind\_param() errors. How to get the actual mysql error and fix it?](//stackoverflow.com/q/22662488) — mario, Apr 02 '20 at 19:57
`affected_rows` is not relevant, since this is a `SELECT` query. But `num_rows = 0` is suspicious. A `COUNT(*)` query should alway return 1 row. — Barmar, Apr 02 '20 at 19:59
@mario, I have this in my script ini_set('display_errors', 1); ini_set('log_errors',1); error_reporting(E_ALL); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); I showed what the result is in my question. — dcary, Apr 02 '20 at 20:00
@Barmar, according to the php manual,https://www.php.net/manual/en/mysqli.affected-rows.php -1 indicates that the query returned an error. — dcary, Apr 02 '20 at 20:01
"Returns the number of rows affected by the last INSERT, UPDATE, REPLACE or DELETE query" It's not one of those queries. — Barmar, Apr 02 '20 at 20:02
@Barmar, would count(*) still return 1 row even if the condition isn't met? Because that's what I'm trying to do, just search the table to see if the input exists in that table. I want it to be able to find the record, or find nothing, which would result in the user re-entering the information. — dcary, Apr 02 '20 at 20:07
Yes, it should always return 1 row, that's why I said `num_rows = 0` indicates a problem. Maybe you need to call `mysqli_stmt_store_result($ticketQry)` first. — Barmar, Apr 02 '20 at 20:08
@Barmar, oh I see what you mean. It should say 0 if nothing comes back. Yeah, so again with my original question, there is something wrong with the SQL however I'm not seeing it. mysqli_stmt_store_result() before the execute? My var_dump is right after the execute(). — dcary, Apr 02 '20 at 20:12
@Barmar, what would be the difference between mysqli_stmt_store_result() and mysqli_stmt_bind_result()? I guess I could have included that, but under the code I shared I bond the result to a variable for comparison. Which I get a result of NULL. However when I put in your code for store_result() I did get a num_row of 1 now. — dcary, Apr 02 '20 at 20:22
`bind_result` doesn't fetch anything, it just sets up which variables will be filled in when you call `fetch`. — Barmar, Apr 02 '20 at 20:23
@Barmar, THANK YOU! This worked. Okay, I was having a heck of a time. This works. — dcary, Apr 02 '20 at 20:27
@Barmar, sorry I have another question. When I do $result = mysqli_fetch($ticketQry); and do var_dump($result), I'm getting the bool(true) I want the actual record, with whatever the count is, 1 or 0. — dcary, Apr 02 '20 at 20:44
You need to use `mysqli_stmt_bind_result($count);`. Then after you fetch, `$count` will contain the value. — Barmar, Apr 02 '20 at 20:46
There are examples in the documentation, you should follow them. — Barmar, Apr 02 '20 at 20:46

score 1 · Accepted Answer · answered Apr 02 '20 at 20:21

You need to call mysqli_stmt_store_result() to buffer the results of a prepared statement.

php > $db = new mysqli('localhost', 'username', 'password', 'dbname');
php > $stmt = $db->prepare("SELECT COUNT(*) from t_users where username = ?");
php > $username = 'barmar';
php > $stmt->bind_param("s", $username);
php > $stmt->execute();
php > var_dump($stmt);
object(mysqli_stmt)#1 (10) {
  ["affected_rows"]=> // affected_rows is initially -1
  int(-1)
  ["insert_id"]=>
  int(0)
  ["num_rows"]=> // num_rows is initially 0
  int(0)
  ["param_count"]=>
  int(1)
  ["field_count"]=>
  int(1)
  ["errno"]=>
  int(0)
  ["error"]=>
  string(0) ""
  ["error_list"]=>
  array(0) {
  }
  ["sqlstate"]=>
  string(5) "00000"
  ["id"]=>
  int(1)
}
php > $stmt->store_result();
php > var_dump($stmt);
object(mysqli_stmt)#1 (10) {
  ["affected_rows"]=> // affected_rows is now 1
  int(1)
  ["insert_id"]=>
  int(0)
  ["num_rows"]=> // num_rows is now 1
  int(1)
  ["param_count"]=>
  int(1)
  ["field_count"]=>
  int(1)
  ["errno"]=>
  int(0)
  ["error"]=>
  string(0) ""
  ["error_list"]=>
  array(0) {
  }
  ["sqlstate"]=>
  string(5) "00000"
  ["id"]=>
  int(1)
}

PHP SQL statement doesn't work but works directly from phpmyadmin

1 Answers1