Jump to content

imgrooot

Members
  • Posts

    383
  • Joined

  • Last visited

  • Days Won

    1

Posts posted by imgrooot

  1. 15 hours ago, Barand said:

    Something like this, perhaps

    TEST DATA

    
    +----+-----------+--------------+------------+
    | id | name      | date_started | days_count |
    +----+-----------+--------------+------------+
    |  1 | Contest 1 | 2019-04-16   |          1 |
    |  2 | Contest 2 | 2019-04-15   |          3 |
    |  3 | Contest 3 | 2019-04-14   |          5 |
    |  4 | Contest 4 | 2019-04-14   |          2 |
    |  5 | Contest 5 | 2019-04-15   |          1 |
    +----+-----------+--------------+------------+

    QUERY

    
    SELECT name
         , date_started as startdate
         , date_started + INTERVAL days_count-1 DAY as enddate
         , datediff(curdate(), date_started) + 1 as daynum
    FROM contests
    WHERE curdate() BETWEEN date_started AND date_started + INTERVAL days_count-1 DAY
    ORDER BY date_started;

    RESULTS

    
    +-----------+------------+------------+--------+
    | name      | startdate  | enddate    | daynum |
    +-----------+------------+------------+--------+
    | Contest 3 | 2019-04-14 | 2019-04-18 |      3 |
    | Contest 2 | 2019-04-15 | 2019-04-17 |      2 |
    | Contest 1 | 2019-04-16 | 2019-04-16 |      1 |
    +-----------+------------+------------+--------+

     

     

    Yes this is what I was looking for. I'll give it a shot.

    Thanks you.

  2. 5 hours ago, ginerjm said:

    Since nothing seems to be happening with this topic, let me ask this:  Can you post the php code that is setting up your email headers for us to review?  I basically want to see what the from address is set to since you never actually confirmed that for us.

    Suere thing. Here is my code setup for sending emails. 

    $from_name    = 'MyWebsite';
    $from_email   = 'info@mywebsite.com';
    
    $user_email   = 'user@email.com';
    $post_subject = 'Hello World';
    $post_message = 'It was a dark and stormy night...';
    
    // Multiple recipients
    $send_to = $user_email; 
    
    // Subject
    $subject = $post_subject;
    
    // Message
    $message = "
    <html>
    <body>
      <p>{$post_message}</p>
    </body>
    </html>
    ";
    
    // To send HTML mail, the Content-type header must be set
    $headers[] = 'MIME-Version: 1.0';
    $headers[] = 'Content-type: text/html; charset=iso-8859-1';
    
    // Additional headers
    $headers[] = 'From: '.$from_name.' <'.$from_email.'>';
    
    // Mail it
    mail($send_to, $subject, $message, implode("\r\n", $headers));

     

  3. Say I am running multiple contests at the same time. Each contest may have a different end date. One contest might run for a day, while the other one might run for 5 days. Below is the code done my way. I was wondering how you can improve it to make it more efficient?

    $current_date = date('Y-m-d H:i:s');
    
    $find_contest = $db->prepare("SELECT days_count, date_started FROM contests WHERE status = :status");
    $find_contest->bindValue(':status', 1);
    $find_contest->execute();
    $result_contest = $find_contest->fetchAll(PDO::FETCH_ASSOC);
    if(count($result_contest) > 0) {
      foreach($result_contest as $row) {
        $days_count      =	$row['days_count']; // NUMBER OF DAYS THE CONTEST IS RUNNING
        $date_started    =	$row['date_started']; // START DATE FOR THE CONTEST
    
        $date_1 = DateTime::createFromFormat('Y-m-d H:i:s',$date_started);
        $date_1->modify('+1 day');
        $end_date_1 = $date_1->format('Y-m-d H:i:s');
    
        $date_2 = DateTime::createFromFormat('Y-m-d H:i:s',$date_started);
        $date_2->modify('+2 days');
        $end_date_2 = $date_2->format('Y-m-d H:i:s');
    
        $date_3 = DateTime::createFromFormat('Y-m-d H:i:s',$date_started);
        $date_3->modify('+3 days');
        $end_date_3 = $date_3->format('Y-m-d H:i:s');
    
        if($days_count == 1) {
    
          if($current_date > $end_date_1 AND $current_date < $end_date_2) {
    
            $day            = 1;
            $end_date       = $end_date_1;
            require 'run-contest.php';
    
          }
    
        } else if($days_count == 2) {
    
          if($current_date > $end_date_1 AND $current_date < $end_date_2) {
    
            $day            = 1;
            $end_date       = $end_date_1;
            require 'run-contest.php';
    
          } else if($current_date > $end_date_2 AND $current_date < $end_date_3) {
    
            $day            = 2;
            $end_date       = $end_date_2;
            require 'run-contest.php';
    
          } else {}
    
        } else {}
    
      }
    }

     

  4. 2 hours ago, requinix said:

    This is what converts.

    
    function convertToBTCFromSatoshi($value) {
        $BTC = $value / 100000000 ;
        return $BTC;
    }

    Do I need to say anything more?

     

    2 hours ago, ginerjm said:

    I may regret asking this but - are you sure about that?  Is going from a to b involves division, wouldn't the reverse involve multiplication?

     

    Correct, the multiplication would do the reverse. Like this. 

    function convertToSatoshi($value) {
        $SAT = $value * 100000000 ;
        return $SAT;
    }

     

    I was more worried about reversing the decimal part of the 2nd function, but I just realized that it's not needed to convert to Satoshie.

  5. Apparently it's bad to use floats when working with bitcoin. But the API i'm using doesn't really give an option to convert Bitcoin to Satoshie or vice versa. https://www.block.io/api/simple/php

    So I'm trying to figure out a way to do that function with PHP.

    Here is the code I found that converts from Satoshie to Bitcoin.  My question is, how do I convert from Bitcoin to Satoshie.

    function convertToBTCFromSatoshi($value) {
        $BTC = $value / 100000000 ;
        return $BTC;
    }
    function formatBTC($value) {
        $value = sprintf('%.8f', $value);
        $value = rtrim($value, '0') . ' BTC';
        return $value;
    }
    echo formatBTC(convertToBTCFromSatoshi(5000));

     

  6. No, there is no way to get the client's timezone with PHP. As you probably know, PHP runs on the server, and the HTTP requests sent by the clients don't contain any timezone information.

     

    What you can do is try to get the timezone with JavaScript or make the user explicitly select their timezone. You can also use the IP address and a geolocation database to make an educated guess (but you still need a way for the user to correct the information).

     

    Got it. Thanks.

  7. I was wondering if there is a way to automatically get a user's timezone? I want to show certain information if the user's current date is less than the expiry date. It works fine if the user is in a default timezone but what if the user is in a different timezone? How I can I make sure I can get their correct current time?

    date_default_timezone_set('America/New_York');
    
    $expiry_date     = trim($row['expiry_date']);
    $current_date    = date('Y-m-d H:i:s');
    
    if($current_date < $expiry_date) {
      // show data
    } else {
      // don't show
    }
    
    
  8. That output is JSON. Are you doing a json_decode somewhere? That's the first step. It will return to you an array or object. Once you have that you don't need individual variables because you can do either

    $variable["status"]
    $variable->status
    depending whether you had it decode as an array or object.

     

     

    My mistake. I am using this api and used their output because it was cleaner looking.https://block.io/api/simple/php

     

    But same as above, if I print_r the variable like this.

    $newInfo = $block_io->get_current_price(array());
    print_r($newInfo);
    

    It will give me the output like this.

    object(stdClass)#5 (2) { ["status"]=> string(7) "success" ["data"]=> object(stdClass)#6 (2) { ["network"]=> string(3) "BTC" ["prices"]=> array(11) { [0]=> object(stdClass)#7 (4) { ["price"]=> string(6) "1590.0" ["price_base"]=> string(3) "AUD" ["exchange"]=> string( "coinspot" ["time"]=> int(1488981211) } [1]=> object(stdClass)#8 (4) { ["price"]=> string( "1201.203" ["price_base"]=> string(3) "USD" ["exchange"]=> string(5) "btc-e" ["time"]=> int(1488981211) } [2]=> object(stdClass)#9 (4) { ["price"]=> string(10) "1149.95741" ["price_base"]=> string(3) "EUR" ["exchange"]=> string(5) "btc-e" ["time"]=> int(1488981211) } [3]=> object(stdClass)#10 (4) { ["price"]=> string(7) "68920.0" ["price_base"]=> string(3) "RUR" ["exchange"]=> string(5) "btc-e" ["time"]=> int(1488981211) } [4]=> object(stdClass)#11 (4) { ["price"]=> string(7) "1203.14" ["price_base"]=> string(3) "USD" ["exchange"]=> string( "coinbase" ["time"]=> int(1488981213) } [5]=> object(stdClass)#12 (4) { ["price"]=> string(6) "1205.9" ["price_base"]=> string(3) "USD" ["exchange"]=> string( "bitfinex" ["time"]=> int(1488981214) } [6]=> object(stdClass)#13 (4) { ["price"]=> string(6) "1202.0" ["price_base"]=> string(3) "EUR" ["exchange"]=> string(7) "litebit" ["time"]=> int(1488981222) } [7]=> object(stdClass)#14 (4) { ["price"]=> string(7) "1206.61" ["price_base"]=> string(3) "USD" ["exchange"]=> string( "bitstamp" ["time"]=> int(1488981224) } [8]=> object(stdClass)#15 (4) { ["price"]=> string(6) "1154.2" ["price_base"]=> string(3) "EUR" ["exchange"]=> string( "bitstamp" ["time"]=> int(1488981224) } [9]=> object(stdClass)#16 (4) { ["price"]=> string(6) "8037.0" ["price_base"]=> string(3) "CNY" ["exchange"]=> string(4) "bter" ["time"]=> int(1488981225) } [10]=> object(stdClass)#17 (4) { ["price"]=> string(6) "7830.0" ["price_base"]=> string(3) "CNY" ["exchange"]=> string( "btcchina" ["time"]=> int(1488981208) } } } } 
    

    Using your json_decode like this doesn't work. It doesn't return me anything. No error either.

     $variable = json_decode($newInfo);
     echo $variable->status;
    
  9. Say I have this array.

    $block_io->get_current_price(array());
    

    That array will give me this output.

    {
    "status" : "success",
    "data" : {
    "network" : "BTC",
    "prices" : [
    {
    "price" : "1500.01",
    "price_base" : "AUD",
    "exchange" : "coinspot",
    "time" : 1488955012
    },
    {
    

    How can I convert the above output into individual variables?

    For eg.

    $status = 'success';
    $network = 'BTC';
    $price = '1500.01';
    ...etc
    
  10. I see your example. Here's the updated query. My question is, how do I convert the $row array into actual singular variables? Also where did "$products" come from?

    $stmt = $db->prepare("SELECT user_id, filled_positions FROM matrix_2");
    if ($stmt->execute()) {
      while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $products[$row['user_id']][] = $row;
        $products[$row['filled_positions']][] = $row;
    
        print_r($row);
      }
    }
    
  11. Say I have a table with 100 users. I want to retrieve those 100 users with each of them having unique variable name. What's the proper way to do that? I know I can create 100 different queries and retrieve them like this. But what's a better solution?

    $find_sponsor_2 = $db->prepare("SELECT user_id, filled_positions FROM matrix_2 WHERE user_id = :user_id");
    $find_sponsor_2->bindValue(':user_id', 2);
    $find_sponsor_2->execute();
    $result_sponsor_2 = $find_sponsor_2->fetchAll(PDO::FETCH_ASSOC);
    if(count($result_sponsor_2) > 0) {
      foreach($result_sponsor_2 as $row) {
        $matrix_user_id_2         = $row['user_id'];
        $filled_positions_2         = $row['filled_positions'];
      }
    } else {
      $errors[] = 'User Id 2 not found in Matrix.';
    }
    
    $find_sponsor_3 = $db->prepare("SELECT user_id, filled_positions FROM matrix_2 WHERE user_id = :user_id");
    $find_sponsor_3->bindValue(':user_id', 3);
    $find_sponsor_3->execute();
    $result_sponsor_3 = $find_sponsor_3->fetchAll(PDO::FETCH_ASSOC);
    if(count($result_sponsor_3) > 0) {
      foreach($result_sponsor_3 as $row) {
        $matrix_user_id_3         = $row['user_id'];
        $filled_positions_3         = $row['filled_positions'];
      }
    } else {
      $errors[] = 'User Id 3 not found in Matrix.';
    }
    
    $find_sponsor_4 = $db->prepare("SELECT user_id, filled_positions FROM matrix_2 WHERE user_id = :user_id");
    $find_sponsor_4->bindValue(':user_id', 4);
    $find_sponsor_4->execute();
    $result_sponsor_4 = $find_sponsor_4->fetchAll(PDO::FETCH_ASSOC);
    if(count($result_sponsor_4) > 0) {
      foreach($result_sponsor_4 as $row) {
        $matrix_user_id_4         = $row['user_id'];
        $filled_positions_4         = $row['filled_positions'];
      }
    } else {
      $errors[] = 'User Id 4 not found in Matrix.';
    

    Someone mentioned I could do it like this. But my question is, how do I get unique variables for each user, such as "$matrix_user_id_2, $matrix_user_id_3, $matrix_user_id_4" as shown in my code above.

    $find_sponsor = $db->prepare("SELECT user_id, filled_positions FROM matrix_2 WHERE user_id in (2,3,4)");
    $find_sponsor>execute();
    $result_sponsor = $find_sponsor->fetchAll(PDO::FETCH_ASSOC);
    print_r($result_sponsor);
    
  12.  

    My 0.02 worth

     

    Tables

    CREATE TABLE `users` (
      `user_id` int(4) unsigned zerofill NOT NULL AUTO_INCREMENT,
      `username` varchar(45) DEFAULT NULL,
      `password` varchar(150) DEFAULT NULL,
      `email` varchar(150) DEFAULT NULL,
      PRIMARY KEY (`user_id`)
    );
    
    CREATE TABLE `matrix` (
      `matrix_id` int(11) NOT NULL AUTO_INCREMENT,
      `user_id` int(11) DEFAULT NULL,
      `sponsor_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`matrix_id`),
      KEY `idx_matrix_user_id` (`user_id`),
      KEY `idx_matrix_sponsor_id` (`sponsor_id`)
    );
    
    -- seed matrix with at least 1 record
    
    INSERT INTO matrix(user_id, sponsor_id) VALUES (null,1);
    

    This sample form adds a new user then inserts the user into the matrix table. If a sponsor is specified then that sponsors id is inserted, otherwise it is added to the first available sponsor (ie with less than 14 users)

     

    Just curious - why 14?

    <?php
    
    // Your PDO connection code goes here
    
    if ($_SERVER['REQUEST_METHOD']=='POST') {
        // NOTE : validation omitted for brevity
        
        $sql = "INSERT INTO users (username,password,email)
                VALUES (:user,:pass,:email)";
        $stmt = $db->prepare($sql);
        $stmt->execute( [
                'user' => $_POST['name'],
                'pass' => password_hash($_POST['pwd'], PASSWORD_DEFAULT),
                'email' => $_POST['email']
            ]);
            
        // get the id of the newly added user
        $userid = $db->lastInsertId();
        
        // add new user into the matrix
        $sql = "INSERT INTO matrix (user_id, sponsor_id)
                SELECT @user as user
                    , CASE 
                        WHEN @sponsor=0 THEN m.sponsor_id
                        ELSE @sponsor
                      END as sponsor
                FROM (
                        SELECT sponsor_id
                          , COUNT(user_id) as tot
                        FROM matrix
                        WHERE sponsor_id IS NOT NULL
                        GROUP BY sponsor_id
                        HAVING tot < 14
                        ORDER BY sponsor_id
                        LIMIT 1
                    ) m
                    JOIN (SELECT @user := :user, @sponsor := :sponsor) init";
        $stmt = $db->prepare($sql);
        $stmt->execute([
                    ':user' => $userid,
                    ':sponsor' => $_POST['sponsor']
                ]);
    }
    
    function userOptions($db, $current=0)
    {
        $sql = "SELECT 
                    user_id
                  , username
                FROM users
                ORDER BY username";
        $res = $db->query($sql);
        $opts = '<option value="0">--No sponsor specified--</option>';
        foreach ($res as $r) {
            $opts .= "<option value='$r[user_id]'>$r[username]</option>\n";
        }
        return $opts;
    }
    
    function currentUsers($db)
    {
        $sql = "SELECT u1.user_id
                  , u1.username
                  , GROUP_CONCAT(u2.user_id ORDER BY u2.user_id SEPARATOR ' | ') as users
                  FROM matrix m
                    INNER JOIN users u1 ON m.sponsor_id = u1.user_id
                    INNER JOIN users u2 ON m.user_id = u2.user_id
                  GROUP BY u1.user_id
                  ORDER BY u1.user_id";
        $res = $db->query($sql);
        $usrs = '';
        foreach ($res as $r) {
            $usrs .= "<tr><td>$r[user_id]</td><td>$r[username]</td><td>$r[users]</td></tr>\n";
        }
        return $usrs;
    }
    ?>
    <!DOCTYPE html>
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <title>codeword_entry</title>
    <meta name="author" content="Barry Andrew">
    <style type="text/css">
    body {
        font-family: sans-serif;
        font-size: 10pt;
    }
    fieldset {
        padding: 15pt;
        background-color: #ccc;
    }
    legend {
        background-color: black;
        color: white;
        padding: 3px;
    }
    table {
        font-family: sans-serif;
        font-size: 10pt;
        border-spacing: 1px;
        min-width: 700px;
    } 
    th {
        background-color: black;
        color: white;
        padding: 5px 3px;
    }
    td {
        padding: 3px;
    }
    </style>
    </head>
    <body>
    <h1>Add New User</h1>
    <form action="" method='POST'>
        <fieldset>
        <legend>User details</legend>
            User name <input type="text" name="name" size="40"><br>
            Password  <input type="password" name="pwd" size="40"><br>
            Email <input type="text" name="email" size="50">
        </fieldset>
        <fieldset>
        <legend>Sponsor</legend>
            Sponsor <select name="sponsor"><?=userOptions($db)?></select>
        </fieldset>
        <input type="submit" name="btnSub" value="Submit">
    </form>
    <hr>
    <h2>Current Users</h2>
    <table>
        <tr><th>ID</th><th>User name</th><th>Sponsored Users</th></tr>
        <?=currentUsers($db)?>
    </table>
    </body>
    </html>
    

     

    Awesome. I will give it shot.  Thanks.

  13. It seems like I haven't explained my situation properly.

     

    1. To put it simply, I want to loop through "matrix" table.

    2. Find all the rows that have user id.

    3. Get all the columns from the row with the lowest user id.

    4. Do if else statement to see if each of the 14 positions in said row are empty.  If they are not, insert the new users until they are filled.

    5. Move on to the next row with the next highest user id. Do the same thing as above. Rinse and repeat.

     

    Here's a query example I have so far.

    $find_sponsor = $db->prepare("SELECT * FROM matrix WHERE user_id > :user_id");
    $find_sponsor->bindValue(':user_id',0);
    $find_sponsor->execute();
    $result_sponsor = $find_sponsor->fetchAll(PDO::FETCH_ASSOC);
    if(count($result_sponsor) > 0) {
      foreach($result_sponsor as $row) {
        $sponsor_id             = $row['sponsor_id'];
        $user_id                = $row['user_id'];
        $filled_positions          = $row['filled_positions'];
        $position_1                 = $row['position_1'];
        $position_2                 = $row['position_2'];
        $position_3                 = $row['position_3'];
        $position_4                 = $row['position_4'];
        $position_5                 = $row['position_5'];
        $position_6                 = $row['position_6'];
        $position_7                 = $row['position_7'];
        $position_8                 = $row['position_8'];
        $position_9                 = $row['position_9'];
        $position_10                = $row['position_10'];
        $position_11                = $row['position_11'];
        $position_12                = $row['position_12'];
        $position_13                = $row['position_13'];
        $position_14                = $row['position_14'];
      }
    
      if(empty($position_1)) {
     
        $update_sponsor = $db->prepare("UPDATE matrix SET position_1 = :position_1, filled_positions = :filled_positions + 1 WHERE user_id = :user_id");
        $update_sponsor->bindParam(':position_1', $new_user_id);
        $update_sponsor->bindParam(':filled_positions', $filled_positions);
        $update_sponsor->bindParam(':user_id', $matrix_user_id);
        if($update_sponsor->execute()) {}
     
      } else if(empty($position_2)) {
     
        $update_sponsor = $db->prepare("UPDATE matrix SET position_2 = :position_2, filled_positions = :filled_positions + 1 WHERE user_id = :user_id");
        $update_sponsor->bindParam(':position_2', $new_user_id);
        $update_sponsor->bindParam(':filled_positions', $filled_positions);
        $update_sponsor->bindParam(':user_id', $matrix_user_id);
        if($update_sponsor->execute()) {}
     
      } else if(empty($position_3)) {
     
        $update_sponsor = $db->prepare("UPDATE matrix SET position_3 = :position_3, filled_positions = :filled_positions + 1 WHERE user_id = :user_id");
        $update_sponsor->bindParam(':position_3', $new_user_id);
        $update_sponsor->bindParam(':filled_positions', $filled_positions);
        $update_sponsor->bindParam(':user_id', $matrix_user_id);
        if($update_sponsor->execute()) {}
     
      } else if(empty($position_4)) {
     
        $update_sponsor = $db->prepare("UPDATE matrix SET position_4 = :position_4, filled_positions = :filled_positions + 1 WHERE user_id = :user_id");
        $update_sponsor->bindParam(':position_4', $new_user_id);
        $update_sponsor->bindParam(':filled_positions', $filled_positions);
        $update_sponsor->bindParam(':user_id', $matrix_user_id);
        if($update_sponsor->execute()) {}
     
      } else if(empty($position_5)) {
     
        $update_sponsor = $db->prepare("UPDATE matrix SET position_5 = :position_5, filled_positions = :filled_positions + 1 WHERE user_id = :user_id");
        $update_sponsor->bindParam(':position_5', $new_user_id);
        $update_sponsor->bindParam(':filled_positions', $filled_positions);
        $update_sponsor->bindParam(':user_id', $matrix_user_id);
        if($update_sponsor->execute()) {}
     
      } else if(empty($position_6)) {
     
        $update_sponsor = $db->prepare("UPDATE matrix SET position_6 = :position_6, filled_positions = :filled_positions + 1 WHERE user_id = :user_id");
        $update_sponsor->bindParam(':position_6', $new_user_id);
        $update_sponsor->bindParam(':filled_positions', $filled_positions);
        $update_sponsor->bindParam(':user_id', $matrix_user_id);
        if($update_sponsor->execute()) {}
     
      } else if(empty($position_7)) {
     
        $update_sponsor = $db->prepare("UPDATE matrix SET position_7 = :position_7, filled_positions = :filled_positions + 1 WHERE user_id = :user_id");
        $update_sponsor->bindParam(':position_7', $new_user_id);
        $update_sponsor->bindParam(':filled_positions', $filled_positions);
        $update_sponsor->bindParam(':user_id', $matrix_user_id);
        if($update_sponsor->execute()) {}
     
      } else if(empty($position_) {
     
        $update_sponsor = $db->prepare("UPDATE matrix SET position_8 = :position_8, filled_positions = :filled_positions + 1 WHERE user_id = :user_id");
        $update_sponsor->bindParam(':position_8', $new_user_id);
        $update_sponsor->bindParam(':filled_positions', $filled_positions);
        $update_sponsor->bindParam(':user_id', $matrix_user_id);
        if($update_sponsor->execute()) {}
     
      } else if(empty($position_9)) {
     
        $update_sponsor = $db->prepare("UPDATE matrix SET position_9 = :position_9, filled_positions = :filled_positions + 1 WHERE user_id = :user_id");
        $update_sponsor->bindParam(':position_9', $new_user_id);
        $update_sponsor->bindParam(':filled_positions', $filled_positions);
        $update_sponsor->bindParam(':user_id', $matrix_user_id);
        if($update_sponsor->execute()) {}
     
      } else if(empty($position_10)) {
     
        $update_sponsor = $db->prepare("UPDATE matrix SET position_10 = :position_10, filled_positions = :filled_positions + 1 WHERE user_id = :user_id");
        $update_sponsor->bindParam(':position_10', $new_user_id);
        $update_sponsor->bindParam(':filled_positions', $filled_positions);
        $update_sponsor->bindParam(':user_id', $matrix_user_id);
        if($update_sponsor->execute()) {}
     
      } else if(empty($position_11)) {
     
        $update_sponsor = $db->prepare("UPDATE matrix SET position_11 = :position_11, filled_positions = :filled_positions + 1 WHERE user_id = :user_id");
        $update_sponsor->bindParam(':position_11', $new_user_id);
        $update_sponsor->bindParam(':filled_positions', $filled_positions);
        $update_sponsor->bindParam(':user_id', $matrix_user_id);
        if($update_sponsor->execute()) {}
     
      } else if(empty($position_12)) {
     
        $update_sponsor = $db->prepare("UPDATE matrix SET position_12 = :position_12, filled_positions = :filled_positions + 1 WHERE user_id = :user_id");
        $update_sponsor->bindParam(':position_12', $new_user_id);
        $update_sponsor->bindParam(':filled_positions', $filled_positions);
        $update_sponsor->bindParam(':user_id', $matrix_user_id);
        if($update_sponsor->execute()) {}
     
      } else if(empty($position_13)) {
     
        $update_sponsor = $db->prepare("UPDATE matrix SET position_13 = :position_13, filled_positions = :filled_positions + 1 WHERE user_id = :user_id");
        $update_sponsor->bindParam(':position_13', $new_user_id);
        $update_sponsor->bindParam(':filled_positions', $filled_positions);
        $update_sponsor->bindParam(':user_id', $matrix_user_id);
        if($update_sponsor->execute()) {}
     
      } else if(empty($position_14)) {
     
        $update_sponsor = $db->prepare("UPDATE matrix SET position_14 = :position_14, filled_positions = :filled_positions + 1 WHERE user_id = :user_id");
        $update_sponsor->bindParam(':position_14', $new_user_id);
        $update_sponsor->bindParam(':filled_positions', $filled_positions);
        $update_sponsor->bindParam(':user_id', $matrix_user_id);
        if($update_sponsor->execute()) {}
     
      } else {}
    }
    
  14. Following the rule described in the last paragraph, how did user4 and user5 get inserted into two different row-positions?

     

    I am creating a 2x3 forced matrix mlm. So in this case, user1 directly sponsored user4 and user5, but since user2 is ALSO under user1, they would go under that user as well.

     

    Let me paint the full picture. I was trying to simplify it for the question but I'll share the full details.

     

    I have two tables. Table1: users. Table2: matrix

     

    Users:

    user_id   |   username   |   password   |   email

    1                    user1                *****           user1@admin.com

    2                    user2                *****           user2@admin.com

    3                    user3                *****           user3@admin.com

    4                    user4                *****           user4@admin.com

    5                    user5                *****           user5@admin.com

    6                    user6                *****           user6@admin.com

    7                    user7                *****           user7@admin.com

     

    matrix:

    user_id, sponsor_id, username, filled_positions, position_1, position_2, position_3, position_4, position_5, position_6, position_7, position_8, position_9, position_10, position_11, position_12, position_13, position_14

         1            0               user1              6                  user2         user3          user4         user5         user6          user7

         2            1               user2              2                  user4         user5

         3            1               user3              2                  user6         user7

         4            2               user4

         5            2               user5

         6            3               user6

         7            3               user7

     

    I already have these 7 users added manually in the database. It starts with a user8. *note that I am only using usernames under each position for visual purposes. In actuality, I'm using user_id of said usernames.

     

    A user signs up through the website. If the user is already being sponsored by another user, then I can make it work. But If the user doesn't have a sponsor, then they have no sponsor id and that's where I have an issue because I can't insert the user in the matrix table without knowing the correct sponsor_id. The correct sponsor_id is from the next empty position in the row. 

     

    So in this scenario, user8 would be the next member. User8 will be placed under position_7 of user1. Once this user's all 14 positions are filled, it will go down to the user row below it. So the next user, which would be User16 and will be placed under position_3 of user2. So it'll keep looping through the rows from left to right and find empty positions.

     

    So once the new user is placed in the correct position in matrix table, then I can get the sponsor id of said row. I can then use it to insert a new user in the same matrix table.

     

    Basically I am going to have 3 querys.

    Query 1: Insert the user in users table. This is simply. Don't worry about this.

    Query 2: Update a matrix row with a new user in anyone of the positions from 1-14(sequential order) in the matrix table.

    Query 3: Insert the same user in the matrix table. This is simple as well. I just need the sponsor_id from Query 2.

     

    Here is a 2x3 matrix diagram for visual purposes.

    wqplbs.jpg

  15. Here's what I am trying to do.

     

    Users Table

    user_id, sponsor_id, username, filled_positions, position_1, position_2, position_3, position_4, position_5

         1            0               user 1              4                   user 2       user 3       user 4         user 5

         2            1               user 2              2                   user 4       user 5

         3            1               user 3

         4            2               user 4

         5            2               user 5

     

     

    Above is a "Users" table.  Here's what I am trying to do.  Insert new users into the table. Say I already have the users table set up with 5 users. I want to add User 6.  I want to loop through the users in the table and find the next empty position and update it with the new user id. 

     

    In this scenario diagram above, the next empty position is Row 1 - position_5. The one after that is Row 2 - position_3 and then Row 2 - position_4...etc. It basically loops through rows and checks each position.  So User 6 will be placed under Row 1 - position_5 and User 7 will be placed under Row 2 - position_3. How can one go on about doing that?

  16. You shouldn't replace the error setting. You should have both: Turn off emulation, turn on exceptions. Also consider setting the default fetch mode (e. g. to PDO::FETCH_ASSOC).

     

     

     

     

    Now you need two separate MATCH conditions (as explained above):

    WHERE
        MATCH (items.item_title) AGAINST (:title_query IN BOOLEAN MODE)
        OR MATCH (brands.brand_name) AGAINST (:brand_query IN BOOLEAN MODE)
    

    You need two parameters :title_query and :brand_query, because the same parameter may not appear more than once. But of course you can bind the exact same string $search_query to both parameters.

     

    So based on your answer, here's my new query.

    WHERE MATCH(items.item_title) AGAINST(:title_query IN BOOLEAN MODE) OR MATCH(brands.brand_name) AGAINST(:brand_query_ IN BOOLEAN MODE) ORDER BY items.item_id DESC LIMIT :limit OFFSET :offset");
    $get_records->bindParam(':title_query', $search_query);
    $get_records->bindParam(':brand_query', $search_query);
    

    The above gives me the error below. The line 117 being the WHERE query.

     Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1191 Can't find FULLTEXT index matching the column list' in C:\xampp\htdocs\..... on line 117
    
  17. That's because you're using emulated prepared statements (which is the default). Turn off PDO::ATTR_EMULATE_PREPARES.

     

     

     

     

    Your query doesn't involve multiple tables, so there's nothing to be shown. Post the failing query.

     

     

    In my database connection, I had this.

    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    

    I replaced that with with this.

    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    

    Is that correct?  After doing that, the query does work with offset and limit parameters.

     

     

    "Items" and "type_1" are 2 different tables. Would that not be considered multiple tables?  Below is my full code for the search page.  I have 4 seperate tables(items, type_1, brands, images).  They are all connected.  Currently the search works by matching the user's search query against the "item_title" in the items table.  But what I would like to do is for the search_query to ALSO search the brand_name in the brands table and retrieve results based on on the matching of both brand name item title.

     

    I have tried maxxd's method above and it doesn't work. Like this. 

    WHERE MATCH(items.item_title, brands.brand_name ) AGAINST(:search_query IN BOOLEAN MODE) ORDER BY items.item_id DESC LIMIT :limit OFFSET :offset
    

    Original code.

    $search_query	= trim(preg_replace('/[^\p{L}\p{N}_]+/u', ' ', $_GET['sq']));
    
    $count_records = $db->prepare("SELECT COUNT(*) FROM items WHERE MATCH(item_title) AGAINST(:search_query IN BOOLEAN MODE)");
    $count_records->bindParam(':search_query', $search_query);
    $count_records->execute();
    $total = $count_records->fetchColumn();
    
    // How many items to list per page
    $limit = 8;
    
    // How many pages will there be
    $pages = ceil($total / $limit);
    
    // What page are we currently on?
    $page = min($pages, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT, array (
    	'options' => array(
    		'default'   => 1,
    		'min_range' => 1,
    	),
    )));
    
    // Calculate the offset for the query
    $offset = ($page - 1)  * $limit;
    
    // Some information to display to the user
    $start = $offset + 1;
    $end = min(($offset + $limit), $total);
    
    if($page > 0) {
    	$offset = ($page - 1)  * $limit;
    } else {
    	$offset = 0;
    }
    
    $get_records = $db->prepare("SELECT items.*, type_1.*, brands.*, images.* FROM items
    LEFT JOIN type_1 ON items.type_1 = type_1.type_1_id
    LEFT JOIN brands ON items.brand_id = brands.brand_id
    LEFT JOIN images ON items.item_id = images.item_id
    WHERE MATCH(items.item_title) AGAINST(:search_query IN BOOLEAN MODE) ORDER BY items.item_id DESC LIMIT :limit OFFSET :offset");
    $get_records->bindParam(':search_query', $search_query);
    $get_records->bindParam(':limit', $limit);
    $get_records->bindParam(':offset', $offset);
    $get_records->execute();
    $result_records = $get_records->fetchAll(PDO::FETCH_ASSOC);
    if(count($result_records) > 0){
       foreach($result_records as $row) {
          // loop for items
        }
    
    } else {
    
       echo '<div id="message-flash">No results found.</div>';
    
    }
    
  18. The whole purpose of prepared statements is that you pass your input to predefined parameters and prevent SQL injections altogether:

    // a static query template with three parameters: search_query, limit, offset
    $items_stmt = $db->prepare('SELECT ... AGAINST(:search_query IN BOOLEAN MODE) ORDER BY ... LIMIT :limit OFFSET :offset');
    
    // bind values to the parameters and execute the statement
    $items_stmt->execute([
       'search_query' => $_GET['sq'],
        'limit' => $limit,
        'offset' => $offset,
    ]);
    

    So the query is a constant string. Do not assemble dynamic queries from user input, not even when you think you've “sanitized” it.

     

     

     

     

    Correct. That's why you need to split the condition into one MATCH ... AGAINST per table and connect them again with OR.

     

    Good to know. 

     

    In terms of your example with prepared parameters, it only works if I set the search_query parameter. If I add offset and limit, it will give an error.

     

    Now my question is, how is your prepared statement different from mine below? Are both correct methods?

    $items_stmt = $db->prepare("SELECT items.*, type_1.* FROM items
    LEFT JOIN type_1 ON items.type_1 = type_1.type_1_id
    WHERE MATCH(items.item_title) AGAINST(':search_query' IN BOOLEAN MODE) ORDER BY items.item_id DESC LIMIT {$limit} OFFSET ".$offset);
    $items_stmt->bindParam(':search_query', $search_query);
    $items_stmt->execute();
    $result_items = $items_stmt->fetchAll(PDO::FETCH_ASSOC);
    if(count($result_items) > 0){
    
    }
    

    Also using the above query, can you give me an example of the MATCH AGAINST per table you're talking about? Because I've tried many different methods and so far no luck.

  19. If you've got the tables joined, wouldn't you just name the columns in the match?

    SELECT   items.*
            ,type_1.*
    FROM items
    LEFT JOIN type_1
        ON items.type_1 = type_1.type_1_id
    WHERE MATCH(items.item_title, type_1.type_1_name ) AGAINST('$search_query' IN BOOLEAN MODE) ORDER BY items.item_id DESC LIMIT {$limit} OFFSET {$offset}
    

    I'm assuming you're validating and sanitizing the variables you're injecting into the query, yes?

     

    I tried that but it gives me an error like this.  I ready somewhere that Fulltext search might not allow multiple tables to be searched.

    Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1210 Incorrect arguments to MATCH' in...
    

    In terms of validating and sanitizing the variable, I have it like this.  I don't know if that's sufficient enough.

    $search_query	= trim(preg_replace('/[^\p{L}\p{N}_]+/u', ' ', $_GET['sq']));
    
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.