أقوم حاليا بتطوير برنامج حسابي ... وهو دقيق جدا ..
قمت بإنشاء استعلام أدى الواجب المطلوب منه ولكني احس ان الأستعلام طويل ومعقد نوعا ما
فأنا لست خبيرا في مجال قواعد البيانات والأستعلامات المحترفة.
وكنت اتمنى ان توفر سوالف سوفت قسم خاص بقواعد البيانات وطريقة كتابة الأستعلامات.
عموما ..
قمت بإنشاء 3 استعلامات لإظهار سطر واحد فقط .. والمطلوب التقليل من الأستعلامات :con2:
الرجاء مراجعة المرفقات لمشاهدة طريقة ظهور البيانات..
-------------------- شرح الأستعلام
1- المطلوب:
- أستخراج مجموع أسعار البضائع ووضعها في فاتورة تظهر المبلغ المستحق ..
- أستخراج مجموع الأرصدة والمبالغ التي دفعها الزبون.
- (جزء قمت بها بشكل جيد) أستخراج المتبقي بعملية حسابية صغيرة .. بطرح مجموع الأرصدة من مجموع أسعار البضائع المشترى.
2- قواعد البيانات:
هناك 4 قواعد ..
أ- images وهي تحوي البضائع
ب- orders .. وهي تحوي الفواتير
ت -orders_receipts .. وهي تحوي الأرصدة
ث - users .. وهي تحوي اسماء العملاء
--------------------------------------------------كود PHP:
-- phpMyAdmin SQL Dump
-- version 2.8.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 19, 2008 at 10:53 PM
-- Server version: 5.0.22
-- PHP Version: 5.1.4
--
-- Database: `account`
--
-- --------------------------------------------------------
--
-- Table structure for table `images`
--
CREATE TABLE `images` (
`imageid` int(11) NOT NULL auto_increment,
`catid` int(11) NOT NULL default '0',
`userid` int(11) NOT NULL default '0',
`comp_id` int(11) NOT NULL default '0',
`name` varchar(255) NOT NULL default '',
`code` varchar(255) default NULL,
`details` text NOT NULL,
`price` decimal(11,3) default NULL,
`quantity` int(11) NOT NULL default '0',
`quantity_store` int(11) NOT NULL,
`date` int(25) NOT NULL default '0',
`active` tinyint(1) NOT NULL default '0',
`hits` int(11) NOT NULL default '0',
`image` varchar(255) default NULL,
`thumbnail` varchar(255) default NULL,
`type` tinyint(1) NOT NULL default '0',
`width` int(11) NOT NULL default '0',
`height` int(11) NOT NULL default '0',
`size` int(11) NOT NULL default '0',
`buy_type` tinyint(1) NOT NULL default '0',
`buy_time` int(25) default NULL,
`time` int(25) NOT NULL default '0',
`expire_date` int(25) NOT NULL,
`period` int(25) default NULL,
PRIMARY KEY (`imageid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
--
-- Dumping data for table `images`
--
INSERT INTO `images` VALUES (1, 0, 0, 0, 'ÓãäÊ', '54', 'ÇáÊÝÇÕíá åäÇ ...', '25.000', 33, 453, 0, 0, 0, NULL, NULL, 0, 0, 0, 0, 0, NULL, 0, 0, NULL);
INSERT INTO `images` VALUES (2, 0, 0, 0, 'Ãáãäíæã', 'dgfd', '', '200.000', 33, 453, 0, 0, 0, NULL, NULL, 0, 0, 0, 0, 0, NULL, 0, 0, NULL);
INSERT INTO `images` VALUES (3, 0, 0, 0, 'ßäßÑí', 'none', '', '100.000', 150, 120, 0, 0, 0, NULL, NULL, 0, 0, 0, 0, 0, NULL, 0, 0, NULL);
-- --------------------------------------------------------
--
-- Table structure for table `orders`
--
CREATE TABLE `orders` (
`id` int(11) NOT NULL auto_increment,
`userid` int(11) NOT NULL default '0',
`items` text NOT NULL,
`amount` decimal(11,3) NOT NULL,
`discount` decimal(11,3) NOT NULL,
`trans_id` bigint(25) NOT NULL,
`payment_id` bigint(25) NOT NULL,
`message` text NOT NULL,
`date` int(25) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
--
-- Dumping data for table `orders`
--
INSERT INTO `orders` VALUES (2, 4, '3,3,1', '0.000', '0.000', 0, 0, '', 1213815142);
INSERT INTO `orders` VALUES (3, 5, '1', '0.000', '0.000', 0, 0, '', 1213816354);
INSERT INTO `orders` VALUES (4, 4, '2', '0.000', '0.000', 0, 0, '', 1213870333);
-- --------------------------------------------------------
--
-- Table structure for table `orders_receipts`
--
CREATE TABLE `orders_receipts` (
`id` int(11) NOT NULL auto_increment,
`orderid` int(11) NOT NULL,
`amount` decimal(11,3) NOT NULL,
`received_from` varchar(255) NOT NULL,
`type` tinyint(1) NOT NULL,
`date` int(25) NOT NULL,
`bank_name` varchar(255) NOT NULL,
`check_num` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
--
-- Dumping data for table `orders_receipts`
--
INSERT INTO `orders_receipts` VALUES (5, 2, '200.000', 'ÇáÓíÏ ãÍãÏ', 0, 1213876957, '', '');
INSERT INTO `orders_receipts` VALUES (6, 2, '25.000', '', 0, 1213879058, '', '');
INSERT INTO `orders_receipts` VALUES (7, 4, '200.000', 'hassan', 0, 1213888806, '', '');
-- --------------------------------------------------------
--
-- Table structure for table `users`
--
CREATE TABLE `users` (
`userid` int(10) unsigned NOT NULL auto_increment,
`user_group` int(11) NOT NULL,
`useremail` varbinary(255) NOT NULL default ' ',
`info` text NOT NULL,
`fname` varchar(255) character set latin1 collate latin1_bin NOT NULL default '',
`company` varchar(255) NOT NULL,
`cpr` int(10) NOT NULL,
`zip` varchar(15) character set latin1 collate latin1_bin NOT NULL default '',
`tel` varchar(255) character set latin1 collate latin1_bin NOT NULL default '',
`mobile` varchar(255) NOT NULL,
`fax` varchar(255) character set latin1 collate latin1_bin NOT NULL default '',
`country` int(11) NOT NULL default '0',
`city` varchar(255) character set latin1 collate latin1_bin NOT NULL default '',
`website` varchar(255) NOT NULL default '',
`pobox` varchar(255) NOT NULL,
`area` varchar(255) NOT NULL,
`block` varchar(255) NOT NULL,
`road` varchar(255) NOT NULL,
`building` varchar(255) NOT NULL,
`reg_date` int(25) NOT NULL default '0',
`photo` varchar(255) NOT NULL default '',
PRIMARY KEY (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
--
-- Dumping data for table `users`
--
INSERT INTO `users` VALUES (4, 0, 0x616d65656e40686f746d61696c2e636f6d, 'ãáÇÍÙÇÊ åäÇ', 0xc387c3a1c393c3adc38f20c3a3c38dc3a3c38f20c383c3a3c3adc3a4, 'æÇíÏ ÊßäæáæÌí', 801110653, '', 0x3937333339353430353633, '97339540563', 0x3137353536363838, 0, '', 'http://www.malkiya.net', '2001', 'ÇáãÇáßíÉ', '15463', '45487', '9898', 0, '');
INSERT INTO `users` VALUES (5, 0, 0x616d65656e40686f746d61696c2e636f6d, 'ãáÇÍÙÇÊ åäÇ', 0xc3a3c38dc393c3a420c38cc39ac39dc391, '', 0, '', 0x3937333339353430353633, '97339540563', 0x3137353536363838, 0, '', 'http://www.malkiya.net', '', 'ÇáãÇáßíÉ', '15463', '45487', '9898', 0, '');
INSERT INTO `users` VALUES (7, 0, '', '', 0x537465766965, '', 801210653, '', 0x353433353433, '5435435', '', 0, '', '', '', '', '', '', '', 0, '');
3- الأستعلامات المستخدمة:
كود PHP:
config.php file here
if(!isset($_GET['page'])){
$page = 1;
} else {
$page = $_GET['page'];
}
// Define the number of results per page
$max_results = 30;
// Figure out the limit for the query based
// on the current page number.
$from = (($page * $max_results) - $max_results);
// Perform MySQL query on only the current page number's results
$view_orders = mysql_query("SELECT * FROM orders ORDER BY id DESC LIMIT $from, $max_results")Or Die(MySQL_Error());
/////////////////////////////////////////////////////////////////////////////////////////////
/////////
echo "
<table width='95%' border='1' align='center' cellpadding='2' cellspacing='0' bordercolor='#666666' bgcolor='#F5F4F3' id='AutoNumber1' style='border-collapse: collapse'>
<tr align='center'>
<td width='6%' bgcolor='#CCCCCC'>حذف</td>
<td width='7%' bgcolor='#CCCCCC'>تعديل</td>
<td width='11%' bgcolor='#CCCCCC'>الحالة</td>
<td width='12%' bgcolor='#CCCCCC'>المتبقي</td>
<td width='10%' bgcolor='#CCCCCC'>المدفوع</td>
<td width='15%' valign='middle' bgcolor='#CCCCCC'>السعر</td>
<td width='39%' valign='middle' bgcolor='#CCCCCC'>أسم العميل</td>
</tr>
</table>
";
///////
while($order_row = mysql_fetch_array($view_orders)){
///////////////////////// COULCULATE ITEMS PRICES TOTAL
$total = 0;
$items = explode(',',$order_row['items']);
$contents = array();
foreach ($items as $item) {
$contents[$item] = (isset($contents[$item])) ? $contents[$item] + 1 : 1;
}
foreach ($contents as $imageid=>$qty) {
// Query
$Products_query= mysql_query("select * from images WHERE imageid='$imageid' ") Or Die(MySQL_Error());
while($row_images = mysql_fetch_array($Products_query)){
$total += ($row_images['price'] * $qty);
} // END WHILE
} // END FOR EACH
//////////////-----------------------------
$total = number_format(($total- $order_row['discount']), 3, '.', '');
///////////////////////// END COULCULATE ITEMS PRICES TOTAL
/////+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
////----------------------------------------
$paid_info = "00.000";
$view_orders_receipts = mysql_query("SELECT * FROM orders_receipts WHERE orderid='{$order_row['id']}' ")Or Die(MySQL_Error());
$num_orders_receipts = mysql_num_rows($view_orders_receipts);
while($orders_receipts_row = mysql_fetch_array($view_orders_receipts)){
$paid_info += stripslashes($orders_receipts_row['amount']);
} // end while
$paid_info = number_format($paid_info, 3, '.', '');
mysql_free_result($view_orders_receipts);
//----
$view_users = mysql_query("SELECT fname FROM users WHERE userid='{$order_row['userid']}' ")Or Die(MySQL_Error());
$num_users = mysql_num_rows($view_users);
if ($num_users < 1) {
$CLIENT_NAME = "";
} else {
while($users_row = mysql_fetch_array($view_users)){
$CLIENT_NAME = stripslashes($users_row['fname']);
}// end while
} // end if no result
mysql_free_result($view_users);
////----------------------------------------
$balance_info = number_format(($total- $paid_info), 3, '.', '');
if ($balance_info <= 0) { $status_info = "<font color='green'>مدفوع</font>"; } else {$status_info = "<font color='red'>غير مدفوع</font>"; }
/////+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
// go on
echo "
<table width='95%' border='1' align='center' cellpadding='2' cellspacing='0' bordercolor='#666666' bgcolor='#F5F4F3' id='AutoNumber2' style='border-collapse: collapse'>
<tr align='center'>
<td width='6%'><a href='orders.php?action=delcon&id={$order_row['id']}'>حذف</a></td>
<td width='7%'><a href='orders_cart.php?id={$order_row['id']}' target='_blank'>تعديل</a></td>
<td width='11%'>$status_info</td>
<td width='12%' dir='rtl'>$balance_info</td>
<td width='10%' dir='rtl'>$paid_info</td>
<td width='15%' valign='middle' dir='rtl'>{$total}</td>
<td width='39%' valign='middle'><a href='orders_cart.php?id={$order_row['id']}' target='_blank'>$CLIENT_NAME</a></td>
</tr>
</table>
";
}
/////////////////////////
// Figure out the total number of results in DB:
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM orders "),0);
// Figure out the total number of pages. Always round up using ceil()
$total_pages = ceil($total_results / $max_results);
// Build Page Number Hyperlinks
if ($total_pages>1){
echo "<center dir='rtl'>اختر الصفحة<br />";
}
// Build Previous Link
if($page > 1){
$prev = ($page - 1);
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\"><<السابق</a> ";
}
for($i = 1; $i <= $total_pages; $i++){
if(($page) == $i){
if ($total_pages>1){
echo "$i ";
}
} else {
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a> ";
}
}
// Build Next Link
if($page < $total_pages){
$next = ($page + 1);
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next\">التالي>></a>";
}
echo "</center>";
رد مع اقتباس
