Concat two columns from mysql and display distinct values in dropdown list

Question

I have the following code to combine the two columns data and display distinct values in the dropdown list:

<?php

    include_once "connect.php";
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); 
    $stmt = $conn->query("SELECT DISTINCT A, B FROM Contracts WHERE something='Ssomething' as amount"); 
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { 
       echo "<option value='" .  $row['amount'] . " " . $row['amount'] . "</option>"; 
    }
    $conn->db=null;

?>

I get the error:

Fatal error: Call to a member function fetch() on a non-object in xyz line 5.

Any help is appreciated. Thank you in advance.

`var_dump($stmt);` before the `while` You will see something interesting. — , Feb 17 '16 at 03:12

score 2 · Answer 1 · edited Jun 20 '20 at 09:12

Fatal error: Call to a member function fetch() on a non-object in xyz line 5.

Normally means the query has failed and returned FALSE into your $stmt object.

In this case the query syntax is incorrect, if you code some error checking it will tell you what the error actually is

<?php
include_once "connect.php";
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); 

$stmt = $conn->query("SELECT DISTINCT CONCAT(A,B) as amount FROM Contracts WHERE something='Ssomething'"); 
if ( $stmt === FALSE ) {
    print_r($conn->errorInfo());
    exit;
}

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { 
   echo "<option value='" .  $row['amount'] . " " . $row['amount'] . "</option>"; 
}
$conn->db=null;
?>

The error in the query is due to the use of something='Ssomething' as amount because you cannot use an alias in a selection criteria.

So try

SELECT DISTINCT CONCAT(A,B) as amount FROM Contracts WHERE something='Ssomething'

as your query. But the distinct would then become unnecessary/irrelevant.

What are the data types of the columns A and B and did you mean to add them or concatenate them — RiggsFolly, Feb 17 '16 at 04:12
Then I will remove the `A+B` suggestion as that is not what you want — RiggsFolly, Feb 17 '16 at 09:32

score 1 · Answer 2 · answered Feb 17 '16 at 03:09

1

SELECT DISTINCT A, B FROM Contracts WHERE something='Ssomething' as amount

You will need to use a specific concat function in mysql.

SELECT 
CONCAT(A, B) as amount
FROM ( SELECT DISTINCT A, B FROM Contracts WHERE something='Ssomething') x

answered Feb 17 '16 at 03:09

Ed Baker

643
1
6
16

Sounds like a PHP issue, have you read through this (defining the variable names) https://stackoverflow.com/questions/19017682/pdo-fatal-error-call-to-a-member-function-fetch-on-a-non-object – Ed Baker Feb 17 '16 at 03:31
is Ssomething supposed to be $something – Ed Baker Feb 17 '16 at 03:33
No. and yes i know it is a php issue hence the question – miababy Feb 17 '16 at 03:39

Concat two columns from mysql and display distinct values in dropdown list

2 Answers2