douhao2721 2018-12-18 00:08
浏览 26
已采纳

php:找不到列,但它在数据库中

I am having trouble figuring out what's wrong whit my code. The code is used to take out specific columns from 2 big tables from a database. But we also need to sum 1 specific value when after I have used the query.

The problem is in this part

  $query = substr($query, 4);
    $sql2 = "SELECT SUM(Forspris) FROM orderrad
    JOIN orderhuvud on orderhuvud.OrderKund = orderrad.Orderdatum
     where ". $query;
        echo $sql2;
        $result = mysqli_query($conn, $sql2) or die(mysqli_error($conn));
        while($row = mysqli_fetch_array($result)){
            echo print_r($row);     }

this is the error i get:

SELECT SUM(Forspris) FROM orderrad JOIN orderhuvud on orderhuvud.OrderKund = orderrad.Orderdatum where OrderKund = '15' AND Orderdatum between '2015-04-16' AND '2015-05-06'Unknown column 'orderrad.Orderdatum' in 'on clause'

but it longer up in the code it can find the column orderdatum in the query

<!doctype html>
<html>
<head>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
<link rel="stylesheet" href="css.css">
<link rel="stylesheet" href="//cdnjs.cloudflare.com/ajax/libs/morris.js/0.5.1/morris.css">

    </head>
    <body>

    <?php
    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "garp";

    $conn = new mysqli ($servername, $username, $password, $dbname);
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }

    /*    $query = $_GET['query']; */
        $orderkund = $_GET['Orderkundinput']; 
        $artikel = $_GET['Artikelinput']; 
        $startDate =$_GET['startDate']; 
        $endDate =$_GET['endDate'] ;

        $MCkys = "orderdatum";

        $min_length = 2;
        $artikelQuery="";
        $orderkundQuery="";
        if(strlen($artikel) >= $min_length){ 
            $artikel = htmlspecialchars($artikel); 

            $artikel = mysqli_real_escape_string($conn, $artikel);
            $artikelQuery = " AND Artikelnr = '$artikel'";
        }
        if (strlen($orderkund) >= $min_length){ 
            $orderkund = htmlspecialchars($orderkund); 

            $orderkund = mysqli_real_escape_string($conn, $orderkund);
            $orderkundQuery = " AND OrderKund = '$orderkund'";
        }

        if (strlen($startDate) >= $min_length){ 
            $startDate = htmlspecialchars($startDate); 

            $startDate = mysqli_real_escape_string($conn, $startDate);
            $startDateQuery = " AND Orderdatum between '$startDate' ";
        }

        if (strlen($endDate) >= $min_length){ 
            $endDate = htmlspecialchars($endDate); 

            $endDate = mysqli_real_escape_string($conn, $endDate);
            $endDateQuery = "AND '$endDate'";
        }

        $query = $artikelQuery.$orderkundQuery.$startDateQuery.$endDateQuery;
        if(strlen($query) >= $min_length){ 
        $sql = "SELECT OrderHuvud.Ordernummer ,OrderHuvud.OrderserieIK ,OrderKund ,Fakturakund ,Orderdatum ,Erreferens ,Levereratvarde ,Radnummer ,Artikelnr ,Benamning ,Leveranstid ,Ursprungligtantal ,Levereratantal ,Forspris ,Bruttopris ,Varukostnad FROM garp.OrderHuvud left join garp.OrderRad on OrderHuvud.Ordernummer = OrderRad.Ordernummer where  OrderHuvud.OrderserieIK = 'K'" .$query;
        $raw_results = $conn->query ($sql);
            $row_cnt = false === $raw_results ? 0 : $raw_results->num_rows;
            echo " <p class='rows'> Numbers of rows loaded: $row_cnt </p>";
            if($row_cnt > 0){
       while($raw_result = mysqli_fetch_array($raw_results)){
           echo "<table class='table'><thead class='thead-light'><tr><th class='col'>".'Ordernummer'."</th><th class='col'>".'OrderserieIK'."</th><th class='col'>".'Orderkund'."</th><th class='col'>".'fakturakund'."</th><th class='col'>".'orderdatum'."</th><th class='col'>".'erreferens'."</th><th class='col'>".'leveratvarde'."</th><th class='col'>".'radnummer'."</th><th class='col'>".'artikelnr'."</th><th class='col'>".'benamning'."</th><th class='col'>".'leveranstid'."</th><th class='col'>".'Ursprungligtantal'."</th><th class='col'>".'Levereratantal'."</th><th class='col'>".'forspris'."</th><th class='col'>".'bruttopris'."</th><th class='col'>".'varukostnad'."</th></tr></thead>";
           echo "<tbody><tr><td>".$raw_result['Ordernummer']."</td><td>".$raw_result['OrderserieIK']."</td><td>".$raw_result['OrderKund']."</td><td>".$raw_result['Fakturakund']."</td><td>".$raw_result['Orderdatum']."</td><td>".$raw_result['Erreferens']."</td><td>".$raw_result['Levereratvarde']."</td><td>".$raw_result['Radnummer']."</td><td>".$raw_result['Artikelnr']."</td><td>".$raw_result['Benamning']."</td><td>".$raw_result['Leveranstid']."</td><td>".$raw_result['Ursprungligtantal']."</td><td>".$raw_result['Levereratantal']."</td><td>".$raw_result['Forspris']."</td><td>".$raw_result['Bruttopris']."</td><td>".$raw_result['Varukostnad']."</td></tr></tbody></table>";

       }
            }

            else{
       echo "No return";
            }
        }
        else{
            echo "Minimum length is ".$min_length;
        }
    $query = substr($query, 4);
    $sql2 = "SELECT SUM(Forspris) FROM orderrad
    JOIN orderhuvud on orderhuvud.OrderKund = orderrad.Orderdatum
     where ". $query;
        echo $sql2;
        $result = mysqli_query($conn, $sql2) or die(mysqli_error($conn));
        while($row = mysqli_fetch_array($result)){
            echo print_r($row);     }
        ?>

enter image description here

展开全部

  • 写回答

1条回答 默认 最新

  • donlih2986 2018-12-18 00:15
    关注
    SELECT SUM(Forspris) FROM orderrad where orderhuvud.OrderKund
    

    Your table is orderrad and in your where you try to use another table (orderhuvud) but you need to join first in order to use it, or select from it.

    SELECT SUM(Forspris) FROM orderrad 
    JOIN orderhuvud on orderhuvud.your_column_to_match = orderrad.your_column_to_match
    where orderhuvud.OrderKund = '15' AND orderhuvud.Orderdatum between '2015-04-16' AND '2015-05-06'
    

    Above code will join your table. In order to join them you need to find this column that both tables share and is the same for them so you join them on this.

    Just a really clear example, please don't hate for the w3school, this example is really really clear for inner join. Read more here!!!

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
编辑
预览

报告相同问题?

悬赏问题

  • ¥20 谁刷目标页面的uv记录器上数据,数据只记录跳转的数值
  • ¥30 数据库软件的安装方法
  • ¥15 一道以太网数据传输题
  • ¥15 python 下载群辉文件
  • ¥50 代码还没怎么运行但是需要代码功能调用数据
  • ¥15 vue请求不到数据,返回状态200,数据为html
  • ¥15 用白鹭引擎开发棋牌游戏的前端为什么这么难找
  • ¥35 哪位专业人士知道这是什么原件吗?哪里可以买到?
  • ¥15 关于#c##的问题:treenode反序列化后获取不到上一节点和下一节点,Fullpath和Handle报错
  • ¥15 一部手机能否同时用不同的app进入不同的直播间?
手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部