imgrooot

Wow that's a wonderful example. I just have one question. Why use (10,2) instead of say (12,2)? Wouldn't it be safer to use a higher threshold if you're expecting bigger to store bigger numbers?

Say I have two functions that convert from Dollar to Cents and Cents to Dollar. function convertToDollar($value) { $amount = $value / 100; return $amount; } function convertToCents($value) { $amount = $value * 100; return $amount; } Currently I convert all the Dollar amounts to cents and store the cents in the mysql database column. When I showcase those amounts on a page, I simply convert the Cents back to Dollar. Am I doing this correctly or is there a better way to store product pricing in mysql table?

Thanks for backing me up. The 100k emails per day is an extreme example I gave. I just want to make sure that a platform I build is able to accommodate that kind of numbers if ever need be and that I'm using the right service to accomplish it. This is merely sending email notifications to registered users, nothing else.

Could you please give me an example of a good service that provides the API? I've used https://www.sender.net to send bulk emails before. But this is using an email list. And it doesn't seem like they have an API.

Say I am using a normal web hosting company like Namecheap. I have a cron job set to send emails to 100,000 users each day, what would be the best way to deliver those emails the fastest? I know there are other platforms that allows you to send mass emails. But those you have to manually input the email list. I want it automated running from the cron job.

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.

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));

Got it. Thanks.

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 }

Oh wow, it works. That was simple. Thank you.

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;

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

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); } }

Can you please show me an example?

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);

Awesome. I will give it shot. Thanks.

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 {} }

Please see my post above.

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.

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?

You're right. My items table was set to MyISAM but brands table wasn't. I changed it to MyISAM as well and now it WORKS! Thank you so much for helping me out.

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

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>'; }

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.

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']));