imgrooot
-
Posts
383 -
Joined
-
Last visited
-
Days Won
1
Posts posted by imgrooot
-
-
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?
-
1 hour ago, requinix said:
If imgroot had just joined then I would be leery too, but I think after ~280 posts they've earned some trust. Perhaps you could try aiming all of your posts in a more helpful direction and away from the critical and insulting?
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.
-
7 hours ago, requinix said:
The best way is to not do it yourself: sending 100k emails per day from a generic hosting company is a reliable way to get your server blacklisted and emails blocked. There are plenty of services that provide APIs.
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.
-
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.
-
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));
-
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.
-
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 } -
Then it's already been decoded into $newInfo. Try doing ->status on that.
Oh wow, it works. That was simple. Thank you.
-
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;
-
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); } } -
One query, get all users and store in an indexed array. Not separate numbered variables.
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); -
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.
-
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 {} } -
That does not look like a good table structure. What are these users and positions?
Please see my post above.
-
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.
-
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?
-
One of the two columns doesn't have a FULLTEXT index. Go check that with phpmyadmin (or whatever tool you're using).
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.
-
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
-
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>'; } -
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.
-
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']));
Is converting product price to cents the best way to store value in the database?
in PHP Coding Help
Posted
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?