· 7 years ago · Nov 25, 2018, 05:47 PM
1<?php
2
3/**************************************************
4*
5* -------------------------------------------------
6* MySQL Basics for PHP
7* -------------------------------------------------
8* Copyright 2001 Mike Gieson
9* http://www.gieson.com/
10*
11*
12**************************************************/
13
14
15
16
17// Setup
18$MY_CONFIG['host'] = "localhost";
19$MY_CONFIG['adminUser'] = "root";
20$MY_CONFIG['adminPwd'] = "hackme";
21$MY_CONFIG['db'] = "myCoolDatabaseName";
22$MY_CONFIG['tableName'] = "music";
23
24
25// NOTE: There are a number of "Helper" functions at the bottom of this script that:
26// - help make the code a bit more streamlined
27// - allow for this script to run scoothly.
28// - allow for the printout to look smashing in your browser.
29
30
31// This sets up the HTML CSS styleshoot on the "page"
32printStyles();
33
34/***********************************************/
35print "<h1>PHP MySQL Test Script</h1>";
36print "<p>Use this script to familiarize yourself with how to work with MySQL through PHP.</p>";
37print '<p><a href="http://www.gieson.com/">Copyright 2011. Mike Gieson ( www.gieson.com )</a></p>';
38/************************************************/
39
40
41
42/***********************************************/
43print "<h1>YOUR CONFIGURATION</h1>";
44/************************************************/
45print "<pre>";
46print 'host: '. $MY_CONFIG['host'] ."\n";
47print 'adminUser: '. $MY_CONFIG['adminUser'] ."\n";
48print 'adminPwd: '. $MY_CONFIG['adminPwd'] ."\n";
49print 'db: ' . $MY_CONFIG['db'] ."\n";
50print 'tableName: '. $MY_CONFIG['tableName'] ."\n";
51print "</pre>";
52
53/***********************************************/
54print "<h1>CREATE DATABASE</h1>";
55/************************************************/
56
57
58
59
60// Connect
61$link = mysql_connect($MY_CONFIG['host'], $MY_CONFIG['adminUser'], $MY_CONFIG['adminPwd'])
62 or die("<BR>Unable to connect. Check username and passowrd.<BR>".mysql_error());
63
64/*
65// Traditionally we would follow immediately with:
66mysql_select_db($MY_CONFIG['db'], $link)
67 or die(mysql_error());
68// But for this example, we need to first check to see if the DB
69// was created, since we're doing everything in one-fell-swoop.
70*/
71
72$db_select = mysql_select_db($MY_CONFIG['db'], $link);
73if(!$db_select){
74 // This is our actual command to create a DB:
75 $sql = 'CREATE DATABASE IF NOT EXISTS '.$MY_CONFIG['db'];
76 $result = do_query($sql);
77 if(!$result){
78 die(mysql_error());
79 }
80} else {
81 print "<p>Database already exists.</p>";
82}
83
84// Traditionally, this goes just after mysql)connect,
85// but since we've just created the DB, we need to put it here
86mysql_select_db($MY_CONFIG['db'], $link)
87 or die(mysql_error());
88
89
90/***********************************************/
91print "<h1>CREATE TABLE</h1>";
92/************************************************/
93
94
95$sql = "DROP TABLE IF EXISTS ". $MY_CONFIG['tableName'];
96do_query($sql);
97
98$sql = 'CREATE TABLE IF NOT EXISTS `'.$MY_CONFIG['tableName'].'` (
99`id` int(8) unsigned NOT NULL,
100`artist` text,
101`album` text,
102`title` text,
103`track` int(8) unsigned NULL,
104`year` int(8) unsigned NULL,
105PRIMARY KEY (`id`),
106FULLTEXT KEY `artist` (`artist`),
107FULLTEXT KEY `album` (`album`),
108FULLTEXT KEY `title` (`title`)
109) ENGINE=MyISAM DEFAULT CHARSET=utf8;';
110
111do_query($sql);
112
113
114
115
116
117
118
119
120/***********************************************/
121print "<h1>ALTER TABLE</h1>";
122/************************************************/
123
124$sql = 'ALTER TABLE ' . $MY_CONFIG['tableName'] . ' MODIFY id int(8) unsigned NOT NULL AUTO_INCREMENT;';
125do_query($sql, $link);
126
127
128
129
130
131
132/***********************************************/
133print "<h1>INSERT STATEMENTS</h1>";
134/************************************************/
135
136$Adata = Array();
137$Adata['id'] = "1";
138$Adata['artist'] = "the beatles";
139$Adata['album'] = "Abbey Road";
140
141$sql = "INSERT INTO " . $MY_CONFIG['tableName'] . " (" . arrayToSQLString($Adata, "key") .") VALUES (" . arrayToSQLString($Adata, "value") . ")";
142do_query($sql);
143show_table();
144
145
146// Not including "artist"
147$Adata = Array();
148$Adata['id'] = "2";
149$Adata['album'] = "Abbey Road 2 test";
150
151$sql = "INSERT INTO " . $MY_CONFIG['tableName'] . " (" . arrayToSQLString($Adata, "key") .") VALUES (" . arrayToSQLString($Adata, "value") . ")";
152do_query($sql);
153show_table();
154
155
156// Not including "id" or "artist"
157// the "id" is set to "auto increment" so the ID is automatically chosen by MySQL
158$Adata = Array();
159$Adata['album'] = "Abbey Road 3 test";
160
161$sql = "INSERT INTO " . $MY_CONFIG['tableName'] . " (" . arrayToSQLString($Adata, "key") .") VALUES (" . arrayToSQLString($Adata, "value") . ")";
162do_query($sql);
163show_table();
164
165
166
167
168
169
170
171
172
173/***********************************************/
174print "<h1>REPLACE STATEMENTS</h1>";
175/************************************************/
176
177$Adata = Array();
178$Adata['id'] = "2";
179$Adata['artist'] = "The Beatles";
180$Adata['album'] = "Let It Be";
181
182$sql = "REPLACE INTO " . $MY_CONFIG['tableName'] . " (" . arrayToSQLString($Adata, "key") . ") VALUES (" . arrayToSQLString($Adata, "value") . ")";
183do_query($sql);
184show_table();
185
186
187// Replace also acts as insesrt if the primary key is not defined. And since the primary key is set to auto increment, MySQL must treat this as a new insert.
188$Adata = Array();
189$Adata['artist'] = "The Beatles";
190$Adata['album'] = "The Magical Mystery Tour";
191
192$sql = "REPLACE INTO " . $MY_CONFIG['tableName'] . " (" . arrayToSQLString($Adata, "key") . ") VALUES (" . arrayToSQLString($Adata, "value") . ")";
193do_query($sql);
194show_table();
195
196
197
198
199
200
201
202
203
204/***********************************************/
205print "<h1>UPDATE STATEMENTS</h1>";
206/************************************************/
207
208
209$Adata = Array();
210$Adata['id'] = "1";
211$Adata['artist'] = "the beatles";
212$Adata['album'] = "Abbey Road";
213
214
215$sql = "UPDATE " . $MY_CONFIG['tableName'] . " SET title=" . myStringPrep("Come Together") . " WHERE id=" . myStringPrep( "1" );
216do_query($sql);
217show_table();
218
219
220$sql = "UPDATE " . $MY_CONFIG['tableName'] . " SET track=" . myStringPrep("1") . ", year=" . myStringPrep("1969") . " WHERE id=" . myStringPrep( "1" );
221do_query($sql);
222show_table();
223
224
225
226
227$sql = "UPDATE " . $MY_CONFIG['tableName'] . " SET title=" . myStringPrep("Mike's Song") . " WHERE id=1";
228do_query($sql);
229show_table();
230
231
232
233
234
235
236
237
238
239
240/***********************************************/
241print "<h1>SELECT STATEMENTS</h1>";
242/************************************************/
243
244
245print "<p>NOTE: Before getting into the SELECT stuff let's first populate the table with a bunch of crap.
246We're using our handy-dandy populate_table_with_generic_data() function, which, if you look at the source code, pops up a few times.<p>";
247
248// Need to add alot of data for experimentation on the next examples:
249populate_table_with_generic_data();
250
251$sql = "SELECT * FROM " . $MY_CONFIG['tableName']. " WHERE title='You Never Give Me Your Money';";
252$result = do_query($sql);
253show_table($result);
254
255
256$sql = "SELECT * FROM " . $MY_CONFIG['tableName']. " WHERE 1;";
257$result = do_query($sql);
258show_table($result);
259
260
261$sql = "SELECT * FROM " . $MY_CONFIG['tableName']. " WHERE track IN ('1','2');";
262$result = do_query($sql);
263show_table($result);
264
265
266$Adata = Array();
267$Adata[] = "You Won't See Me";
268$Adata[] = "I'm Looking Through You";
269
270$sql = "SELECT title,album,year FROM " . $MY_CONFIG['tableName']. " WHERE title IN (" . arrayToSQLString($Adata, "value") .");";
271$result = do_query($sql);
272show_table($result);
273
274
275$sql = "SELECT * FROM " . $MY_CONFIG['tableName']. " WHERE title RLIKE (". myStringPrep('you') .") OR title RLIKE (". myStringPrep('mustard') . ");";
276$result = do_query($sql);
277show_table($result);
278
279
280$sql = "SELECT * FROM " . $MY_CONFIG['tableName']. " WHERE title RLIKE (". myStringPrep('you') .") AND title RLIKE (". myStringPrep('me') . ");";
281$result = do_query($sql);
282show_table($result);
283
284
285$sql = "SELECT * FROM " . $MY_CONFIG['tableName']. " WHERE title RLIKE (". myStringPrep('you') .") OR (title RLIKE (". myStringPrep('the') . ") AND title RLIKE (". myStringPrep('me') . "));";
286$result = do_query($sql);
287show_table($result);
288
289
290$sql = "SELECT * FROM " . $MY_CONFIG['tableName']. " WHERE 1 ORDER BY RAND() LIMIT 3;";
291$result = do_query($sql);
292show_table($result);
293
294
295$sql = "SELECT DISTINCT year FROM " . $MY_CONFIG['tableName']. " WHERE 1;";
296$result = do_query($sql);
297show_table($result);
298
299
300
301$sql = "SELECT DISTINCT artist FROM " . $MY_CONFIG['tableName']. " WHERE 1;";
302$result = do_query($sql);
303show_table($result);
304
305
306$sql = "SELECT DISTINCT artist,album FROM " . $MY_CONFIG['tableName']. " WHERE 1 ORDER BY RAND() LIMIT 5;";
307$result = do_query($sql);
308show_table($result);
309
310
311$sql = "SELECT MAX(id) FROM " . $MY_CONFIG['tableName'];
312$result = do_query($sql);
313show_table($result);
314
315$sql = "SELECT MAX(track) FROM " . $MY_CONFIG['tableName'];
316$result = do_query($sql);
317show_table($result);
318
319
320
321
322
323
324
325
326
327
328/***********************************************/
329print "<h1>DELETE STATEMENTS</h1>";
330/************************************************/
331
332$sql = "DELETE FROM " . $MY_CONFIG['tableName'] . " WHERE album=" . myStringPrep("rubber soul");
333do_query($sql);
334show_table();
335
336
337$Adata = Array();
338$Adata[] = "Her Majesty";
339$Adata[] = "Because";
340$Adata[] = "Something";
341$Adata[] = "Oh! Darling";
342
343
344$sql = "DELETE FROM " . $MY_CONFIG['tableName'] . " WHERE title IN (" . arrayToSQLString($Adata, "value") . ");";
345do_query($sql);
346show_table();
347
348// Repopulate table
349populate_table_with_generic_data();
350//show_table();
351
352$sql = "DELETE FROM " . $MY_CONFIG['tableName'] . " WHERE title RLIKE ('you') OR artist RLIKE ('me') OR artist RLIKE ('my');";
353do_query($sql);
354show_table();
355
356
357
358
359
360print '<p><a href="http://www.gieson.com/">MySQL basics for PHP. Copyright 2011 Mike Gieson.</a></p>';
361
362
363
364
365/*********************************************************************
366
367Helper function and other miscellaneous crap
368
369*********************************************************************/
370
371
372function myStringPrep($value){
373 if (get_magic_quotes_gpc()) {
374 $value = stripslashes($value);
375 }
376 if (!is_numeric($value)) {
377 $value = mysql_real_escape_string($value);
378 }
379 //$value = "'" . str_replace("'", "\'", $value) . "'";
380 //if (!is_numeric($value)) {
381 $value = "'" . $value . "'";
382 //}
383 return $value;
384}
385
386function arrayToSQLString($theArray, $theKind){
387 $Aretval = array();
388 foreach ($theArray as $key => $val) {
389 if($theKind == "key"){
390 $Aretval[] = $key;
391 } else if($theKind == "value"){
392 $Aretval[] = myStringPrep($val);
393 } else if($theKind == "all"){
394 $Aretval[] = $key . "=" . myStringPrep($val);
395 }
396 }
397 return implode(",", $Aretval);
398}
399
400function do_error(){
401 print "<br><br>---------------ERROR:-----------------<br><br>";
402 print (mysql_error());
403 exit;
404}
405
406function do_query($theQuery){
407 global $link, $sql;
408 print '<pre class="codedBox">';
409 print '$sql = "'.$theQuery.'";<br>';
410 print 'mysql_query($sql);<br>';
411 $query = mysql_query($theQuery, $link);
412 if (!$query) {
413 do_error();
414 } else {
415 print "<br>>> RESULT: ";
416 print ($query);
417
418 }
419 print '</pre>';
420 return $query;
421}
422
423function show_table($result = null){
424 global $MY_CONFIG, $link;
425 if($result == null){
426 $sql = "SELECT * FROM " . $MY_CONFIG['tableName'] . ";";
427 $result = mysql_query($sql, $link);
428 }
429 if($result){
430
431
432 $printTable = "";
433
434 $printTable .= '<table width="495" border="0" cellpadding="5" cellspacing="0" class="singleOutline">'."\n";
435 $count = 0;
436 $countCols = 0;
437 while (@$line = mysql_fetch_assoc($result)) {
438
439 // Header Row
440 if($count == 0){
441 $printTable .= "<tr>\n";
442 foreach ($line as $key => $val) {
443 $printTable .= '<td valign="top" nowrap="nowrap" class="tableHeader">' . $key . "</td>\n";
444 $countCols = $countCols + 1;
445 }
446 $printTable .= "</tr>\n";
447 }
448
449 // Data Row
450 $printTable .= "<tr>\n";
451 foreach ($line as $key => $val) {
452 $printTable .= '<td align="left" valign="top" nowrap="nowrap">' . $val . "</td>\n";
453 }
454 $printTable .= "</tr>\n";
455
456 $count = $count + 1;
457
458 }
459
460 $printTable .= "<tr>\n";
461 $printTable .= '<td align="left" valign="top" nowrap="nowrap" colspan="'.$countCols.'" class="tableHeader">Records: ' . $count . "</td>\n";
462 $printTable .= "</tr>\n";
463
464 $printTable .= "</table>\n";
465 $printTable .= "<p> </p>\n";
466 $printTable .= "<p> </p>\n";
467
468 print $printTable;
469
470
471 } else {
472 do_error($sql);
473 }
474}
475
476function show_raw_data($theQueryResult){
477 $count = 1;
478 print "<pre>";
479 while ($line = mysql_fetch_assoc($theQueryResult)) {
480 print "<BR>line #" . ($count) . "\n";
481 foreach ($line as $key => $val) {
482 print "key: " . $key . " \tval: ". $val . "\n";
483 }
484 $count = $count + 1;
485 }
486 print "</pre>";
487}
488
489function populate_table_with_generic_data(){
490 global $MY_CONFIG;
491 $sql = "REPLACE INTO " . $MY_CONFIG['tableName'] . ' (id, artist, album, title, track, year)
492 VALUES
493 ("1", "the beatles", "Abbey Road", "Come Together", "1", "1969"),
494 ("2", "the beatles", "Abbey Road", "Something", "2", "1969"),
495 ("3", "the beatles", "Abbey Road", "Maxwell\'s Silver Hammer", "3", "1969"),
496 ("4", "the beatles", "Abbey Road", "Oh! Darling", "4", "1969"),
497 ("5", "the beatles", "Abbey Road", "Octopus\'s Garden", "5", "1969"),
498 ("6", "the beatles", "Abbey Road", "I Want You (She\'s So Heavy)", "6", "1969"),
499 ("7", "the beatles", "Abbey Road", "Here Comes the Sun", "7", "1969"),
500 ("8", "the beatles", "Abbey Road", "Because", "8", "1969"),
501 ("9", "the beatles", "Abbey Road", "You Never Give Me Your Money", "9", "1969"),
502 ("10", "the beatles", "Abbey Road", "Sun King", "10", "1969"),
503 ("11", "the beatles", "Abbey Road", "Mean Mr. Mustard", "11", "1969"),
504 ("12", "the beatles", "Abbey Road", "Polythene Pam", "12", "1969"),
505 ("13", "the beatles", "Abbey Road", "She Came in Through the Bathroom Window", "13", "1969"),
506 ("14", "the beatles", "Abbey Road", "Golden Slumbers", "14", "1969"),
507 ("15", "the beatles", "Abbey Road", "Carry That Weight", "15", "1969"),
508 ("16", "the beatles", "Abbey Road", "The End", "16", "1969"),
509 ("17", "the beatles", "Abbey Road", "Her Majesty", "17", "1969"),
510 ("18", "beatles", "Rubber Soul", "Drive My Car", "1", "1965"),
511 ("19", "beatles", "Rubber Soul", "Norwegian Wood (This Bird Has Flown)", "2", "1965"),
512 ("20", "beatles", "Rubber Soul", "You Won\'t See Me", "3", "1965"),
513 ("21", "beatles", "Rubber Soul", "Nowhere Man", "4", "1965"),
514 ("22", "beatles", "Rubber Soul", "Think for Yourself", "5", "1965"),
515 ("23", "beatles", "Rubber Soul", "The Word", "6", "1965"),
516 ("24", "beatles", "Rubber Soul", "Michelle", "7", "1965"),
517 ("25", "beatles", "Rubber Soul", "What Goes On", "8", "1965"),
518 ("26", "beatles", "Rubber Soul", "Girl", "9", "1965"),
519 ("27", "beatles", "Rubber Soul", "I\'m Looking Through You", "10", "1965"),
520 ("28", "beatles", "Rubber Soul", "In My Life", "11", "1965"),
521 ("29", "beatles", "Rubber Soul", "Wait", "12", "1965"),
522 ("30", "beatles", "Rubber Soul", "If I Needed Someone", "13", "1965"),
523 ("31", "beatles", "Rubber Soul", "Run for Your Life", "14", "1965");';
524
525 do_query( $sql);
526}
527
528
529function printStyles(){
530 $styles = '<style type="text/css">
531
532 h1 {
533 font-family: Arial, Helvetica, sans-serif;
534 font-size: 16px;
535 font-weight: bold;
536 color: #666;
537 padding-top: 40px;
538 }
539 .codedBox {
540 background-color: #EFEFEF;
541 padding: 5px;
542 border: 1px solid #CCCCCC;
543 font-family: "Courier New", Courier, mono;
544 font-size: 12px;
545 color: #3300FF;
546 }
547 .mysqlCommand {
548 color: #CC0000;
549 }
550 .cellBkgd {
551 background-color: #E6F2FF;
552 }
553 .tableHeader {
554 background-color: #E6F2FF;
555 font-weight: bold;
556 }
557 table.singleOutline {
558 border-width: 0px 0px 0px 0px;
559 border-spacing: 0px;
560 border-style: outset outset outset outset;
561 border-color: #CCCCCC;
562 border-collapse: collapse;
563 background-color: white;
564 font-family: Arial, Helvetica, sans-serif;
565 font-size: 12px;
566 }
567 table.singleOutline th {
568 border-width: 1px 1px 1px 1px;
569 padding: 3px 3px 3px 3px;
570 border-style: inset inset inset inset;
571 border-color: #CCCCCC;
572 background-color: white;
573 -moz-border-radius: 0px 0px 0px 0px;
574 }
575 table.singleOutline td {
576 border-width: 1px 1px 1px 1px;
577 padding: 3px 3px 3px 3px;
578 border-style: inset inset inset inset;
579 border-color: #CCCCCC;
580 background-color: #FAF9E6;
581 -moz-border-radius: 0px 0px 0px 0px;
582 }
583 table {
584 font-family: Arial, Helvetica, sans-serif;
585 font-size: 12px;
586 margin-top: 0px;
587 margin-right: 0px;
588 margin-bottom: 0px;
589 margin-left: 0px;
590 padding-top: 0px;
591 padding-right: 0px;
592 padding-bottom: 0px;
593 padding-left: 0px;
594 }
595 p {
596 font-family: Arial, Helvetica, sans-serif;
597 font-size: 12px;
598 }
599</style>';
600 print $styles;
601}
602?>