PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!
Moderator: General Moderators
GeXus
Forum Regular
Posts: 631 Joined: Sat Mar 11, 2006 8:59 am
Post
by GeXus » Sat Apr 21, 2007 10:16 pm
I just started using mysqli for stored procs and i'm having a bit of a pickle.. Here is my code
Code: Select all
$db = mysql_connection();
if(isset($_GET['cat_id'])){
$cat_id = $_GET['cat_id'];
}else{
$cat_id = '1';
}
if(isset($_GET['delete_cat_id'])){
$delete_cat_id = $_GET['delete_cat_id'];
if($db->query("CALL delete_category_node($delete_cat_id);") === TRUE){
echo "Category Deleted";
}
}
$query = $db->query("CALL get_category_nodes($cat_id);");
$row = $query->fetch_array(MYSQLI_ASSOC);
while($row = $query->fetch_array(MYSQLI_ASSOC)){
echo "<a href=?cat_id=" . $row['category_id'] . ">" . $row['name'] . "</a> [ <a href=\"?delete_cat_id=" . $row['category_id'] . "\">x</a> ]<br/>";
}
$db->close();
Everything works just fine, but when I delete a category (by setting delete_cat_id) and it calls the $db->query to a SP, I receive this error
Fatal error: Call to a member function fetch_array() on a non-object in /home/welike/public_html/tree.php on line 24
However, when i'm not deleting (delete_cat_id is not set) that code works fine...
Any help would be awsome! Thanks!
volka
DevNet Evangelist
Posts: 8391 Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger
Post
by volka » Sat Apr 21, 2007 10:28 pm
There's no error handling in your script. try
Code: Select all
$sql = "CALL get_category_nodes($cat_id);";
$query = $db->query($sql);
if ( !$query ) {
echo '<div>Error: ', $db->error, '</br>Query: ', htmlentities($sql), "</div>\n";
die();
}
$row = $query->fetch_array(MYSQLI_ASSOC);
GeXus
Forum Regular
Posts: 631 Joined: Sat Mar 11, 2006 8:59 am
Post
by GeXus » Sat Apr 21, 2007 10:39 pm
I got this
Error: Commands out of sync; you can't run this command now
Query: CALL get_category_nodes(1);
Not sure what that means..
GeXus
Forum Regular
Posts: 631 Joined: Sat Mar 11, 2006 8:59 am
Post
by GeXus » Sat Apr 21, 2007 10:48 pm
I added in $query->close();, from what I understand this should free the result so it can go to the next, but it doesnt work....
Code: Select all
if(isset($_GET['delete_cat_id'])){
$delete_cat_id = $_GET['delete_cat_id'];
if($query = $db->query("CALL delete_category_node($delete_cat_id);") === TRUE){
echo "Category Deleted";
$db->mysqli_free_result($query);
$query->close();
}
}
$sql = "CALL get_category_nodes($cat_id);";
$query = $db->query($sql);
if ( !$query ) {
echo '<div>Error: ', $db->error, '</br>Query: ', htmlentities($sql), "</div>\n";
die();
}
$row = $query->fetch_array(MYSQLI_ASSOC);
while($row = $query->fetch_array(MYSQLI_ASSOC)){
echo "<a href=?cat_id=" . $row['category_id'] . ">" . $row['name'] . "</a> [ <a href=\"?delete_cat_id=" . $row['category_id'] . "\">x</a> ]<br/>";
}
$query->close();
$sql = "CALL get_categories();";
$query = $db->query($sql);
if ( !$query ) {
echo '<div>Error: ', $db->error, '</br>Query: ', htmlentities($sql), "</div>\n";
die();
}
$row = $query->fetch_array(MYSQLI_ASSOC);
while($row = $query->fetch_array(MYSQLI_ASSOC)){
echo "<a href=?cat_id=" . $row['category_id'] . ">" . $row['name'] . "</a> [ <a href=\"?delete_cat_id=" . $row['category_id'] . "\">x</a> ]<br/>";
}
$query->close();
$db->close();
volka
DevNet Evangelist
Posts: 8391 Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger
Post
by volka » Sat Apr 21, 2007 11:12 pm
$db->mysqli_free_result($query);
If the script really reached that line you should have got a fatal error since there is no such method defined for a mysqli object, see
http://de2.php.net/mysqli
Is your code really not indented at all?
GeXus
Forum Regular
Posts: 631 Joined: Sat Mar 11, 2006 8:59 am
Post
by GeXus » Sat Apr 21, 2007 11:23 pm
I've indented it for you
This is the full code.. (less db connection)
Code: Select all
<?
session_start();
error_reporting(E_ALL);
require_once('includes/config.php');
$db = mysql_connection();
if(isset($_GET['cat_id'])){
$cat_id = $_GET['cat_id'];
}else{
$cat_id = 1;
}
if(isset($_GET['delete_cat_id'])){
$delete_cat_id = $_GET['delete_cat_id'];
if($query = $db->query("CALL delete_category_node($delete_cat_id);") === TRUE){
echo "Category Deleted";
$query->close();
}
}
//GET CATEGORIES BY NODE
$sql = "CALL get_category_nodes($cat_id);";
$query = $db->query($sql);
if ( !$query ) {
echo '<div>Error: ', $db->error, '</br>Query: ', htmlentities($sql), "</div>\n";
die();
}
$row = $query->fetch_array(MYSQLI_ASSOC);
while($row = $query->fetch_array(MYSQLI_ASSOC)){
echo "<a href=?cat_id=" . $row['category_id'] . ">" . $row['name'] . "</a> [ <a href=\"?delete_cat_id=" . $row['category_id'] . "\">x</a> ]<br/>";
}
$query->close();
//GET ALL CATEGORIES
$sql = "CALL get_categories();";
$query = $db->query($sql);
if ( !$query ) {
echo '<div>Error: ', $db->error, '</br>Query: ', htmlentities($sql), "</div>\n";
die();
}
$row = $query->fetch_array(MYSQLI_ASSOC);
while($row = $query->fetch_array(MYSQLI_ASSOC)){
echo "<a href=?cat_id=" . $row['category_id'] . ">" . $row['name'] . "</a> [ <a href=\"?delete_cat_id=" . $row['category_id'] . "\">x</a> ]<br/>";
}
$query->close();
$db->close();
?>
This is what I get..
TELEVISIONS [ x ]
PORTABLE ELECTRONICS [ x ]
Error: Commands out of sync; you can't run this command now
Query: CALL get_categories();
The two categories shown, are from the 'GET CATEGORIES BY NODE' part.. it should then show the 'GET ALL CATEGORIES', but it's throwing that error...
GeXus
Forum Regular
Posts: 631 Joined: Sat Mar 11, 2006 8:59 am
Post
by GeXus » Sun Apr 22, 2007 11:44 am
Anyone?
GeXus
Forum Regular
Posts: 631 Joined: Sat Mar 11, 2006 8:59 am
Post
by GeXus » Sun Apr 22, 2007 1:23 pm
Also, this works fine if I have inline sql... but when using the stored procs it's causing this error..