Get column names from one table and values from another table in MySQL and show in a tabular view
I have two different tables 'products' & 'prod_attributes'. I am storing product column names in one table and product values in another table. I am doing this, because I have different products data like lights, fans, computers etc. and their columns names are different for each product. Their structure is as follows:
prod_attributes table
id attr_name
1 Model No
2 Description
3 Watts
4 Lumen Output
5 TR Offer Lumen Output
6 Strip Lumen Output
7 Color Temp
8 Output Voltage
9 Certification
10 Warranty
11 Price
products table
id prod_attributes_id prod_attr_value
1 1 ABC9801
2 2 2FT LED T8 GLASS TUBE, DUAL END BYPASS
3 3 10
4 4 0
5 8 1200
6 9 3500K, 4000K, 5000K
7 9 UL, DLC
8 10 5 YEAR OR 50,000 WARRANTY
9 11 5.95
Now, I want to show the products list in a tabular view like below:
Model No Description Watts Lumen Output TR Offer Lumen Output Strip Lumen Output Color Temp Output Voltage Certification Warranty Price
ABC9801 2FT LED T8 GLASS TUBE 10 0 N/A N/A N/A 1200 DLC 5 Yrs 5.95
UDH2837 Ceiling Fan N/A N/A N/A N/A N/A 200 UL 1 Yrs 10.00
How can I write MySQL query to retrieve the data and show it in a tabular view in PHP.??
do you know?
how many words do you know
See also questions close to this topic
-
How to upload a video using ACF in WordPress?
I am still at learning phase in WordPress. I cannot figure out in ACF which field type should I choose to upload a video file and which code should I write to show it at the front-end. Please look into the below code. I am using the_field('') but it isn't working. Can anyone help
<video width="954" height="535" controls class="tm-mb-40"> <source src="video/wheat-field.mp4" type="video/mp4"> Your browser does not support the video tag. </video>
-
delete a table form a database using laravel command
i need to delete a database table using laravel artisan command . not like this command php artisan migrate:rollback --step=5
i need to create like this route or controller code .
Route::get('/clear/database', function () {
Artisan::call('cache:clear'); return redirect('/');
});
. i also try public function dd()
{ Schema::drop('table_name'); }
but it not working . gives me error like this SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (SQL: drop table
table_name
)no foreign key for the table . what should i do ?
thanks in advance!
-
Creating Sticky Navbar with Drop Down Menu HTML
I am creating a HTML web page which contains a sticky navbar with drop down menu. However, when I created one, the dropdown menu does not works in the sticky navbar and so goes vise versa. below is the screenshot of both the result of the two codes.
*image with dropdown menu but without sticky navbar
*image with sticky navbar but without dropdown menu
below is the code for "image with dropdown menu but without sticky navbar"
<!DOCTYPE html> <html> <head> <meta name="viewport" content="width=device-width, initial-scale=1"> <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font- awesome/4.7.0/css/font-awesome.min.css"> <style> body {margin:0;font-family:Arial} .topnav { overflow: hidden; background-color: #333; } .topnav a { list-style-type: none; float: left; display: block; color: #f2f2f2; text-align: center; padding: 14px 16px; text-decoration: none; font-size: 17px; position: sticky; } .active { background-color: #04AA6D; color: white; } .topnav .icon { display: none; } .dropdown { float: left; overflow: hidden; } .dropdown .dropbtn { font-size: 17px; border: none; outline: none; color: white; padding: 14px 16px; background-color: inherit; font-family: inherit; margin: 0; } .dropdown-content { display: none; position: absolute; background-color: #f9f9f9; min-width: 160px; box-shadow: 0px 8px 16px 0px rgba(0,0,0,0.2); z-index: 1; } .dropdown-content a { float: none; color: black; padding: 12px 16px; text-decoration: none; display: block; text-align: left; } .topnav a:hover, .dropdown:hover .dropbtn { background-color: #555; color: white; } .dropdown-content a:hover { background-color: #ddd; color: black; } .dropdown:hover .dropdown-content { display: block; } @media screen and (max-width: 600px) { .topnav a:not(:first-child), .dropdown .dropbtn { display: none; } .topnav a.icon { float: right; display: block; } } @media screen and (max-width: 600px) { .topnav.responsive {position: relative;} .topnav.responsive .icon { position: absolute; right: 0; top: 0; } .topnav.responsive a { float: none; display: block; text-align: left; } .topnav.responsive .dropdown {float: none;} .topnav.responsive .dropdown-content {position: relative;} .topnav.responsive .dropdown .dropbtn { display: block; width: 100%; text-align: left; } } </style> </head> <body> <div class="header"> <h2>Scroll Down</h2> <p>Scroll down to see the sticky effect.</p> </div> <div class="topnav" id="myTopnav"> <a href="#home" class="active">Home</a> <a href="#news">News</a> <a href="#contact">Contact</a> <div class="dropdown"> <button class="dropbtn">Dropdown <i class="fa fa-caret-down"></i> </button> <div class="dropdown-content"> <a href="#">Link 1</a> <a href="#">Link 2</a> <a href="#">Link 3</a> </div> </div> <a href="#about">About</a> <a href="javascript:void(0);" style="font-size:15px;" class="icon" onclick="myFunction()">☰</a> </div> <div style="padding-left:16px"> <h2>Responsive Topnav with Dropdown</h2> <p>Resize the browser window to see how it works.</p> <p>Hover over the dropdown button to open the dropdown menu.</p> </div> <h3>Sticky Navigation Bar Example</h3> <p>The navbar will <strong>stick</strong> to the top when you reach its scroll position.</p> <p><strong>Note:</strong> Internet Explorer do not support sticky positioning and Safari requires a -webkit- prefix.</p> <p>Some text to enable scrolling. </p> <p>Some text to enable scrolling. </p> <p>Some text to enable scrolling. </p> <p>Some text to enable scrolling. </p> <p>Some text to enable scrolling. </p> <p>Some text to enable scrolling. </p> <p>Some text to enable scrolling. </p> <p>Some text to enable scrolling. </p> <p>Some text to enable scrolling. </p> <p>Some text to enable scrolling. </p> <p>Some text to enable scrolling. </p> <p>Some text to enable scrolling. </p> <p>Some text to enable scrolling. </p> <p>Some text to enable scrolling. </p> <p>Some text to enable scrolling. </p> <script> function myFunction() { var x = document.getElementById("myTopnav"); if (x.className === "topnav") { x.className += " responsive"; } else { x.className = "topnav"; } } </script> </body> </html>
below is the code for "image with sticky navbar but without dropdown menu"
<!DOCTYPE html> <html> <head> <meta name="viewport" content="width=device-width, initial-scale=1"> <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font- awesome/4.7.0/css/font-awesome.min.css"> <style> body { font-size: 20px; } body {margin:0;} ul { list-style-type: none; margin: 0; padding: 0; overflow: hidden; background-color: #333; position: -webkit-sticky; /* Safari */ position: sticky; top: 0; } li { float: left; } li a { display: block; color: white; text-align: center; padding: 16px 20px; text-decoration: none; } li a:hover { background-color: #111; } /*======================================================================*/ body { background-color:white; } ul { list-style-type: none; margin: 0; padding: 0; overflow: hidden; background-color: #38444d; } li { float: left; } li a, .dropbtn { display: inline-block; color: white; text-align: center; padding: 14px 16px; text-decoration: none; } li a:hover, .dropdown:hover .dropbtn { background-color: red; } li.dropdown { display: inline-block; } .dropdown-content { display: none; position: absolute; background-color: #f9f9f9; min-width: 160px; box-shadow: 0px 8px 16px 0px rgba(0,0,0,0.2); z-index: 1; } .dropdown-content a { color: black; padding: 12px 16px; text-decoration: none; display: block; text-align: left; } .dropdown-content a:hover {background-color: #f1f1f1;} .dropdown:hover .dropdown-content { display: block; } footer { text-align: center; padding: 3px; background-color: DarkSalmon; color: white; } </style> </head> <body> <div class="header"> <h2>Scroll Down</h2> <p>Scroll down to see the sticky effect.</p> </div> <ul> <li><a href="#home">Home</a></li> <li><a href="#news">News</a></li> <li class="dropdown"> <a href="javascript:void(1)" class="dropbtn">Dropdown</a> <div class="dropdown-content"> <a href="#">Link 1</a> <a href="#">Link 2</a> <a href="#">Link 3</a> </div> </li> </ul> <h3>Sticky Navigation Bar Example</h3> <p>The navbar will <strong>stick</strong> to the top when you reach its scroll position.</p> <p><strong>Note:</strong> Internet Explorer do not support sticky positioning and Safari requires a -webkit- prefix.</p> <p>Some text to enable scrolling. </p> <p>Some text to enable scrolling. </p> <p>Some text to enable scrolling. </p> <p>Some text to enable scrolling. </p> <p>Some text to enable scrolling. </p> <p>Some text to enable scrolling. </p> <p>Some text to enable scrolling. </p> <p>Some text to enable scrolling. </p> <p>Some text to enable scrolling. </p> <p>Some text to enable scrolling. </p> <p>Some text to enable scrolling. </p> <p>Some text to enable scrolling. </p> <p>Some text to enable scrolling. </p> <p>Some text to enable scrolling. </p> <p>Some text to enable scrolling. </p> <footer> <p>Author: Hege Refsnes<br> <a href="mailto:hege@example.com">hege@example.com</a></p> </footer> </body> </html>
Please i need some help with this as i am new to html and css.
-
Why does this Sql query shows the possible changes but does not implement it?
So, I want to change the prefix of my tables and the following command shows the possible changes that will take place which seems alright but does not seem to implement it.
SELECT Concat('RENAME TABLE ', TABLE_NAME, ' TO fan_', SUBSTRING_INDEX(TABLE_NAME, 'pc_',-1), ';') FROM information_schema.tables WHERE table_name like 'pc_%' and table_schema='testdbhere'
Moreover, this isn't a writing privilege issue as changing the tables name individually works perfectly from the same user.
-
how to get weeklytotal and yesterday record in mysql in one table
Hi Everyone i am trying to implement query to get weekly and yesterday data in same table, dummy output i have shared below, if yesterday not exist as per employee_id it should we showing 0 also as per my table week start from monday and end at sunday.please help me out how to query this get weekly_total and yesterday record and one table.
Table name-dailydata-
Sample data
employee_id date total 20 2022-04-25 10 20 2022-04-26 20 20 2022-04-27 20 20 2022-04-28 10 20 2022-04-29 20 20 2022-04-30 30 20 2022-04-31 40 20 2022-05-01 50 40 2022-04-26 20 expected output
employee_id weekly_total yesterday_record 20 200 40 40 20 0 mysql query to get weekly data
select employee_id,sum(total) as week_total from dailydata where date between '2022-04-25' and '2022-05-01'
-
Join to another table only matching specific records
I have a table of ports:
drop table if exists ports; create table ports(id int, name char(20)); insert into ports (id, name ) values (1, 'Port hedland'), (2, 'Kwinana');
And a table of tariffs connected to those ports:
drop table if exists tariffs; create table tariffs(id int, portId int, price decimal(12,2), expiry bigint(11)); insert into tariffs (id, portId, price, expiry ) values (1, 2, 11.00, 1648408400), (2, 2, 12.00, 1648508400), (3, 2, 13.00, 1648594800), (4, 2, 14.00, 1651273200), (5, 2, 15.00, 2250000000 ); insert into tariffs (id, portId, price, expiry ) values (1, 1, 21.00, 1648408400), (2, 1, 22.00, 1648508400), (3, 1, 23.00, 1648594800), (4, 1, 24.00, 1651273200), (5, 1, 25.00, 2250000000 );
Each tariff has an expiry.
I can easily make a query to figure out the right tariff for as specific date for each port. For example at timestamp
1648594700
the right tariff is:SELECT * FROM tariffs WHERE 1648594700 < expiry AND portId = 2 ORDER BY expiry LIMIT 1
Result:
id portId price expiry 3 2 13.00 1648594800
However, in my application I want to be able to pull in the right tariff starting from the
ports
record.For one record, I can do this:
SELECT * FROM ports LEFT JOIN tariffs on tariffs.portId = ports.id WHERE 1648594700 < tariffs.expiry AND ports.id = 2 LIMIT 1
Result:
id name id portId price expiry 2 Kwinana 3 2 13.00 1648594800
This feels a little 'dirty', especially because I am doing a lookup on a record, and then forcing only one result using LIMIT. But, OK.
What I cannot do, and can't work out how to do, is a query that will return a list of ports, and each port having a
price
field that matches the constraint above (that is, the record with the highestexpiry
compared to1648594700
for each port).This obviously won't work:
SELECT * FROM ports left join tariffs on tariffs.portId = ports.id where 1648594700 < tariffs.expiry
Since the result of the query, testing with timestamp
1648594700
, would be:id name id portId price expiry 2 Kwinana 3 2 13.00 1648594800 2 Kwinana 4 2 14.00 1651273200 2 Kwinana 5 2 15.00 2250000000 1 Port he 3 1 23.00 1648594800 1 Port he 4 1 24.00 1651273200 1 Port he 5 1 25.00 2250000000
Instead, the result for all ports (before further filtering) should be:
id name id portId price expiry 2 Kwinana 3 2 13.00 1648594800 1 Port he 3 1 23.00 1648594800
Is there a clean, non-hacky way to have such a result? As an added constraint, is this possible for this to be done in ONE query, without temp tables etc.?
-
How to convert DataTable to List<T> in C#
For convert DataTable to List you can used the AutoMapper Library and you can do it automatically
-
h:dataTable Setting the width of the cell has no effect
In
h:dataTable
, set the width of the style ofh:column
, but it is invalid. The code is as follows:<h:dataTable rowClasses="column50" .column50 { max-width: 100px; }
- How can I apply datatable to my other page