1
\$\begingroup\$

This is what the code does:

  1. Check if the user's website exists and is active.
  2. After checking the user IP already exists and if the weather 24 hours have passed, if so it deletes the IP database.
  3. Check if there is an ads in the database
  4. Insert a view in the database, insert the ip database
<?php 
    require_once("../mysql/configurations.php");
    $type     = addslashes($_GET["type"]);
    $userid   = addslashes($_GET["user_id"]);
    $siteid   = addslashes($_GET["site_id"]);
    $status   = "Active";
    $user_ip  = $_SERVER['REMOTE_ADDR'];
    $isp      = gethostbyaddr($user_ip);
    $time     = date('Y-m-d H:i:s');
    $time_now = strtotime(date('Y-m-d H:i:s'));
    $dataI    = date('Y-m-d');
    openConnection(); 
    $stmt0 = $mysqli->prepare("SELECT * FROM web WHERE id=? AND userid =? AND status=?"); 
    $stmt0->bind_param('iis', $siteid, $userid, $status);
    $stmt0->execute();
    $stmt0->store_result();
    if($stmt0->num_rows == true) {
        $stmt1 = $mysqli->prepare("SELECT * FROM ips WHERE ip=? AND expired > ?"); // >
        $stmt1->bind_param('ss', $user_ip, $time_now);
        $stmt1->execute();
        $res = $stmt1->get_result();
        if($res->num_rows >= 1) {
            $res->data_seek($row_no);
            $get = $res->fetch_assoc();
            $stmt2  = $mysqli->prepare("DELETE FROM ips WHERE id=?");
            $stmt2->bind_param("i", $get["id"]);
            $stmt2->execute();
            $stmt2->close();
            $res->close();
            session_start();
            $_SESSION["ttt"] = $type;
            echo include("show.php");
        } else {
            $stmt3    = $mysqli->prepare("SELECT * FROM ips WHERE ip=?");
            $stmt3->bind_param("s", $user_ip);
            $stmt3->execute();
            $res = $stmt3->get_result();
            if($res->num_rows == 1) {
                session_start();
                $_SESSION["ttt"] = $type;
                echo include("show.php");
                session_destroy();   
            } else {
                $browser  = getBrowser();
                $tipo     = "Click";
                $status   = "Active";
                $value    = 0.30;
                $stmt4 = $mysqli->prepare("SELECT * FROM a WHERE tipo=? AND saldo > ? AND status=? AND formato=? ORDER by RAND()");
                $stmt4->bind_param("sdss", $tipo, $value, $status, $type);
                $stmt4->execute();
                $res = $stmt4->get_result();
                if($res->num_rows == 1) {
                    error_reporting(true);
                    $res->data_seek($row_no);
                    $row = $res->fetch_assoc();
                    $vv = $row["ecpm"];
                    $stmt5 = $mysqli->prepare("UPDATE a SET views=views+1, gastos=gastos+?, saldo=saldo-? WHERE id=?");
                    $stmt5->bind_param("ddi", $vv, $vv, $row["id"]);
                    $stmt5->execute();
                    $stmt8 = $mysqli->prepare("INSERT INTO ips (ip, expired, date) VALUES (?, ?, ?)");
                    $stmt8->bind_param("sss", $user_ip, next_data(1), $time);
                    $stmt8->execute(); 
                    $stmt8->close();
                    $stmt5->close();
                    $res->close();
                    $sql4 = $mysqli->prepare("SELECT * FROM views WHERE siteid=? AND userid=? AND date=?");
                    $sql4->bind_param("iis", $siteid, $userid, $dataI);
                    $sql4->execute();
                    $res = $sql4->get_result();
                    if($res->num_rows == 1) {
                        $sql5 = $mysqli->prepare("UPDATE views SET views=views+1, value=value+? WHERE siteid=? AND userid=? AND date=?");
                        $sql5->bind_param("diis", $vv, $siteid, $userid, $dataI);
                        $sql5->execute();
                        $sql5->close();
                        $res->close();
                    } else if($res->num_rows >= 2) {
                        while($res->num_rows > 1) {
                            $res->data_seek($row_no);
                            $row = $res->fetch_assoc();
                            $stmt6 = $mysqli->prepare("DELETE FROM views WHERE id=?");
                            $stmt6->bind_param("i", $row["id"]);
                            $stmt6->execute();
                        }
                    } else {
                        $imp = 1;
                        $stmt7 = $mysqli->prepare("INSERT INTO views (userid, siteid, date, views, value) VALUES (?, ?, ?, ?, ?)");
                        $stmt7->bind_param("iisid", $userid, $siteid, $dataI, $imp, $vv);
                        $stmt7->execute();
                    }

                } else {
                    session_start();
                    $_SESSION["ttt"] = $type;
                    echo include("show.php");
                    session_destroy();   
                }  
            }
        }
    } 
?>

I'm studying PHP and MySQL and I'd like to know how can I improve this script to better and to use on my website with 1000 concurrent users executing this script.

\$\endgroup\$
9
  • \$\begingroup\$ @SirPython done \$\endgroup\$ – Yan Silva Feb 13 '16 at 15:06
  • \$\begingroup\$ I almost forgot. There is one more thing that needs to be changed: the title of your post should reflect the function/purpose of your code. \$\endgroup\$ – SirPython Feb 13 '16 at 15:17
  • \$\begingroup\$ @SirPython I think now it is ok, right? \$\endgroup\$ – Yan Silva Feb 13 '16 at 15:22
  • \$\begingroup\$ It's better and will do, but it's still a little general. \$\endgroup\$ – SirPython Feb 13 '16 at 15:45
  • 1
    \$\begingroup\$ I assume you've created INDEXes on the tables/fields? \$\endgroup\$ – user1149 Feb 13 '16 at 18:52
1
\$\begingroup\$

As a first obvious remark you can increase performance using only one query instead of two when looking for IPs. Currently you're doing:

    //...
    $stmt1 = $mysqli->prepare("SELECT * FROM ips WHERE ip=? AND expired > ?");
    //...
    if($res->num_rows >= 1) {
      //...
      $stmt2  = $mysqli->prepare("DELETE FROM ips WHERE id=?");
      //...
      session_start();
      $_SESSION["ttt"] = $type;
      echo include("show.php");
    } else {
      $stmt3 = $mysqli->prepare("SELECT * FROM ips WHERE ip=?");
      //...
      if($res->num_rows == 1) {
        session_start();
        $_SESSION["ttt"] = $type;
        echo include("show.php");
        //...

while you could merely look for IP, then work depending on expired or not at the same time avoiding to repeat :

    //...
    $stmt1 = $mysqli->prepare("SELECT * FROM ips WHERE ip=?");
    //...
    if($res->num_rows >= 1) {
      session_start();
      $_SESSION["ttt"] = $type;
      echo include("show.php");
      //...
      if ($row['expired'] > $time_now) {
        //...
        $stmt2  = $mysqli->prepare("DELETE FROM ips WHERE id=?");
        //...
        $_SESSION["ttt"] = $type;
        echo include("show.php");
      } else {
        //...

For the rest of the logics, it seems there is no other noticeable point, though I can't be sure since I don't know the detailed data you work with.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.