| ameenov |
06-19-2008 06:53 PM |
REQUEST: Query from 4 tables with a short query line
I'm developing an accounting system & i would like to query the following:
1- Select the order from the table (orders) filed (id), by passing the id across the browser using ($_GET['id']). then output the result with the fields (userid,items). Note: items = is a text file with a list of items which are coma separated.
2- calculate the total of the items paid from (images) field (price). using the (items) from the (orders) table.
3 - Select & output the name of the client from table (users) filed name (fname).
4- Select & output the the total of amount paid by the client from table (orders_receipts) filed name (amount).
------------------------
I made a query which worked but it is very long, i want to make it short query.
#DATABASE
PHP Code:
-- phpMyAdmin SQL Dump
-- version 2.8.2
--
-- 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, '', 'malkiya', '2001', '????????', '15463', '45487', '9898', 0, '');
INSERT INTO `users` VALUES (5, 0, 0x616d65656e40686f746d61696c2e636f6d, '??????? ???', 0xc3a3c38dc393c3a420c38cc39ac39dc391, '', 0, '', 0x3937333339353430353633, '97339540563', 0x3137353536363838, 0, '', 'malkiya', '', '????????', '15463', '45487', '9898', 0, '');
INSERT INTO `users` VALUES (7, 0, '', '', 0x537465766965, '', 801210653, '', 0x353433353433, '5435435', '', 0, '', '', '', '', '', '', '', 0, '');
#PHP QUERIES
PHP Code:
$view_orders = mysql_query("SELECT * FROM orders ORDER BY id DESC LIMIT 1, 50")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>
";
}
|
More Information