fife

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%')

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

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; }

ok I definitely know I wrote that last one wrong. I just cant get how to write the query at all

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

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.

Barand I totally wish I had your experience. lol only another 30 years to go. Can you please explain further?

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";

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