A Very Small Db Class For Beginner

	A Very Small Db Class For Beginner
	Discussion by manifest139 with 3 Replies. Last Update: November 22, 2009, 9:55 pm

manifest139

May be it helps for an MySQL entry

CODE

</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

CODE

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

?>

Sat Nov 21, 2009 Reply New Discussion

yordan

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.

Sat Nov 21, 2009 Reply New Discussion

manifest139

QUOTE (yordan)

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;

It is a query from this Project:

http://sourceforge.net/projects/openrentstock/

Sun Nov 22, 2009 Reply New Discussion