[SOLVED] sql statement to extract a string problem

[geroido]

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

 

 

[geroido]

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

 

[geroido]

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

[geroido]

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