如何正确地将此mySQL查询实现为PHP文件并显示结果? [英] How do I correctly implement this mySQL query into a PHP file and display the results?

问题描述

主要主题:

tl_iso_product_collection_item表: http://puu.sh/mUf65/b1498aa7fa.png

结构: http://puu.sh/mUf78/f25448e1d7.png

每当我尝试执行此操作时，就会出现以下消息:

Notice: Undefined index: configuration on line 12

我实际上尝试了一切，但我只是不知道问题可能在哪里.有人可以帮忙吗?预先感谢.

解决方案

每当对列使用函数时，都需要给它们分配一个别名.这样做的原因是因为您可能在函数中使用了许多列，而MySQL不会自动知道要为单列输出使用哪一列，它会为您生成与您使用的函数类似的列名. >

您遇到的解决方案绝对有效.但是，出于可读性考虑，建议使用别名和列名.

因此您的查询应如下所示:

SELECT 
    SUBSTRING(LEFT(configuration,
            LOCATE('abhol_firma', configuration) - 30),
        LOCATE('treuhand_betrag', configuration) + 22,
        100) as configuration   /* note the alias here */
FROM
    tl_iso_product_collection_item
WHERE
    LOCATE('abhol_firma', configuration) > 0
        AND LOCATE('treuhand_betrag', configuration) > 0
ORDER BY id DESC
LIMIT 1

Main topic: https://stackoverflow.com/questions/35163890/you-have-an-error-in-your-sql-syntax-check-the-manual-that-corresponds-to-your?sfb=2

SELECT SUBSTRING(LEFT(configuration, LOCATE('abhol_firma', configuration) -30), LOCATE('treuhand_betrag', configuration) +22, 100) FROM tl_iso_product_collection_item WHERE LOCATE('abhol_firma', configuration) > 0 AND LOCATE('treuhand_betrag', configuration) > 0 ORDER BY id DESC LIMIT 1

So basically I need to implement this code into a PHP file and display the result. My code so far looks like this:

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);
$connection = mysqli_connect('localhost', 'user', 'pw', 'db');
$query = "SELECT SUBSTRING(LEFT(configuration, LOCATE('abhol_firma', configuration) -30), LOCATE('treuhand_betrag', configuration) +22, 100) FROM tl_iso_product_collection_item WHERE LOCATE('abhol_firma', configuration) > 0 AND LOCATE('treuhand_betrag', configuration) > 0 ORDER BY id DESC LIMIT 1";
$result = mysqli_query($connection, $query);

if($result === FALSE) { 
    echo mysqli_error($connection);
} else
    while($row = mysqli_fetch_array($result)){
echo $row['configuration'];
}
mysqli_close($connection); 
?>

The tl_iso_product_collection_item table: http://puu.sh/mUf65/b1498aa7fa.png

Structure: http://puu.sh/mUf78/f25448e1d7.png

Whenver I try to execute this the following message appears:

Notice: Undefined index: configuration on line 12

I tried literally everything and I just don't know where the problem may be. Can anyone help out? Thanks in advance.

解决方案

Whenever you use functions for columns, you need to assign them an alias. The reason for this is because you may be using many columns in the function, and MySQL won't automatically know which one to use for the single column output, and it would generate you a column name similar to the function you used.

The solution which you came across is absolutely valid. However for readability purposes, using aliases and column names is recommended.

So your query should read as such:

SELECT 
    SUBSTRING(LEFT(configuration,
            LOCATE('abhol_firma', configuration) - 30),
        LOCATE('treuhand_betrag', configuration) + 22,
        100) as configuration   /* note the alias here */
FROM
    tl_iso_product_collection_item
WHERE
    LOCATE('abhol_firma', configuration) > 0
        AND LOCATE('treuhand_betrag', configuration) > 0
ORDER BY id DESC
LIMIT 1

这篇关于如何正确地将此mySQL查询实现为PHP文件并显示结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！