honkmaster Posted May 15, 2023 Share Posted May 15, 2023 (edited) 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 1 Quote Link to comment Share on other sites More sharing options...
Barand Posted May 15, 2023 Share Posted May 15, 2023 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"; 2 Quote Link to comment Share on other sites More sharing options...
honkmaster Posted May 17, 2023 Author Share Posted May 17, 2023 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 Quote Link to comment Share on other sites More sharing options...
ginerjm Posted May 17, 2023 Share Posted May 17, 2023 $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. Quote Link to comment Share on other sites More sharing options...
honkmaster Posted May 17, 2023 Author Share Posted May 17, 2023 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); Quote Link to comment Share on other sites More sharing options...
Barand Posted May 17, 2023 Share Posted May 17, 2023 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) Quote Link to comment Share on other sites More sharing options...
ginerjm Posted May 17, 2023 Share Posted May 17, 2023 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. Quote Link to comment Share on other sites More sharing options...
ginerjm Posted May 17, 2023 Share Posted May 17, 2023 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? Quote Link to comment Share on other sites More sharing options...
honkmaster Posted May 18, 2023 Author Share Posted May 18, 2023 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(); } ?> Quote Link to comment Share on other sites More sharing options...
Barand Posted May 18, 2023 Share Posted May 18, 2023 Then change $pdo in my code to $conn (as that is your db handle) Quote Link to comment Share on other sites More sharing options...
honkmaster Posted May 18, 2023 Author Share Posted May 18, 2023 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> Quote Link to comment Share on other sites More sharing options...
Barand Posted May 18, 2023 Share Posted May 18, 2023 Works OK for me, but try putting $data = []; at start of the code. 1 Quote Link to comment Share on other sites More sharing options...
honkmaster Posted May 18, 2023 Author Share Posted May 18, 2023 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted May 18, 2023 Share Posted May 18, 2023 The only conclusion I can come to, if $res is not a traversable PDOStatement object, is that query() failed and returned false Quote Link to comment Share on other sites More sharing options...
Solution honkmaster Posted May 24, 2023 Author Solution Share Posted May 24, 2023 Hi thanks for help, i fixed this by group concat in the sql REPLACE(GROUP_CONCAT(merge.Description), ',', CHAR(13)) Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.