geroido

Hi The nested query below gives me results like geroido geroido geroido lynnie lynnie paulie paulie The outer query then gives me just the distinct values like: geroido lynnie paulie It all works well but I need the CustFName, CustSName, StreetAddr, Town, County also. Can you tell me what to add to the outer query to retrieve these values aswell? I've tried: $query = "select distinct (cust), CustFName, CustSName, StreetAddr, Town, County from (SELECT DISTINCT SUBSTRING(Ordernum,1,LENGTH(Ordernum)-14) as cust, CustFName, CustSName, StreetAddr, Town, County FROM orderdetails )A "; but the query stops working. $query = "select distinct (cust) from (SELECT DISTINCT SUBSTRING(Ordernum,1,LENGTH(Ordernum)-14) as cust, CustFName, CustSName, StreetAddr, Town, County FROM orderdetails )A ";

Hi guys The previous post is wrong. I am in fact getting distinct values but they are distinct orders so if the table holds the following 4 values, I still get geroido being returned twice geroido20080913105404 geroido20080913105404 geroido20080913105404 geroido20080825105322 Can I wrap another query around this one to then get just distinct values from the result of the below query? So the query below works well but now I want to put the results through a second query to get distinct values. $query = "SELECT DISTINCT SUBSTRING(Ordernum,1,LENGTH(Ordernum)-14) as cust, CustFName, CustSName, StreetAddr, Town, County FROM orderdetails";

Hi I've altered my sql to the following but still getting all of the records not getting distinct values. It is working however to extract just the first part. $query = "SELECT DISTINCT SUBSTRING(Ordernum,1,LENGTH(Ordernum)-14) as cust, CustFName, CustSName, StreetAddr, Town, County FROM orderdetails"; Any ideas

Really it sounds simple but as usual is not. A field in my table (Ordernum) contains values like: geroido20080913105404 geroido20080825105322 paulie20080623204307 lynnie20081102175032 I want to go through these records, extract just the name part(geroido, paulie, lynnie etc.) but I want just a distinct result. So in the example above, although there are two instances for geroido, I just want geroido to be returned once and that goes for any other name that has multiple entries.

yeah, I know that. It's a problem but too late now. I'm on a deadline Do you know of a mysql substring function where I could scan these records, extract the first part and then just show the distinct ones?

Hi I have entries in my database which are unique order numbers. they look like this like this: Paulie20080913202737 geroido20080913105404 geroido20080913105404 geroido20080825105322 lynnie20080825111708 What I want to do is extract only the first part i.e. geroido, lynnie or paulie. I am achieving that with the sql statement below - the main line being $row->cust = preg_replace('/[0-9]/', '', $row->cust);. This extracts everything except number 0-9. It works great. What I get is: Paulie geroido geroido geroido lynnie However, because I know that geroido is one customers with many orders, what I really want to do is just display a distinct value so I want to have just Paulie geroido lynnie Can you help me out? $query = "SELECT DISTINCT(Ordernum) as cust, CustFName, CustSName, StreetAddr, Town, County from orderdetails group by Ordernum "; $results = mysql_query($query, $link) or die("Sorry, the database is unavailable"); if(mysql_num_rows($results) > 0){ ?><TABLE border=2><TR><TD><Font size=3 color="black">Select customer</TD><TD><Font size=3 color="black">Business name</TD></FONT><TD><Font size=3 color="black">Customer name</FONT></TD><TD><Font size=3 color="black">Town</TD></FONT><TD><Font size=3 color="black">County</TD></FONT><TD><Font size=3 color="black">Service type</TD></FONT></TR><? while($row = mysql_fetch_object($results)){ $row->cust = preg_replace('/[0-9]/', '', $row->cust); echo ("<tr><td>"); echo "<a href='cls_indcust.php?id=" .$row->cust. "'>Send comment</a>"; echo ("</td><td>"); echo $row->cust; echo ("</td><td>"); echo $row->CustFName . " " .$row->CustSName; echo ("</td><td>"); echo $row->StreetAddr; echo ("</td><td>"); echo $row->Town; echo ("</td><td>"); echo $row->County; echo ("</td>"); echo ("</tr>"); } } } else{ echo("<i>There are currently have no customers in the database</i>"); }

Hi I have managed to find the problem. It was mostly unrelated to the sql. Sorry, but it appears that the $_SESSION variable I was using to store the clientID had some white space in it so it couldn't find the record. I used the trim function to clean it up and hey presto alls well. The left join instead of inner join was also part of the problem. Thanks

Hi This is further to a post I did yesterday but still not working. I'll just explain a little further. I have two tables which look I represent below(simplified for this example). The important field is MenuItemID. What I want to do is search through the tables using a clientID and extract all orders from orderdetails that are for a particular clientID only. For example, OrderNum 1 has MenuItemID 69 and 67. These two can only belong to clientID CLS_carey4780bc7a6e1bc. So if I have the clientID, I want to get every order that relates to that clientID only. OrderNum 2 has MenuItemID 71 and 80 and these can only belong to CLS_owen4785f5b9ce484 etc. You can see that CLS_patrick3425dc4b9sd has no orders. I've tried the following sql statement but it is getting every record regardless of the clientID. Can you help? $query = "SELECT orderdetails.Ordernum, SUM(orderdetails.itemcost * orderdetails.ItemQuantity) as mytotal, orderdetails.CustFName, orderdetails.CustSName, orderdetails.StreetAddr, orderdetails.Town, orderdetails.County, orderdetails.TelNum, orderdetails.orddate, ClientID from orderdetails left join menuitemdetails on orderdetails.MenuItemID=menuitemdetails.MenuItemID and ClientID = '".$_SESSION['clsrevid']."' group by orderdetails.Ordernum "; The clientID is held in $_SESSION['clsrevid'] and could be any clientID. menuitemdetails clientID MenuItemID MenuItemName CLS_carey4780bc7a6e1bc 69 chicken pie CLS_carey4780bc7a6e1bc 67 Soup CLS_owen4785f5b9ce484 71 Pizza CLS_owen4785f5b9ce484 80 Apple pie CLS_patrick3425dc4b9sd 91 Vol au vents orderdetails OrderNum MenuItemID CustFName 1 69 John 1 67 John 2 71 mary 2 80 mary

Hi I have two tables that I'm trying to join and get results from - orderdetails and menuitemdetails. What I'm trying to do is pull all the the orders from orderdetails pertaining to a particular client. The client id is held in menuitemdetails and I use $_SESSION['clsrevid']." to compare it. Now the query is getting every record regardless of the id held in $_SESSION['clsrevid'].". Any idea why it's not just getting the records of the specific client. the clientID is of the form CLS_owen4785f5b9ce484 or CLS_carey4780bc7a6e1bc. I've echoed the query and it seems ok. The echo result is: SELECT orderdetails.Ordernum, SUM(orderdetails.itemcost * orderdetails.ItemQuantity) as mytotal, orderdetails.CustFName, orderdetails.CustSName, orderdetails.StreetAddr, orderdetails.Town, orderdetails.County, orderdetails.TelNum, orderdetails.orddate, ClientID from orderdetails left join menuitemdetails on orderdetails.MenuItemID=menuitemdetails.MenuItemID and ClientID = ' CLS_owen4785f5b9ce484' group by orderdetails.Ordernum actual query: $query = "SELECT orderdetails.Ordernum, SUM(orderdetails.itemcost * orderdetails.ItemQuantity) as mytotal, orderdetails.CustFName, orderdetails.CustSName, orderdetails.StreetAddr, orderdetails.Town, orderdetails.County, orderdetails.TelNum, orderdetails.orddate, ClientID from orderdetails left join menuitemdetails on orderdetails.MenuItemID=menuitemdetails.MenuItemID and ClientID = '".$_SESSION['clsrevid']."' group by orderdetails.Ordernum ";

I have found the solution to my problem with the following sql statement: $query = "Select SUBSTRING(Ordernum,1, $len) as string, Ordernum, MenuItemID, orddate, ordtime FROM orderdetails WHERE SUBSTRING(Ordernum,1, $len) = '".$_SESSION['username']."' group by Ordernum order by orddate asc "; Thanks

Hi This is further to an earlier post. I'm getting the length of a any particular username. Each ordernum in the table has the username concatenated on the to beginning of it as in: geroido20080825122526 'geroido' being the username I go through each record extracting the username from each ordernum so that I can display this users orders. How can I add a where clause where the substring extracted is equal to the username ($_SESSION['username']) so that in this example geroido = geroido. I store the result in 'string'. Can I add something like WHERE string = '".$_SESSION['username']."' $len = strlen($_SESSION['username']); echo $len; $query = "Select SUBSTRING(Ordernum,1, $len) as string, Ordernum, MenuItemID, orddate, ordtime FROM orderdetails group by Ordernum ";

Hi I solved most of my problem with the substring function $query = "Select SUBSTRING(Ordernum,1, $len) as string, Ordernum, MenuItemID, orddate, ordtime FROM orderdetails group by Ordernum ";

Hi I had a look at the substring function. Can you tell me how to start counting at zero (the beginning of the string) to the designated string length held in $len. I've tried: SELECT SUBSTRING(Ordernum,0, $len) and also how do I assign the result so I can see it as in for example: SELECT SUBSTRING(Ordernum,0, $len) as string I can't see how to do this with the code examples below. mysql> SELECT SUBSTRING('Quadratically',5); -> 'ratically' mysql> SELECT SUBSTRING('foobarbar' FROM 4); -> 'barbar' mysql> SELECT SUBSTRING('Quadratically',5,6); -> 'ratica' mysql> SELECT SUBSTRING('Sakila', -3); -> 'ila' mysql> SELECT SUBSTRING('Sakila', -5, 3); -> 'aki' mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2); -> 'ki'

Hi I'm looking for a way to solve this problem. I have database entries in the form: geroido20080825122526 These are unique order numbers, the first part (geroido) being the users username. So when a user logs in, I have their username say 'geroido' for example. Firstly, I get the username string length as follows: $len = strlen($_SESSION['username']); In the case of 'geroido' this will give me the length of 7. What I now want to do is form an sql statement that can trawl through my table, extract the first 7 ($len) characters of the ordernum or however long the particular username is and if they match the username display them else you have no orders with us. So I was thinking something like: $query = "Select DISTINCT(Ordernum), MenuItemID, orddate, ordtime FROM orderdetails where substr(Ordernum, 0, $len) = '".$_SESSION['username']);."' group by Ordernum "; Is this possible. Sorry if this is confusing

Hi I'm having a problem here that I didn't anticipate. Can you help? I have database entries in the form geroido20080825122526. They are unique order numbers (Ordernum). The first part (geroido) is the username. What I'm trying to do is allow the user to view all of his/her orders. So I get the length of the current username and I trawl through the database extracting the first characters of each Ordernum based on the username length. If the characters match the username, then this is that usernames record and I display them. This all works very well. What I didn't anticipate is if a user has no orders. I was expecting it to go directly to the else statement 'Sorry, you have not placed any orders with us' but it doesn't. I realise my mistake is that of course it will find records and not go to the else statement even if the user has no order records, it finds everybody elses orders. It displays nothing cos they don't match the string. I was wondering how to fix this. My sql statement would seem to be the problem. Can I include the 'substr($row->Ordernum, 0, $len);' in an sql statement so that it is looking for a particular users records? So I was thinking something like: $query = "Select DISTINCT(Ordernum), MenuItemID, orddate, ordtime FROM orderdetails where substr($row->Ordernum, 0, $len) = '".$_SESSION['username']);."' group by Ordernum "; Is this possible. Sorry if this is confusing $len = strlen($_SESSION['username']); echo $len; $query = "Select DISTINCT(Ordernum), MenuItemID, orddate, ordtime FROM orderdetails group by Ordernum "; $results = mysql_query($query, $link) or die("Error performing query"); if (mysql_num_rows($results) > 0){ ?><TABLE cellspacing="10" ><TR><TD><font color="black" size=4>Select</FONT></TD><TD><font color="black" size=4>Order number</font></TD><TD><font color="black" size=4>Date</font></TD><TD><font color="black" size=4>Time</font></TD></TR><? while($row = mysql_fetch_object($results)){ $str = substr($row->Ordernum, 0, $len); if ($str == $_SESSION['username']){ ?><form id="form2" method="get" action="myords.php"><? echo ("<tr>"); echo ("<td>"); echo "<a href='myords.php?resid=" .$row->Ordernum. "'>View order</a>"; echo ("</td>"); echo ("<td> "); echo $row->Ordernum ; echo ("</td>"); echo ("<td>"); echo $row->orddate ; echo ("</td>"); echo ("<td>"); echo $row->ordtime ; echo ("</td>"); echo("</tr>"); } } } else{ echo("<H3><i>Sorry, you have not placed any orders with us </i>"); echo ("</H3>"); } echo ("</FORM>"); echo ("</TABLE>"); ?>