Jump to content

Landslyde

Members
  • Posts

    93
  • Joined

  • Last visited

  • Days Won

    1

Everything posted by Landslyde

  1. I appreciate you, Barand. Every time I think I have a good grasp of the LEFT JOIN, something like this shows me I'm still on a slippery slope. I understand your explanation. And even more than the working solution you provided, the explanation was what I was looking for. Something more to help me try to solidify LEFT JOINS. Thank you for taking time to offer that. It helped.
  2. Barand: Truth is, another person helped me with this. The query, the quarters table, all from him. But that was some time back and I can't recall who it was or what forum it was on for me to go back and review my history. This use to work, but somehow I've fouled it up. There will be many members, so having the quarters table store quarterly info isn't an option. Each member must be able to see their own quarterly earnings. As you suggested, I changed the query to: $stmt = $db->prepare('SELECT qtr AS Quarter, SUM(amount_paid) as Total FROM quarters AS q LEFT JOIN history AS h ON YEAR(last_payment) = YEAR(CURDATE()) WHERE qtr = QUARTER(last_payment) AND memberid = :memberid GROUP BY qtr'); $stmt->bindValue(':memberid', $_SESSION["memberid"], PDO::PARAM_INT); but this provided nothing. Even the LEFT JOIN's ON clause looks strange to me. If you decide to help me further, would you also briefly explain the query? I have almost 50 working pages full of queries a lot larger than this, all done by me, but this one...this one's kicking me good. I just don't get it. Thanks.
  3. I tried to edit the above post, but I wasn't "authorized" for that. I misread. Adminer is web-based. The Linux GUI I use for MySQL / MariaDB is JPDB Admin. There's a FREE edition and one for $15. Not a bad way to go.
  4. I use Adminer for MySQL / MariaDB. Very intuitive. Nice, clean interface. And it comes with a slew of skins to fit personal preferences.
  5. I'm failing miserably at this, but here's what I have: MariaDB [master]> describe quarters; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | qtr | int(11) | NO | PRI | NULL | | +-------+---------+------+-----+---------+-------+ MariaDB [master]> describe history; +--------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+------------------+------+-----+---------+----------------+ | historyid | int(10) unsigned | NO | PRI | NULL | auto_increment | | amount | float | NO | | NULL | | | subsidy | char(1) | NO | | NULL | | | last_payment | date | NO | | NULL | | | amount_paid | float | NO | | NULL | | | balance | float | NO | | NULL | | | attend | char(1) | NO | | N | | | attend_date | date | NO | | NULL | | | groupid | int(11) unsigned | NO | | NULL | | | clientid | int(10) unsigned | NO | MUL | NULL | | | memberid | int(10) unsigned | NO | MUL | NULL | | +--------------+------------------+------+-----+---------+----------------+ MariaDB [master]> Select clientid, last_payment, amount_paid -> From history -> WHERE YEAR(last_payment) = YEAR(CURDATE()) -> AND memberid = "1"; +----------+--------------+-------------+ | clientid | last_payment | amount_paid | +----------+--------------+-------------+ | 3 | 2016-01-26 | 100 | | 3 | 2016-10-29 | 15 | | 3 | 2016-01-30 | 15 | | 4 | 2016-01-26 | 30 | | 4 | 2016-10-29 | 30 | | 4 | 2016-01-30 | 15 | | 1 | 2016-01-26 | 15 | | 1 | 2016-10-29 | 30 | | 1 | 2016-01-30 | 100 | | 2 | 2016-01-26 | 30 | | 2 | 2016-10-29 | 30 | | 2 | 2016-01-30 | 30 | | 3 | 2016-01-28 | 100 | | 4 | 2016-01-30 | 15 | | 1 | 2016-01-30 | 100 | | 2 | 2016-01-30 | 30 | | 5 | 2016-01-29 | 30 | | 5 | 2016-02-02 | 30 | | 3 | 2016-02-02 | 15 | | 4 | 2016-02-02 | 30 | | 1 | 2016-02-02 | 15 | | 2 | 2016-02-02 | 30 | +----------+--------------+-------------+ There's returnable data from `amount_paid`, but the following query returns 0 rows. MariaDB [master]> SELECT qtr AS Quarter, SUM(amount_paid) as Total -> FROM quarters q -> LEFT JOIN history h ON qtr = QUARTER(last_payment) -> WHERE YEAR(last_payment) = YEAR(CURDATE()) -> AND memberid = "1" -> GROUP BY qtr; Empty set (0.01 sec) For the love of Mary! What am I doing wrong?
  6. I cldn't agree more, sir. Learning to do all of this the right way is hard to do when, like you say, there are so many opinions and myriad bad advice out there, opinions and advice that seem worthy to the unskilled eye. Thanks for your input. I appreciate it a lot. And i'll definitely be looking in to Survive the Deep End.
  7. benanamen: Thank you for pointing that out abt the intended use of var_dump, and the excerpt of how to GET the key from the URL. I appreciate that. I plan on using a table to store this once it's generated. I was only testing to see how it was all working, and even my testing was being done the wrong way. I see that now from your provided example. When I put it in production, it'll be done the right way. Jacques1: I actually got that "weird, self-made random number generator" idea from SO. Most Google searches pull up their site and put it front and center for clicking. So when I see an idea from there, a way of doing things, I tend to not ask a lot of questions regarding its validity. But I thank you for pointing out to me that there's a better, more appropriate way. I'll study the mcrypt suggestion. I appreciate both of you guys giving me good feedback and pointing me in the right direction. It's true that I tend to stumble along in the dark at times
  8. As usual, I'm in over my head. I'm generating a key to be used in an confirmation email. For testing, I have: <?php $email = 'email@email.com'; echo $key = sha1($email.'my_super_duper_secret_sauce_here'.microtime()); $url = 'https://www.mysite.com/?'.$key; echo $url; $key2 = var_dump(parse_url($url, PHP_URL_QUERY )); echo $key2; if($key == $key2) { echo "="; } else { echo "!="; } ?> which produces: 3d6d7dddc7cc9b3571078e8032f69c5ee4ef1256 https://www.mysite.com/?3d6d7dddc7cc9b3571078e8032f69c5ee4ef1256 string(40) "3d6d7dddc7cc9b3571078e8032f69c5ee4ef1256" != How do I get rid of string(40) and the beginning and tailing quotation marks so that all I have left for $key2 will equal the $key? I've tried substr(), trim(), and rtrim(). And while one of those (or some combination) may be what I need, I don't know how to use them to get rid of the unwanted chars from using var_dump(parse_url($url, PHP_URL_QUERY )) Any help on this is appreciated. Thanks.
  9. Each of the clients has a memberID, and their clients are associated to them by this memberID. In whittling down the code and removing the username and password session vars, is it a risk to tie their memberIDs to a session var? It's either that or have them log in at every query. So is using a memberID session var a damaging approach?
  10. Like I said, guys: I'm new to this. I get what you've all said. Even in the beginning I thought of doing it this way. But for some reason I got stuck on all the clients needing their own logins, and that made for a lot of extra work. It isn't all bad though, because I learned a whole lot in the process. And today, I learned a better way. I appreciate all of your comments. Thanks. Time for a new pot of coffee so I can start untangling this mess I've made!
  11. When I first started this project, PHP was new to me and I didn't have a clue how to use it. Here, seven months later, I still don't know what I'm doing. I have 47 pages that are MySQL intensive. Each client has their own login credentials to the database. I've been using the $_SESSION global to carry these credentials across the pages. I realize this is a huge security issue, but I don't know any other way to keep their username / password handy as they log in and out of the database. I've searched and not found anything resembling my need here. Most of the discussions relate to setting $_SESSION['foo'] = 'bar' and carying that from page to page. I cld really use some insight on this. Thanks.
  12. I appreciate you taking the time to show me this, mac_gyver. It helped a lot. Of all the times I searched on how to load options in a select box, not once did I come across code like yours. Clean, very nice. Also new to me was changing a buttons label/value. Pure silk, that. I'm putting it together right now. If possible, I'll send you a link with a username and pass to get in to my site so you can view it. I've been working on it for 6 months straight Thanks again for the insight and taking the time I know you probably don't really have to show me that code. Much appreciated.
  13. mac_gyver: Read only wldnt solve my issue. This is for therapists to track payment and attendance history (among other details) for their attendees. I have to have that select box for the groupid disabled after they make a selection. Why? I'll try to explain, and I'll post more code for you. When the Group is selected from the first select box, via the groupid in table a, names are loaded into a second select box. The id and memberid associated with those names are then written to table b to be used solely for attendance purposes. When the therapist posts a payment for the attendee, it's inserted into table c, then that attendeeid is deleted from the table b. What's left then gets written to table c as not being in attendance through clicking a 'Flush' button. I have a 'Dump' button set next to the Groupid select box. When clicked, it removes all the id's and memderid's from the table b. But I have no safeguard to ensure they click that button before trying to load a new Group. If they loaded another Group before dumping what's already in table b, then the table gets needlessly cluttered until the Dump button is clicked. So the only way I saw to make sure they clicked the dump button was to disable the Group select box, then re-enabling it once the dump button was clicked. And that wld be perfect, except that the disabling, as it is now, causes the select box for the names to not be loaded. Here's more of the code: <form method="post" class="subform" action="<?php echo htmlentities($_SERVER['PHP_SELF']);?>"> <div class="row" style="background: skyblue; margin-top:-23px"> <div class="col-sm-2"> <div class="input-group"> <select id="groupselect" class="form-control" name="groupid" onchange="disableFunc()"> <option name="0"<?php echo ($_POST['groupid'] == '0') ? ' selected="selected"' : ''; ?> value="0" >GROUP</option> <option name="1"<?php echo ($_POST['groupid'] == '1') ? ' selected="selected"' : ''; ?> value="1" >1</option> <option name="2"<?php echo ($_POST['groupid'] == '2') ? ' selected="selected"' : ''; ?> value="2" >2</option> <option name="3"<?php echo ($_POST['groupid'] == '3') ? ' selected="selected"' : ''; ?> value="3" >3</option> <option name="4"<?php echo ($_POST['groupid'] == '4') ? ' selected="selected"' : ''; ?> value="4" >4</option> <option name="5"<?php echo ($_POST['groupid'] == '5') ? ' selected="selected"' : ''; ?> value="5" >5</option> <option name="6"<?php echo ($_POST['groupid'] == '6') ? ' selected="selected"' : ''; ?> value="6" >6</option> <option name="7"<?php echo ($_POST['groupid'] == '7') ? ' selected="selected"' : ''; ?> value="7" >7</option> <option name="8"<?php echo ($_POST['groupid'] == '8') ? ' selected="selected"' : ''; ?> value="8" >8</option> <option name="9"<?php echo ($_POST['groupid'] == '9') ? ' selected="selected"' : ''; ?> value="9" >9</option> <option name="10"<?php echo ($_POST['groupid'] == '10') ? ' selected="selected"' : ''; ?> value="10" >10</option> </select> <span class="input-group-btn"> <!--<input type="submit" name="load" class="btn btn-default" value="Load">--> <input type="submit" name="dump" class="btn btn-danger" value="Dump"> </span> </div> </div> <div class="col-sm-3"> <div class="input-group"> <select class="form-control" name="view" style="text-transform: uppercase"> <option value="">Select</option> <?php if ($_SERVER["REQUEST_METHOD"] == "POST") { // First brace $payment = $paymentErr = ""; $_SESSION['groupid'] = $_POST['groupid']; $memberid = $_SESSION["memberid"]; /* We use $memberid to load the Select options box (below) to make sure that that ONLY attendee information for THAT member is loaded */ $_SESSION['id'] = $_POST['view']; /* Set session vars for uname and pwd1 from invalidlogin.php to be able to login using login.php */ $uname = $_SESSION['uname']; // Set $name for login.php $pwd1 = $_SESSION['pwd1']; // Set $pwd1 for login.php require_once 'login.php'; // This file contains database access credentials // Load the Select Box with names that match the groupid and memberid $stmt = $db->prepare('SELECT a.attendeeid, fname, lname, a.groupid, a.memberid, s.attendeeid, suspend FROM attendees AS a JOIN suspended AS s ON a.attendeeid = s.attendeeid WHERE a.memberid = :memberid AND suspend = "N" AND a.groupid = :groupid ORDER BY lname'); $stmt->bindValue(':memberid', $memberid, PDO::PARAM_INT); $stmt->bindValue(':groupid', $_SESSION['groupid'], PDO::PARAM_INT); $stmt->execute(); $result = $stmt->fetchAll(); foreach($result as $row){ // Load the flush Table with the IDs from the selected group if($_SESSION['flush'] == 0) { $stmt = $db->prepare('INSERT INTO flush (attendeeid, memberid) VALUES(:attendeeid, :memberid)'); $stmt->bindValue(':attendeeid', $row[0], PDO::PARAM_INT); $stmt->bindValue(':memberid', $row[4], PDO::PARAM_INT); $stmt->execute(); } echo "<option value='$row[0]'"; // Holds selected name from click to click without resetting to Select if(isset($_SESSION['id']) and $_SESSION['id'] == $row[0]) { echo ' selected="selected"'; } echo ">$row[2], $row[1]</option>"; }?> </select> <?php $_SESSION['flush'] = 1; ?> <span class="input-group-btn"> <input type="submit" name="fetch" class="btn btn-default" value="Fetch"> </span> </div> </div> <div class="col-sm-offset-6"> <div class="input-group"> <span class="input-group-btn"> <input type="submit" name="flush" class="btn btn-danger" value="Flush"> </span> </div> </div> </div> <?php if($_POST['dump']) { ?> <div class="row" style="margin-top:20px"> <div class="col-sm-3"> <label style="text-transform: uppercase; color: red" class="control-label">Dumped Group <?php echo $_SESSION['groupid']; ?></label> </div> </div> <?php } else{ ?> <div class="row" style="margin-top:20px"> <div class="col-sm-3"> <label style="text-transform: uppercase; color: red" class="control-label">Group <?php echo $_SESSION['groupid']; ?> Loaded</label> </div> </div> <?php } ?> <div class="row" style="margin-top:20px"> <div class="col-sm-3"> <label style="text-transform: uppercase" class="control-label">Payment:</label> </div> </div> <div class="row"> <div class="col-sm-3"> <div class="input-group"> <span class="input-group-addon">$</span> <input class="form-control" type="text" name="payment" placeholder="0.00" value="<?php if (isset($_POST['payment'])) { echo $payment; } ?>"> <span class="input-group-btn"> <input type="submit" name="postpayment" class="btn btn-primary" value="Post"> </span> <span class="error"><?php echo $paymentErr;?></span> </div> </div> </div> <?php if($_POST['dump']) { $stmt = $db->prepare('DELETE FROM flush WHERE memberid = :memberid'); $stmt->bindValue(':memberid', $_SESSION['memberid'], PDO::PARAM_INT); $stmt->execute(); $_SESSION['flush'] = 0; echo '<script type="text/javascript">alert("Please select a new Group"); </script>'; } My code isn't elegant, so I hope you can make sense of it. Disabling that Group select box is the ideal solution for me. Any suggestions?
  14. <script type="text/javascript" language="javascript"> function disableFunc() { document.getElementById("groupselect").disabled = true; } </script> <form method="post" class="subform" action="<?php echo htmlentities($_SERVER['PHP_SELF']);?>"> <div class="row" style="background: skyblue; margin-top:-23px"> <div class="col-sm-2"> <div class="input-group"> <select id="groupselect" class="form-control" name="groupid" onchange="disableFunc()"> <option name="0"<?php echo ($_POST['groupid'] == '0') ? ' selected="selected"' : ''; ?> value="0" >GROUP</option> <option name="1"<?php echo ($_POST['groupid'] == '1') ? ' selected="selected"' : ''; ?> value="1" >1</option> <option name="2"<?php echo ($_POST['groupid'] == '2') ? ' selected="selected"' : ''; ?> value="2" >2</option> <option name="3"<?php echo ($_POST['groupid'] == '3') ? ' selected="selected"' : ''; ?> value="3" >3</option> <option name="4"<?php echo ($_POST['groupid'] == '4') ? ' selected="selected"' : ''; ?> value="4" >4</option> <option name="5"<?php echo ($_POST['groupid'] == '5') ? ' selected="selected"' : ''; ?> value="5" >5</option> <option name="6"<?php echo ($_POST['groupid'] == '6') ? ' selected="selected"' : ''; ?> value="6" >6</option> The onchange function works as it shld. My problem is the HTML. I got this select box code from another forum. Without the onchange event in the code, when the user selects a Groupid option, it automatically loads another select box with names. But with it in the code (as I've shown it here), the select box gets disabled immediately causing no names to be loaded in the other select box. I need to be able to load the other select box with names before the groupid select box disables. Any help on this is appreciated. Thanks.
  15. In my searches, I saw that a few times. I didn't know how to use it though. It simply adds a new field. Thanks for letting me know how to use it, Jacques1. Much appreciated. I'm new at this and am learning a lot from people like you. Thanks.
  16. Maybe this will be helpful to you. $product_name = "Poulan" /* Set this variable to the product name you're looking for. Of course, for this field you'll be using the entry from an input box or a select box. */ $stmt = $db->prepare('SELECT product_name, product_code, price, details FROM products WHERE product_name = :product_name $stmt->bindValue(':product_name', $product_name, PDO::PARAM_INT); $stmt->execute(); $result = $stmt->fetchAll(); ?> <div class="row"> <div class="table-repsonsive"> <table class="table table-bordered table-hover table-striped" style="margin-top:30px"> <thead> <tr> <th style="text-transform: uppercase">Product Name</th> <th style="text-transform: uppercase">Product Code</th> <th style="text-transform: uppercase">Product Price</th> <th style="text-transform: uppercase">Product Details</th> </tr> </thead> <?php foreach($result as $row ) { $i++; ?> <tbody> <tr> <td style="width:auto;"><?php echo $row[0] ?></td> /* Outout for Product Name */ <td style="width:auto;"><?php echo $row[1] ?></td> /* Outout for Product Code */ <td style="width:auto;"><?php echo $row[2] ?></td> /* Outout for Product Price */ <td style="width:auto;"><?php echo $row[3] ?></td> /* Outout for Product Details */</tr> I hope this helps you.
  17. Hello Forum: I smoked down the Google servers on this one and didn't come up with what I'm looking for. I have a MySQL table that stores a couple fields, one of them being a date field (set by CURDATE()) when it was written to the table. I'm trying to use the PHP date() and the sql field to find the number of days that's elapse, but I can't seem to get that to work for me. Here's what I'm working with: $stmt = $db->prepare('SELECT a.attendeeid, s.attendeeid, fname, lname, suspend, reason, sdate, a.memberid, s.memberid FROM attendees AS a JOIN suspended AS s ON a.attendeeid = s.attendeeid WHERE a.memberid = :memberid AND suspend = "Y"'); $stmt->bindValue(':memberid', $memberid, PDO::PARAM_INT); $stmt->execute(); $result = $stmt->fetchAll(); ?> <div class="row"> <div class="table-repsonsive"> <table class="table table-bordered table-hover table-striped" style="margin-top:30px"> <thead> <tr> <th style="text-transform: uppercase">First</th> <th style="text-transform: uppercase">Last</th> <th style="text-transform: uppercase">Suspended</th> <th style="text-transform: uppercase">Suspension Date</th> <th style="text-transform: uppercase">Days on suspension</th> <th style="text-transform: uppercase">Reason</th> </tr> </thead> <?php foreach($result as $row ) { $i++; ?> <tbody> <?php echo '<tr style="'.getbgc($i). '">' ?> <td style="width:auto;"><?php echo $row[2] ?></td> <td style="width:auto;"><?php echo $row[3] ?></td> <td style="width:auto;"><?php echo $row[4] ?></td> <td style="width:auto;"><?php echo $row[6] ?></td> <td style="width:auto;"><?php echo strtodate(date('Y-m-d')) - $row[6] ?></td> // This is not working <td style="width:auto;"><?php echo $row[5] ?></td> $row[6] holds sdate from the query. Does anyone know how I can do this? Thanks.
  18. That's powerful, Barand. Your queries are like Jaguars next to my VW Bugs
  19. And that's the way I have it. So the quarters table holds no values except 1, 2, 3 & 4, right? And is only used to provide null values for quarters with no last_payment date through the LEFT JOIN? Am I better understanding it now?
  20. Thanks for that information, Barand. I see where this wld provide me null values for empty quarters, allowing my display of earnings to properly align with the table headers Quarters 1 through 4. At least I think that's what this will do. I'd earlier thought abt creating this table so the clients cld view year-to-year quarterly earnings. All I'd have to add to your table wld be the clientID. And I see where having a row for each quarter is necessary for the left join to work. I'll admit that it's a little confusing to me, but I'll test it out and watch how the gears turn. Much appreciated, Barand.
  21. Hello Forum: I used the query provided by Barand & Psycho: $stmt = $db->prepare('SELECT QUARTER(last_payment), SUM(amount_paid) as total FROM history WHERE YEAR(last_payment) = YEAR(CURDATE()) GROUP BY QUARTER(last_payment)'); The built-in MySQL functions are sweet. I'll have to figure out a workaround for the empty quarters though. All of my last_payment dates, put in by me for testing, are of the 2nd quarter. So when I ran this, with table headers for Quarter 1 through Quarter 4, the 2nd quarter 'total' fell under the Quarter 1 header. So I ran an UPDATE and changed the first 15 last_payment entries to reflect 1st Quarter dates, re-ran the query and got 1st and 2nd quarter to display correctly in the table. Hmm...I just thought of a fix for this Problem solved. Barand: the QUARTER() function works like a charm. Saves on a lot of needless code. Many thanks to you for offering this information. I know this is a good board with good people like you three that offer help to peeps like me. I read the board all the time, several boards, and I often see those who come here just to get others to write their code for them. That's why I feel reluctant to overstay with myriad questions, my pride foreclosing on me in midstream. But, as all of you have seen, my rookie solutions are not not not the best in town I hope that one day I'll be as good as this as you guys are, being able to help others when they come to the Forum. Again, my thanks to to you guys for your time and efforts. Learning is a blast!
  22. Seems like I have a lot to learn. I mean, it's obvious I'm not very smart abt any of this. That's from inexperience. I see what you've done in the function, Psycho, and I kind of understand it. I'll work with it and see what it does. I admit mine looks cluttered and clunky, like it was done by a 3rd grader, but that's the only way I cld understand it. The stuff you guys showed me from the onset didn't make sense to me...I just didn't understand. I appreciate you guys taking the time to further explain things to me. Means a lot.
  23. I actually did it the way maxxd suggested, with the substr() function. It works best for my application: require_once 'root_login.php'; $stmt = $db->prepare('SELECT NOW()'); $stmt->execute(); $row = $stmt->fetch(); $year = substr($row[0], 0, 4); $q2s = $year.'-04-01'; $q2e = $year.'-06-30'; $stmt = $db->prepare('SELECT SUM(amount_paid) as q2 FROM history WHERE last_payment BETWEEN :q2s and :q2e'); $stmt->bindValue(':q2s', $q2s, PDO::PARAM_INT); $stmt->bindValue(':q2e', $q2e, PDO::PARAM_INT); $stmt->execute(); $result = $stmt->fetchAll(); ?> <div class="row"> <div class="table-repsonsive"> <table class="table table-bordered table-striped table-hover" style="margin-top:30px"> <thead> <tr> <th style="text-transform: uppercase">Quarter 2</th> </tr> </thead> <?php foreach($result as $row ) { $i++; ?> <tbody> <?php echo '<tr style="'.getbgc($i). '">' ?> <td style="width:auto;color: #fff;"><?php echo number_format($row['q2'],2) ?></td> </tr> </tbody> In doing it this way, I'll always have the current year to produce the quarter breakdowns. This is just a small sample to demonstrate what I needed to accomplish and how I wanted to go abt it. I appreciate everyone's input.
  24. Thanks, maxxd. I don't understand the DateTime object, but the substr() is something I can easily wrap my mind around. And, for what I'm needing, it'll work out perfectly for me. Your answer is much appreciated. UPDATE! Lol...By the time I finished my response to maxxd and sent it in, I saw several of you had also responded. Best Forum on the net! You guys are great! As for the backticks, I'm truly new at this and thought they were correct, even though my working query doesn't use them I didn't want to look too rookie to all of you...that sure backfired! Barand & Psycho: I like the way you guys did that. Is easy to see and understand. Best answser goes to Barand and his use of the Quarter() function, although I learned a little something from each of you. Many thanks.
×
×
  • 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.