Jump to content

How To Put Info Form Mysql To ..


TKW22
 Share

Recommended Posts

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.

  • hi
    • a 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

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

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

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

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

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

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...