Jump to content

Kristoff1875

Members
  • Posts

    242
  • Joined

  • Last visited

Everything posted by Kristoff1875

  1. You sir, are, as your status suggests, an absolute guru. What a star. Thank you. I found that after changing a few of the key tables etc, it worked but missed a few vital details off the list i've got, however by changing it to: SELECT c.* , j.* , MAX(p.PostDate) as postedOn FROM Clients c INNER JOIN Jobs j ON c.ClientId=j.ClientId LEFT JOIN Posts p ON j.JobNumber=p.JobNumber AND c.ClientId=p.ClientId GROUP BY c.ClientId , j.JobNumber ORDER BY postedOn DESC So selecting all from the tables, the results show fine. Could I be really cheeky though, would you be able to explain what that code is actually doing? I love that you've helped out so much, but I want to understand what it's doing as at the moment, it's working, but i've learnt nothing! Many thanks again.
  2. Sorry to bump but tried loads of things and can't seem to get it to work, any ideas much appreciated.
  3. I can't seem to get it to sort by the date now, any suggestions much appreciated: SELECT Jobs.*, MAX(PostDate) as postDate FROM Jobs LEFT JOIN Posts ON Posts.JobNumber=Jobs.JobNumber GROUP BY Jobs.ClientID, Jobs.JobNumber ORDER BY postDate
  4. This is where it gets a bit (more?!) confusing... I'm currently working on the admin side which is just for me, I have a list of all of the jobs, from all of the clients. The client side will, I assume, be much simpler, seeing as I will only be calling for the jobs relating to their ClientID. At the moment i've got: SELECT Jobs.*, MAX(PostDate) as postDate FROM Jobs LEFT JOIN Posts ON Posts.JobNumber=Jobs.JobNumber GROUP BY Jobs.ClientID ORDER BY postDate Which is showing all of the jobs, but not in the correct order. My hunch was that by Grouping by the the ClientID rather than by JobNumber, it'd show all of the jobs, but now I only see one of that client's jobs in the list, plus it's not in any order. Update: Think i've sorted the problem with it only showing 1 from each client or 1 of each job: SELECT Jobs.*, MAX(PostDate) as postDate FROM Jobs LEFT JOIN Posts ON Posts.JobNumber=Jobs.JobNumber GROUP BY Jobs.ClientID, Jobs.JobNumber ORDER BY postDate Just testing the sorting! ANOTHER UPDATE: It's not sorting with the latest item to have a post at the top, to be honest, I can't make out an order to it at all! Scrap that, It's sorting by JobNumber. All the 1's are together, then all the 2's and so on.
  5. Right, I think i've noticed why it's not displaying all of the jobs... Each Client has a job number relating to the current job, so each Client will have a JobNumber 1 and at the moment if ClientID 12345 JobID 1 is being called, then ClientID 12346 or any client's after this JobID 1 is not being shown...
  6. Thanks for this, I was only getting the job numbers display in the list so used: SELECT Jobs.*, MAX(PostDate) as postDate FROM Jobs LEFT JOIN Posts ON Posts.JobNumber=Jobs.JobNumber GROUP BY Jobs.JobNumber ORDER BY PostDate Which is displaying results, but not the recently updated ones first, and it's also not displaying all of the results.
  7. Column 'JobNumber' in field list is ambiguous Assuming this is because JobNumber is in both tables so it doesn't know which I am calling?
  8. Thanks for this buddy, I don't have a JobID and a JobTitle, I only have JobNumber and ClientID. I've used the following changing to my column's names etc: $result = mysql_query("SELECT JobNumber, MAX(PostDate) as postDate FROM Jobs LEFT JOIN Posts ON Posts.JobNumber=Jobs.JobNumber GROUP BY JobNumber ORDER BY postDate"); As my date was PostDate not PostedOn, but i'm currently getting: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/gdesignz/public_html/clients/admin/index.php on line 220 Thanks again for your help, any pointers on what i've missed/messed up would be ace.
  9. I've set up a client area for my clients to have a record of communication kept as discussed here: http://forums.phpfreaks.com/topic/272466-relating-new-rows-in-2-different-tables/ And all is working well, however i'm wondering if I can make a slight tweak and if anyone could give me a heads up on how to go about it. Currently i've got a jobs list on the main page, that lists everything from the Jobs table with no restrictions. Within all of these jobs are various posts that are stored in the "Posts" table. Currently the jobs are sorted by the newest job added to "Jobs", would it be possible to show the one with the latest post in "Posts" at the top? And whilst doing that, would it be possible to add an unread message icon like the type you get on forums to notify you of an unread message. As I say, not looking for answers, more pointers of where to start! Thanks in advance Chris
  10. Every client has a number though, they're assigned a number before a sample is even assigned. This script that i've put together is just for client's to be able to login and check progress of a job along with leaving messages so i've got a record of all interactions, rather than having some Skype, Facebook, Voicemail messages etc, they're all in one place.
  11. You're an absolute legend Barand, that works perfectly, just what I was after. Thanks again. Is that now 2 or 3 beers I owe you when you're next in Birmingham!?
  12. Getting empty values when using the MAX function, when removed it displays all the job numbers related to that client.
  13. Very possibly a noob/blonde/tired (delete as you wish) moment, but I was assuming that it'd be used to call the maximum value of a load of choices? But seeing as I have no choices, i'm guessing it will call the highest value relating to the client ID? If so how would I I go about echoing the value in a field, but + 1?
  14. As I said to begin with, I'm sure it was going to seem confusing, and very difficult to explain. The basic reason for the way of storing them this way is because I already have a client job order in place and as each client has jobs in numerical order, I can relate to them really quickly. They can tell me it's job number 26 and then I go to their client folder, and locate job 26 in a list of jobs 1-30 for example. At the moment i'm using: "INSERT INTO Jobs (ClientID, JobNumber) values '{$_POST['ClientID']}', '{$_POST['JobNumber']}'" And simply adding the job number in a field, so i'm just trying to simplify that part slightly so it automatically gives me the next number for the JobNumber field for that ClientID (I have changed that code slightly as I actually select a field from the database to find the ClientID) Infact, just thinking about it, is it possible to find the highest number in a column where ClientID = ClientID and then to use a + 1 value in PHP? Just thinking I have a dropdown of all the Client's and then could pre-populate the JobID field on select of the Client dropdown, with the next number before I send it to the form?
  15. Thanks for the quick reply. I have an auto-increment primary key, but the ID I want to relate is related to my filing system. For example I have client ID's, and then job id's that follow. For example CLIENT007's first job id is job 1, then the next job is job 2. If I use the auto-increment on the jobs table, then I have a situation where it could go: JobID: 1; Client: CLIENT007; JobID: 2; Client: CLIENT002; JobID: 3; Client: CLIENT007; Where CLIENT007 would never have had a Job 2.
  16. Season's Greetings guys, I'm not sure if this is possible, i'm a semi noob so go easy on me! I have 2 tables, Clients & Jobs. When I post a new Job, i've got a page that adds jobs as a new row, with the chosen Client ID, so for example if the Client ID is 'CLIENT007' and the job number is '007', In my Jobs table, I get "ClientID: CLIENT007; JobID: 007;" Hope this makes sense so far. Anyway is there a way of me finding the last job number and incrementing the next job? So instead of having to enter a JobID it will do something like: Client ID = CLIENT007 last JobID = 007, so next JobID is 008. Bare in mind that I can't simply auto increment the job ID as I could have CLIENT002 with JobID 123. Hope this makes some sort of sense!! Many thanks in advanced!
  17. Should this be right? INSERT INTO Jobs (ClientID, Username, JobNumber, Description, Status) SELECT ClientID, '{$_POST['ClientUsername']}', '{$_POST['JobNumber']}', '{$_POST['Description']}' '{$_POST['Status']}' FROM Clients WHERE Username = '{$_POST['ClientUsername']}'" Because I want it to find the ClientID based on the username that is posted. At the moment i'm getting: Column count doesn't match value count at row 1 Ah hold on, missing comma! With the comma it works. Thanks so much
  18. Hi guys, i'm currently using: mysql_query("INSERT INTO Jobs (Username, JobNumber, Description, Status) VALUES ('$_POST[ClientUsername]', '$_POST[JobNumber]', '$_POST[Description]', '$_POST[status]')") or die(mysql_error()); Which is inserting all the right values in to my database, only thing is I also want "ClientID" from Clients table to be fetched and put in like this: mysql_query("INSERT INTO Jobs (Username, ClientID JobNumber, Description, Status) VALUES ('$_POST[ClientUsername]', $ClientID, '$_POST[JobNumber]', '$_POST[Description]', '$_POST[status]')") or die(mysql_error()); As I say, this is on a different table. I've tried lots of different ways, like INSERT SELECT etc, but can't seem to have any joy. Any help much appreciated. Thanks
  19. Have I added this in the wrong place? $headers = 'MIME-Version: 1.0' . "\r\n"; $headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n"; $headers .= 'From: *** <noreply@***.com>' . "\r\n"; mail( $info['email'],$subject,$message,$headers ); echo $info['email'].' done.'; if($info = mysql_fetch_array( $data )) $id=$info['id']; $arrayIds[] = $info['id']; } $updateQuery = "UPDATE completed SET followupsent = NOW() WHERE id IN ("; foreach($arrayIds as $id) { $updateQuery .= "$id, "; } $updateQuery = rtrim($updateQuery, ", ") . ")"; $update = mysql_query($updateQuery) or die(mysql_error()); ?>
  20. Right the page is all working now, but I have one more question about this. The code at the moment is like this: <?php mysql_connect("localhost", "***", "***") or die(mysql_error()); mysql_select_db("***") or die(mysql_error()); $data = mysql_query(" SELECT * FROM completed WHERE followupsent='0000-00-00 00:00:00' AND valuesent + INTERVAL 7 DAY <= NOW() ") or die(mysql_error()); if($info = mysql_fetch_array( $data )) $id=$info['id']; if ($info['valuation'] != ""){ $body='email body'; $subject = '***'; $message = $body; $headers = 'MIME-Version: 1.0' . "\r\n"; $headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n"; $headers .= 'From: *** <noreply@***.com>' . "\r\n"; mail( $info['email'],$subject,$message,$headers ); echo $info['email'].' done.'; } mysql_connect("localhost", "***", "***") or die(mysql_error()); mysql_select_db("***") or die(mysql_error()); $update = mysql_query("UPDATE completed SET followupsent = NOW() WHERE id = '$id'") or die(mysql_error()); ?> Will that loop through all the applicable fields and send the email?
  21. Have used: AND valuesent + INTERVAL 7 DAY <= NOW() And it's working I think!
  22. Right, getting there, but another problem with the 7 day code: $data = mysql_query(" SELECT * FROM completed WHERE id='724682' AND followupsent='0000-00-00 00:00:00' AND DATE(valuesent) < DATE_SUB( NOW(), INTERVAL 7 DAY ") Throwing up: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6 Removing the date line and using: $data = mysql_query(" SELECT * FROM completed WHERE id='724682' AND followupsent='0000-00-00 00:00:00' ") Works perfectly, but obviously doesn't check for items a week old... The ID field is going to be removed, but I need it now so it only sends to the test account and not all of the other members.
×
×
  • 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.