在Mysql Excel上传期间使用PHP如何验证手机号码栏？ [关闭]

Question

During excel upload using PHP i want to validate each mobile number inside column of excel file tha i have to upload and sometimes my code upload blank rows in mysql? also how to make my code to upload csv file ?

<?php

ini_set("display_errors", 1);
require_once "include/connect.php";
require_once 'Excel/reader.php';
$school_id = $_SESSION['SCHOOL_ID'];
$class      = explode("$", $_REQUEST['student_class']);
$class_id   = $class[0];
$class_name = $class[1];

if (isset($_POST['submit'])) {
    $allowedExts = array(
    "xls",
    "xlsx"
);
$temp        = explode(".", $_FILES["file"]["name"]);
$extension   = end($temp);
if (($_FILES["file"]["type"] == "application/vnd.ms-excel") && ($_FILES["file"]["size"] < 524288) && in_array($extension, $allowedExts)) {
    if ($_FILES["file"]["error"] > 0) {
        echo "Error: " . $_FILES["file"]["error"] . "<br>";
    } else {
        echo "Upload: " . $_FILES["file"]["name"] . "<br>";
        echo "Type: " . $_FILES["file"]["type"] . "<br>";
        echo "Size: " . ($_FILES["file"]["size"] / 1024) . " kB<br>";
        echo "Stored in: " . $_FILES["file"]["tmp_name"];


        $data = new Spreadsheet_Excel_Reader();
        $data->setOutputEncoding('CP1251');
        $data->read($_FILES["file"]["tmp_name"]);

        $html = "<table border='1'>";

        if (count($data->sheets[0][cells]) > 0) // checking sheet not empty
            {
            echo "<br /><br />Total no. of Students added:" . count($data->sheets[0][cells]) . "<br />";
            for ($j = 2; $j <= count($data->sheets[0][cells]); $j++) // loop used to get each row of the sheet
                {
                $html .= "<tr>";
                for ($k = 1; $k <= count($data->sheets[0][cells][$j]); $k++) // This loop is created to get data in a table format.
                    {
                    $html .= "<td>";
                    $html .= $data->sheets[0][cells][$j][$k];
                    $html .= "</td>";

                }

                $roll_no      = $data->sheets[0][cells][$j][1];
                $admission_no = $data->sheets[0][cells][$j][2];
                $st_name      = $data->sheets[0][cells][$j][3];
                $father_name  = $data->sheets[0][cells][$j][4];
                $mother_name  = $data->sheets[0][cells][$j][5];
                $st_address   = $data->sheets[0][cells][$j][6];
                $st_mobile    = $data->sheets[0][cells][$j][7];
                $st_dob       = $data->sheets[0][cells][$j][8];

                $query = "insert into student_info(roll_no,admission_no,st_name,father_name,mother_name,st_address,st_mobile,st_dob,school_id,st_class_name,st_class) values('" . $roll_no . "','" . $admission_no . "','" . $st_name . "','" . $father_name . "','" . $mother_name . "','" . $st_address . "','" . $st_mobile . "','" . $st_dob . "','" . $school_id . "','" . $class_name . "','" . $class_id . "')";

                mysql_query($query);
                $html .= "</tr>";

            }

        }

        $html .= "</table>";
        echo $html;
        echo "<br>";
        echo '<p style="color: green; text-align: left">Data sucessfully inserted in your database</p>';
    }
} else {
    echo '<p style="color: red; text-align: left">Invalid File- Check file size(size<500kb) / Check extension</p>';
}
}

what i want from my code-

• I don't want to upload wrong mobile number(st_mobile)
• Mobile number column can also be left blank.
• My code can also upload csv file
• sometimes my code upload blank rows in Mysql database,how to deal with that?

Answer 1

To prevent blank imports, you should check to see if each variable is empty or not before performing the SQL query. Obviously you can remove optional variables from the if statement as required.

if($roll_no and $admission_no and $st_name and $father_name and $mother_name and $st_address and $st_mobile and $st_dob)
{
    mysql_query($query);
}

As for validating mobile numbers, you could use preg_match:

$st_mobile = preg_match("/^(+440?|0)7[0-9]{9}$/", trim($st_mobile)) ? $st_mobile : "";