Jump to content

TapeGun007

Members
  • Content Count

    296
  • Joined

  • Last visited

Community Reputation

2 Neutral

About TapeGun007

  • Rank
    Advanced Member
  • Birthday 12/11/1968

Profile Information

  • Gender
    Male
  • Location
    San Diego, CA
  1. TapeGun007

    Join or Union?

    Barand, Thanks as always. I did discover through my trials and errors that the conditions in the WHERE clause were causing some issues. After much reading I had put them in the JOIN ON section, but you confirmed what I wasn't 100% sure of. So your definition confirms also that I was supposed to use the INNER JOIN because I did only want to show records if the match was in both tables. I did not know that LEFT JOINS were much slower however, that part I had not yet discovered.
  2. TapeGun007

    Join or Union?

    Is this a less correct way to write it then? This gave me the result I was looking for, but being a bit OCD, I like to ensure I'm writing solid code. SELECT * FROM Deals INNER JOIN Prospects ON Prospects.ProspectID = Deals.ProspectID AND Deals.Status = 'Won' AND Deals.PIGLead = ? ORDER BY SoldDate DESC Yeah, I just put the * in there to shorten it.
  3. TapeGun007

    Join or Union?

    Ok never mind. I got it. I had to use an INNER JOIN. I had also made an error above, the Status column was set to "Won" not "Sold". Thanks for helping me get on the right path.
  4. TapeGun007

    Join or Union?

    ON Prospects.ProspectID = Deals.ProspectID AND Deals.PIGLead = 2 WHERE Deals.Status = 'Sold' So... the ON line works sort of, but it lists clients that nothing was sold to. So I tried the WHERE above and now nothing comes up. LOL.
  5. TapeGun007

    Join or Union?

    Ok, so I was on the right track. I wrote this code: SELECT Prospects.ProspectID, Prospects.FirstName, Prospects.LastName, Prospects.Address, Prospects.Address2, Prospects.City, Prospects.State, Prospects.Zip, Deals.ProspectID, Deals.Product, Deals.Price FROM Prospects LEFT JOIN Deals ON Prospects.ProspectID = Deals.ProspectID So this works. But how can I narrow it down to just like WHERE PIGLead = 2 ? I now see why Union does not work, there would not be an equal amount of rows returned (if I'm understanding this correctly).
  6. TapeGun007

    Join or Union?

    I've been reading so many websites about LEFT JOIN, and now UNION, so before I go off on a major rabbit trail please help me figure out which is best by telling you what I want to accomplish. I'm going attach two images of my tables: The first table is called Prospects, and the 2nd table is simply called Deals. I'm going to try to write what I want in a non working version of code, but I hope it will translate what I want better: Under Table Deals, the field Status must be equal to "Sold" Under Tables Deals and Prospects, the ProspectID must match Under Table Deals, the PIGLead must be equal to 2 I want the table to show the Prospect information (name, address, phone, etc), then under the Prospect information, I want to see the deals sold to them where Status = "Sold". I want to see the Product and Price. Best method?
  7. TapeGun007

    Auto Insert based upon value

    Is it better or are you able to auto insert a percentage based upon a value. Example: Tech1 sells a $1,000 referral item and gets 5% commission Tech1 sells a $2,500 referral item and gets 7% commission instead. I know I could code this in php no problem. I'm just wondering if this makes sense or if it's possible just to auto insert either 5% for under $2,500 or 7% if the sale is equal or over $2,500. There is only 5% and 7%, no other options. The sale amount is stored in the same table.
  8. TapeGun007

    Finding Past Due Dates

    New question related to this. So... when a new prospect is created, the database inserts an automatic value of null. But then I set a follow up date and then when that date passes, I need to reset that value to a null. I simply put if $followupdate !isset then $followupdate = null; But when $followupdate is written to the database, it is now "0000-00-00" and I cannot seem to get the value back to null in mySQL. Everything works fine the way I have it, but call me OCD.
  9. TapeGun007

    Finding Past Due Dates

    Ah, I probably got some bad information in my reading, but I was under the perception that when the date is set to 0000-00-00 it is considered a NULL. This, of course, did not make sense to me. In my testing just now, I see the NULL value when auto inserted by mySQL. Thanks.
  10. TapeGun007

    Finding Past Due Dates

    Ah nvm, I got it unless there is a better way to write this, I would be interested. SELECT * FROM Prospects WHERE UserID = ? AND FollowUpDate < ? AND FollowUpDate > '0000-00-00'
  11. TapeGun007

    Finding Past Due Dates

    mySQL Server Version 5.7.23
  12. TapeGun007

    Finding Past Due Dates

    Not all items in my database have a due date. I already have a page that lists everything that is due today and that's fine. What I want to do is sort out all of the null dates "0000-00-00" but list the overdue items (past today) and not the current due dates (today's date). This was my failed attempt: $stmt = $pdo->prepare('SELECT * FROM Prospects WHERE UserID = ? AND FollowUpDate IS NOT NULL AND FollowUpDate < ?'); $stmt->execute([$ID,$today]); The above will list all the dates that are null anyway. I can't seem to figure out how to write this correctly. Thanks.
  13. TapeGun007

    Adding

    Uh... thanks benanamen. Heheh. I kept searching, reading, and finally found the solution: $stmt = $pdo->prepare("SELECT SUM(Price) AS sumprice FROM Deals"); $stmt->execute(); $row = $stmt->fetch(PDO::FETCH_ASSOC); echo $row['sumprice'];
  14. TapeGun007

    Adding

    This might be more of a mySQL question or a PHP question, I'm not exactly sure. I have a field in my table (Deals) called "Price". All I want is the sum of that field. I tried using something like: $price += $row['Price']; This ended up as one long string, even though the database is set as a decimal number field. Then I tried doing it from mySQL like this (from what I read), but I don't think I have the full answer: foreach($stmt->query('SELECT SUM(Price) FROM Deals') as $row) { echo "<tr>"; echo "<td>Total</td>"; echo "<td> ->".$row['SUM(Price)']."</td>"; echo "</tr>"; } Anyway, if someone could just show me how to get that sum, I would greatly appreciate it because I am missing something here.
  15. TapeGun007

    Framework Recommendation

    Just curious, but is it better to use a php framework say like Laravel than coding just plain php? And which framework do you recommend? I've read several websites that suggest Laravel is the better one but who knows who really posted that, so ... just curious from some real dev's what they prefer.
×

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.