Turning MYSQL select query output as HTML format

Question

Please see my below mentioned query which I getting as tabular output format in mysql. I want to change this query output to html format, how to do this? i am new to Mysql.

    SELECT StoreName,
        StoreCode AS `Code`,
        LogoUrl,
        BusinessStart AS `Starting Time`,
        BusinessEnd AS `Closing Time`,
        ClosedOn AS `Closed Days`,
        MinOrderPrice AS `Minimum Order Price`,
        DeliveryCharges AS `Delivery Charges`,
        PackingTime AS `Packing Time`,
        Validity AS `Validity`,
        PhoneNumber AS `Contact Number`,
        ZipCode AS `Post Code`,
        Floor AS `Street`,
        Line1 AS `Line`,
        Line2 AS `County`,
        Line3 AS `Land Line`,
        CONCAT(c.Code, ' - ' ,c.Symbol) AS `Currency`
    FROM store s INNER JOIN currency c ON c.CurrencyId = s.CurrencyId
    WHERE StoreId = 4;

How are you running this code presently? Are you using PHP or some other scripting language? — halfer
– halfer, Commented Sep 3, 2015 at 13:16
Please update the question with that important information. Do you want to download HTML in MySQL Workbench? Does that application support that feature? — halfer
– halfer, Commented Sep 3, 2015 at 13:22
you should use other language such as PHP, Pyhton, C#, ASP, Java etc. to connect to the database, parse query result and output formatted HTML — Alex
– Alex, Commented Sep 3, 2015 at 13:26
mentioned query output i want to pass as html format. now i am getting as tabular format. instead of that how to create html and pass as string? — Vijay P.V
– Vijay P.V, Commented Sep 3, 2015 at 13:33

Angelos Hadjiphilippou · Accepted Answer · 2015-09-03 14:48:01Z

1

Vijay, given your comment that you are new to mysql, i would agree with halfer and Alex above that you should use a secondary language to parse those results and output html. Now for whatever reason you need to convert your result set to HTML, my best guess and suggestion is as follows example below

SELECT 
      CONCAT("<div>",
           GROUP_CONCAT(
                CONCAT("<span>",username,"</span><br/>"),
                CONCAT("<span>",email,"</span>")
           )
      ,"</div>")  AS html FROM users;

this would return a single string containing something like:

<div><span>username</span><br/><span>[email protected]</span></div>

If this is what you want to achieve, then the example above set's you on the right track to format your result any way you like with html in between.

answered Sep 3, 2015 at 14:48

Angelos Hadjiphilippou

1675 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

fboes Over a year ago

…and if you are afraid of HTML errors, do at least CONCAT("<span>",REPLACE(REPLACE(username,'>','>'),'<','<'),"</span><br/>")

Olly · Accepted Answer · 2015-09-03 15:29:52Z

I don't have any context as to why you want to do this, but if I needed to do a similar task I would do it in two stages. First I would export the MySQL data into a simple machine-readable format such as CSV. You can do this using the SELECT .. INTO command (https://dev.mysql.com/doc/refman/5.0/en/select-into.html).

Once you have the data in CSV, I'd use a scripting language to convert to HTML. Here's some rudimentary Ruby code to demonstrate.

require 'csv'

File.open("output.html", 'w') do |file|
  file.write <<-HEADER
    <!DOCTYPE html>
    <html lang="en">
      <head>
        <meta charset="utf-8">
        <title>title</title>
      </head>
      <body>
        <table>
  HEADER

  CSV.foreach("your-file.csv") do |row|
    file.write <<-HTML
      <tr>
        <td>#{row[0]}</td>
        <td>#{row[1]}</td>
        <td>#{row[2]}</td>
      </tr>
    HTML
  end

  file.write <<-FOOTER
    </table>
    </body>
    </html>
  FOOTER
end

This code expects a CSV file called your-file.csv which has three columns, e.g.

first column,second column,third column

This CSV will be converted to an HTML file called output.html which looks like this:

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <title>title</title>
  </head>
  <body>
    <table>
  <tr>
    <td>first column</td>
    <td>second column</td>
    <td>third column</td>
  </tr>
</table>
</body>
</html>

Collectives™ on Stack Overflow

Turning MYSQL select query output as HTML format

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related