Member Avatar for millsy007

I have a query that brings though a list of passenger names, is there a way in which I can set the length of each of these names to equal 10 characters. So even if I have shorter names eg "bob, demarcus, adam and scott" they would display with the shorter names filled by spaces, eg:

bob |demarcus |adam |scott

My Query is:

(SELECT   tb2.journey_dttm as departure, ('8' - tb2.occupancy) AS seats,
(SELECT IF(GROUP_CONCAT(passenger_name SEPARATOR '|') IS NULL, '', GROUP_CONCAT(passenger_name ORDER BY passenger_name SEPARATOR '|')) 
FROM passengers WHERE journey_id = tb2.id ORDER BY passenger_name) AS passengers FROM shuttle AS tb1 LEFT JOIN journey AS tb2 ON  ";
( tb1.id = tb2.shuttle_id ) LEFT JOIN trip_route AS tb3 ON ( tb2.route_id = tb3.id )
WHERE tb1.depart_dttm = '$depart_dttm' ORDER BY tb2.id ASC, tb2.route_id ASC);
  • 3 Contributors
  • 6 Replies
  • 95 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by manojjena1982

Dani AI

Generated

Brief answer: this is a presentation problem, not a SQL one. SQL should deliver the names; the fixed-width appearance must be enforced in the presentation layer. was on the right track about handling layout in HTML/CSS, and 's idea of padding at the database level will produce trailing spaces — but those spaces are collapsed by HTML, so padding in SQL alone won't produce the visible columns you expect.

Two practical, robust options:

  • Render each passenger as its own element (cell or inline block) in your PHP/HTML output. That makes alignment simple and accessible, and it works whether your tables are generated dynamically or not. You can loop the passenger list you already return, create a fixed number of output slots (one per seat), and fill blanks for empty seats.
  • If you prefer a single TD with fixed-width name blocks, wrap each name in an element and control width with CSS. Do not rely on literal spaces. Use CSS (for example, fixed width using the ch unit or pixels, plus overflow/text-overflow) or white-space: pre if you truly want preserved whitespace.

Example pattern (sanitized output + CSS-driven sizing):

$names = explode('|', $row['passengers']);
foreach ($names as $n) {
    $safe = htmlspecialchars($n, ENT_QUOTES, 'UTF-8');
    echo '<span class="pname" title="'. $safe .'">'. $safe .'</span>';
}
.pname {
  display: inline-block;
  width: 10ch;
  white-space: nowrap;
  overflow: hidden;
  text-overflow: ellipsis;
  font-family: monospace;
}

Quick tips: escape output to avoid XSS, use title to show full names on hover, decide whether to truncate long names or expand cells, and if seat count is fixed create exactly that many output slots so alignment is stable.

Recommended Answers

All 6 Replies

Member Avatar for almostbob

output into a table, and the width will adjust to the widest name, or set the td widths
tables are still useful for tabular data

Member Avatar for millsy007

Hi, it is being output to a table, but all of the passnger names go as a list within a passenger td, is it not possible to do something in the SQL query?

Member Avatar for almostbob

Hi, it is being output to a table, but all of the passnger names go as a list within a passenger td, is it not possible to do something in the SQL query?

sql provides the output data (in rows)
formatting is done by your php script & the html css it generates
nest tables,

<tr>
<td>
<table><tr><TD></td></tr></table>
</td>
<td>
<table><tr><TD></td></tr></table>
</td>
Member Avatar for millsy007

But my tables are created dynamically by the sql query, so I cannot hardcode in the nested tables, could I perhaps change my seperator from | to a html element that would organise the display?

Member Avatar for almostbob

dynamically code the nested tables the same way the code generates the tables.
The code you have makes horizontal lines
one way is to output the sql query

<tr><td>$s</td><td>$s</td><td>$s</td><td>$s</td><td>$s</td></tr>

one way to make vertical lines

<td>%s<br>%s<br>%s<br>%s<br>%s<br>%s<br>%s<br></td>

its your code, mauck around with it :)

Member Avatar for manojjena1982

Hi!
Try this..

create table data2 as select rpad(name,10,' ') as name  from data
select length(name) from data2

here i am creating a table with the select statement to find out the length on other table. So, its coming as 10.

I think it may help you

manoj

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.