How do I correctly implement this mySQL query into

问题:

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.

回答1:

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

回答2:

You need to assign column alias in this query since you're using SUBQUERY function, which has no name in order to get it directly via the $row array.

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

回答3:

replacing

while($row = mysqli_fetch_array($result)){
echo $row['configuration'];
}

with

while($row = mysqli_fetch_array($result)){
print_r($row[0]);
}

solved the problem