在php中使用左连接的sql查询不起作用

Question

我需要一点帮助。我在我的数据库中执行了一个sql查询，它工作得很好。但是，当我尝试将这个sam sql查询放入php中时，它不起作用。我找不出哪里出了问题。

    //connection variables
$host = "localhost";
$database = "kjnjkyeo3";
$user = "root";
$pass = "probajovo11";

//connection to the database
$connection = mysql_connect($host, $user, $pass, $database)
or die ('cannot connect to the database: ' . mysql_error());


$sql = "SELECT ps_orders.id_order, ps_order_detail.id_order, ps_order_detail.product_reference AS Itemno,  ps_order_detail.product_quantity AS Saleqty, ROUND(ps_order_detail.total_price_tax_incl, 2) AS Cost, DATE_FORMAT(ps_orders.date_add , \'%Y%m%dT%T\' ) AS Dateoftrans\n"
    . "FROM ps_orders\n"
    . "LEFT JOIN kjnjkyeo3.ps_order_detail ON ps_orders.id_order = ps_order_detail.id_order";

//loop to show all the tables and fields
$loop = mysql_query($sql)
or die ('cannot select tables');

我在sql查询中做了一大堆更改，但我总是得到不能选择表的msg。当我执行类似“从$database选择表”这样的简单查询时，它工作得很好。

有人能帮我解决这个问题吗？

Thx在前面。

这是一个更老的项目，我做了修改

//connection variables
$host = "localhost";
$database = "kjnjkyeo3";
$user = "root";
$pass = "probajovo11";

//connection to the database
$connection = mysql_connect($host, $user, $pass)
or die ('cannot connect to the database: ' . mysql_error());

//select the database
mysql_select_db($database)
or die ('cannot select database: ' . mysql_error());

$sql = "SELECT ps_orders.id_order, ps_order_detail.id_order, ps_order_detail.product_reference AS Itemno,  ps_order_detail.product_quantity AS Saleqty, ROUND(ps_order_detail.total_price_tax_incl, 2) AS Cost, DATE_FORMAT(ps_orders.date_add , \'%Y%m%dT%T\' ) AS Dateoftrans FROM ps_orders LEFT JOIN ps_order_detail ON ps_orders.id_order = ps_order_detail.id_order";

//loop to show all the tables and fields
$loop = mysql_query($sql)
or die ('cannot select tables');

但它还是不能工作

Answer 1

$connection = mysql_connect($host, $user, $pass) or die ('cannot connect to the database: ' . mysql_error());
mysql_select_db($database);
$sql = "SELECT ps_orders.id_order, ps_order_detail.id_order,
 ps_order_detail.product_reference AS Itemno,  ps_order_detail.product_quantity AS Saleqty, ROUND(ps_order_detail.total_price_tax_incl, 2) AS Cost, DATE_FORMAT(ps_orders.date_add , \'%Y%m%dT%T\' ) AS Dateoftrans FROM ps_orders LEFT JOIN kjnjkyeo3.ps_order_detail ON ps_orders.id_order = ps_order_detail.id_order";

Answer 2

mysql_connect不允许您像这样选择数据库。你必须使用mysql_select_db，有一天你也会最终转向mysqli或PDO。

$connection = mysql_connect($host, $user, $pass) or die ('cannot connect to the database: ' . mysql_error());
mysql_select_db($database);

Answer 3

mysql_connect不接受$database参数。http://dk1.php.net/manual/en/function.mysql-connect.php，改用：

$connection = mysql_connect($host, $user, $pass)
mysql_select_db($database, $connection)