Jump to content
Larry Ullman's Book Forums

Mysqli Object Oriented Query


Recommended Posts

Hi all,

 

I've been playing around with mysqli object oriented query.

I have a database called 'records2'.

Inside the 'records2' database, I created one table called 'players' and it is of MyISAM type.

The 'players' table has 3 fields: id, firstname, lastname.

 

If I only enter one entry, then the mysqli->query() works fine, but if I enter 2 or more entries inside the 'players' table, then suddenly the code below if($result = $mysqli->query("SELECT * FROM players ORDER BY id")) doesn't get called anymore at all.

 

Here is the code I use...as far as I can tell, there is nothing wrong with the code. What could be another possible explanation of why it works for a single entry and not for multiple entries?

 

To see what I mean in action, here is the video I posted on youtube to explain and show how the program is not working:

http://www.youtube.com/watch?v=gL9q7MeI-A8

 

 

Here is the code:

 

connect-db.php

<?php

 

$server = 'localhost';

$user = 'root';

$pass = 'root';

$db = 'records2';

 

 

$mysqli = new mysqli($server, $user, $pass, $db);

 

mysqli_report(MYSQLI_REPORT_ALL);

 

echo "ECHOING FROM connect-db.php"."<br>";

 

 

if ($mysqli->connect_errno) {

printf("Connect failed: %s\n", $mysqli->connect_error);

exit();

}

?>

 

view.php

 

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">

<html>

<head>

<title> View Records</title>

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>

 

</head>

 

<body>

<h1> View Records</h1>

 

<?php

include('connect-db.php');

 

if($result = $mysqli->query("SELECT * FROM players ORDER BY id"))

{

 

if($result->num_rows > 0)

{

echo "<table border = '1' cellpadding = '10'>";

 

echo "<tr><th>ID</th><th>First Name</th><th>Last Name</th></tr>";

 

while($row = $result->fetch_object())

{

echo "<tr>";

 

 

echo "<td>" . $row->id . "</td>";

echo "<td>" . $row->firstname . "</td>";

echo "<td>" . $row->lastname . "</td>";

 

echo "</tr>";

}

 

echo "</table>";

}

else

{

echo "No results to display!";

}

 

}

else

{

echo "Error: " . $mysqli->error;

}

 

 

$mysqli->close();

?>

Link to comment
Share on other sites

Another solution, I think, is $mysqli->multi_query()

 

$query  = 'SELECT * FROM table1';

$query .= 'SELECT * FROM another_table';
$mysqli->multi_query($query);

 

 

I concatenate a lot of strings to make up a large query in a statistics script. I did this procedural, bot It seems like the function works exactly the same way in OOP. It took me awhile to figure out the errors.

 

If this is what you need, remember to use mysqli::use_result() for the first query and mysqli::next_result for the others. From the manual on multi_query:

To retrieve the resultset from the first query you can use mysqli_use_result() or mysqli_store_result(). All subsequent query results can be processed using mysqli_more_results() and mysqli_next_result().

 

Looks like you solved id, but might be worth mentioning anyway. I guess it works because you tell the DBS to don't give a f*** about the problem. Maybe I'm wrong. :P

Link to comment
Share on other sites

 Share

×
×
  • Create New...