Data Project

Simple Table Lists

This is a simple demonstration of MySQL queries running live on a web-based database. The data has been returned to a PHP script and output to a web page using HTML, CSS and Javascript.

Listing the customers table

First off is a list of the first nine fields of the Customer table, ordering the results by customer ID.

The SQL code to achieve this is the same for both MySQL and T-SQL as shown:

MySQL Code and T-SQL Code (MS SQL)


		SELECT cust_id, cust_name, cust_add1, cust_add2,
 
        cust_add3, cust_county, cust_postcode, cust_salutation,
 
        cust_firstname, cust_surname
 
        FROM exc_customer
 
        ORDER BY cust_id ASC

Only the first nine fields are listed due to the width of the HTML table that would be required for all fields:

cust_id	cust_name	cust_add1	cust_add2	cust_add3	cust_county	cust_postcode	cust_salutation	cust_firstname	cust_surname
4567	Acorn Traders Ltd	Factory Road	Burwell	Cambridge	Cambridgeshire	CB5 0BN	Mrs	Anthea	Adams
4568	Billingham's of Braithwaite Ltd	Low Lane	Braithwaite	Doncaster	South Yorkshire	DN10 4PX	Mr	Bertie	Billingham
4569	Colcon Ltd	Granite Close	Enderby	Leicester	Leicestershire	LE3 2XF	Mr	Cecil	Collins
4570	Dimbleby PLC	Hallam Mill	Hallam Street	Stockport	Cheshire	SK2 6PT	Miss	Diana	Dimbleby
4571	East Empire Ltd	Wigwam Lane	Hucknall	Nottingham	Nottinghamshire	NG15 7SZ	Ms	Elaine	East
4572	Frannock Manor PLC	Clywedog Road	North Wrexham Industrial Estate	Wrexham	Clwyd	LL13 9XN	Mr	Frederick	Franklin
4573	Gimley of Gosborton Ltd	Long Leys Road		Lincoln	Lincolnshire	PE11 4NL	Mr	George	Gimley
4574	Hamshaw Ltd	Whitby Avenue	Ingol	Preston	Lancashire	PR2 3GA	Miss	Hannah	Hamshaw
4575	Immingham Supplies	Lancaster Approach	North Killingholme	Immingham	North East Lincolnshire	DN40 3JZ	Mrs	Irene	Ingols
4576	Jedson Manufacturing Ltd	Beacon Road	Trafford Park	Manchester	Greater Manchester	M17 1AF	Mr	Jamie	Jedson
4577	Kimbolton Carriers Ltd	Thrapston Road	Kimbolton	Huntingdon	Huntingdonshire	PE28 0HW	Mr	Ken	Kendrick
4578	Lemmy's Wines and Spirits Ltd	Station Road	Swineshead	Boston	Lincolnshire	PE20 3PN	Mr	Liam	Lee
4579	Mulgroon's Balloons Ltd	Spidlington Road	Faldingworth	Market Rasen	Lincolnshire	LN8 3SQ	Miss	Miranda	Mulgroon
4580	Norma's Cakes Ltd	Amington Road	Yardley	Birmingham	West Midlands	B25 8ET	Mrs	Norma	Nixon
4581	Oswald Furnishings Ltd	Diamond Avenue	Kirkby-in-Ashfield	Nottingham	Nottinghamshire	NG17 7GR	Mr	Oliver	Oswald
4582	Peacock & Bradshaw Ltd	Peterly Road	Cowley	Oxford	Oxfordshire	OX4 2TY	Mr	Peter	Peacock
4583	Quentin's Cabins Ltd	Walton New Road	Bruntingthorpe	Lutterworth	Leicestershire	LE17 5RD	Mr	Quentin	Quinn
4584	Rena's Supplies Ltd	Wigan Road	Euxton	Chorley	Lancashire	PR7 6JJ	Mrs	Rena	Rimshaw
4585	Shenley Shovels Ltd	Harper Lane	Shenley	Radlett	Hertfordshire	WD7 9HE	Mr	Simon	Sixsmith
4586	Tyneside Machines Ltd	Sycamore Street	Wallsend	North Tyneside	Tyne and Wear	NE31 1AX	Mr	Tim	Timpkins
4587	United Uniforms Ltd	Longley Road	Medway City Estate	Rochester	Kent	ME2 4DU	Ms	Una	Unsworth
4588	Venga Coverings Ltd	Cholmley Way	Eskdale Industrial Estate	Whitby	North Yorkshire	YO22 4NJ	Mr	Vinnie	Venga
4589	Wilton's Fine Products Ltd	Key Business Park	Kingsbury Road	Birmingham	West Midlands	B24 9PT	Mr	Will	Wilton
4590	Xavier Coast to Coast Ltd	Marchwood Industrial Estate	Normandy Way	Southampton	Hampshire	SO40 4PB	Mrs	Xena	Xavier
4591	Yaxley Pies Ltd	Station Road	Broadway	Yaxley	Huntingdonshire	PE7 3EN	Miss	Yasmine	Yolander
4592	Zimmerman's of Sawtry Ltd	Brookside Industrial Estate	Sawtry	Huntingdon	Huntingdonshire	PE28 5SB	Mr	Zak	Zanetti
4593	Ambletons PLC	Unit 15 Ballon Wood Ind Est	Coventry Lane	Nottingham	Nottinghamshire	NG9 3GJ	Mr	Arnold	Ambleton
4594	Beckley Banners Ltd	Unit 77 Regal Works	Johns Road, Kirkdale	Liverpool	Merseyside	L20 8PR	Mr	Bertie	Beckley
4595	Cedric Collier Ltd	Unit 98 Bergen Way	Southfields Industrial Estate	Hull	East Yorkshire	HU7 0YQ	Mr	Cedric	Collier
4596	Duke Demolition Ltd	762 Edinburgh Road	Newhouse	Motherwell	North Lanarkshire	ML1 5SY	Mr	Derek	Duke
4597	Emmerson Enterprises Ltd	Unit 72 Ely Distribution Centre	Argyle Way	Cardiff	South Glamorgan	CF5 5NJ	Mr	Eddie	Emmerson
4598	Fentham Farms Ltd	Sopwith Drive	Brooklands Industrial Park	Weybridge	Surrey	KT13 0UZ	Miss	Fiona	Finley
4599	Gurnock Garage Ltd	Green Lane	Coltishall	Norwich	Norfolk	NR12 7AJ	Mr	Garry	Gumton
4600	Henry Hammerton Ltd	Yellowfield Lane, Springfield	Colton	Ulverston	Cumbria	LA12 8HE	Mr	Henry	Hammerton
4601	Irlington Industries Ltd	Plot 22 Pexton Road	Kellythorpe	Driffield	East Yorkshire	YO25 9DJ	Mr	Ian	Icke
4602	Jeff Johnston Ltd	Knightons Lane	Dunsfold	Godalming	Surrey	GU8 4NY	Mr	Jeff	Johnston
4603	Kensome & Kingston Ltd	Stafford Road	Knightly	Stafford	West Midlands	ST20 0JR	Mrs	Karen	Kensome
4604	Local Landmarks Ltd	Kendall Business Park	Appleby	Kendall	Cumbria	LA9 6EW	Mrs	Lena	Lilly
4605	Motorman Ltd	Unit 92 Shorten Brook Way	Altham Business Park	Accrington	Lancashire	BB5 5YJ	Mr	Matthew	Marsh
4606	Noreen Nightingale Ltd	Gatehead Road	Crosshouse	Kilmarnock	East Ayrshire	KA2 0HP	Ms	Noreen	Nightingale
4607	Oakham Optometrists Ltd	52 Station Road	Uppingham	Oakham	Rutland	LE15 9TZ	Mr	Orville	Owen
4608	Purvis & Purvis Ltd	92 Beech Street	Padiham	Burnley	Lancashire	BB12 7EE	Mr	Paul	Purvis
4609	Quakerman's Quills Ltd	Unit 52 Ormidale Square	Tiverton Business Park	Tiverton	Devon	EX16 6TW	Mr	Quincy	Quant
4610	Renley of Roehampton Ltd	Unit 72 Aston Fields Ind Estate	Aston Road	Bromsgrove	Worcestershire	B60 3EX	Mr	Roger	Renley
4611	Sally's Supplies Ltd	57 Camford Way	Sundon Park	Luton	Bedfordshire	LU3 3AN	Mrs	Sally	Smithers
4612	Thompson's Tents Ltd	Hovefields Avenue	Burnt Mills	Basildon	Essex	SS13 1EB	Mr	Toby	Thompson
4613	Ursula Umbridge Ltd	Northolt Road	London Heathrow Airport	Hounslow	Greater London	TW6 2JA	Mrs	Ursula	Umbridge
4614	Violet Vans Ltd	Daneshill East Industrial Estate	Bell Road	Basingstoke	Hampshire	RG24 8PX	Mrs	Violet	Vardy
4615	Williams of Walsham Ltd	Jade House Farm	Knapton	North Walsham	Norfolk	NR28 0RX	Mr	Wayne	Williams
4616	X-cel Ltd	Windsor Trading Estate	Downham	Billericay	Essex	CM11 1QE	Mrs	Xaria	Xanda
4617	Yeovil Yards Ltd	Unit 99 Abbey Trading Estate	Stourton Way	Yeovil	Somerset	BA21 3AR	Mr	Yusef	Yorke
4618	Zen Products Ltd	Europa Way	Lune Industrial Estate	Lancaster	Lancashire	LA1 5QP	Mr	Zane	Zellweger

Here are the cust_id and cust_name fields along with the remaining fields from the Customer table, ordered by the cutomer ID.

Again, the SQL query for MySQL and T-SQL to produce the data is the same and as follows:

MySQL Code and T-SQL Code (MS SQL)


		SELECT cust_id, cust_name, cust_mobile, cust_email,
 
        cust_email_opt_out, cust_thirdparty

        FROM exc_customer

        ORDER BY cust_id ASC

cust_id	cust_name	cust_mobile	cust_email	cust_email_opt_out	cust_thirdparty
4567	Acorn Traders Ltd	07570 667889	atraders@hotmailtest.com	0	0
4568	Billingham's of Braithwaite Ltd	07579 567567	bertie@billinghamstest.co.uk	0	0
4569	Colcon Ltd	07978 444333	colcon@gmailtest.com	0	0
4570	Dimbleby PLC	07976 444323	dimblebyplc@talktalktest.co.uk	0	0
4571	East Empire Ltd	07571 443667	sales@eastempiretest.com	0	0
4572	Frannock Manor PLC	07578 222111	fred@frannockstest.com	0	0
4573	Gimley of Gosborton Ltd	07579 876543	georgegimley@aoltest.com	0	0
4574	Hamshaw Ltd	07979 987876	hh@hamshawltdtest.com	0	0
4575	Immingham Supplies	07979 787676	iingols@gmailtest.com	0	0
4576	Jedson Manufacturing Ltd	07572 653489	jamiej@jedsonstest.com	0	0
4577	Kimbolton Carriers Ltd	07878 676767	kkendrick@kimboltoncarrierstest.co.uk	0	0
4578	Lemmy's Wines and Spirits Ltd	07570 232767	llee@lemmystest.com	0	0
4579	Mulgroon's Balloons Ltd	07978 212343	miranda@mulgroonstest.co.uk	0	0
4580	Norma's Cakes Ltd	07578 675465	nnixon@normascakestest.com	0	0
4581	Oswald Furnishings Ltd	07579 765476	osfurnishings@yahootest.co.uk	0	0
4582	Peacock & Bradshaw Ltd	07576 775655	peterp@hotmailtest.com	0	1
4583	Quentin's Cabins Ltd	07978 743432	qquinn@talktalktest.co.uk	0	0
4584	Rena's Supplies Ltd	07976 555444	rrimshaw@renastest.com	1	1
4585	Shenley Shovels Ltd	07571 676754	simon.sixsmith@shenleysshovelstest.co.uk	0	0
4586	Tyneside Machines Ltd	07667 898989	t.timpkins@tmltdtest.co.uk	0	0
4587	United Uniforms Ltd	07976 443344	uunsworth@aoltest.com	0	1
4588	Venga Coverings Ltd	07976 567654	vinnie.venga@yahootest.co.uk	0	0
4589	Wilton's Fine Products Ltd	07571 656565	will@wiltonstest.com	0	1
4590	Xavier Coast to Coast Ltd	07578 555664	xena@xavierctoctest.com	0	0
4591	Yaxley Pies Ltd	07579 643987	yasmine@yaxleypiestest.co.uk	0	1
4592	Zimmerman's of Sawtry Ltd	07573 458792	zakzanetti@hotmailtest.com	0	0
4593	Ambletons PLC	07572 643324	aa@hotmailtest.com	1	0
4594	Beckley Banners Ltd	07573 650909	bb@beckleytest.com	0	0
4595	Cedric Collier Ltd	07574 888976	cedric@collierstest.com	0	0
4596	Duke Demolition Ltd	07574 775544	theboss@dukestest.co.uk	0	0
4597	Emmerson Enterprises Ltd	07978 978676	eddie@emmersonstest.com	0	0
4598	Fentham Farms Ltd	07978 423332	ff@fenthamfarmstest.net	0	0
4599	Gurnock Garage Ltd	07978 455455	gary@gurnockstest.com	0	0
4600	Henry Hammerton Ltd	07977 669898	henry@hammertontest.co.uk	0	0
4601	Irlington Industries Ltd	07978 195743	ii@irlingtonltdtest.com	1	1
4602	Jeff Johnston Ltd	07978 923245	mrjohnston@johnstontest.com	0	0
4603	Kensome & Kingston Ltd	07978 499188	kk@kandktest.com	0	0
4604	Local Landmarks Ltd	07978 399993	lenalilly@locallandmarkstest.com	0	0
4605	Motorman Ltd	07977 911233	matt@motormantest.com	1	1
4606	Noreen Nightingale Ltd	07977 288881	nn@noreennightingaletest.com	0	0
4607	Oakham Optometrists Ltd	07987 779933	orville@oakham-optometriststest.com	0	0
4608	Purvis & Purvis Ltd	07978 831765	pp@purvisandpurvistest.com	0	0
4609	Quakerman's Quills Ltd	07978 498004	quincy@quakermanstest.com	0	0
4610	Renley of Roehampton Ltd	07978 755000	roger@renleystest.com	0	0
4611	Sally's Supplies Ltd	07571 726678	sally@smitherstest.com	0	0
4612	Thompson's Tents Ltd	07646 449988	tthompson@hotmailtest.com	0	0
4613	Ursula Umbridge Ltd	07978 545480	sales@umbridgetest.com	0	0
4614	Violet Vans Ltd	07578 449988	vv@violetvanstest.net	0	0
4615	Williams of Walsham Ltd	07676 693214	wayne@wwtest.co.uk	0	0
4616	X-cel Ltd	07678 778881	xx@xceltest.com	0	0
4617	Yeovil Yards Ltd	07978 654765	yusef@yeovilyardstest.com	0	0
4618	Zen Products Ltd	07978 776634	zane@zenstest.com	0	0

Listing the staff table

The next set of data is from a simple query listing all fields from the Staff table, ordering the results by Staff ID number.

Both the SQL query for MySQL and T-SQL to produce the data is the same:

MySQL Code and T-SQL Code (MS SQL)


		SELECT staff_id, staff_mobile, staff_add1, staff_add2,
 
        staff_add3, staff_county, staff_postcode,
 
        staff_salutation, staff_firstname,
 
        staff_surname, staff_email
 
        FROM exc_staff
 
        ORDER BY staff_id ASC

staff_id	staff_mobile	staff_add1	staff_add2	staff_add3	staff_county	staff_postcode	staff_salutation	staff_firstname	staff_surname	staff_email
50	07575 757572	1 High Street	Bigthorpe	Aldershot	Hampshire	GU11 5TY	Mrs	Anthea	Allen	a.allen@widge-it.com
51	07575 757573	2 Acacia Avenue	Smallthorpe	Brighton	East Sussex	BN42 5YT	Mr	Billy	Beaumont	b.beaumont@widge-it.com
52	07575 757574	3 North Bank	Midthorpe	Cirencester	Gloucestershire	GL7 5YY	Mr	Charles	Chuzzlewit	c.chuzzlewit@widge-it.com
53	07575 757575	4 East Street	Upthorpe	Didcot	Oxfordshire	OX11 5TY	Miss	Deirdrie	Dawson	d.dawson@widge-it.com
54	07575 757576	5 West Road	Downthorpe	Enderby	Leicestershire	LE19 5YY	Mr	Ernie	Ecclestone	e.ecclestone@widge-it.com
55	07575 757577	6 South View	Midside	Filey	Yorkshire	YO14 5TY	Mrs	Francesca	Faversham	f.faversham@widge-it.com
56	07575 757578	7 Lower Bank	Nearside	Guildford	Surrey	GU3 5YT	Miss	Gina	Ginola	g.ginola@widge-it.com
57	07575 757579	8 Upper Lane	Rightside	Hartlepool	County Durham	TS25 5YY	Mr	Harry	Harrison	h.harrison@widge-it.com

Listing the transactions table

To produce the required output data from the Transactions table and order the results by Transaction ID number we can use the following queries.

Note that MySQL and T-SQL use different syntax to format the output of UK dates:

MySQL Code


		SELECT trans_id, trans_staff_id, trans_cust_id,
 
        DATE_FORMAT(trans_date, '%d/%m/%Y') AS trans_date,
 
        trans_grn_widgets_sold, trans_blu_widgets_sold,
 
        trans_grn_widgets_sales, trans_blu_widgets_sales,
 
        trans_ins_sales FROM exc_transactions
 
        ORDER BY trans_id ASC

T-SQL Code (MS SQL)


		SELECT trans_id, trans_staff_id, trans_cust_id,
 
        CONVERT (varchar(10), trans_date, 103) AS trans_date,

        trans_grn_widgets_sold, trans_blu_widgets_sold,
 
        trans_grn_widgets_sales, trans_blu_widgets_sales,
 
        trans_ins_sales FROM exc_transactions
 
        ORDER BY trans_id ASC

trans_id	trans_staff_id	trans_cust_id	trans_date	trans_grn_widgets_sold	trans_blu_widgets_sold	trans_grn_widgets_sales	trans_blu_widgets_sales	trans_ins_sales
52	50	4589	03/01/2017	1	0	500.00	0.00	0.00
53	51	4583	03/01/2017	1	3	500.00	1800.00	100.00
54	52	4568	04/01/2017	2	0	1000.00	0.00	100.00
55	53	4570	04/01/2017	4	8	2000.00	4500.00	0.00
56	55	4582	05/01/2017	5	5	2500.00	3000.00	100.00
57	56	4582	05/01/2017	2	3	1000.00	1800.00	0.00
58	50	4587	09/01/2017	3	1	1500.00	600.00	0.00
59	51	4589	09/01/2017	3	1	1500.00	600.00	100.00
60	52	4582	10/01/2017	4	1	2000.00	600.00	100.00
61	53	4568	11/01/2017	4	1	2000.00	600.00	100.00
62	55	4568	12/01/2017	3	2	1500.00	1200.00	100.00
63	51	4618	12/01/2017	2	0	1000.00	0.00	0.00
64	56	4567	13/01/2017	7	2	3500.00	1200.00	100.00
65	50	4583	16/01/2017	3	6	1500.00	3600.00	0.00
66	51	4584	16/01/2017	2	7	1000.00	4000.00	200.00
67	52	4583	18/01/2017	5	1	2500.00	600.00	0.00
68	53	4585	20/01/2017	6	6	3000.00	3500.00	100.00
69	55	4616	20/01/2017	4	5	2000.00	3000.00	100.00
70	56	4617	20/01/2017	6	6	3000.00	3600.00	0.00
71	50	4618	25/01/2017	3	3	1500.00	1800.00	0.00
72	51	4587	25/01/2017	2	4	1000.00	2400.00	0.00
73	52	4589	25/01/2017	7	2	3300.00	1200.00	0.00
74	51	4567	25/01/2017	0	2	1200.00	0.00	0.00
75	53	4570	25/01/2017	3	1	1500.00	600.00	0.00
76	52	4571	30/01/2017	6	1	3000.00	600.00	0.00
77	53	4572	30/01/2017	4	0	2000.00	0.00	0.00
78	55	4573	30/01/2017	6	2	3000.00	1200.00	0.00
79	50	4585	06/02/2017	2	3	1000.00	1800.00	0.00
80	51	4582	07/02/2017	3	4	1500.00	2400.00	0.00
81	51	4618	07/02/2017	2	5	1000.00	3000.00	0.00
82	52	4601	07/02/2017	3	5	1500.00	3000.00	0.00
83	53	4602	07/02/2017	2	2	1000.00	1200.00	0.00
84	54	4603	07/02/2017	3	5	1500.00	3000.00	0.00
85	55	4616	07/02/2017	4	2	2000.00	1200.00	0.00
86	56	4582	07/02/2017	1	4	500.00	2400.00	0.00
87	57	4583	08/02/2017	1	6	500.00	3600.00	0.00
88	50	4616	13/02/2017	5	1	2500.00	600.00	0.00
89	51	4572	13/02/2017	5	1	2500.00	600.00	0.00
90	51	4570	13/02/2017	4	1	2000.00	600.00	0.00
91	52	4585	13/02/2017	4	4	2000.00	2400.00	200.00
92	53	4617	13/02/2017	3	5	1500.00	3000.00	0.00
93	54	4567	15/02/2017	3	7	1500.00	4200.00	0.00
94	55	4571	15/02/2017	3	3	1500.00	1800.00	0.00
95	56	4572	15/02/2017	3	3	1500.00	1800.00	0.00
96	57	4591	15/02/2017	2	4	1000.00	2400.00	0.00
97	57	4592	16/02/2017	2	8	1000.00	4800.00	200.00
98	50	4618	20/02/2017	4	6	2000.00	3400.00	0.00
99	51	4601	20/02/2017	3	6	1500.00	3550.00	0.00
100	52	4602	20/02/2017	3	5	1500.00	3000.00	0.00
101	52	4603	20/02/2017	3	5	1500.00	3000.00	0.00
102	53	4583	20/02/2017	3	4	1500.00	2400.00	0.00
103	54	4585	23/02/2017	4	4	2000.00	2400.00	0.00
104	55	4617	23/02/2017	4	4	2000.00	2400.00	0.00
105	56	4616	23/02/2017	5	4	2500.00	2400.00	0.00
106	57	4571	24/02/2017	5	6	2500.00	3600.00	0.00
107	50	4611	28/02/2017	3	6	1500.00	3600.00	0.00
108	51	4612	28/02/2017	3	6	1500.00	3600.00	0.00
109	52	4613	28/02/2017	3	6	1500.00	3600.00	0.00
110	52	4603	28/02/2017	3	3	1500.00	1800.00	0.00
111	53	4616	02/03/2017	4	3	2000.00	1800.00	0.00
112	54	4614	03/03/2017	4	3	2000.00	1800.00	0.00
113	55	4572	03/03/2017	3	3	1500.00	1800.00	0.00
114	56	4603	03/03/2017	3	3	1500.00	1800.00	0.00
115	57	4571	03/03/2017	3	3	1500.00	1800.00	0.00
116	56	4601	07/03/2017	2	5	1000.00	3000.00	100.00
117	57	4602	08/03/2017	2	5	1000.00	3000.00	0.00
118	55	4603	09/03/2017	2	5	1000.00	3000.00	100.00
119	54	4583	09/03/2017	2	4	1000.00	2400.00	0.00
120	54	4585	09/03/2017	3	4	1500.00	2400.00	0.00
121	53	4612	10/03/2017	3	3	1500.00	1800.00	0.00
122	52	4616	10/03/2017	3	3	1500.00	1800.00	0.00
123	50	4617	10/03/2017	3	3	1500.00	1800.00	0.00

Click the button below to advance to the next stage

Next: >> Building Date Formats

Or use the above 'SQL Queries' dropdown menu to jump to any stage.