1

I have checked everywhere thoroughly, and have gone through everything possible to find an answer to this. Besides saying "the code doesn't work" which obviously is not enough, I have yet to find anything that will even come close to this. I'm probably going to get downvotes, but let's see how this goes.

I am learning how to do prepared statements for a search query from the user end, and I have to do it for multiple queries. I have to bind parameters to these multiple queries, and then execute them and use them and receive multiple rows. This is most of my code, and what I currently have is not reporting any errors whatsoever. It just returns a blank white page.

I am doing this from a simple test.php file, and those are the results I'm getting.

Now for the code.

$prep1 = $test->prepare("SELECT * FROM sb__bans WHERE sb__bans.authid=? ORDER BY sb__bans.bid DESC");
$prep2 = $test->prepare("SELECT * FROM sb__bans AS bans INNER JOIN sb__admins AS admins ON bans.aid = admins.aid WHERE bans.authid=? ORDER BY bans.bid DESC");
$prep3 = $test->prepare("SELECT * FROM sb__bans AS bans INNER JOIN sb__servers AS servers ON bans.sid = servers.sid WHERE bans.authid=? ORDER BY bans.bid DESC");

$search = "steam";

$prep1->bind_param("s", $search);
$prep2->bind_param("s", $search);
$prep3->bind_param("s", $search);

$prep1->execute();
$prep2->execute();
$prep3->execute();

while($row = $prep1->fetch() && $admin = $prep2->fetch() && $sv = $prep3->fetch()) {
echo $row['test'];
echo $admin['test'];
echo $sv['test'];
}

The database is initialized above this as $test = new mysqli("localhost", "test", "test", "test");

$search = "steam" steam would be replaced with the the post variable of course, but for testing reasons I've removed that for now and am testing with just a simple variable.

What seems to be the problem here?

Thanks in advance.

Joel
  • 17
  • 1
  • 1
  • 10

2 Answers2

0

Regarding the general question you asked.

There is not a single problem with having multiple queries prepared. While speaking of getting results from a prepared query, there is indeed a problem caused by the result buffering. In order to be able to execute another query, you have to call store_result()/get_result() right after execute.

Regarding the particular problem you have.

  1. To get errors you have to ask PHP for them.
  2. There is absolutely no point in making three queries, you have to make just one. If you have a trouble making one, ask another question marking it with mysql tag and bringing your 3 queries along.
  3. Even for multiple queries it's just wrong idea to do multiple fetches in a single loop. Fetch your query results one by one.
  4. Your mysqli syntax even for a single query is incomplete. You need to re-read your tutorial and practice on a single query first.
Community
  • 1
  • 1
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
-1

Two points:

  1. Based on personal experience, you can only have one prepared statement in existence at a time. I suspect this is because the db requires each PS to have a session-unique name, and the PHP layer is passing some common default name rather than generating a unique name for each PS. By comparison, the PostgreSQL driver allows an optional name for each PS, but still allows only one unnamed PS to exist. Essentially this means that you must prepare, bind, execute and fetch one PS completely before you can prepare the next PS.

  2. You're misusing mysqli_stmt::fetch(). fetch() returns only true or false, and is used to update variables which have previously been bound with mysqli_stmt::bind_result(). To retrieve values into a $row array, you must first call mysqli_stmt::get_result() to return a mysqli_result, and then call mysqli_result::fetch_array().

Darwin von Corax
  • 5,201
  • 3
  • 17
  • 28
  • I've sorted them so that one is prepared at a time and then binds the result (since I cannot use get_result because I do not have mysqlnd) and then I use fetch_array, but I am getting this error before it is able to fetch array `PHP Warning: mysqli_stmt::bind_result(): Number of bind variables doesn't match number of fields in prepared statement` – Joel Apr 20 '16 at 03:24
  • If you're doing `SELECT *` you have to `bind_result()` as many variables as there are columns in the table. You might actually find it easier to do all this using [PDO](http://php.net/manual/en/book.pdo.php). – Darwin von Corax Apr 20 '16 at 03:30
  • I did not know that. Thanks. I took your tip and decided to rewrite it to use PDO, and everything seems to work now. The only problem I have left now is that for some reason empty() is not working on a clearly empty array. I'll save that for another question though. Thanks for the help. – Joel Apr 20 '16 at 03:51
  • @YourCommonSense Do you have anything useful to add to that? – Darwin von Corax Apr 20 '16 at 06:24