查询从2个表中选择多个值

Table names:

表名：

tbla_can_types
tbla_can_types

Fields are fac_id,type_id,type_name,status

字段是fac_id，type_id，type_name，status

tbla_canteen_rates (there is no data in this table)
tbla_canteen_rates（此表中没有数据）

Fields are `fac_id,cat_id,type_id,from time,to_time,rate,off_rate,status,effective_date.,can_id(sequence number)

字段是`fac_id，cat_id，type_id，from time，to_time，rate，off_rate，status，effective_date。，can_id（序列号）

<?php
include('adodb/adodb.inc.php');
$conn=&ADONewconnection('oci8');
$conn->Pconnect('conn','hostname','username','pwd');
$fac_id=$_GET['fac_id'];
$cat_id=$_GET['cat_id'];
$file=fopen("text.txt","w+");
global $newId;
header("Content-type: text/xml");
echo('<?xml version="1.0" encoding="iso-8859-1"?>');
echo "<data>";
$ids = explode(",",$_POST["ids"]);

$rates="select type_id,from_time,to_time,rate,off_rate,status,to_char(effective_date,'dd-Mon-yyyy') e_date,can_id from tbla_canteen_rates where fac_id=$fac_id and cat_id=$cat_id order by to_number(substr(from_time,0,length(from_time)-3))";
    $rs1=$conn->Execute($rates);
    if(!$rs1->EOF)
    echo $rs1->fields[0].'~'.$rs1->fields[1].'~'.$rs1->fields[2].'~'.$rs1->fields[3].'~'.$rs1->fields[4].'~'.$rs1->fields[5].'~'.$rs1->fields[6].'~'.$rs1->fields[7];
    $temp_id=$rs1->fields[0];
    echo $temp_id;
    $rs1->MoveNext();

    for ($i=0; $i < sizeof($ids); $i++)
    { 
        $rowId = $ids[$i]; //id or row which was updated 
        $newId = $rowId; //will be used for insert operation    
        $mode = $_POST[$rowId."_!nativeeditor_status"]; //get request mode
        switch($mode)
        {
        case "inserted":
          $insert= "insert into tbla_canteen_rates(type_id,from_time,to_time,rate,off_rate,status,effective_date,fac_id,cat_id )values
            ($temp_id,
             '".$_POST[$rowId."_c1"]."',
             '".$_POST[$rowId."_c2"]."',
             '".$_POST[$rowId."_c3"]."',
             '".$_POST[$rowId."_c4"]."',
             '".$_POST[$rowId."_c5"]."',to_date('".$_POST[$rowId."_c6"]."','dd-Mon-yyyy'),$fac_id,$cat_id)";
          $conn->Execute($insert);
          fwrite($file,$insert);
          $action='add_row($rowId)';
        break;
        case "deleted":
            $delete = "delete from tbla_canteen_rates where can_id=".$rowId ;
            $conn->Execute($delete);
            fwrite($file,$delete);
            $action ='delete_row($rowId)';
        break;
        default:
            $update ="update tbla_canteen_rates set 
                    type_id=$temp_id,
                    from_time='".$_POST[$rowId."_c1"]."',
                    to_time='".$_POST[$rowId."_c2"]."',
                    rate='".$_POST[$rowId."_c3"]."',
                    off_rate='".$_POST[$rowId."_c4"]."',
                    status='".$_POST[$rowId."_c5"]."',
                    effective_date=to_date('".$_POST[$rowId."_c6"]."','dd-Mon-yyyy'),
                    fac_id='$fac_id',cat_id='$cat_id'
                    where  can_id=".$rowId;
            $conn->Execute($update);
            fwrite($file,$update);
            $action = 'update_row($rowId)';
        break;
        }   
        echo "<action type='".$action."' sid='".$rowId."' tid='".$newId."'/>";
    }
    echo "</data>";
    ?>

In the query i need id for type_id in a variable i have written but every time it is getting id of first element only.

在查询中，我需要在我编写的变量中使用type_id的id，但每次只获取第一个元素的id。

in this i need an id for type_id.

在这里我需要一个type_id的id。

please send me the code 4 that

请把代码4发给我

Regards

问候

Pawan

爬完

1 个解决方案

#1

I've got a sneaking suspicion that you've got several questions in there, but since you asked about the sql query, this should work:

我有一个潜行的怀疑，你在那里有几个问题，但既然你问过sql查询，这应该工作：

Note: I'm assuming fac_id is the id that binds the two tables together. "var_fac_id" and "var_cat_id" are the values from your comboboxes.

注意：我假设fac_id是将两个表绑定在一起的id。 “var_fac_id”和“var_cat_id”是组合框中的值。

Select t1.type_name, t2.from_time, t2.to_time, t2.rate
FROM tbla_can_types as t1 LEFT JOIN tbla_canteen_rates as t2
ON t1.fac_id = t2.fac_id
WHERE t1.fac_id = var_fac_id AND t2.cat_id = var_cat_id

Further notes: I think this should work despite the "AND t2.cat_id = var_cat_id" (which obviously would find not results in an empty table), since it's a left join. If it returns an empty set, however, see if it works if you remove it.

进一步说明：我认为这应该有效，尽管“AND t2.cat_id = var_cat_id”（显然不会在空表中找到结果），因为它是左连接。但是，如果它返回一个空集，请查看是否可以删除它。

Update:
your comment above makes me think the tables are bound together by type_id instead. If that's the case, use ON t1.type_id = t2.type_id instead of ON t1.fac_id = t2.fac_id

更新：上面的评论让我觉得这些表是由type_id绑定在一起的。如果是这种情况，请使用ON t1.type_id = t2.type_id而不是ON t1.fac_id = t2.fac_id

Update #2 based on poster's comments
I'm not going to do your homework, dude. And homework is the only place where they'd tell you to not do joins. I will send you to some resources that will help you learn this stuff, though. Check out the basic mysql example from php.net: http://www.php.net/manual/en/mysql.examples-basic.php . That sample actually gets you pretty close to what you need. Also their overall mysql documentation, which is good for looking up what specific functions do: http://www.php.net/manual/en/book.mysql.php . Finally, remember that you're learning two languages here -- PHP, along with its functions to access a mysql DB, and -- SQL, which is a query language used within DBs. For SQL, check out http://sqlzoo.net/ , which seems a decent introduction on how to write queries.

根据海报的评论更新＃2我不会做你的功课，老兄。家庭作业是他们告诉你不要加入的唯一地方。不过，我会寄给你一些资源，帮助你学习这些东西。查看php.net的基本mysql示例：http：//www.php.net/manual/en/mysql.examples-basic.php。该示例实际上让您非常接近您的需求。还有他们的整体mysql文档，这有助于查找具体功能：http：//www.php.net/manual/en/book.mysql.php。最后，请记住，您在这里学习两种语言 - PHP，以及访问mysql数据库的函数，以及 - SQL，这是DB中使用的查询语言。对于SQL，请查看http://sqlzoo.net/，这对于如何编写查询似乎是一个不错的介绍。

Good luck on your assignment.

祝你的任务顺利。

#1