doute7910 2011-05-12 18:16
浏览 52
已采纳

无法从Web查询Mysql数据库

I am having trouble setting up a query, it seems very simple but I cannot seem to determine if my database logic is incorrect or whether the query is incorrect.

There is one database with 3 tables, clients, orders and package.

The clients table has 3 fields, ID{primary key auto increment), email (varchar) and organisation (varchar).

The orders table has 10 fields, ID(from above), OderID (PRimary, autoincrement), WorkID (from package table), and othr fields relating to file paths, comments, feedback and date.

The package table has 2 fields, WorkID (primary Key autoincrem), name(varchar)

This is what I want the query to do:

The query must get all the rows from orders table where WorkID= 1 and must get the organisation field from clients based on each order. I am then going to order the rows by date.

Am I meant to be suing a join to get this query to work or is there a flaw in the database logic? Now yes I already know there is an error below as i am not comparing an ID from the two tables, but what I want to happen is for it to first get all the rows from orders where WorkID = 1 then add the clients.organisation field to each row found from WorkId = 1 where ID from clients corresponds to the ID assigned to that row.

Thanks for any help

$query =    "SELECT * 
             FROM orders INNER JOIN clients ON orders.ID = clients.ID
             WHERE WorkID = 1
             ORDER BY Date DESC";

WORKING NOW__________________________------------------------->>>>>>>>>>>>>>>>>>>>>>>>>>>

Ok guys ive got the query working but for some odd reason the number of rows being echoed in while loop i have set up is always 1 less than num_rows. Anyone have any idea as to why? This is my echo

<?php
include "../includes/connect.php";

$query =    "SELECT * 
             FROM orders INNER JOIN clients ON orders.ID = clients.ID
             WHERE WorkID = 1
             ORDER BY Date DESC";

$result = mysqli_query( $link , $query );


?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title></title>
<meta name="google-site-verification" content="qH5HloAtcJbjEVuEx3vDy_Rmj7Zjw8Mtsuuqdrd1c3Y" />
<link href="../styles/dark-main.css" rel="stylesheet" type="text/css" />
<link href="../styles/nivo-slider.css" rel="stylesheet"  type="text/css" media="screen" />
<link href="../styles/jquery.galleryview-3.0.css" rel="stylesheet" type="text/css" />
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.6/jquery.min.js" type="text/javascript"></script>
<script type="text/javascript" src="../scripts/jquery.nivo.slider.pack.js"></script>
<script type="text/javascript" src="../scripts/page-scripts.js"></script>
<script type="text/javascript" src="../scripts/jquery.easing.1.3.js"></script>
<script type="text/javascript" src="../scripts/jquery.galleryview-3.0.js"></script>
<script type="text/javascript" src="../scripts/jquery.timers-1.2.js"></script>
<script type="text/javascript" src="../scripts/validator.js"></script>


</head>

<body class="portfolio">

<!--TOPBAR STARTS HERE-->
<?php include"../includes/topbar.php"; ?>
<!--TOPBAR ENDS HERE-->

<!--HEADER STARTS HERE-->
<?php include"../includes/header.php"; ?>
<!--HEADER ENDS HERE-->

<!--SLIDER STARTS HERE-->
<?php include"../includes/slider.php"; ?>
<!--SLIDER ENDS HERE-->

<!--MIANCONTENT STARTS HERE-->

<div class="contentcontainer">

    <div class="contentcontainercenter">



    <div class="portfoliowrapper">
    <h2>Portfolio</h2>
    <div class="box">

    <div id="conversionworks" class="conversionwork">
    <h3>Heading</h3>
    <span class="message"> message </span>

    <div class="blockwrapper">

    <?php
    while( $row = mysqli_fetch_array( $result ) ){                           
  echo '
  <div class="itembox">
    <div class="imagewrapper">
    <a class="thumbnail" href=""><img src='. $row['ThumbPath'].' /><span><img src='. $row['FilePath'].' /></span></a>
    </div>
    <div class="detailsbox">
    Company:<span class="details"> '.$row['Organisation'].' </span><br />
    Theme:<span class="details">  '.$row['Theme'].' </span><br />
    Uses:<span class="details">  '.$row['Tech Used'].' </span>
    </div>
    </div>'      
;}
?> 

Also i have a question i regards to safety of a database. I have file paths stored in the database which get echoed according to each record fetch no user interaction all done via the server on page load, is it insecure to do so?

2nd question i have mutiple queries on the 1 page, what would be the best way to include each query where it is required? (Would putting it in a separate file and calling it before the echo be best?)

展开全部

  • 写回答

2条回答 默认 最新

  • douluozhan4370 2011-05-12 18:29
    关注

    You probably need something like that:

    SELECT * 
    FROM orders o
    INNER JOIN clients c ON (c.id = o.id)
    WHERE o.WorkID = 1
    ORDER BY `Date` DESC
    

    Some side notices : you should change your column naming convention to something more descriptive (I'm talking about orders.id which is in your case is a reference to clients.id). Personally, I prefer to use id as a primary key in each table. Some people like to name it as [TABLE_NAME]_id, in your case order_id, client_id. Secondly, don't use *; list all required fields. Also, I'd recommend to use a newer sql syntax for joining table (use JOIN keyword instead of listing all tables in FROM clause).

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)
编辑
预览

报告相同问题?

悬赏问题

  • ¥15 pycharm倒入虚拟环境的时候,显示这个,但是我的虚拟环境已经创建了
  • ¥15 FPGA芯片60进制计数器
  • ¥15 前端js怎么实现word的.doc后缀文件在线预览
  • ¥20 macmin m 4连接iPad
  • ¥15 DBIF_REPO_SQL_ERROR
  • ¥15 根据历年月数据,用Stata预测未来六个月汇率
  • ¥15 DevEco studio开发工具 真机联调找不到手机设备
  • ¥15 请教前后端分离的问题
  • ¥100 冷钱包突然失效,急寻解决方案
  • ¥15 下载honeyd时报错 configure: error: you need to instal a more recent version of libdnet
手机看
程序员都在用的中文IT技术交流社区

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

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

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

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

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

客服 返回
顶部