Jump to content

fife

Members
  • Posts

    381
  • Joined

  • Last visited

Profile Information

  • Gender
    Not Telling

fife's Achievements

Advanced Member

Advanced Member (4/5)

3

Reputation

  1. Hey Guys. Long time no speak to you all. I hope everyone is well. OK so I have a search page which searches through a list of members. Works great and here is the code for that..... mysql_select_db($database_dbcon, $dbcon);$query_allClients = "SELECT userid, fname, sname, uad1, upost, utoken FROM `user` WHERE (branchid=".$_SESSION['cBranch']." AND permid!=".intval(99)." AND permid!=".intval(4).") AND statusid!='".intval(7)."' AND ( lfname LIKE '%$searchfld%' ORlsname LIKE '%$searchfld%' OR fname LIKE '%$searchfld%' OR sname LIKE '%$searchfld%' OR uad1 LIKE '%$searchfld%' OR uad2 LIKE '%$searchfld%' ORuarea LIKE '%$searchfld%' ORupost LIKE '%$searchfld%') ";$query_limit_allClients = sprintf("%s LIMIT %d, %d", $query_allClients, $startRow_allClients, $maxRows_allClients);$allClients = mysql_query($query_limit_allClients, $dbcon) or die(mysql_error());$row_allClients = mysql_fetch_assoc($allClients); do{echo $row_allClients['fname'].' '.$row_allClients['sname'].' '.$row_allClients['upost'] .' '.$row_allClients['uad1']; } while($row_allClients = mysql_fetch_assoc($allClients)); I have now added the ability to have multiple addresses assigned to one member. To do this I have move the forename and the surname of the (landlord) over to a landlord table. When I add a landlord I add the forename and surname to that table and I then add their home address to the users table with a extra field call ishome = '1' else ishome ='0' I have also added the landlordid field to the user table so I can find any address a particular landlord owns. Tables landlord user landlordid userid lfname ishome lsname landlordid fname sname Now when I search for a postcode the original search query still works great. The only thing I want to change is formatting of how it looks. So currently it brings back user 1 pr7ygf 64 some street name user 2 pr75tf 3 some other street name user 3 pr74rt 5 awesome street etc what I want is for it to effectively group the search results under the landlord each address belongs to, so for example....... landlord 1 user1 pr7ygf 64 some street name landlord 2 user 2 pr75tf 3 some other street name user 3 pr74rt 5 awesome street Ive tried to change the query to whats below however i'm sure sure of how to displace the formatted data SELECT user.userid, user.fname, user.sname, user.uad1, user.upost, user.utoken, landlord.lfname, landlord.lsname FROM `user` INNER JOIN landlord ON user.landlordid = landlord.landlordid WHERE (user.branchid=".$_SESSION['cBranch']." AND user.permid!=".intval(99)." AND user.permid!=".intval(4).") AND user.statusid!='".intval(7)."' AND (user.fname LIKE '%$searchfld%' OR user.sname LIKE '%$searchfld%' OR user.uad1 LIKE '%$searchfld%' OR user.uad2 LIKE '%$searchfld%' ORuser.uarea LIKE '%$searchfld%' ORuser.upost LIKE '%$searchfld%')
  2. This is not a question and I know this post doesn't belong here but this is where I started and you guys are the people I want to thank. A big thank you to all PHP Freakers on this site. Over the years I've had many issues with code and this site has taken me from a complete novice knowing absolutely nothing to where I am today. I now have my own web company, I have my own premises and things are going great. I wouldn't be here without you Freakers. You all rock my world thank you very much. When I get on top of my work I will make it my mission to help more on this site. I'll never forget where all this came from. You guys did this for me! So remember that when you help people on this site your not only solving an issue that person has. You're expanding their knowledge so they can be more successful and one day help others just like you. This truly is the best coding help site out there with the best people on it. Again thank you all x
  3. ha. I see it. Had to walk away but I finally get it. Updated my query and it didn't work. I could see there was nothing wrong with the query so went looking for my error. I then noticed that my start time was in 24 hr clock and my end time was in 12hr clock. As soon as I fixed that it worked. Thank you very much Barand Awesome answer as usual. I had to write it out on paper several times before i saw what you were talking about. We'd all be in big trouble without people like you on this forum. Again thank you very much for your time. I'm going to have to start sending you money at this rate Query now looks as follows and works perfectly public function checkClashStylist($bdate, $stime,$etime, $stylist){$query = sprintf("SELECT id FROM table WHERE (sys=%s AND bran=%s AND bookingDate=%s AND idstylist=%s) AND (stime < %s) AND (etime > %s)",// 1:30 >$this->db->GetSQLValueString($this->sys, "int"),$this->db->GetSQLValueString($this->bran, "int"),$this->db->GetSQLValueString($bdate, "date"),$this->db->GetSQLValueString($stylist, "int"),$this->db->GetSQLValueString($etime, "text"),$this->db->GetSQLValueString($stime, "text"));$result = $this->db->query($query);if($result && $this->db->num_rows($result) > 0){//there is a clashreturn true; }//no clash carry onreturn false; }
  4. ok I definitely know I wrote that last one wrong. I just cant get how to write the query at all
  5. ok so maybe I wrote that wrong. I just tried this $query = sprintf("SELECT ib FROM table WHERE (sys=%s AND bran=%s AND bookingDate=%s AND idstylist=%s) AND (bookingETime=%s > bookingSTime=%s) AND (bookingSTime=%s < bookingETime=%s)",$this->db->GetSQLValueString($this->sys, "int"),$this->db->GetSQLValueString($this->bran, "int"),$this->db->GetSQLValueString($bdate, "date"),$this->db->GetSQLValueString($stylist, "int"),$this->db->GetSQLValueString($etime, "text"),$this->db->GetSQLValueString($stime, "text") ,$this->db->GetSQLValueString($stime, "text"),$this->db->GetSQLValueString($etime, "text") );$result = $this->db->query($query); but that didnt work eother and still allowed the bookings
  6. OK I see my flaw. So I need to also parse the end time of the booking im trying to make so I changed my query to $query = sprintf("SELECT id FROM table WHERE (sys=%s AND bran=%s AND bookingDate=%s AND idstylist=%s) AND (%s > bookingSTime) AND (%s < bookingETime)",$this->db->GetSQLValueString($this->sys, "int"),$this->db->GetSQLValueString($this->bran, "int"),$this->db->GetSQLValueString($bdate, "date"),$this->db->GetSQLValueString($stylist, "int"),$this->db->GetSQLValueString($etime, "text"),$this->db->GetSQLValueString($stime, "text"));$result = $this->db->query($query); however this still allow a booking to be booked that should of clashed.
  7. Barand I totally wish I had your experience. lol only another 30 years to go. Can you please explain further?
  8. you seem to be posting in the $user_email instead of the $userID. you should change the $this->id to email to make it easier to read. Under the if ($rs && $rs->num_rows > 1) { return; } just return an error message return "Some error message";
  9. Merry Christmas All Hope everyone here is very well today. ok so I have a booking system where I store the id of the client, idstylist, date, start time and end time of a particular booking. Im trying to write a query that runs when placing a new booking. It basically checks that the stylist is not already busy with another client. I have a working query below $query = sprintf("SELECT id FROM table WHERE (sys=%s AND bran=%s AND bookingDate=%s AND idstylist=%s) AND (%s BETWEEN sTime AND eTime)",$this->db->GetSQLValueString($this->sys, "int"),$this->db->GetSQLValueString($this->bran, "int"),$this->db->GetSQLValueString($bdate, "date"),$this->db->GetSQLValueString($stylist, "int"), $this->db->GetSQLValueString($stime, "text"));$result = $this->db->query($query); The above query works however there is a flaw. Lets say the booking ends a 10:30:00 and I want to book the client in at 10:30:00 for another treatment the query says no. So I changed the query so it now looks like this.... $query = sprintf("SELECT id FROM table WHERE (sys=%s AND bran=%s AND bookingDate=%s AND idstylist=%s) AND (sTime>=%s AND eTime<%s)",$this->db->GetSQLValueString($this->sys, "int"),$this->db->GetSQLValueString($this->bran, "int"),$this->db->GetSQLValueString($bdate, "date"),$this->db->GetSQLValueString($stylist, "int"), $this->db->GetSQLValueString($stime, "text"),$this->db->GetSQLValueString($stime, "text"));$result = $this->db->query($query); Now when I try to make a booking if the stylist has any other bookings whatsoever that day it says there is a clash and doesn't allow me to complete the form. Can someone please help me write a query that will just check if the stylist is already on a job at the $stime of the booking or show me where I've gone wrong with the queries? in the second query between the sTime and eTime I've tried AND , OR and neither work
  10. Barand. Apologies for the late reply. Thank you for your time on this topic. You always seem to go that extra mile. Unfortunately I dont understand your solution and wouldn't be able to fix it if it broke. Ive opted to scratch this feature for now while I think about how to do it differently. Thank you anyway and Merry Christmas to all the PHP Freaks
  11. NotionCommotion this indeed is showing expired tests (with a few changes to the query. Only so I understand it) but it is not showing tests they have not had mysql_select_db($database_dbconnect, $dbconnect); $query_testReq = sprintf("SELECT treatmentTest.idtreatmentTest, treatmentTest.treatmentTestName FROM treatmentTest LEFT JOIN userTest ON userTest.idtreatmentTest = treatmentTest.idtreatmentTest WHERE userTest.iduser=%s AND userTest.userTestExpired=%s", GetSQLValueString(60, "int"), GetSQLValueString(1, "int")); $testReq = mysql_query($query_testReq, $dbconnect); $row = mysql_fetch_assoc($testReq); $totalRows_testReq = mysql_num_rows($testReq); print_r($row); //outputs Array ( [idtreatmentTest] => 1 [treatmentTestName] => Skin test ) there is a second treatment that they have not had which doesnt show in the list
  12. Hi Barand. Thanks for your continued help with my php problems. When the page is run it tests all the current tests the client has had against an expiry date field in the userTest table. If today is > than the expirydate field it updates the Expired field to 1 for each of the test. This is how I find all the expired tests. That bit works ok as I tested it before. The only problem I'm having is the one above. I still have the tables from my previous attempt I've just added the userTest table. NotionCommotion thank you I'll try that in a mo and get back to you on this post
  13. Hey Guys. Im trying to show if the selected user has had all the required tests they need so they can have a treatment at a hairdressers. For example the client needs to of had a skin test before they can have their hair dyed. Im struggling on showing which tests the user still needs to have Currently I'm working with 3 tables. The treatmentTest table which stores all the required tests treatmentTest idtreatmentTest the user table which has the users details user iduser and the usertest table which stores when they had the test and if it has expired (if it has expired is set to 1) userTest iduser idtreatmentTest expired so I need this box to pop up with a warning on each test that is still outstanding but I dont want it to show if there are no tests needed. I obviously need to loop through the results of my query but the query its self is confusing me... <ul class="list-unstyled"> <li>[treatment Name]</li> <li class="text-italic text-muted">Expired [date]</li> <li><a href="#" class="btn btn-success">Update</a></li> </ul> First I tried looping through all the treatments then inside that loop writing the following <?php do { //now foreach treatment test get the resulting test from the usertest table if (isset($row_testReq['idtreatmentTest'])) { $col_userTest = $row_testReq['idtreatmentTest']; } if (isset($_SESSION['cClient'])) { $colname_userTest = $_SESSION['cClient']; } if (isset($expire)) { $colname_userTestExpire = '1'; } mysql_select_db($database_dbconnect, $dbconnect); $query_userTest = sprintf("SELECT * FROM userTest WHERE idtreatmentTest = %s AND iduser = %s AND userTestExpire=%s", GetSQLValueString($col_userTest, "int"), GetSQLValueString($colname_userTest, "int"), GetSQLValueString($colname_userTestExpire, "text")); $userTest = mysql_query($query_userTest, $dbconnect) or die(mysql_error()); $row_userTest = mysql_fetch_assoc($userTest); $totalRows_userTest = mysql_num_rows($userTest); ?> <li class="list-group-item"> <div class="col-sm-2"><i class="fa fa-2x fa-close text-danger"></i></div> <div class="col-sm-9"> <ul class="list-unstyled"> <?php if($totalRows_userTest>0){?> <li><?php echo $row_testReq['treatmentTestName'];?></li> <li class="text-italic text-muted">Expired <?php echo $fn->sqlShowDate($row_userTest['userTestExpire']); ?></li> <li><a href="client-treat-test.php?i=<?php echo $row_testReq['idtreatmentTest'];?>&t=<?php echo $row_testReq['treatmentTestToken'];?>" class="btn btn-success">Update</a></li> <?php } ?> </ul> </div> <div class="clearfix"></div> </li> <?php } while ($row_testReq = mysql_fetch_assoc($testReq)); ?> this doesnt work properly at all. It gave me all the treatments that have expired but if a new treatmenttest was added it wouldnt display that there was a test required. Does anybody know a different way? I also tried getting all the treatments then all the rows in the userTest table then comparing what the differences where but I struggle with arrays comparisons and im not even sure that was the best way to go about it. Am I going to need two queries? One like the loop above and one that looks for expired treatments and one that looks for treatments the user hasn't had or can this be done in one and if so how??
  14. OK scratch that I now have some code to play with. I solved it by adding an extra table called usertest which stores all the tests the user has had and when they where performed.
  15. Hi Guys I need some help with DB design and implementation of a feature my next website will have. OK so I'm building a system that will warn the current user that the current client needs a certain test before they can have their next treatment. Not all treatments required tests. So far I have the following tables user treatment treatmentTest treatmentUser iduser idtreatment idtreatmentTest idtreatment idtreatmentTest treatmentTestExpiry iduser dateOfTreatment (stored in yyyy-mm-dd) (the treatmentTestExpiry stores how long until the treatment will expire e.g +1 month) So every time a user comes in for a treatment we log the treatment in the treatmentuser table. This works fine and I can see what treatments the user has had. So certain treatments required a test to be done before the user can have that actual treatment. What I want is when you view the users profile page it says if they have not had a particular test or a previous test has expired and they will need another. I have no idea how to go about this. Im just wondering if someone could help talk me through it?
×
×
  • 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.