[ 一日30分 인생승리의 학습법] PHP, VBA and SQL Useful Scripts PHP, VBA 및 SQL 유용한 스크립트
소개
이 팁에서는 유틸리티 프로그램의 기능과 비교할 수 있는 유용한 스크립팅의 기본 사항을 독자에게 제시합니다.
소셜 네트워크와 같은 통신 소프트웨어를 만들기 위한 PHP 스크립트도 MySQL 기반 데이터 모델과 함께 제공됩니다.
VBA 스크립트는 SQL 서버 및 기타 데이터 기반 소프트웨어에 대해 동일한 모델을 수반합니다.
배경
특정 작업을 수행할 때 복잡한 작업을 코딩하는 것보다 스크립팅 기술을 아는 것으로 충분합니다.
따라서 프로그래밍된 데이터 명령을 수행하기 위해 라이브러리와 같은 자율 모듈을 사용하는 이 소프트웨어의 개발을 피합니다.
코드 사용
PHP 및 VBA 스크립트를 진행하기 전에 SQL 스크립트도 배워야 합니다.
소셜 네트워크의 경우 MySQL의 데이터 모델은 다음과 같습니다.
CREATE TABLE `connection` ( `id` int(11) NOT NULL, `usera` int(32) NOT NULL, `userb` int(32) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE `message` ( `id` int(32) NOT NULL, `user` int(32) NOT NULL, `text` varchar(256) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE `user` ( `id` int(32) NOT NULL, `name` varchar(32) NOT NULL, `password` varchar(32) NOT NULL, `hash` varchar(32) NOT NULL, `comment` varchar(256) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ALTER TABLE `connection` ADD PRIMARY KEY (`id`), ADD KEY `connection_usera_index` (`usera`) USING HASH, ADD KEY `connection_userb_index` (`userb`) USING HASH; ALTER TABLE `message` ADD PRIMARY KEY (`id`), ADD KEY `message_user_index` (`user`) USING HASH; ALTER TABLE `user` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `user_name_unique_index` (`name`), ADD UNIQUE KEY `user_hash_unique_index` (`hash`); ALTER TABLE `connection` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4; ALTER TABLE `message` MODIFY `id` int(32) NOT NULL AUTO_INCREMENT; ALTER TABLE `user` MODIFY `id` int(32) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12; ALTER TABLE `connection` ADD CONSTRAINT `fk_connection_usera` FOREIGN KEY (`usera`) REFERENCES `user` (`id`), ADD CONSTRAINT `fk_connection_userb` FOREIGN KEY (`userb`) REFERENCES `user` (`id`); ALTER TABLE `message` ADD CONSTRAINT `fk_message_user` FOREIGN KEY (`user`) REFERENCES `user` (`id`); COMMIT;
소셜 네트워크용 PHP 스크립트는 기본 페이지에서 다음과 같이 설명할 수 있습니다.
<!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>Главная страница</title> <link rel="icon" type="image/jpeg" href="images/favicon.jpg"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <script> var hash = null, id = null; </script> </head> <body> <?php include "db.php"; ?> <?php include "authentication.php"; ?> <center> <img src="images/Logo.jpg"> <br> <br> <hr> <div id="profile"> <iframe id="profileFrame" width="100%" height="320" src="profile.php?hash=<?php echo $hash . "&id=" . $userInformation["id"]; ?>"> </iframe> </div> <div id="message"> <iframe id="messageFrame" width="100%" height="320" src="message.php?hash=<?php echo $hash; ?>"> </iframe> </div> <div id="search"> <iframe id="searchFrame" width="100%" height="320" src="search.php?hash=<?php echo $hash; ?>"> </iframe> </div> </center> </body> </html>
여기서 메타 값 viewport
은 반응형 페이지 레이아웃에 맞게 조정됩니다. 변수 hash
는 보안상의 이유를 나타내는 반면 id
동일한 테이블에 있는 현재 사용자의 ID입니다.
함께 제공되는 Android 클라이언트는 기본 활동 클래스에서 다음과 같이 정의됩니다.
package com.example.baldar; import androidx.appcompat.app.AppCompatActivity; import android.os.Bundle; import android.view.View; import android.webkit.WebResourceRequest; import android.webkit.WebView; import android.webkit.WebViewClient; import android.widget.Button; public class MainActivity extends AppCompatActivity { @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); Button refresh = (Button) findViewById(R.id.refresh); final WebView myWebView = (WebView) findViewById(R.id.webview); myWebView.getSettings().setJavaScriptEnabled(true); myWebView.setWebViewClient(new WebViewClient() { @Override public boolean shouldOverrideUrlLoading (WebView view, WebResourceRequest request) { view.loadUrl(request.getUrl().toString()); return false; } }); myWebView.loadUrl("http://baldar.great-site.net/?i=1"); refresh.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View view) { myWebView.reload(); } }); } }
Excel 워크시트에 SQL 스키마를 생성하기 위한 VBA 스크립트는 다음과 같이 정의됩니다.
Sub Generate_Scripts() Dim i As Integer Dim j As Integer Dim tableName As String i = 2 j = i tableName = CStr(Cells(i, 1).Value) While CStr(Cells(i, 1).Value) <> "END" If tableName <> CStr(Cells(i, 1).Value) Then Open tableName & ".sql" For Output As #1 Print #1, "create table " & tableName; "(" For k = j To i - 1 Print #1, " " & CStr(Cells(k, 2).Value) & " " & CStr(Cells(k, 3).Value) If CStr(Cells(k, 4).Value) = "Y" Then Print #1, " primary key" End If If CStr(Cells(k, 5).Value) <> "" _ And CStr(Cells(k, 6).Value) <> "" Then Print #1, " references " & CStr(Cells(k, 5).Value) & _ "(" & CStr(Cells(k, 6).Value) & ")" End If If k < i - 1 Then Print #1, " ," End If Next Print #1, ");" Close #1 tableName = CStr(Cells(i, 1).Value) j = i End If i = i + 1 Wend End Sub
SAP 개체에 대한 통계를 수집하기 위한 SQL 스크립트는 다음과 같이 정의됩니다.
SELECT A.RNR, A.NUM_ROWS, B.START_LOAD, B.END_LOAD FROM (SELECT RNR, sum(REQ_SELECT) NUM_ROWS FROM sapsr3.RSMONFACT WHERE RNR in (SELECT DISTINCT RNR FROM sapsr3.RSMONICTAB WHERE INFOCUBE in ('X_CUBE' /* Target */ ) AND to_date(substr(to_char(TIMESTAMP), 1, 8), 'yyyymmdd') _ BETWEEN (sysdate - 3) AND (sysdate + 1)/* Time period _ shifted 1 day past and future */ ) GROUP BY RNR) A INNER JOIN (SELECT RNR, (to_date(min(TIMESTAMP), 'YYYYMMDDHH24MISS') + 0.25) START_LOAD, (to_date(max(TIMESTAMP), 'YYYYMMDDHH24MISS') + 0.25) END_LOAD FROM sapsr3.RSMONMESS WHERE RNR in (SELECT DISTINCT RNR FROM sapsr3.RSMONICTAB WHERE INFOCUBE in ('X_CUBE' /* Target */ ) AND to_date(substr(to_char(TIMESTAMP), 1, 8), 'yyyymmdd') _ BETWEEN (sysdate - 3) _ AND (sysdate + 1)/* Time period shifted 1 day past and future */ ) GROUP BY RNR) B ON A.RNR = B.RNR WHERE B.START_DATE BETWEEN (sysdate - 2) AND sysdate /* Time period */
가볼만한 곳
따라서 스크립팅 프로그래밍 기법을 익히고, 소셜 네트워크, 안드로이드 클라이언트, SQL, VBA 스크립트 등을 예로 데이터 구성 및 분석을 위한 별도의 모듈 지원으로 복잡한 프로그래밍 없이 일반적인 작업을 충분히 해결할 수 있는 방법을 보여주었다.
역사
- 2022년 10월 27일 - 최초 출시
- 2022년 11월 2 일 - 링크 제거
- 2022년 11월 4 일 - 라이선스 변경
특허
이 문서는 관련 소스 코드 및 파일과 함께 The Code Project Open License(CPOL) 에 따라 사용이 허가되었습니다.
Introduction
In this tip, we will present to the reader the basics of useful scripting which can be compared to the functionality of utility programs.
The PHP scripts for creating a communication software like social network is also presented along with data model based on MySQL.
VBA scripts accompany the same model for SQL server and other data-driven software.
Background
When doing particular jobs, it's enough to know scripting techniques rather than coding complex tasks.
Thus, we avoid the development of this software using autonomous modules like libraries for doing the programmed data instructions.
Using the Code
Before proceeding to PHP and VBA scripts, it's necessary to learn SQL scripts too.
For our social network, the data model is as follows in MySQL:
CREATE TABLE `connection` ( `id` int(11) NOT NULL, `usera` int(32) NOT NULL, `userb` int(32) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE `message` ( `id` int(32) NOT NULL, `user` int(32) NOT NULL, `text` varchar(256) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE `user` ( `id` int(32) NOT NULL, `name` varchar(32) NOT NULL, `password` varchar(32) NOT NULL, `hash` varchar(32) NOT NULL, `comment` varchar(256) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ALTER TABLE `connection` ADD PRIMARY KEY (`id`), ADD KEY `connection_usera_index` (`usera`) USING HASH, ADD KEY `connection_userb_index` (`userb`) USING HASH; ALTER TABLE `message` ADD PRIMARY KEY (`id`), ADD KEY `message_user_index` (`user`) USING HASH; ALTER TABLE `user` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `user_name_unique_index` (`name`), ADD UNIQUE KEY `user_hash_unique_index` (`hash`); ALTER TABLE `connection` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4; ALTER TABLE `message` MODIFY `id` int(32) NOT NULL AUTO_INCREMENT; ALTER TABLE `user` MODIFY `id` int(32) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12; ALTER TABLE `connection` ADD CONSTRAINT `fk_connection_usera` FOREIGN KEY (`usera`) REFERENCES `user` (`id`), ADD CONSTRAINT `fk_connection_userb` FOREIGN KEY (`userb`) REFERENCES `user` (`id`); ALTER TABLE `message` ADD CONSTRAINT `fk_message_user` FOREIGN KEY (`user`) REFERENCES `user` (`id`); COMMIT;
The PHP scripts for our social networks can be described in the main page as follows:
<!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>Главная страница</title> <link rel="icon" type="image/jpeg" href="images/favicon.jpg"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <script> var hash = null, id = null; </script> </head> <body> <?php include "db.php"; ?> <?php include "authentication.php"; ?> <center> <img src="images/Logo.jpg"> <br> <br> <hr> <div id="profile"> <iframe id="profileFrame" width="100%" height="320" src="profile.php?hash=<?php echo $hash . "&id=" . $userInformation["id"]; ?>"> </iframe> </div> <div id="message"> <iframe id="messageFrame" width="100%" height="320" src="message.php?hash=<?php echo $hash; ?>"> </iframe> </div> <div id="search"> <iframe id="searchFrame" width="100%" height="320" src="search.php?hash=<?php echo $hash; ?>"> </iframe> </div> </center> </body> </html>
Here, the meta value viewport
is tuned for responsive page layout. The variable hash
stands for security reasons, whereas id
is an id of the current user in the same table.
The accompanying Android client is defined as follows in the main activity class:
package com.example.baldar; import androidx.appcompat.app.AppCompatActivity; import android.os.Bundle; import android.view.View; import android.webkit.WebResourceRequest; import android.webkit.WebView; import android.webkit.WebViewClient; import android.widget.Button; public class MainActivity extends AppCompatActivity { @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); Button refresh = (Button) findViewById(R.id.refresh); final WebView myWebView = (WebView) findViewById(R.id.webview); myWebView.getSettings().setJavaScriptEnabled(true); myWebView.setWebViewClient(new WebViewClient() { @Override public boolean shouldOverrideUrlLoading (WebView view, WebResourceRequest request) { view.loadUrl(request.getUrl().toString()); return false; } }); myWebView.loadUrl("http://baldar.great-site.net/?i=1"); refresh.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View view) { myWebView.reload(); } }); } }
VBA script for generating SQL schema on the Excel work sheet is defined as follows:
Sub Generate_Scripts() Dim i As Integer Dim j As Integer Dim tableName As String i = 2 j = i tableName = CStr(Cells(i, 1).Value) While CStr(Cells(i, 1).Value) <> "END" If tableName <> CStr(Cells(i, 1).Value) Then Open tableName & ".sql" For Output As #1 Print #1, "create table " & tableName; "(" For k = j To i - 1 Print #1, " " & CStr(Cells(k, 2).Value) & " " & CStr(Cells(k, 3).Value) If CStr(Cells(k, 4).Value) = "Y" Then Print #1, " primary key" End If If CStr(Cells(k, 5).Value) <> "" _ And CStr(Cells(k, 6).Value) <> "" Then Print #1, " references " & CStr(Cells(k, 5).Value) & _ "(" & CStr(Cells(k, 6).Value) & ")" End If If k < i - 1 Then Print #1, " ," End If Next Print #1, ");" Close #1 tableName = CStr(Cells(i, 1).Value) j = i End If i = i + 1 Wend End Sub
The SQL script for gathering statistics for SAP objects is defined as follows:
SELECT A.RNR, A.NUM_ROWS, B.START_LOAD, B.END_LOAD FROM (SELECT RNR, sum(REQ_SELECT) NUM_ROWS FROM sapsr3.RSMONFACT WHERE RNR in (SELECT DISTINCT RNR FROM sapsr3.RSMONICTAB WHERE INFOCUBE in ('X_CUBE' /* Target */ ) AND to_date(substr(to_char(TIMESTAMP), 1, 8), 'yyyymmdd') _ BETWEEN (sysdate - 3) AND (sysdate + 1)/* Time period _ shifted 1 day past and future */ ) GROUP BY RNR) A INNER JOIN (SELECT RNR, (to_date(min(TIMESTAMP), 'YYYYMMDDHH24MISS') + 0.25) START_LOAD, (to_date(max(TIMESTAMP), 'YYYYMMDDHH24MISS') + 0.25) END_LOAD FROM sapsr3.RSMONMESS WHERE RNR in (SELECT DISTINCT RNR FROM sapsr3.RSMONICTAB WHERE INFOCUBE in ('X_CUBE' /* Target */ ) AND to_date(substr(to_char(TIMESTAMP), 1, 8), 'yyyymmdd') _ BETWEEN (sysdate - 3) _ AND (sysdate + 1)/* Time period shifted 1 day past and future */ ) GROUP BY RNR) B ON A.RNR = B.RNR WHERE B.START_DATE BETWEEN (sysdate - 2) AND sysdate /* Time period */
Points of Interest
Thus, we have learned the scripting programming techniques and showed the way which is enough to solve common tasks without using the complex programming with separate module support for data composition and analysis on the example of social network, Android client, SQL and VBA scripts.
History
- 27th October, 2022 - Initial release
- 2nd November, 2022 - Removed links
- 4th November, 2022 - License changed
License
This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)
[출처] https://www.codeproject.com/Tips/5345385/PHP-VBA-and-SQL-Useful-Scripts
광고 클릭에서 발생하는 수익금은 모두 웹사이트 서버의 유지 및 관리, 그리고 기술 콘텐츠 향상을 위해 쓰여집니다.