SyncViews

So can anyone explain why I get that error because Ive really got no idea why it doesn't work with a stored procedure ?

opps thought I included the connection in the first code snippet... mysqli

This worked fine untill I made one of the sql statements into a prodcedure. Now I'm getting some error about commands being out of sync <?php ... $result = $sql->query("CALL forum_get_index()") or exit(__LINE__.':'.$sql->error); $i = -1; while($row = $result->fetch_assoc()) { //check if viewing is allowed. If viewing of a section is dissallowed but viewing of the contained forums is allowed the forum will appear in the section "above" if(!($user_admin || $row["visible"])) { //check all valid permissions forum_permission($row["forum_id"], $permissions); if(!$permissions["visible"]) continue; } unset($row["visible"]); if($row["depth"] == 0) { //increment counter and reduce amount of needed data $sections[++$i]["data"] = array( 'id' => $row["forum_id"], 'name' => $row["forum_name"], 'desc' => $row["forum_desc"] ); } else { $row["last_post_time"] = date("M j Y, h:i A",$row["last_post_time"]); unset($row["depth"]); $sections[$i]["forums"][] = $row; } } $result->close(); ... ?> <?php function forum_permission($forum, &$permissions) { global $sql, $user_id; //check user permissions $result = $sql->query("SELECT visible, create_topic, post_reply FROM permissions_forum_user WHERE user_id=$user_id AND forum_id=$forum") or exit(__LINE__.':'.$sql->error); //<---ERROR HERE<--- if($result->num_rows > 0) { $data = $result->fetch_assoc(); if($data["visible"]) $permissions["visible"] = true; if($data["create_topic"]) $permissions["create_topic"] = true; if($data["post_reply"]) $permissions["post_reply"] = true; } $result->close(); //check users groups $result = $sql->query(" SELECT p.visible, p.create_topic, p.post_reply FROM group_users u, permissions_forum_group p WHERE p.group_id = u.group_id AND u.user_id = $user_id ") or exit(__LINE__.':'.$sql->error); while($row = $result->fetch_assoc()) { if($row["visible"]) $permissions["visible"] = true; if($row["create_topic"]) $permissions["create_topic"] = true; if($row["post_reply"]) $permissions["post_reply"] = true; } $result->close(); } ?> The procedure DELIMITER $$ DROP PROCEDURE IF EXISTS forum_get_index$$ CREATE PROCEDURE forum_get_index() BEGIN SELECT node.forum_id, node.forum_name, node.forum_desc, node.topics, node.posts, node.last_topic_id, node.last_topic_name, node.last_post_id, node.last_post_time, node.last_user_id, node.last_user_name, node.visible, sub.depth FROM forums AS node, ( SELECT node.forum_id AS forum_id, COUNT( parent.forum_id ) -1 AS depth FROM forums AS node, forums AS parent WHERE node.left BETWEEN parent.left AND parent.right GROUP BY node.forum_id ORDER BY node.left ) AS sub WHERE node.forum_id = sub.forum_id GROUP BY node.forum_id HAVING sub.depth < 2 ORDER BY node.left; END$$

So is there anyway I can get around it without having to make 1 query to get the forums list, make the results comma seperated and insert them into a second query? EDIT: Seeing as mysql now has stored procedures is there an easy way to use them to do this?

I don't see why this shouldn't work... UPDATE forums SET topics=topics+1, posts=posts+1, last_topic_name='Test topic...', last_topic_id=14, last_post_id=19, last_post_time=1209662581, last_user_id=1, last_user_name='SyncViews' WHERE forum_id IN ( SELECT parent.forum_id FROM forums AS forum, forums AS parent WHERE forum.left BETWEEN parent.left AND parent.right AND forum.forum_id = 5 ) #1093 - You can't specify target table 'forums' for update in FROM clause

I was goign through that today using the "nested set model" from that page but I'm having some trobel with an update statement. It needs to update the last post/topic details of all the forums from the current one to the root but I'm getting an error and I'm not sure why The query thats sent to the server: UPDATE forums SET topics=topics+1, posts=posts+1, last_topic_name='Test topic...', last_topic_id=14, last_post_id=19, last_post_time=1209662581, last_user_id=1, last_user_name='SyncViews' WHERE forum_id IN ( SELECT parent.forum_id FROM forums AS forum, forums AS parent WHERE forum.left BETWEEN parent.left AND parent.right AND forum.forum_id = 5 ) The error:

Each of my records stores the id of the "parent" record or 0 if it's a top level record. Is there an easy way to select every record in this chain rather than this that makes tons of calls for long chains. from my new topic script ...$parent_id = id of forum for topic to be created in <?php //get parent forum list $getparent = $sql->prepare("SELECT forum_name, parent_id FROM forums WHERE forum_id=?"); while($parent_id != 0) { $forum_id = $parent_id; $getparent->bind_param("i", $parent_id); $getparent->execute(); $getparent->bind_result($forum_name, $parent_id); $getparent->fetch(); $parents[] = array('id' => $forum_id, 'name' => $forum_name); } if($parents)$smarty->assign('parent_forums', array_reverse($parents)); $getparent->close(); ... //update forums $parents_str = ''; foreach($parents as $i=>$parent) { if($i==0)$parents_str .= $parent["id"]; else $parents_str .= ", $parent[id]"; } $sql->query(" UPDATE forums SET topics=topics+1, posts=posts+1, last_topic_name='$subject', last_topic_id=$topic_id, last_post_id=$post_id, last_post_time=$time, last_user_id=$user_id, last_user_name='$user_name' WHERE forum_id IN ($parents_str) ") or exit(__LINE__.':'.$sql->error); ?>

But then I still have a copy for each user. Is it posible to have just one copy of it for everyone? (not so much for this data but for some larger data this could massivly reduce the amount of ram being used...)

Right now almost every page is making the same sql querys to get things like paths to the users template directory etc. eg: $result = $mysqli->query("SELECT style_path, style_name FROM styles WHERE style_id=$_SESSION[style]"); This seems a waste of resources and I'm certain it would be more efficent just to keep the whole lot in memory as an array... $styles 0 => 'name' => 'Default' 'path' => 'styles/default' 1 => 'name' => 'Deep Space' 'path' => 'styles/deepspace' So is there someway I can have this array present accross all my scripts? A shared global if you like.

<?php ... //update forums $parents_str = ''; foreach($parents as $i=>$parent) { if($i==0)$parents_str . $parent["id"]; else $parents_str . ", $parent[id]"; } $sql->query(" UPDATE forums SET topics=topics+1, posts=posts+1, last_topic_name='$subject', last_topic_id=$topic_id, last_post_id=$post_id, last_post_time=$time, last_user_id=$user_id, last_user_name='$user_name' WHERE forum_id IN ($parents_str) ") or exit(__LINE__.':'.$sql->error); ...

1) I need to insert some data into two tables. The problem is the two records need to "point" to each other... <?php mysql_query("INSERT INTO topics (date, title, forum_id) VALUES ($date, '$subject', $_GET[forum]) ")or exit(mysql_error()); $topic_id = mysql_insert_id(); mysql_query("INSERT INTO posts (date, content, topic_id, user_id) VALUES ($time, '$content', $topic_id, $user_id) ")or exit(mysql_error()); $post_id = mysql_insert_id(); mysql_query("UPDATE topics SET first=$post_id WHERE topic_id=$topic_id")or exit(mysql_error()); ?> I tried doing this but it seems it's not allowed.. <?php ... mysql_query(" INSERT INTO topics, posts (topics.date, topics.title, topics.forum_id, topics.first, posts.topic_id, posts.user_id, posts.content, posts.date) VALUES ($time, '$subject', $_GET[forum], posts.post_id, topics.topic_id, $user_id, '$content', $time) ")or exit(mysql_error()); ... ?> Do I really need to make 3 seprate querys? 2)This makes loads of querys I'd rather not... <?php ... $con = mysql_connect($sql_host, $sql_user, $sql_pass) or exit('Failed to connect to database: ' . mysql_error()); $con2 = mysql_connect($sql_host, $sql_user, $sql_pass) or exit('Failed to connect to database: ' . mysql_error()); mysql_select_db($sql_db, $con2); $con3 = mysql_connect($sql_host, $sql_user, $sql_pass) or exit('Failed to connect to database: ' . mysql_error()); mysql_select_db($sql_db, $con3); //get sections $result1 = mysql_query(" SELECT section_id, section_name FROM sections ORDER BY display ASC ",$con)or exit(mysql_error($con)); for($i=0; $row1 = mysql_fetch_array($result1); ++$i) { $sections[$i]["DATA"]['ID'] = $row1["section_id"]; $sections[$i]["DATA"]['NAME'] = $row1["section_name"]; //get forums $result2 = mysql_query(" SELECT forum_id, forum_name, forum_desc, forum_topics, forum_posts FROM forums WHERE section_id=$row1[section_id] ORDER BY display ASC ", $con2) or exit(mysql_error($con2)); for($i2=0; $row2 = mysql_fetch_array($result2); ++$i2) { $sections[$i]["FORUMS"][$i2]["ID"] = $row2["forum_id"]; $sections[$i]["FORUMS"][$i2]["NAME"] = $row2["forum_name"]; $sections[$i]["FORUMS"][$i2]["DESC"] = $row2["forum_desc"]; $sections[$i]["FORUMS"][$i2]["TOPICS"] = $row2["forum_topics"]; $sections[$i]["FORUMS"][$i2]["POSTS"] = $row2["forum_posts"]; //get last post data if($row2["forum_topics"]) { $sections[$i]["FORUMS"][$i2]["LAST"] = true; $result3 = mysql_query(" SELECT users.user_id, users.username, posts.date, topics.topic_id, topics.title FROM users, posts, topics WHERE topics.forum_id=$row2[forum_id] AND topics.topic_id=posts.topic_id AND posts.user_id=users.user_id ORDER BY posts.date DESC LIMIT 0,1 ", $con3) or exit(mysql_error($con3)); $row3 = mysql_fetch_array($result3); $sections[$i]["FORUMS"][$i2]["LAST_TOPIC_ID"] = $row3["topic_id"]; $sections[$i]["FORUMS"][$i2]["LAST_TOPIC_NAME"] = $row3["title"]; $sections[$i]["FORUMS"][$i2]["LAST_TIME"]= date("M j Y, h:i A",$row3["date"]); $sections[$i]["FORUMS"][$i2]["LAST_USER_ID"] = $row3["user_id"]; $sections[$i]["FORUMS"][$i2]["LAST_USER_NAME"] = $row3["username"]; } else $sections[$i]["FORUMS"][$i2]["LAST"] = false; } } $smarty->assign ('SECTIONS', $sections); ... ?>

Ok it's addin quotes this time. I was going to do it the same way as the other bb codes (see parse_part) but the problem is it then won't work with say this because the quote tags are nolonger in the same string so won't be matched... [quote] [code] some quoted code [/code] [/quote] but if I prase it before spliting then this wouldn't work instead [code] [quote] this is what quote tags look like [/quote] [/code] I really have no idea how to do it so both the above examples work... <?php function parse($str) { $parts = preg_split('#(\[/?code\])#i', $str, -1, PREG_SPLIT_DELIM_CAPTURE); // every fourth index will contain content to be parsed (starting at 0) foreach ($parts as $i => $part) { switch($i % 4) { case 0: $parts[$i] = parse_part($part); break; case 1: $parts[$i] = '<div class="bb_code_outer"><span class="bb_code_title">CODE:</span><pre class="bb_code">'; break;//code header case 2: $parts[$i] = htmlentities($part, ENT_QUOTES); break; case 3: $parts[$i] = '</pre></div>'; break;//code footer } } return join('', $parts); } function parse_part($str) { $str = htmlentities($str, ENT_QUOTES); //add new lines $str = str_replace("\n","<br />\n",$str); //smilies $smilie_root = $url_root.'/smilies/'; $str = str_replace('','<img scr="'.$smilie_root.'smile.png" alt="smile" title="smile" />',$str); $str = str_replace('','<img scr="'.$smilie_root.'sad.png" alt="sad" title="sad" />',$str); $str = str_replace('','<img scr="'.$smilie_root.'biggrin.png" alt="biggrin" title="biggrin" />',$str); $str = str_replace('','<img scr="'.$smilie_root.'tongue.png" alt="tongue" title="tongue" />',$str); //bb codes $bb_codes = array( '/\[b\](.*?)\[\/b\]/is', //BOLD '/\[i\](.*?)\[\/i\]/is', //ITALIC '/\[u\](.*?)\[\/u\]/is', //UNDERLINE '/\[url\](.*?)\[\/url\]/is', //URL '/\[url\=(.*?)\](.*?)\[\/url\]/is', //URL2 '/\[img\](.*?)\[\/img\]/is', //IMAGE '/\[mail\](.*?)\[\/img\]/is', //MAIL '/\[mail\=(.*?)\](.*?)\[\/mail\]/is', //MAIL2 '/\[colo?r\=(.*?)\](.*?)\[\/colo?r\]/is', //COLOUR '/\[size\=(.*?)\](.*?)\[\/size\]/is', //SIZE '/\[font\=(.*?)\](.*?)\[\/font\]/is', //FONT '/\[align\=(left|center|right)\](.*?)\[\/align\]/is', //ALIGN '/\[pre\](.*?)\[\/pre\]/is' //PREFORMATTED ); $bb_html = array( '<span style="font-weight: bold">$1</span>', //BOLD '<span style="font-style: italic">$1</span>', //ITALIC '<span style="text-decoration: underline">$1</span>', //UNDERLINE '<a href="$1">$1</a>', //URL '<a href="$1">$2</a>', //URL2 '<img src="$1" alt="$1" />', //IMAGE '<a href="mailto:$1">$1</a>', //MAIL '<a href="mailto:$1">$2</a>', //MAIL2 '<span style="color: $1">$2</span>', //COLOUR '<span style="font-size: $1">$2</span>', //SIZE '<span style="font-family: $1">$2</span>', //FONT '<span style="text-align: $1">$2</span>', //ALIGN '<pre>$1</pre>' //PREFORMATTED ); $str = preg_replace ($bb_codes, $bb_html, $str); return $str; } ?>

tabel users user_id user_name ... tabel pms to from ... The id's from the pms (to and from) tabel match up with the users in the users tabel. I though about doing it like below but thats makes two rows for one pm but I only want one row SELECT users.user_id, users.user_name, pms.pm_id, pms.time, pms.content, pms.subject FROM users, pms WHERE pms.to = users.user_id OR pms.from = users.user_id is there someway I can make it just one row per pm with a result like this? pm_id time content subject to.user_id to.user_name from.user_id from.user_name

opps...it's WHERE categorys.category_id=sections.category_id

database structure categorys: category_id category_name category_display sections: section_id category_id section_name section_decription section_display The category_id in the sections tabel says what category the section is under. I need to query the database and get these into some arrays but I'm not sure what is the best way. Each "group" needs to be order by the _display valuse in assending order category | |--section -> section data | |--section -> section data category | |--section -> section data So I should have 3 "layers" of arrays eg Eg to get the name of the first section in the first catagory: $data[0][0]["section_name"] I have got it to an extent but it doubles up the category data and I'm not sure it's a particualy fast query either... The other problem is I can't see a good way to seperate the catagorys so I can insert the catagory names in between where as with a big array I could just use a double foreach loop <?php $query = mysql_query(' SELECT category.category_name, sections.sections_name, sections.sections_desc FROM categorys, sections WHERE category.category_id=category.category_id ORDER BY categorys.display, sections.display ') or exit(mysql_error()); ?>