A Very Small Db Class For Beginner

[manifest139 0]

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);    ?>

[yordan 10]

This is a php tutorial more than a MySQL tutorial, isn't it ?SQL stands for "Simple Query Language", like "select user_needs from users", which is a rather simple self-understandable query.Your tutorial describes the php implementation for fetching rows, which is also interesting but is not so clear as the pure sql syntax.

[manifest139 0]

This is a php tutorial more than a MySQL tutorial, isn't it ?SQL stands for "Simple Query Language", like "select user_needs from users", which is a rather simple self-understandable query.
Your tutorial describes the php implementation for fetching rows, which is also interesting but is not so clear as the pure sql syntax.

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/

[yordan 10]

Wow! Ten joins and two group-by ? And how much time do you expect for that to be processed ? Two centiseconds ?