Jump to content
xisto Community

manifest139

Members
  • Content Count

    2
  • Joined

  • Last visited

Everything posted by manifest139

  1. I understand. May be you (as Pro.) can help to optimize a bit this query? [code]SELECT article.id as id,  article.name as name,  article.description,  article.image as a_image,  article.url as a_url,  article.stock,  article.stock_warn,  article.auto_def,  MAX(article_owner.rs_user_id = 1) as is_owner,  MAX(article_owner.rs_user_id = 1 AND article_owner.is_creator = 1) as is_creator,  booking.count as b_count,  booking.project_id,  IFNULL(booking_extra.price_valued_damage, 0) - (SELECT  SUM(booking.price_damage)  FROM booking  WHERE (booking.project_id = project.id  AND booking.article_id = article.id)  ) as rest_a_damage,  booking_extra.price_valued_damage as a_damage,  booking_extra.retour_sum as a_retour,  booking_extra.date_count_changed as date_changed,  booking.tip as b_tip,  booking.tip_retour as b_tip_retour,  IF(booking_extra.retour_sum IS NULL, booking_extra.state_warn, 0) as b_warn,  article_group.id as g_id,  article_group.name as g_name,  article_section.id as s_id,  article_section.name as s_name,  article_user_option.favo IS NULL as is_favo,    /* calc auto-retour booking */  IF((booking_extra.retour_sum IS NULL OR booking_extra.retour_sum = ''),  ROUND(booking_extra.count_sum - (booking_extra.count_sum / 100 * article.auto_def)),  NULL) as calc_auto_def,    /* subselect aviablility */  (SELECT  article.stock - (  /* sub projects before and same day */  SUM(  IF (project.date_start <= '2008-09-22 00:00:00' ,  booking_extra.count_sum,  0)  )  -  SUM(  /* if retour before day */  IF( (project.date_end < '2008-09-22 00:00:00' AND project.date_end IS NOT NULL),  ROUND(booking_extra.count_sum - (booking_extra.count_sum / 100 * article.auto_def)),  0  )  )  )    FROM booking_extra    LEFT JOIN project  ON (booking_extra.project_id = project.id)    WHERE (booking_extra.article_id = article.id /* JOIN to parent query */  AND booking_extra.retour_sum IS NULL) /* only not checked out */    GROUP BY article.id  LIMIT 1  ) as count_aviability      FROM article    LEFT JOIN article_group  ON (article.article_group_id = article_group.id)    LEFT JOIN article_section  ON (article_section.id = article_group.article_section_id)    LEFT JOIN article_owner  ON (article_owner.article_id = article.id  AND article_owner.rs_user_id = 1)    LEFT JOIN booking  ON (booking.article_id = article.id  AND booking.project_id = 2  AND booking.client_id = 2 )    LEFT JOIN booking_extra  ON (booking_extra.article_id = article.id  AND booking_extra.project_id = 2)    LEFT JOIN article_user_option  ON (article_user_option.article_id = article.id  AND article_user_option.rs_user_id = 1)    LEFT JOIN project  ON (project.id = booking_extra.project_id)    WHERE booking_extra.project_id = 2 AND article.deleted != 1 AND article_section.article_section_branch_id = 1      GROUP BY article.id  ORDER BY article_section.name, article_group.name, article.name;[/code] It is a query from this Project: https://sourceforge.net/projects/openrentstock/
  2. May be it helps for an MySQL entry </P><P><?php class Db_Default{   // connection  var $db_user = 'username';  var $db_pass = 'password';  var $db_db = 'database_name';  var $db_host = 'localhost';   var $connection;   // table names (examples)  // --one user has many needs--  var $tbl_user = 'users';  var $tbl_user_needs = 'user_needs';   function Db_Default(){  $this->connection = mysql_connect($this->db_host, $this->db_user, $this->db_pass);  mysql_select_db($this->db_db);  }   function query($sql){  $result = mysql_query($sql, $this->connection);  return $result;  }   function get_all($sql){  $result = $this->query($sql);  $ar = array();  while($row = mysql_fetch_assoc($result)){$ar[] = $row;}  return count($ar) ? $ar : null;  }   function get_row($sql){  $result = $this->query($sql);  $row = mysql_fetch_assoc($result);  return count($row) ? $row : null;  }   function get_one($sql){  $result = $this->query($sql);  $row = mysql_fetch_array($result);  return isset($row[0]) ? $row[0] : null;  }  } ?></P><P> Examples <?php // example // select all users $db = new Db_Default(); $all_users = $db->get_all("SELECT * FROM {$db->tbl_users} ORDER BY {$db->tbl_users}.name"); // next example with left join // get all needs from one user $id = (int)$_POST['id_user']; $q = "SELECT {$db->tbl_users}.name as username,  {$db->tbl_user_needs}.hardware,  {$db->tbl_user_needs}.software    FROM {$db->tbl_users}    LEFT JOIN {$db->tbl_user_needs}  ON {$db->tbl_users}.id = {$db->tbl_user_needs}.id_users    WHERE {$db->tbl_users}.id = '$id';"; $all_users_needs = $db->get_all($q); // next example // check if user exists with get_one() $id = (int)$_POST['id_user']; $q = "SELECT COUNT(*) >= 1 FROM {$db->tbl_users} WHERE id = '$id' LIMIT 1;"; $user_exists = (bool)$db->get_one($q); // next example // select one single row from user $id = (int)$_POST['id_user']; $q = "SELECT {$db->tbl_users}.* FROM {$db->tbl_users} WHERE id '$id' LIMIT 1;"; $user_data = $db->get_row($q); ?>
×
×
  • Create New...

Important Information

Terms of Use | Privacy Policy | Guidelines | We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.