Php + MySQL Library Management System (III) and mysql Library Management System

Php + MySQL Library Management System (III) and mysql Library Management System
1. the simple insert operation for a single book is the uniqueness of the primary key. That is to say, there cannot be a library client with the same number. You can check whether this book exists before inserting it and then report it to the user. you can directly perform the operation and parse the returned results of SQL Execution. The cause of the error can also be resolved and reported to the user.
After the data is submitted to the page, it is best to verify the submitted data to prevent errors during data insertion or data errors in the database.
The Code is as follows:

<? Phprequire ("MySqlUtils. php "); header (" Content-type: text/html; charset = UTF-8 "); $ bookID = $ _ POST ['booknumber']; $ classfication = $ _ POST ['bookclass']; $ bookName = $ _ POST ['bookname']; $ press = $ _ POST ['Press ']; $ publicTime =$ _ POST ['date']; $ author =$ _ POST ['author']; $ price =$ _ POST ['price']; $ numberAll = $ _ POST ['number']; $ isRightForm = checkForm (); $ isRightInsert = insertBook (); if ($ isRightInsert) {echo "<Script> Alert ('book receiving successfully') </Script> "; echo" <Script> window. location. href = 'admindoaddonebook. php' </Script> ";}else {echo" <Script> alert ('book receiving failed. Please try again! ') </Script> "; echo" <Script> window. location. href = 'admindoaddonebook. php' </Script> ";} function checkForm () {// The following checks the validity of the book number. global $ bookID; if ($ bookID = null) {echo "<Script> alert ('book number cannot be blank ') </Script>"; echo "<Script> window. location. href = 'admindoaddonebook. php '</Script> "; return false;} $ isRightBookID = preg_match ('/[0-9]/', $ bookID); if (! $ IsRightBookID) {echo "<Script> alert ('book number contains invalid characters') </Script>"; echo "<Script> window. location. href = 'admindoaddonebook. php' </Script> "; return false;} // check the book name global $ bookName; if (null = $ bookName) {echo "<Script> alert ('book name cannot be blank ') </Script>"; echo "<Script> window. location. href = 'admindoaddonebook. php' </Script> "; return false;} // press global $ press; if (null = $ press) {echo "<Script> alert ('Press cannot be blank ') </Script>"; echo "<Script> window. location. href = 'admindoaddonebook. php' </Script> "; return false;} // author: global $ author; if (null = $ author) {echo "<Script> alert ('author cannot be blank ') </Script>"; echo "<Script> window. location. href = 'admindoaddonebook. php' </Script> "; return false;} // price global $ price; if (null = $ price) {echo "<Script> alert ('price cannot be blank ') </Script>"; echo "<Script> window. location. href = 'admindoaddonebook. php' </Script> "; return false ;} $ IsRightPrice = preg_match ('/[0-9]./', $ price); if (! $ IsRightPrice) {echo "<Script> alert ('book price contains invalid characters') </Script>"; echo "<Script> window. location. href = 'admindoaddonebook. php' </Script> "; return false;} // stock global $ numberAll; if (null = $ numberAll) {echo "<Script> alert ('inventory cannot be blank ') </Script>"; echo "<Script> window. location. href = 'admindoaddonebook. php '</Script> "; return false;} $ isRightAllNumber = preg_match ('/[0-9]/', $ numberAll); if (! $ IsRightAllNumber) {echo "<Script> alert ('books inventory contains invalid characters') </Script>"; echo "<Script> window. location. href = 'admindoaddonebook. php' </Script> "; return false;} return true;} function insertBook () {global $ bookID; global $ classfication; global $ bookName; global $ press; global $ publicTime; global $ author; global $ price; global $ numberAll; $ link = getLink (); if ('0' = $ link) {echo "<Script> alert ('database connection failed'); </Script>"; return f Alse;} $ class = null; if ('1' = $ classfication) {$ class = 'human';} else if ('2' = $ classfication) {$ class = 'auxiliary ';} else if ('3' ==$ classfication) {$ class = 'game';} else if ('4' ===$ classfication) {$ class = 'tech';} else if ('5' ==$ classfication) {$ class = 'LIFE';} else {$ class = 'tech ';} $ SQL = "insert into book values (". $ bookID. ",'". $ class. "',". "'". $ bookName. "',". "'". $ press. "',". "'". $ publicTime. "',". "'". $ author. "',". $ Price. ",". $ numberAll. ",". $ numberAll. ");"; $ isRightInsert = getResoures ('libray', $ SQL); closeConnect ($ link); return $ isRightInsert;}?>

2. Batch import of data exists in an excel table. For more information, see here.

<? Php // introduce PHPExcelrequire (". /phpexcel/PHPExcel. php "); require (". /phpexcel/PHPExcel/Reader/Excel2007.php "); require (". /phpexcel/PHPExcel/Reader/Excel5.php "); require (". /phpexcel/PHPExcel/Cell. php "); header ('content-Type: text/html; charset = UTF-8; '); $ isUpLoadSuccess = false; $ failReason = null; if ($ _ FILES ["filePath"] ["type"] = "application/octet-stream ") {if ($ _ FILES ["filePath"] ["error"]> 0) {$ failReason = "R Eturn Code :". $ _ FILES ["filePath"] ["error"]; // echo "Return Code :". $ _ FILES ["filePath"] ["error"]. "<br/>";} else {if (file_exists ("upload /". $ _ FILES ["filePath"] ["name"]) {// echo $ _ FILES ["filePath"] ["name"]. "already exists. "; $ failReason = $ _ FILES [" filePath "] [" name "]. "already exists. "; $ failReason =" this file already exists ";} else {move_uploaded_file ($ _ FILES [" filePath "] [" tmp_name "]," upload /". $ _ FILES ["filePa Th "] [" name "]); $ isUpLoadSuccess = true; // echo" Stored in :". "upload /". $ _ FILES ["filePath"] ["name"] ;}} else {// echo "Invalid file "; $ failReason = 'unselected file or file invisible or file type error';} if ($ isUpLoadSuccess) {// echo "<Script> alert ('uploaded successfully '); </Script> ";}else {echo" <Script> alert ('". $ failReason. "'); </Script>"; echo "<Script> window. location. href = 'admindoaddbook. php' </Script> "; exit (0);} $ filePath = ". /upload /". $ _ FILES ["filePa Th "] [" name "]; $ objPHPExcel = new PHPExcel (); $ objPHPExcelReader = new PHPExcel_Reader_Excel2007 (); // determine whether the object can read if ($ objPHPExcelReader-> canRead ($ filePath) {// echo "yes ";} else {// when the version is incorrect, change the version $ objPHPExcelReader = new PHPExcel_Reader_Excel5 (); if ($ objPHPExcelReader-> canRead ($ filePath )) {} else {echo "<Script> alert ('file does not exist or the file is unreadable. Check the file and try again! '); </Script> "; return ;}$ objPHPExcel = $ objPHPExcelReader-> load ($ filePath ); // read the first worksheet in the excel file $ currentSheet = $ objPHPExcel-> getSheet (0); // obtain the largest column number $ allColumn = $ currentSheet-> getHighestColumn (); // obtain the largest row number $ allRow = $ currentSheet-> getHighestRow (); // connect to the database $ link = mysql_connect ("127.0.0.1", "root ","") or die ("cannot connect to Database ". mysql_error (); if ($ link) {// echo "connection successful! ";}$ Db_select = mysql_select_db (" libray "); if ($ db_select) {// echo" yes ";}$ allInsertNumber = 0; $ insertSuccessNumber = 0; for ($ currentRow = 2; $ currentRow <= $ allRow; $ currentRow ++) {$ SQL = "insert INTO Book values ("; for ($ currentColumn = 'a '; $ currentColumn <= $ allColumn; $ currentColumn ++) {$ str = $ currentColumn. $ currentRow; $ val = $ currentSheet-> getCell ($ str)-> getValue (); if ($ currentColumn = 'B' | $ cu R1_column = 'C' | $ currentColumn = 'D' | $ currentColumn = 'F') {// $ val = convertToUTF8 ($ val ); $ val = "'". $ val. "'";} if ($ currentColumn = 'E') {$ val = excelTime ($ val); $ val = "'". $ val. "'";} if ('A '! = $ CurrentColumn) {$ SQL = $ SQL. "," ;}$ SQL = $ SQL. $ val;} $ SQL = $ SQL. ")"; $ B = mysql_query ($ SQL); // echo "$ SQL <br>"; $ allInsertNumber ++; if ($ B) {// echo "yes"; $ insertSuccessNumber ++;} else {// echo "no" ;}// echo "$ SQL ";} $ result = "$ allInsertNumber data records in total. $ insertSuccessNumber data records are successfully executed! "; Echo" <Script> alert ('". $ result. "'); </Script>"; echo "<Script> window. location. href = 'admindoaddbook. php' </Script> "; // The tool function solves Chinese garbled characters. function convertToUTF8 ($ str) {return iconv ('utf-8', 'gb2312 ', $ str );} function excelTime ($ date, $ time = false) {if (function_exists ('gregoriantojd ') {if (is_numeric ($ date) {$ jd = GregorianToJD (1, 1, 1970); $ gregorian = JDToGregorian ($ jd + intval ($ date)-25569); $ d Ate = explode ('/', $ gregorian); $ date_str = str_pad ($ date [2], 4, '0', STR_PAD_LEFT ). "-". str_pad ($ date [0], 2, '0', STR_PAD_LEFT ). "-". str_pad ($ date [1], 2, '0', STR_PAD_LEFT ). ($ time? "00:00:00": ''); return $ date_str ;}} else {$ date = $ date> 25568? $ Date + 1: 25569;/* There was a bug if Converting date before 1-1-1970 (tstamp 0) */$ ofs = (70*365 + 17 + 2) * 86400; $ date = date ("Y-m-d", ($ date * 86400)-$ ofs ). ($ time? "00:00:00": '');} return $ date;}?>