The Arcive of vBulletin Modifications Site.

ameenov · #1 06-19-2008, 06:53 PM

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>
    ";
    }