Extracting a substring from a database record problem

[geroido]

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

[GingerRobot]

You really shouldn't be storing data like this. I would expect an order table to have an order number and user id of the user who ordered the item - you therefore shouldn't be storing the user's name in the order table, and certainly not as part of the order number. It would be worthwhile reading up on database normalisation.

 

However, as an immediate solution to your problem, you should use the MySQL SUBSTRING function. You can then select only those rows where the first part on the order number is the username. This would also prevent the inefficient method of selecting all the rows and cycling through them.

[geroido]

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'

 

[GingerRobot]

1.) You need to set the position to 1, not 0:

 

SELECT SUBSTRING(Ordernum,1, $len)

 

2.) Exactly like that