Jump to content

Leaderboard


Popular Content

Showing content with the highest reputation since 01/25/2020 in Posts

  1. 2 points
    the convention around here is "New question, new thread". That allows for short, direct answer to short, direct questions instead of long, rambling threads where all the "Goodness" gets lost. Some comments on the above: the use of "global" breaks encapsulation, requiring the environment "outside" the function to provide the variable. It is better to pass the data as an argument to the function. What value does admin['gender'] have? Any value passed that resolves to true will cause the ternary operator to return "Mr" and everything else will return "Mrs". The code makes no attempt to ensure that the array indexes used actually exist; this may or may not be an issue. What if the individual is female and not married? They might object to being called "Mrs". What if the individual is not gender-identifying? They would object most strongly to be referred to by either of the terms used here. Marital status and/or gender are both Personal Data and should be stored in the User's "record" (whatever form that takes) so that it can be managed by/on behalf of the User and changed over time. Regards, Phill W.
  2. 1 point
    My query returns two output columns model_name cats Try echo model->model_name . " " . $model->cats . "<br>"; (of course, without seeing the code that actually runs the query and gets the results, that's just a guess.)
  3. 1 point
    Don't do that. Not in the actual table at least. Some people recommend this stupidity to try and avoid name collisions in their queries (such as two tables have a Label column) but such issues can be easily handled using the table.column syntax in your query rather than cluttering up column names in the table. SELECT o.Label as o_label, s.Label as s_label FROM order o INNER JOIN status s ON s.Id=o.Status One of the applications I work on was original designed using a scheme like that where every column has a table specific prefix to it and it's super annoying (long names, broken autocomplete) for no real benefit. I've been slowly undoing that when I can and just giving the columns nice simple names. I'd also suggest just using the full table name in your constraint names rather than some alias. It makes things very clear when someone 6 months later needs to decipher things.
  4. 1 point
    There are a few reasons you might want to know the name (index hints, changing the index, etc), but all are fairly rare so it's not really important that you name them explicitly. If you need the name, you could always look it up later. I tend to name my indexes and constraints just to be explicit. I use the format IX_table_name_column_name to keep things simple.
  5. 1 point
    Sounds good to me. Assuming you want multiple categories applied to each blog post, that is.
  6. 1 point
    round(floatval($item['in_bytes'])/1000000,2); I used 'floatval' instead of 'intval' since that is what 'round' wants. The 2 gives 2 decimal places but you can use what you want.
  7. 1 point
    The issue I think requinix is trying to point to is that if the article doesn't even exist, you don't want to show a 'This is a premium article.' error, you want to display a proper 404/article not found error. If you just display 'This is a premium article' in both cases, that will lead to confusion and frustration for your users. If the article doesn't exist there's no need to do any extra work for determining if the user has a matching entitlement to go with that article so you can cut all that processing out and not waste time on it. Only if the article exists do you then need to worry about processing entitlements. Whether or not the article being free involves entitlement checks depends on what kind of solution you end up using for determining free. If it's just a flag on the article then you can skip the entitlements checks if that flag is true. If you do some free plan that everyone is a member of by default then you will need to do the checks. So your logic structure would essentially be best setup as Does article exist? No? Display 404 and quit Is the article free? Yes? Display article and quit Is the user entitled to the article? No? Display entitlement error and quit Display article. Unless you have a compelling reason to do otherwise, I'd probably just start out with requinix's is_free column suggestion to make things easy. If in the future you decide that's not good enough then you can refactor it into something else. I spent a few minutes trying to think of a reason not to do a simple is_free column and couldn't really come up with any scenario where more than that would be necessary. Don't nitpick your query count. One vs two queries will make absolutely zero noticible difference in the runtime of your code. If two queries make the program flow nicer, use two queries.
  8. 1 point
    I would certainly have the likes/dislikes in a table as Barand suggested, along with an FK to the user who made the like/dislike rating. This allows you to do a number of important things, like for example, preventing a person from liking or disliking the same person repeatedly. There is a cost to summarizing data like that repeatedly in a sub-query so frequently people will implement a de-normalized summary, or better yet, put the summary in a caching system like Redis. The summarized(denormalized) totals could be in the person table if you really wanted them to be, but you should still have the detail person_rating table to store the individual like/dislike rows.
  9. 1 point
    One table for likes , one for dislikes and and a third for views Perhaps combine like/dislikes with a flag column to distinguish
  10. 1 point
  11. 1 point
    Agree with chhorn: put in the person table, unless you want the historic record of Avatars. Hopefully you are using the InnoDB engine for all your MySQL tables. If not, you can run ALTER TABLE on them to change the engine from the default MyISAM. With InnoDB, when you SELECT a row from an InnoDB table by the primary key, the read activity includes all the data anyways, since InnoDB has clustered indexes. This type of micro-optimization isn't really that useful to consider when thinking about DB design, but you might as well sleep well at night knowing that there's no significant performance cost to putting it into the person table.
  12. 1 point
    There are many different ways to handle this. Personally I would probably use Redis hashes for this, or a mongoDb collection, but if you don't have those options, then one of the simplest and cleanest options is to use SQLLite3. There is full support for Sqllite via a node library, and it has essentially no footprint other than the underlying c libraries. Tables can be persisted to the filesystem or used in memory. I see no reason not to persist them to a file. You would need a simple table keyed by robot name with the following fields: robot (text) user (text) from (integer) to (integer) The from and to columns would be timestamp values. Personally, it would be nicer to use websockets rather than polling, but either way, the client code will query the db for a particular robot where the current timestamp > from and < to. If any rows come back then, you check if the user is the current user. If not, then display to the user that "user x currently has this robot". The entire table could also be queried to show which robots are currently assigned to which users. If a user wants to assign themselves an available robot, then then simply insert a new row with the current time as from, and some reasonable future time that syncs with your client "checkin" ajax code. 5 or 10 minutes might be a good starting point. The "checkin" code would query current user's robot row, and update the from, extending it by 5 or 10 minutes at each checkin. The advantage of this is that any allocations will expire if the user doesn't release the robot properly or their page dies. Sqllite will handle concurrency and locking for you via transaction support (you wrap your select and insert/update activity in a transaction). To release an allocation, you'll just need a routine that deletes the row for that robot. You might also want another table for the robots themselves, and even use a standard relational 1-M model with an id for key, but that is up to you. Somehow the code needs to know the fulll list of available robots, and you might as well use sqllite for both problems. Just to keep the database pruned and small, I'd suggest writing a small program that runs from cron, and cleans up any expired robot allocation rows ( current timestamp > to). You could run this in whatever iteration you would like. There might be some value in having a historic record of robot allocations, so cleaning out old rows removes that, whenever you actually run the pruning.
  13. 1 point
    This is my take on your question. Have a sign in page. This will give the user an identification. On that page list all things that are to be worked on. Identify which items are in use and which are open. Maybe back ground color etc.. The user selects what he/she wants to work on first - that assigns this page to her. Then have user pick which he wants to be notified when available. Add him to the list. You can work thing out from there.
  14. 1 point
    I bet you need the avatar more often, so a new table would involve a join or at least a separat query every time. so if your users table does not seem to be flooded with columns anyway, or the user can store multiple avatars, maybe for historical reasons, i would stick on two columns within the users table for performance and maintainability - you still can simply let the column out on queries that don't need the data.
  15. 1 point
    MySQL/InnoDB is highly performant which is one of the reasons (along with simplicity) it continues to be used. All RDBMS's depend on indexing, and the fundamental index type they all provide and implement is the Btree index. FWIW, I have extensive experience with Sybase/Sql Server, Oracle and MySQL. MySQL is unique in that it has alternative engines, as well as forks (MariaDB, Percona). PostgreSQL is often preferred by people coming from Oracle, as it is very similar down to database objects, datatypes and the stored procedure/trigger language (PL/SQL) that are foundations of Oracle use through the years. Sybase/SQL Server were also popularized through use of the stored proc/trigger language T-SQL. Comparatively, MySQL didn't even have Stored Procedures/Triggers until version 5.x beta'd in 2005. The bigger question in the open source world is not MySQL vs the commercial DB's but rather MySQL vs PostgreSQL. Even more recently many RDBMS alternatives have come into the mainstream under the NoSQL or Document DB monikers, most notably MongoDB and CouchDB. This has to do with concerns about scalability, which you don't have reasonable expections will be an issue for you that can't be solved with a bigger server.
  16. 1 point
    Years ago I wrote a web page (using VB with SQLServer) which showed all bus stops within 200m of your location (this could be changed by a slider) with their scheduled departure times/destinations in the next hour. This was fine out in the 'burbs where there might only be a handful of stops inside the search area. In the city centre it was a different picture with hundreds of stops and thousands of journeys. If the search radius exceed 50m it was timing out after 30secs. This was at a time when I had started using PHP but my bosses were against it (cos it wasn't Micro$oft). I migrated the required database tables so that I had a MySQL version and rewrote the VB code in PHP. Absolutely no problem in the city centre until I wound up the search radius to 2 Km. That was the last time I used VB.
  17. 1 point
    If your structure is normalized correctly you should be fine. Make sure that your tables are using the InnoDB engine. Any queries that can be satisfied by using the primary key index return all data in the record by reading the index itself. This is because InnoDB has "clustered indexes" which is a fancy term meaning that the data is actually stored in the primary key index. All primary and foreign keys are inherently indexed. So the main concern in regards to other required indexes would be queries that have where clauses that aren't keys in the table definition. This is the single most important detail. Your queries need to use a relevant index. An obvious example would be username & password columns in a user table. Essentially you just need to focus on the WHERE clauses of your queries and make sure that you have an index to handle the query. For example, if you always query for user by username & password, have an index on username, password, and not individual indexes on username and password. If you have a monolithic server (webserver, php, mysql running on one box) then you have to plan for contention. MySQL needs some tweaking to provide a sufficient InnoDB buffer pool allocation. If it's a small DB as you describe your entire dataset can be in the buffer pool cache the majority of the time.
  18. 1 point
    A single query with a join is more efficient than separate queries.
  19. 1 point
    I see three blue colors: navy blue with writing, light blue with writing, and the dark blue/gray with images. I can't figure what you're describing according to that. I'm guessing that you want to have text + image / image + text alternating on desktop and then just text + image on mobile. Is that it? Rearrange everything in the markup so that it is correct: text first, image second. Desktop is where you should be making it read backwards. Do that by using looking into whatever options your CSS framework gives you regarding repositioning inside its grid system; if there isn't anything then use order.
  20. 1 point
    I have a wonderful rectangular device on my desk. When i press on the area marked "+", a "+" appears on the screen. Similarly, pressing "-" area gives a "-". Works with letters, the long narrow area at the bottom and numbers too. You should get one. Use a monospace font like courier. Use spaces, never tabs, and paste into a code area in the forum so you get a monospaced font. For query output you can use the mysql command line interface
  21. 1 point
    You are definitely on the right track. TABLE: plan +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | plan_id | int(11) | NO | PRI | | | | plan_name | varchar(45) | YES | | | | +-----------+-------------+------+-----+---------+-------+ TABLE: plan_version +-------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------+------+-----+---------+-------+ | plan_id | int(11) | NO | PRI | | | | version_no | int(11) | NO | PRI | | | | valid_from | date | YES | | | | | valid_until | date | YES | | | | +-------------+---------+------+-----+---------+-------+ TABLE: entitlement +------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------+------+-----+---------+-------+ | plan_id | int(11) | NO | PRI | | | | version_no | int(11) | NO | PRI | | | | feature_id | int(11) | NO | PRI | | | +------------+---------+------+-----+---------+-------+ TABLE: feature +------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | feature_id | int(11) | NO | PRI | | | | title | varchar(45) | YES | | | | | page | varchar(100) | YES | | | | +------------+--------------+------+-----+---------+-------+ Suppose the data is... TABLE: plan TABLE: entitlement +---------+-----------+ +---------+------------+------------+ | plan_id | plan_name | | plan_id | version_no | feature_id | +---------+-----------+ +---------+------------+------------+ | 1 | Silver | | 1 | 1 | 1 | | 2 | Gold | | 1 | 1 | 2 | | 3 | Platinum | | 1 | 1 | 3 | +---------+-----------+ | 1 | 2 | 1 | | 1 | 2 | 2 | TABLE: plan_version | 1 | 2 | 3 | +---------+------------+------------+-------------+ | 1 | 2 | 4 | | plan_id | version_no | valid_from | valid_until | | 2 | 1 | 1 | +---------+------------+------------+-------------+ | 2 | 1 | 2 | | 1 | 1 | 2019-06-01 | 2019-12-31 | | 2 | 1 | 3 | | 1 | 2 | 2020-01-01 | 9999-12-31 | | 2 | 1 | 4 | | 2 | 1 | 2019-06-01 | 2019-10-31 | | 2 | 1 | 5 | | 2 | 2 | 2019-11-01 | 2020-02-29 | | 2 | 1 | 6 | | 2 | 3 | 2020-03-01 | 9999-12-31 | | 2 | 2 | 1 | | 3 | 1 | 2019-12-01 | 9999-12-31 | | 2 | 2 | 2 | +---------+------------+------------+-------------+ | 2 | 2 | 3 | | 2 | 2 | 4 | TABLE: feature | 2 | 2 | 5 | +------------+-------+------------+ | 2 | 2 | 6 | | feature_id | title | page | | 2 | 2 | 7 | +------------+-------+------------+ | 2 | 3 | 1 | | 1 | AAA | Page1.php | | 2 | 3 | 2 | | 2 | BBB | Page2.php | | 2 | 3 | 3 | | 3 | CCC | Page3.php | | 2 | 3 | 4 | | 4 | DDD | Page4.php | | 2 | 3 | 5 | | 5 | EEE | Page5.php | | 2 | 3 | 6 | | 6 | FFF | Page6.php | | 2 | 3 | 7 | | 7 | GGG | Page7.php | | 2 | 3 | 8 | | 8 | HHH | Page8.php | | 3 | 1 | 1 | | 9 | JJJ | Page9.php | | 3 | 1 | 2 | | 10 | KKK | Page10.php | | 3 | 1 | 3 | +------------+-------+------------+ | 3 | 1 | 4 | | 3 | 1 | 5 | | 3 | 1 | 6 | | 3 | 1 | 7 | | 3 | 1 | 8 | | 3 | 1 | 9 | | 3 | 1 | 10 | +---------+------------+------------+ to summarize... SELECT p.plan_name , v.version_no , DATE_FORMAT(v.valid_from, '%m/%d/%Y') as `From` , DATE_FORMAT(v.valid_until, '%m/%d/%Y') as `Until` , GROUP_CONCAT(f.title ORDER BY title SEPARATOR ', ') as features FROM plan p JOIN plan_version v USING (plan_id) JOIN entitlement e USING (plan_id, version_no) JOIN feature f USING (feature_id) GROUP BY plan_id, version_no; +-----------+------------+------------+------------+--------------------------------------------------+ | plan_name | version_no | From | Until | features | +-----------+------------+------------+------------+--------------------------------------------------+ | Silver | 1 | 06/01/2019 | 12/31/2019 | AAA, BBB, CCC | | Silver | 2 | 01/01/2020 | 12/31/9999 | AAA, BBB, CCC, DDD | | Gold | 1 | 06/01/2019 | 10/31/2019 | AAA, BBB, CCC, DDD, EEE, FFF | | Gold | 2 | 11/01/2019 | 02/29/2020 | AAA, BBB, CCC, DDD, EEE, FFF, GGG | | Gold | 3 | 03/01/2020 | 12/31/9999 | AAA, BBB, CCC, DDD, EEE, FFF, GGG, HHH | | Platinum | 1 | 12/01/2019 | 12/31/9999 | AAA, BBB, CCC, DDD, EEE, FFF, GGG, HHH, JJJ, KKK | +-----------+------------+------------+------------+--------------------------------------------------+ to get the ones in effect on a particular date, add a where clause to the above query ... WHERE CURDATE() BETWEEN v.valid_from AND v.valid_until ... +-----------+------------+------------+------------+--------------------------------------------------+ | plan_name | version_no | From | Until | features | +-----------+------------+------------+------------+--------------------------------------------------+ | Silver | 2 | 01/01/2020 | 12/31/9999 | AAA, BBB, CCC, DDD | | Gold | 2 | 11/01/2019 | 02/29/2020 | AAA, BBB, CCC, DDD, EEE, FFF, GGG | | Platinum | 1 | 12/01/2019 | 12/31/9999 | AAA, BBB, CCC, DDD, EEE, FFF, GGG, HHH, JJJ, KKK | +-----------+------------+------------+------------+--------------------------------------------------+ ** ** ** Now to address your question about preventing date overlaps. Suppose that on March 1st you are adding a new feature 'LLL/page11.php' for Platinum plans and you want to introduce a new version. Add new feature LLL Select Platinum v1 record for editing On the edit form, change date_from to "2020-03-01" and add LLL to feature list. However, when you update the version table, leave the "from" date as it is and change the "until" date to the date before the new one (2020-02-29), save insert a new record for Platinum / v2 / 2020-03-01 / 9999-12-31 insert entitlement records for new version
  22. 1 point
    It depends - can people have more than one occupation and/or title in your system? If so, create a couple junction tables for that data. Other than that I'd consider splitting name into first and last just to make searching easier, but the rest looks fine.
  23. 1 point
    That is not the way to handle it. Set a unique constraint on the DB column(s) Attempt the insert and catch the duplicate error if any.
  24. 1 point
    Your method creates a connection each time you run a query. That method creates a single connection for each object and uses it for all the queries in the object. Better still would be to create a single connection for the page and pass that one connection to each new object when it is created. At the top of each page, have require 'db_inc.php'; // <--- CONTAINS ----- # const HOST = "localhost"; # const USER = "root"; # const PWD = ""; # const DBNAME = "cms"; # # function pdoconnect() # { # $dsn = 'mysql:host=' . HOST . ';dbname=' . DBNAME; # $pdo = new PDO ($dsn, USER, PWD); # $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); # $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); # $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); # return $pdo; # } # $connection = pdoconnect(); // create connection then your code becomes <?php class dbh { protected $conn; public function __construct($connection) { $this->conn = $connection; } } class PostsData extends dbh{ public function getPosts() { $sql = "SELECT * FROM posts_tbl"; $stmt = $this->conn->prepare($sql); $stmt->execute(); $result = $stmt->fetchAll((PDO::FETCH_OBJ)); return $result; } public function addPost($filter_author, $filter_title, $filter_txt) { $sql = "INSERT INTO posts_tbl (post_author, post_title, post_txt) VALUES (?, ?, ?)"; $stmt = $this->conn->prepare($sql); $stmt->execute([$filter_author, $filter_title, $filter_txt]); } } $post = new PostsData($connection); // pass connection to new object $posts = $post->getPosts(); foreach ($posts as $post) { echo $post->post_title . '<br>'; } ?>
  25. 1 point
    You are still connecting in each of your functions instead of just once. Try <?php class dbh{ private $host = "localhost"; private $user = "root"; private $pwd = ""; private $dbname = "cms"; protected $conn; // make available to subclass public function __construct() { $this->conn = $this->connect(); } private function connect() { $dsn = 'mysql:host=' . $this->host . ';dbname=' . $this->dbname; $pdo = new PDO ($dsn, $this->user, $this->pwd); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); return $pdo; } } class PostsData extends dbh{ public function getPosts() { $sql = "SELECT * FROM posts_tbl"; $stmt = $this->conn->prepare($sql); $stmt->execute(); $result = $stmt->fetchAll((PDO::FETCH_OBJ)); return $result; } public function addPost($filter_author, $filter_title, $filter_txt) { $sql = "INSERT INTO posts_tbl (post_author, post_title, post_txt) VALUES (?, ?, ?)"; $stmt = $this->conn->prepare($sql); $stmt->execute([$filter_author, $filter_title, $filter_txt]); } } $post = new PostsData(); $posts = $post->getPosts(); foreach ($posts as $post) { echo $post->post_title . '<br>'; } ?>
  26. 1 point
    And? It says that you don't have matching arguments and parameters. That means you have x number of defined parms in your query statement and y number of arguments (variables) bound to those. Fix that and try again. Could be the ssi string is short one character? AND upon a re-read of the message it is definitely the "ssi" string. The number of these parms must match the number of arguments you are supplying.
  27. 1 point
    use (NOT) FIND_IN_SET(str,strlist). the strlist parameter can be supplied via a single prepared query place-holder.
  28. 1 point
    if all you are doing is mapping input values to output values, don't write out conditional logic for every possible choice. if the input to output mapping doesn't contain any calculable relationship, define a data structure (array, database table) to map the input to the output. for the example values you have shown, wouldn't you just break apart the value at the '-' character and use the first part as the lowest range value?
  29. 1 point
    Nevermind. I figured it out. You make your links to your page as the friendly links and htaccess reads and converts the seo friendly link or directs it to the appropriate page.
  30. 1 point
    I personally do the following and call it a day: define("APP_ROOT", dirname(dirname(__FILE__))); define("PRIVATE_PATH", APP_ROOT . "/private"); define("PUBLIC_PATH", APP_ROOT . "/public"); require_once PRIVATE_PATH . "/vendor/autoload.php"; require_once PRIVATE_PATH . "/security/security.php"; require_once PRIVATE_PATH . "/config/config.php";
  31. 1 point
    It's generally not good design if you have to pull a path like that dynamically. A script sending the email should simply write the required /LifeSaverHTML/Details/20 or whatever directly. By the way, dirname on the REQUEST_URI can be very easily fooled. Definitely don't do that.
  32. 1 point
    CSS is for styling, not content. Though you have some limited ability to inject content via CSS, I'm 99.9% sure you can't control an href attribute from CSS. To elaborate on Barand's response to this, PHP will only interpolate variables in double-quoted strings. For instance, $var = "you"; echo "Hi there, $var!"; This will output Hi there, you! However, $var = "you"; echo 'Hi there, $var!'; This will output Hi there, $var! It's an important distinction. What? <?php opens a PHP block, and ?> closes it. So OK, technically you're on the right track; however if you just type <? into HTML, you're going to get <? on your screen.
  33. 1 point
    HTML knows nothing about php variables. If it's in the HTML section (as this is) it needs embedding inside php tags Either <a href='test.php?id=<?php echo $uri; ?>'>link to page2</a> or use short tags <a href='test.php?id=<?=$uri?>'>link to page2</a>
  34. 1 point
    Your syntax was just messed up. You can see how much simpler Barand's code is. There are a couple of ways to do the same thing but none are complicated. When you find yourself adding too many quotes and other symbols you are probably on the wrong track. PHP recognizes anything that starts with $ as a variable and does substitution. If you need to have a string that starts with $ and do not want substitution then you need to escape (\) the $, thus \$whatever.
  35. 1 point
    Cut-down version echo "<tr> <td> <a href='Details/21/index.php?id=$id'>$id</a> </td> </tr>";
  36. 1 point
    I'd guess that satellite[j] does not exist for the current value of j. You need more satellites It's better to use foreach() when iterating through arrays instead of for() (which expects a satellite to always be present for every value of j)
  37. 1 point
    The method of counting the dates not present between the logout dates has a major flaw - it does not take into account those days when they should not be present (ie weekends and holidays) and just counts them as absent on those days. The method I usually use in these situations is to create a temporary table (workdays) which contains all the weekday dates for the required period. In the logout data I have also the data for several users during this period (Oct 1 to Oct 24 2019). CODE TO GENERATE workday DATES $startdate = '2019-10-01'; $enddate = '2019-10-25'; $interval = DateInterval::createFromDateString('next weekday'); $dateperiod = new DatePeriod( new DateTime($startdate), $interval, new DateTime($enddate)); $db->exec("CREATE TEMPORARY TABLE workday (day DATE not null primary key)"); foreach ($dateperiod as $d) { $data[] = "('{$d->format('Y-m-d')}')"; } $db->exec("INSERT INTO workday VALUES " . join(',', $data)); I also have a "holiday" table. In this example, Oct 14th is designated a holiday. So the data I now have is... TABLE: ajoo_log TABLE: ajoo_holiday +-------+---------------------+--------+ +------------+------------+-------------+ | recno | timeoflogout | userid | | holiday_id | hol_date | hol_name | +-------+---------------------+--------+ +------------+------------+-------------+ | 3 | 2019-09-30 13:13:15 | 3 | | 1 | 2019-10-14 | October Day | | 4 | 2019-09-30 13:13:15 | 4 | +------------+------------+-------------+ | 5 | 2019-09-30 13:13:15 | 5 | | 9 | 2019-10-01 07:47:35 | 4 | TABLE: ajoo_user TEMP TABLE: workday | 10 | 2019-10-01 07:47:35 | 5 | +--------+-------+ +------------+ | 14 | 2019-10-03 13:28:17 | 4 | | userid | name | | day | | 15 | 2019-10-03 13:28:17 | 5 | +--------+-------+ +------------+ | 18 | 2019-10-04 21:41:17 | 3 | | 3 | Curly | | 2019-10-01 | Tue | 19 | 2019-10-04 21:41:17 | 4 | | 4 | Larry | | 2019-10-02 | | 20 | 2019-10-04 21:41:17 | 5 | | 5 | Mo | | 2019-10-03 | | 23 | 2019-10-05 11:18:18 | 3 | +--------+-------+ | 2019-10-04 | | 24 | 2019-10-05 11:18:18 | 4 | | 2019-10-07 | Mon | 25 | 2019-10-05 11:18:18 | 5 | | 2019-10-08 | | 28 | 2019-10-08 16:56:55 | 3 | | 2019-10-09 | | 29 | 2019-10-10 16:56:55 | 4 | | 2019-10-10 | | 30 | 2019-10-16 16:56:55 | 5 | | 2019-10-11 | | 33 | 2019-10-18 16:37:43 | 3 | | 2019-10-14 | Mon | 34 | 2019-10-18 16:37:43 | 4 | | 2019-10-15 | | 35 | 2019-10-18 16:37:43 | 5 | | 2019-10-16 | | 38 | 2019-10-19 12:31:23 | 3 | | 2019-10-17 | | 40 | 2019-10-19 12:31:23 | 5 | | 2019-10-18 | | 43 | 2019-10-21 15:23:54 | 3 | | 2019-10-21 | Mon | 44 | 2019-10-21 15:23:54 | 4 | | 2019-10-22 | | 45 | 2019-10-21 15:23:54 | 5 | | 2019-10-23 | | 48 | 2019-10-24 15:37:35 | 3 | | 2019-10-24 | | 49 | 2019-10-24 16:37:35 | 4 | +------------+ | 50 | 2019-10-24 17:37:35 | 5 | +-------+---------------------+--------+ Now to calculate the days absent. A cross join of the user table with workday table gives me a date for each user when they were expected to attend. Left joining to the log table and the holiday tables tells me when they were absent (no matching logout and no matching holiday) SELECT u.name , SUM(ISNULL(timeoflogout) AND ISNULL(hol_date)) as total_absent FROM ajoo_user u CROSS JOIN workday w LEFT JOIN ajoo_log l ON u.userid = l.userid AND w.day = DATE(l.timeoflogout) LEFT JOIN ajoo_holiday h ON w.day = h.hol_date GROUP BY u.userid Comparison of original method with this new method ORIGINAL RESULTS NEW RESULTS +----------+--------------+ +-------+--------------+ | username | total_absent | | name | total_absent | +----------+--------------+ +-------+--------------+ | Curly | 14 | | Curly | 12 | | Larry | 16 | | Larry | 10 | | Mo | 15 | | Mo | 10 | +----------+--------------+ +-------+--------------+
  38. 1 point
    It's not about the checkbox being checked. The problem is that the $stmt3 query returned a row, you read it, and that you didn't finish reading until it was done. Unbuffered statements (which you are apparently using) must be fully read from before you can start another query, and "fully read from" means you don't just read the one row you were expecting but you keep going until you don't get any more rows. But the solution here isn't to do that. The whole point of prepared statements is that you only need to do one of them. Preparing the same statement over and over again inside a loop is the exact opposite of how it should be used. Prepared statements should be prepared once, have their input variables set up once, and then what you repeat in a loop is setting the variables to some new values and executing the statement. But the solution here isn't to do that either. You're potentially running a bunch of queries here, right? One to get the list of categories, then one for each of those categories to see if the post uses it. Wouldn't it be better if you just ran one query for all the categories and it told you whether the post used each one? SELECT bc.catID, bc.catTitle, NOT ISNULL(bpc.postID) AS inuse FROM blog_cats bc LEFT JOIN blog_post_cats bpc ON bc.catID = bpc.catID WHERE bpc.postID = ? ORDER BY bc.catTitle Prepare that query, bind in your $postID, execute it, then fetch all the results from it. Each row will tell you the category ID and name, as well as whether it is in use for the post.
  39. 1 point
    Current standards are to create responsive websites using CSS media queries. Don't detect the user agent, don't detect mobile or desktop. No Javascript. You should be able to open the site on your desktop browser, shrink the window, and see the mobile experience.
  40. 1 point
    If what you really want is just the content of the latest row, then SELECT MAX(timeoflogout) as timeoflogout , SUM(absents)) as tot FROM ( SELECT recno , DATEDIFF(timeoflogout, @prevlog) - 1 as absents , @prevlog := timeoflogout as timeoflogout FROM ( SELECT recno , timeoflogout FROM ajoo ORDER BY timeoflogout ASC ) as sorted JOIN (SELECT @prevlog := NULL) as initialise ) recs; +---------------------+------+ | timeoflogout | tot | +---------------------+------+ | 2019-10-24 17:37:35 | 15 | +---------------------+------+ EDIT: P.S. Just curious - what is your next query that requires this data?
  41. 1 point
    "absents" is a column alias. You can't reference an alias inside the SELECT or WHERE parts of the same query. If you want the cumulative then you'll need an extra subquery SELECT recno , timeoflogout , absents , @tot := @tot + IFNULL(absents,0) as total FROM ( SELECT recno , DATEDIFF(@prevlog, timeoflogout) - 1 as absents , @prevlog := timeoflogout as timeoflogout FROM ( SELECT recno , timeoflogout FROM ajoo ORDER BY timeoflogout DESC ) as sorted JOIN (SELECT @prevlog := NULL, @tot := 0) as initialise ) recs; +-------+---------------------+---------+-------+ | recno | timeoflogout | absents | total | +-------+---------------------+---------+-------+ | 30 | 2019-10-24 17:37:35 | NULL | 0 | | 29 | 2019-10-21 15:23:54 | 2 | 2 | | 28 | 2019-10-19 12:31:23 | 1 | 3 | | 27 | 2019-10-18 16:37:43 | 0 | 3 | | 26 | 2019-10-15 16:56:55 | 2 | 5 | | 25 | 2019-10-05 11:18:18 | 9 | 14 | | 24 | 2019-10-04 21:41:17 | 0 | 14 | | 23 | 2019-10-03 13:28:17 | 0 | 14 | | 21 | 2019-10-02 07:47:35 | 0 | 14 | | 4 | 2019-09-30 13:13:15 | 1 | 15 | +-------+---------------------+---------+-------+ "absents" is now a column in the subquery (a dynamic temporary table)
  42. 1 point
    This will calculate the "absents" value between consecutive logouts SELECT recno , DATEDIFF(@prevlog, timeoflogout) - 1 as absents , @prevlog := timeoflogout as timeoflogout FROM ( SELECT recno , timeoflogout FROM ajoo ORDER BY timeoflogout DESC ) as sorted JOIN (SELECT @prevlog:=NULL) as initialise +-------+---------+---------------------+ | recno | absents | timeoflogout | +-------+---------+---------------------+ | 30 | | 2019-10-24 17:37:35 | | 29 | 2 | 2019-10-21 15:23:54 | | 28 | 1 | 2019-10-19 12:31:23 | | 27 | 0 | 2019-10-18 16:37:43 | | 26 | 2 | 2019-10-15 16:56:55 | | 25 | 9 | 2019-10-05 11:18:18 | | 24 | 0 | 2019-10-04 21:41:17 | | 23 | 0 | 2019-10-03 13:28:17 | | 21 | 0 | 2019-10-02 07:47:35 | | 4 | 1 | 2019-09-30 13:13:15 | +-------+---------+---------------------+ You can accumulate the total of "15" as you process the query results.
  43. 1 point
    Well for one, var json = JSON.parse(JSON.stringify(data)); don't do that. You're starting with an object, turning it into a JSON string, and then turning it right back into an object. There's no point. That aside, one improvement would be to use object syntax instead of array syntax for accessing members. Array syntax with [ ]s is fine when the name is in a variable, or it has some weird name you know but can't write out (like it has hyphens or symbols), but for names like "students" there's no need. Example: for (var key in data.students) { (note that this is using the original data variable you started with) An easy second thing is semicolons. Control structures, like for loops, don't need semicolons after the body.
  44. 1 point
    You obviously don't know any php and furthermore are not willing to open up a book and look anything up. The trim function (mentioned above) is one that removes leading and trailing spaces from a string. That is all. It has nothing to do with a loop or with output. Why are you into something this deep if you don't know anything about php or programming? As a professional programmer I find it ridiculous when people with no knowledge think they can just jump into it and hope someone can hold their hand for the next 2 months teaching them what they should already have tried to learn for themselves. Have fun. It's a learning process that can possibly give you a new set of knowledge that you may find invaluable. But - it takes work. Good bye
  45. 1 point
    Make ticket_number an auto_increment column CREATE TABLE raffle ( name VARCHAR(50) ticket_number INT NOT NULL auto_increment primary key ); INSERT INTO raffle (name) VALUES ('Curly'), ('Curly'), ('Curly'), ('Larry'), ('Larry'), ('Larry'), ('Larry'), ('Larry'), ('Mo'), ('Mo'), ('Mo'), ('Mo'), ('Mo'), ('Mo'); SELECT name, ticket_number FROM raffle; +-------+---------------+ | name | ticket_number | +-------+---------------+ | Curly | 1 | | Curly | 2 | | Curly | 3 | | Larry | 4 | | Larry | 5 | | Larry | 6 | | Larry | 7 | | Larry | 8 | | Mo | 9 | | Mo | 10 | | Mo | 11 | | Mo | 12 | | Mo | 13 | | Mo | 14 | +-------+---------------+
  46. 1 point
    The "check" is actually the result of the animation. Look at the animation itself for where you can change the color.
  47. 1 point
    A few notes about text bounding boxes which, I hope, will help in precise placement of your text. Suppose I have the text string "The lazy fox" which I want to display using 150pt Vivaldi . My image is 4896 x 3672 and I want the text placed at the bottom right but 250 pixels from the edges of the image. $box = imagettfbbox(150,0,'c:/windows/fonts/vivaldii.ttf','The lazy fox'); gives this array of coordinates of the four corners $box = Array ( [0] => 23 [1] => 55 [2] => 871 [3] => 55 [4] => 871 [5] => -140 [6] => 23 [7] => -140 ) You may wonder why it can't just give a rectangle from (0,0) to (width, height) to make sizing simple, but there is extra information to be extracted from the array Text width = (871 - 23) = 848 Text height = 55 - (-140) = 195 The baseline will be 140px from the top The text is offset 23 px to the right. My text, therefore, will be in a rectangle 848 x 195 positioned 250 px from right and bottom edges. The top left x coord of the rectangle will be (4896 - 250 - 848) = 3798 and top left y coord will be (3672 - 250 - 195) = 3227. However, to land the text precisely into this area we position it on the baseline and at the required x offset, ie (3798 - 23 , 3227 + 140) = (3775, 3367). I use a simple custom function to assist with this process function metrics($font, $fsize, $str) { $box = imagettfbbox($fsize, 0, $font, $str); $ht = abs($box[5] - $box[1]); $wd = abs($box[4] - $box[0]); $base = -$box[5]; $tx = -$box[0]; return [ 'width' => $wd, 'height' => $ht, 'ascent' => $base, 'offsetx' => $tx ]; } $box = metrics ('c:/windows/fonts/vivaldii.ttf', 150, 'The lazy fox'); $box = Array ( [width] => 848 [height] => 195 [ascent] => 140 [offsetx] => -23 )
  48. 1 point
    Alternative model which allows multiple siblings jdev_nroll; jdev_sibling; +----+--------+---------+-------+-----------+------------+ +------------+----------+ | id | sname | ctclass | shift | ctstudent | dob | | sibling_id | elder_id | +----+--------+---------+-------+-----------+------------+ +------------+----------+ | 1 | Curly | 1 | 0 | N | 2007-01-20 | | 2 | 1 | | 2 | Larry | 1 | 0 | Y | 2010-12-21 | | 3 | 1 | | 3 | Mo | 1 | 0 | Y | 2011-02-22 | | 3 | 2 | | 4 | Peter | 1 | 0 | N | 2009-01-03 | | 4 | 5 | | 5 | Paul | 1 | 0 | N | 2006-12-21 | | 9 | 8 | | 6 | Mary | 1 | 0 | Y | 2010-09-20 | | 9 | 10 | | 7 | Jane | 1 | 0 | N | 2008-03-08 | | 10 | 8 | | 8 | John | 1 | 0 | N | 2006-10-04 | +------------+----------+ | 9 | George | 1 | 0 | Y | 2010-10-26 | | 10 | Ringo | 1 | 0 | Y | 2009-11-15 | +----+--------+---------+-------+-----------+------------+ SELECT a.id as sibling_id , a.sname as sibling_name , TIMESTAMPDIFF(YEAR,a.dob,curdate()) as sibling_age , a.ctclass as class , b.id as elder_id , b.sname as elder_name , TIMESTAMPDIFF(YEAR,b.dob,curdate()) as elder_age , b.ctstudent as elder_ctstudent FROM jdev_nroll a JOIN jdev_sibling s ON a.id = s.sibling_id JOIN jdev_nroll b ON s.elder_id = b.id WHERE a.ctstudent = 'Y' ORDER BY a.id +------------+--------------+-------------+-------+----------+------------+-----------+-----------------+ | sibling_id | sibling_name | sibling_age | class | elder_id | elder_name | elder_age | elder_ctstudent | +------------+--------------+-------------+-------+----------+------------+-----------+-----------------+ | 2 | Larry | 9 | 1 | 1 | Curly | 13 | N | | 3 | Mo | 8 | 1 | 1 | Curly | 13 | N | | 3 | Mo | 8 | 1 | 2 | Larry | 9 | Y | | 9 | George | 9 | 1 | 8 | John | 13 | N | | 9 | George | 9 | 1 | 10 | Ringo | 10 | Y | | 10 | Ringo | 10 | 1 | 8 | John | 13 | N | +------------+--------------+-------------+-------+----------+------------+-----------+-----------------+
  49. 0 points
    I've just looked at the data in your sql dump files. Did I say the design was terrible? I apologise for the gross understatement and for referring to it as a database As I hope I demonstrated in my earlier posts, if you get the data right the processing becomes a whole lot easier. Nothing in the other tables matches those column names in your prices table, so all of my solutions go out of the window. There might have been some hope if the first column in prices mapped to id #1 and the 2nd column to id #2 but even that isn't the case. I'll check if dropping "Price" suffix from the column names can save the day. I'll have a look at your code now, but first I need a stiff drink, or several - a large gin and tonic should help.
This leaderboard is set to New York/GMT-05:00
  • Newsletter

    Want to keep up to date with all our latest news and information?
    Sign Up
×
×
  • 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.