Concatenated data

[honkmaster]

Hi looking for some help with below issue I'm facing, any help would be great

I have an SQL table call Merge with the below data in

Id    StoreId    Description    
1    B013    Alana
2    B013    Sleepmotion
3    B013    Sleepmotion
4    B013    Sleepmotion
5    B013    Hyde & Sleep    
6    B017    Alana
7    B017    Isabella
8    B017    Sleepmotion
9    B017    Sleepmotion
10    B017    Sleepmotion
11    B017    Hyde & Sleep

I'm trying to get the data to display as a table with description concatenated as below

B013    Alana
            Sleepmotion
            Sleepmotion
            Sleepmotion
            Hyde & Sleep
    
B017    Alana
            Isabella
            Sleepmotion
            Sleepmotion
            Sleepmotion
            Hyde & Sleep

Edited May 15, 2023 by honkmaster

[Barand]

try

$res = $pdo->query("SELECT storeid
                         , description
                    FROM merge
                    ORDER BY storeid     
                    ");
foreach ($res as $r) {
    $data[$r['storeid']][] = $r['description'];
}

echo "<table>\n";
foreach ($data as $store =>$prods) {
    echo "<tr style='vertical-align: top;'><td>$store</td><td>" . join('<br>', $prods) . "</td></tr>\n";
}
echo "</table>\n";

 

[honkmaster]

Hi thank for response, I tried suggestion an I get the following errors

Notice: Undefined variable: pdo in /Applications/XAMPP/xamppfiles/htdocs/workflow/mergetest.php on line 11

Fatal error: Call to a member function query() on null in /Applications/XAMPP/xamppfiles/htdocs/workflow/mergetest.php on line 11

 

[ginerjm]

$pdo is the handle for the database connection that Barand uses.  I use pdo as well.  What db connection do you make - use that handle.

[honkmaster]

Hi Thanks for that

I connect using 

require_once('../Connections/workflow.php');

 

<?php
# FileName="Connection_php_mysql.htm"
# Type="MYSQL"
# HTTP="true"
$hostname_workflow = "localhost";
$database_workflow = "workflow";
$username_workflow = " ";
$password_workflow = " ";
$workflow = mysql_pconnect($hostname_workflow, $username_workflow, $password_workflow) or trigger_error(mysql_error(),E_USER_ERROR); 
?>

 

Below is my database query 

 

mysql_select_db($database_workflow, $workflow);
$query_rsTest = "SELECT merge.`Description`, merge.StoreId FROM merge GROUP BY merge.StoreId";
$rsTest = mysql_query($query_rsTest, $workflow) or die(mysql_error());
$row_rsTest = mysql_fetch_assoc($rsTest);
$totalRows_rsTest = mysql_num_rows($rsTest);

[Barand]

Functions with names beginning "mysql_" no longer exist in PHP (since 2015 when version 7.0 was released).

You need to use either

• mysqli
• PDO (recommended)

[ginerjm]

To be sure, you should show us the code that makes your db connection, not just a reference to a module containing it.  As Barand has said, it 'looks' like it is well out-of-date.

[ginerjm]

Hmmm,  You were online looking at this topic when I posted my last.  Now you are gone.  Couldn't find the code that you wrote that I requested to see perhaps?  Or gone looking for some replacement code?  

[honkmaster]

Hi Sorry for delay had to deal with couple of issues!! Thanks for response, below is the connection script.

 

<?php
$servername = "localhost";
$database = "workflow";
$username = "";
$password = "";

try {
  $conn = new PDO("mysql:host=$servername;$database=myDB", $username, $password);
  // set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  echo "Connected successfully"; 
} catch(PDOException $e) {
  echo "Connection failed: " . $e->getMessage();
}
?>

[Barand]

Then change $pdo in my code to $conn (as that is your db handle)

[honkmaster]

Hi Thank you so much for help, the connection is working now but I get the following errors

Connected successfully 
Warning: Invalid argument supplied for foreach() in /Applications/XAMPP/xamppfiles/htdocs/workflow/mergetest.php on line 18

Notice: Undefined variable: data in /Applications/XAMPP/xamppfiles/htdocs/workflow/mergetest.php on line 23

Warning: Invalid argument supplied for foreach() in /Applications/XAMPP/xamppfiles/htdocs/workflow/mergetest.php on line 23
 

<?php require_once('Connections/workflow-new.php'); ?>
<?php error_reporting(E_ALL); ?>
<?php ini_set('display_errors', '1'); ?>
<!doctype html>
<html>
<head>
<meta charset="UTF-8">
<title>Untitled Document</title>
</head>

<body>
<?php
$res = $conn->query("SELECT StoreId
                         , Description
                    FROM merge
                    ORDER BY StoreId     
                    ");
foreach ($res as $r) {
    $data[$r['StoreId']][] = $r['Description'];
}

echo "<table>\n";
foreach ($data as $store =>$prods) {
    echo "<tr style='vertical-align: top;'><td>$store</td><td>" . join('<br>', $prods) . "</td></tr>\n";
}
echo "</table>\n";
?>
</body>
</html>

 

[Barand]

Works OK for me, but try putting

$data = [];

at start of the code.

[honkmaster]

I added $data = [];

and only this error now

Connected successfully 
Warning: Invalid argument supplied for foreach() in /Applications/XAMPP/xamppfiles/htdocs/workflow/mergetest.php on line 19

 

[Barand]

The only conclusion I can come to, if $res is not a traversable PDOStatement object, is that query() failed and returned false

[honkmaster]

Hi thanks for help,

i fixed this by group concat in the sql 

REPLACE(GROUP_CONCAT(merge.Description), ',', CHAR(13))