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

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

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

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

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

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.

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?

Thanks Psycho. Where would people like me be without you moderators. This really is the best php forum out there!!

Yeah actually i just tried and fixed it with the following query mysql_select_db($database_dbconnect, $dbconnect);$query_Asset = "SELECT idAsset, AssetName FROM Asset WHERE idLocation = '1' AND idAsset NOT IN (SELECT AssetDocStand.idAsset FROM AssetDocStand INNER JOIN Asset ON AssetDocStand.idAsset = Asset.idAssetWHERE AssetDocStand.idDocumentStandards = '2' AND Asset.idLocation ='1')";$AssetUp = mysql_query($query_Asset, $dbconnect) or die(mysql_error());while ($f = mysql_fetch_assoc($AssetUp)) {$array1[] = $f;}

I have two arrays and all I want from them is the differences to show and any item that existed in both not to show. Please find my workings below mysql_select_db($database_dbconnect, $dbconnect);$query_Asset = "SELECT idAsset, AssetName FROM Asset WHERE idLocation = '1'";$AssetUp = mysql_query($query_Asset, $dbconnect) or die(mysql_error()); $array1 = array(); while ($f = mysql_fetch_assoc($AssetUp)) {$array1[] = $f;} mysql_select_db($database_dbconnect, $dbconnect);$query_DocUp = "SELECT Asset.idAsset, Asset.AssetName FROM AssetDocStand INNER JOIN Asset ON AssetDocStand.idAsset = Asset.idAssetWHERE AssetDocStand.idDocumentStandards = '2' AND Asset.idLocation ='1'";$DocUp = mysql_query($query_DocUp, $dbconnect) or die(mysql_error());$array2 = array(); while ($f = mysql_fetch_assoc($DocUp)) {$array2[] = $f;} print_r($array1); echo "<br><br><br>"; print_r($array2); shows the following when the page is loaded. Array ( [0] => Array ( [idAsset] => 10000005 [AssetName] => HP ) [1] => Array ( [idAsset] => 10000006 [AssetName] => HP Server ) [2] => Array ( [idAsset] => 10000009 [AssetName] => HP Laptop ) [3] => Array ( [idAsset] => 10000010 [AssetName] => Office Printer ) [4] => Array ( [idAsset] => 10000023 [AssetName] => test ) ) Array ( [0] => Array ( [idAsset] => 10000023 [AssetName] => test ) ) Now when i type $array3 = array_diff($array1, $array2 ); print_r($array3); I get Array ( ) Ive also tried array_diff_key() which also produces nothing. All I want to be left with is Array ( [0] => Array ( [idAsset] => 10000005 [AssetName] => HP ) [1] => Array ( [idAsset] => 10000006 [AssetName] => HP Server ) [2] => Array ( [idAsset] => 10000009 [AssetName] => HP Laptop ) [3] => Array ( [idAsset] => 10000010 [AssetName] => Office Printer ))

Infact this allows me to change the question almost. No From this way I have these 2 arrays Array ( [idAsset] => 10000005 [AssetName] => HP ) Array ( [idAsset] => 10000006 [AssetName] => HP Server ) Array ( [idAsset] => 10000009 [AssetName] => HP Laptop ) Array ( [idAsset] => 10000010 [AssetName] => Office Printer ) Array ( [idAsset] => 10000023 [AssetName] => test ) Array ( [idAsset] => 10000023 [AssetName] => test ) Is it possible to find the difference only and iterate through them?

Ok. I have done this which is exactly what we are after if I only want counts but if I want the names of the assets left it no longer works. Its almost like I need the differences between the 2 arrays. mysql_select_db($database_dbconnect, $dbconnect);$query_Asset = "SELECT idAsset, AssetName FROM Asset WHERE idLocation = '1'";$AssetUp = mysql_query($query_Asset, $dbconnect) or die(mysql_error());$array1 = mysql_fetch_assoc($AssetUp);$totAsset = mysql_num_rows($AssetUp); mysql_select_db($database_dbconnect, $dbconnect);$query_DocUp = "SELECT AssetDocStand.idAsset FROM AssetDocStand INNER JOIN Asset ON AssetDocStand.idAsset = Asset.idAssetWHERE AssetDocStand.idDocumentStandards = '2' AND Asset.idLocation ='1'";$DocUp = mysql_query($query_DocUp, $dbconnect) or die(mysql_error());$array2 = mysql_fetch_assoc($DocUp);$totStand = mysql_num_rows($DocUp); $tot = $totAsset - $totStand;echo $tot;

Guys. This is still not right. Let me explain where we are up to. Psycho if I dump your query after I have removed the archive field and directly inputted values I get this Array ( [idAssetDocStand] => ) Array ( [idAssetDocStand] => ) Array ( [idAssetDocStand] => ) Array ( [idAssetDocStand] => ) Array ( [idAssetDocStand] => 6 ) Array ( [idAssetDocStand] => 10 ) I've now removed irrelevant info from the table and inserted the values directly to make it easier to find what I am after. mysql_select_db($database_dbconnect, $dbconnect);$query_DocUp = "SELECT idAssetDocStand FROM Asset LEFT JOIN AssetDocStand ON AssetDocStand.idAsset = Asset.idAsset WHERE (AssetDocStand.idAsset IS NULL OR AssetDocStand.idDocumentStandards <> '2') AND Asset.idLocation = '1'";$DocUp = mysql_query($query_DocUp, $dbconnect) or die(mysql_error());$totalRows_DocUp = mysql_num_rows($DocUp); I'm just trying to count all assets that don't have a risk assessment in the current location. In my case it should be 4. I have 5 assets. One of them has a row in the AssetDocStand table saying it has a risk assessment, Another way of saying it is: "I need all the assets in the current location that dont have a record in the AssetDocStand table where the idDocumentStandards = 2 " I think because I didn't fully understand what I was after I've done a poor job of explaining what I need. That last explanation above is totally accurate

Gosh I hope I make sense because I'm confusing myself now

Hi Psycho That seems to give me a count of rows in the AssetDocStand table where the current asset has a record. If that's the way I explained it im sorry. What I really want is all the assets from the current location that don't have a document uploaded that belongs to standard 2 for example

Sorry matey. right so asset 10 (laptop) in location 5 (head office) needs to conform to the standard with the id of 6 (build directive). I'm trying to use my AssetDocStand table to say that asset (laptop) has a document uploaded that conforms to the standard of the (build directive). The field idAssetDocStand is irrelevant. I just need to pull something back from the AssetDocStand table so I can count how many rows are returned. We could select * if need be. Ive reposted the query to represent this. please just tell me if i'm still not making sense. i hope I am. AssetDocStand Laptop, idDocument, Build Directive function GetUploadedDocsNoAsset($doctype,$archive, $location){ $colname_DocUp = "-1";if (isset($doctype)) { $colname_DocUp = $doctype;} $colname_Archive = "-1";if (isset($archive)) { $colname_Archive = $archive;}$colname_Location = "-1";if (isset($location)) { $colname_Location = $location;} $query_DocUp = sprintf("SELECT AssetDocStand.* FROM AssetDocStand LEFT JOIN Asset ON AssetDocStand.idAsset = Asset.idAsset WHERE AssetDocStand.idDocumentStandards = %s AND AssetDocStand.Archive=%s AND Asset.idLocation = %s", GetSQLValueString($colname_DocUp, "int"),GetSQLValueString($colname_Archive, "text"),GetSQLValueString($colname_Location, "int"));$DocUp = mysql_query($query_DocUp);$totalRows_DocUp = mysql_num_rows($DocUp);return $totalRows_DocUp;}

Jazzman1 I tried your change but its only giving me all the assets that do have a document uploaded not dont.