CODEWITHSUNDEEP
htmlmysql
Vijay P.V
Vijay P.V

Reputation: 145

Turning MYSQL select query output as HTML format

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;

Upvotes: 0

Views: 5028

Answers (2)

Olly
Olly

Reputation: 7758

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>

Upvotes: 1

Angelos Hadjiphilippou
Angelos Hadjiphilippou

Reputation: 167

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.

Upvotes: 1

Related Questions