TKW22 Posted June 9, 2009 Share Posted June 9, 2009 how to put into form mysql to a list like this.what i want is to do something like this if a mysql row = the same as an old one it'll do this .say main cat in hi.then you have a matching main cat it gets the link out of it and goes into a new list like so. hia link[*]back to cats mostly for a menu and a template engine i'v ben working on a long time.sorry for not being clear. Link to comment Share on other sites More sharing options...
justsomeguy Posted June 9, 2009 Share Posted June 9, 2009 It's hard to understand what you're asking for, if you want to store a list like that in a database you just need an extra field to keep track of which parent each item belongs to. You would use either a recursive function to get the entire list out, or you can load one parent at a time by looking for nodes that have a certain parent. Link to comment Share on other sites More sharing options...
TKW22 Posted June 9, 2009 Author Share Posted June 9, 2009 It's hard to understand what you're asking for, if you want to store a list like that in a database you just need an extra field to keep track of which parent each item belongs to. You would use either a recursive function to get the entire list out, or you can load one parent at a time by looking for nodes that have a certain parent.thanks that's what im looking for.what would the code look like?im still very new to php. Link to comment Share on other sites More sharing options...
justsomeguy Posted June 9, 2009 Share Posted June 9, 2009 Here's an example of a recursive function to do something similar, from one of my applications. A recursive function is one that calls itself. The relationship is similar, a user group in my application can have child user groups, so this function gets all of the children and grandchildren of any user group. This returns an array of user group info, the "children" element in the array is an array of the child groups. Notice that the function calls itself to get the children for the current group. # get a tree structure of all usergroups with checkboxes (unchecked)function get_usergroup_checknodes($par = 0){ global $db; $retval = array(); $db->sql('SELECT * FROM usergroups WHERE parent=%d ORDER BY title ASC'); $db->add_param($par, $esc=false); $ugs = $db->select(); foreach ($ugs as $ug) { $cur = array( 'text' => $ug['title'], 'ugid' => $ug['id'], 'checked' => false, 'children' => get_usergroup_checknodes($ug['id']) ); $retval[] = $cur; } return $retval;} Here's example output, the array is in JSON format: [ { "text":"MCCCD", "ugid":"7", "checked":false, "children": [ { "text":"XYZ Community College <i>(1)<\/i>", "ugid":"8", "checked":false, "children": [ { "text":"XYZ Healthcare Program <i>(3)<\/i>", "ugid":"9", "checked":false, "children": [ { "text":"Faculty <i>(0)<\/i>", "ugid":"11", "checked":false, "children":[] }, { "text":"Students <i>(0)<\/i>", "ugid":"10", "checked":false, "children":[] }, { "text":"XYZ Guest Agency <i>(0)<\/i>", "ugid":"12", "checked":false, "children":[] } ] } ] } ] }, { "text":"Test UG <i>(0)<\/i>", "ugid":"13", "checked":false, "children":[] }] Link to comment Share on other sites More sharing options...
TKW22 Posted June 10, 2009 Author Share Posted June 10, 2009 Here's an example of a recursive function to do something similar, from one of my applications. A recursive function is one that calls itself. The relationship is similar, a user group in my application can have child user groups, so this function gets all of the children and grandchildren of any user group. This returns an array of user group info, the "children" element in the array is an array of the child groups. Notice that the function calls itself to get the children for the current group.# get a tree structure of all usergroups with checkboxes (unchecked)function get_usergroup_checknodes($par = 0){ global $db; $retval = array(); $db->sql('SELECT * FROM usergroups WHERE parent=%d ORDER BY title ASC'); $db->add_param($par, $esc=false); $ugs = $db->select(); foreach ($ugs as $ug) { $cur = array( 'text' => $ug['title'], 'ugid' => $ug['id'], 'checked' => false, 'children' => get_usergroup_checknodes($ug['id']) ); $retval[] = $cur; } return $retval;} Here's example output, the array is in JSON format: [ { "text":"MCCCD", "ugid":"7", "checked":false, "children": [ { "text":"XYZ Community College <i>(1)<\/i>", "ugid":"8", "checked":false, "children": [ { "text":"XYZ Healthcare Program <i>(3)<\/i>", "ugid":"9", "checked":false, "children": [ { "text":"Faculty <i>(0)<\/i>", "ugid":"11", "checked":false, "children":[] }, { "text":"Students <i>(0)<\/i>", "ugid":"10", "checked":false, "children":[] }, { "text":"XYZ Guest Agency <i>(0)<\/i>", "ugid":"12", "checked":false, "children":[] } ] } ] } ] }, { "text":"Test UG <i>(0)<\/i>", "ugid":"13", "checked":false, "children":[] }] i don't know why but after i edit your code all i get is a error Link to comment Share on other sites More sharing options...
Synook Posted June 10, 2009 Share Posted June 10, 2009 What error are you getting? It tells you nicely what went wrong Link to comment Share on other sites More sharing options...
TKW22 Posted June 10, 2009 Author Share Posted June 10, 2009 What error are you getting? It tells you nicely what went wrong Fatal error: Call to a member function sql() on a non-object in some url on line 18 Link to comment Share on other sites More sharing options...
Synook Posted June 10, 2009 Share Posted June 10, 2009 I don't know what DB abstraction class JSG is using but you need an instance of whatever it is assigned to the global (i.e. top-level) variable $db. Link to comment Share on other sites More sharing options...
justsomeguy Posted June 10, 2009 Share Posted June 10, 2009 Yeah that's just an example from one of my applications, the key point is to notice that the function calls itself to get the children of the current item. That's what you need to do with a list or tree structure like that. If you wanted to change that example so it runs you'll need to replace the database stuff with your regular mysql functions. This is the database class I'm using if you want to use that: <?phpclass tc_lms_database{ private $host = ''; private $user = ''; private $pass = ''; private $db = ''; private $conn = false; public $sql_stmt = ''; public $params = false; private $result = false; private $rowset = false; private $last_stmt = ''; /* Constructor Set the host, username, password, and database, then connect */ function __construct($h = '', $u = '', $p = '', $d = '') { if ($h != '') { $this->host = $h; $this->user = $u; $this->pass = $p; $this->db = $d; $this->connect(); } } /* connect Connect to the database using the supplied information, trigger an error if there was a problem */ function connect($h = '', $u = '', $p = '', $d = '') { if ($h != '') { $this->host = $h; $this->user = $u; $this->pass = $p; $this->db = $d; } $this->conn = mysql_connect($this->host, $this->user, $this->pass) or trigger_error(mysql_error(), E_USER_ERROR); if ($this->db != '') mysql_select_db($this->db, $this->conn) or trigger_error($this->error(), E_USER_ERROR); } /* reset Reset the SQL statement and all parameters */ function reset() { $this->sql_stmt = ''; $this->params = false; } /* sql Set the SQL statement */ function sql($str) { $this->sql_stmt = $str; } /* add_param Add a parameter for the SQL statement, and optionally escape it. Parameters should be added in the same order that they appear in the SQL */ function add_param($val, $escape = true) { if ($escape) $val = "'" . $this->escape($val) . "'"; if (!$this->params) $this->params = array(); $this->params[] = $val; } /* exec Execute a SQL statement and return the result. If the argument is true, do not die on an error. This returns a mysql result resource. */ function exec($passthrough_errors = false) { $this->check_db(); if ($this->sql_stmt == '') trigger_error('SQL statement not set', E_USER_ERROR); if (is_array($this->params) && count($this->params)) { $this->sql_stmt = vsprintf($this->sql_stmt, $this->params); $this->params = false; } $this->last_stmt = $this->sql_stmt; if ($passthrough_errors) return mysql_query($this->sql_stmt); else return mysql_query($this->sql_stmt) or trigger_error($this->error(), E_USER_ERROR); } /* get_row Get a single row from the given result set */ function get_row($result) { return mysql_fetch_assoc($result); } /* select Execute a SELECT statement, with an optional limit and page number. This returns the results as an array of rows, not as a mysql result resource. e.g.: $db->select(10, 3); // get the 10 items on page 3 */ function select($limit = 0, $page = 0) { $this->check_db(); if ($this->sql_stmt == '') trigger_error ('SQL statement not set', E_USER_ERROR); if (is_array($this->params) && count($this->params)) { $this->sql_stmt = vsprintf($this->sql_stmt, $this->params); $this->params = false; } if ($limit) { $this->sql_stmt .= ' LIMIT '; if ($page) $this->sql_stmt .= (($page - 1) * $limit) . ','; $this->sql_stmt .= $limit; } $this->last_stmt = $this->sql_stmt; $this->result = mysql_query($this->sql_stmt) or trigger_error($this->error(), E_USER_ERROR); $this->rowset = array(); while ($row = mysql_fetch_assoc($this->result)) { // convert null to empty string foreach ($row as $k => $v) { if (is_null($v)) $row[$k] = ''; } $this->rowset[] = $row; } mysql_free_result($this->result); return $this->rowset; } /* delete Execute a DELETE statement with an optional limit */ function delete($limit = 0) { $this->check_db(); if ($this->sql_stmt == '') trigger_error('SQL statement not set', E_USER_ERROR); if (is_array($this->params) && count($this->params)) { $this->sql_stmt = vsprintf($this->sql_stmt, $this->params); $this->params = false; } if ($limit) $this->sql_stmt .= " LIMIT {$limit}"; $this->last_stmt = $this->sql_stmt; return mysql_query($this->sql_stmt) or trigger_error($this->error(), E_USER_ERROR); } /* insert Insert a record into the given table. The $params variable should be an associative array of column names and values. e.g.; $db->insert('table_name', array('id' => 10, 'username' => 'Joe')); */ function insert($table, $params) { $this->check_db(); if (!is_array($params) || count($params) == 0) return false; $names = ''; $values = ''; foreach ($params as $k => $v) { $names .= "{$k},"; $values .= "'" . $this->escape($v) . "',"; } $names = substr($names, 0, -1); $values = substr($values, 0, -1); $this->last_stmt = "INSERT INTO {$table} ({$names}) VALUES ({$values})"; return mysql_query("INSERT INTO {$table} ({$names}) VALUES ({$values})", $this->conn) or trigger_error($this->error(), E_USER_ERROR); } /* update Update a record in the given table. Similar to insert, but the third parameter is the expression to use in the WHERE clause. e.g.: $db->update('table_name', array('username' => 'John'), 'id=10'); */ function update($table, $params, $where = '') { $this->check_db(); if (!is_array($params) || count($params) == 0) return false; $cols = ''; foreach ($params as $k => $v) $cols .= "{$k}='" . $this->escape($v) . "',"; $cols = substr($cols, 0, -1); if ($where != '') $where = " WHERE {$where}"; $sql = "UPDATE {$table} SET {$cols} {$where}"; $this->last_stmt = $sql; return mysql_query($sql, $this->conn) or trigger_error($this->error(), E_USER_ERROR); } /* escape Escape a string for use in a query */ function escape($str) { /* if (get_magic_quotes_gpc()) $str = stripslashes($str); */ return mysql_real_escape_string($str, $this->conn); } /* strip Remove selected characters from a string */ function strip($str) { $replace = array( "'", '"', '-', ';' ); return str_replace($replace, '', $str); } /* insert_id Get the last autoassigned ID */ function insert_id() { return mysql_insert_id($this->conn); } /* num_rows Get the number of rows produced by the given SQL statement */ function num_rows($sql) { return mysql_num_rows(mysql_query($sql, $this->conn)); } /* check_db Make sure the database has been connected to */ function check_db() { if ($this->conn == false) die('Database not initialized'); } /* error Generate the last error message */ function error() { global $page_mode, $sess; if (empty($page_mode)) $page_mode = ''; return 'Page mode: ' . $page_mode . '; User ID: ' . $sess->userid . '; Database Error #' . mysql_errno($this->conn) . ': ' . mysql_error($this->conn) . '. SQL Query: ' . $this->last_stmt; } /* get_last_sql Get the last SQL statement executed, after parameter replacement */ function get_last_sql() { return $this->last_stmt; }}?> $db = new tc_lms_database($host, $user, $pass, $database); Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.