Landslyde

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.

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.

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.

I use Adminer for MySQL / MariaDB. Very intuitive. Nice, clean interface. And it comes with a slew of skins to fit personal preferences.

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?

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.

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

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.

Thank you, Jacques1.

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?

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!

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.

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.

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?

<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.