Get all "username" and "id" in MySQL database and split using php

Question

I have MySQL database like this.

id........username  
 1 .......rap
 2 .......dafy
 3 .......kay

I want to make them like thies

username-id,username-id,use...

for ex.

rap-1,dafy-2,kay-3

the code should use the param to prevent the sql injection, but i have no idea how to write code.

so you just want to fetch all rows and join their id and username together? — DontBe3Greedy, Feb 16 '20 at 16:15
yes and the code should use param and the output should be like thies "rap-1,dafy-2,kay-3 " — sorax, Feb 16 '20 at 16:20

score 5 · Answer 1 · edited Jun 05 '20 at 13:28

5

Use this query for a result:

SELECT GROUP_CONCAT(CONCAT(`username` , '-',`id`) SEPARATOR ',')
FROM table1;

Result

GROUP_CONCAT(CONCAT(`username` , '-',`id`) SEPARATOR ',')
rap-1,dafy-2,kay-3 .

Example PHP code with PDO:

<?php
  $pdo = new PDO('mysql:host=localhost;dbname=databasename', 'username', 'password');
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $sql = "SELECT GROUP_CONCAT(CONCAT(`username` , '-',`id`) SEPARATOR ',') user FROM Table1";
  foreach ($pdo->query($sql) as $row) {
   echo $row['user']."<br />";
  }
  $pdo ->close();

edited Jun 05 '20 at 13:28

Dharman

30,962
25
85
135

answered Feb 16 '20 at 16:40

nbk

45,398
8
30
47

1

GROUP:CONCAT has also a ORDER BY option – nbk Feb 16 '20 at 16:45
I personally like to discover myself and of course i don't know, what you want with this. – nbk Feb 16 '20 at 17:25
This works, but mind that usernames containing `-` or `,` could break the consumer. Eventually those have to be escaped first. In general if you have access to all consumers a different data format, with a bit more structure, might be wiser. – johannes Feb 16 '20 at 18:40
@johannes that works also with comma and - see http://sqlfiddle.com/#!9/06340e/1 – nbk Feb 16 '20 at 19:30
@nbk yes, it works to produce it but can be hard to parse, again ... – johannes Feb 17 '20 at 22:08

score 1 · Accepted Answer · edited Jun 05 '20 at 13:26

1

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("database","my_user","my_password","my_db");


// Obviously change this to match yours
$sql = "SELECT GROUP_CONCAT(CONCAT(username , '-',id) SEPARATOR ',') as grp FROM table";
$result =  $mysqli->query($sql);
$final_result = []
while($row = $result->fetch_assoc())
{
    array_push($final_result, $row['grp']);
}


$mysqli -> close();

edited Jun 05 '20 at 13:26

Dharman

30,962
25
85
135

answered Feb 16 '20 at 16:21

DontBe3Greedy

564
5
12

Fatal error: Call to a member function query() on null......WHY – sorax Feb 16 '20 at 16:36
did you call your database funtion in a different file? or are you doing it in a funciton – DontBe3Greedy Feb 16 '20 at 16:38
https://stackoverflow.com/questions/30992830/fatal-error-call-to-a-member-function-query-on-null read that – DontBe3Greedy Feb 16 '20 at 16:38
in the function you are calling this program. do global $database_variable – DontBe3Greedy Feb 16 '20 at 16:41
obviously change database_variable to the actual name of the variable – DontBe3Greedy Feb 16 '20 at 16:41
Fatal error: Call to a member function query() on null....nothing changed – sorax Feb 16 '20 at 16:48
i just fiexed it – DontBe3Greedy Feb 16 '20 at 16:53
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/207939/discussion-between-sorax-and-dontbe3greedy). – sorax Feb 16 '20 at 17:11

Get all "username" and "id" in MySQL database and split using php

2 Answers2