pkenny9999 Posted January 14, 2008 Share Posted January 14, 2008 I am trying to make the results from a query output in a certain order, I realize that I have some other issues with my code, but I'm going problem at a time. How do I take the results and Order them by the field 'address' and also how do I get it to omit the street number and sort by the first letter of the street name? Here's the code so far, I have tried the following code $query = "SELECT * FROM Address ORDERED BY address;" but I recieve Query Failed. /* Performing SQL query */ $query = "SELECT * FROM Address"; $first_one = 1; if($lastname != "") { if($first_one == 1) { $query = "$query WHERE"; $first_one = 0; $query = "$query lastname LIKE '%$lastname%'"; } else { $query = "$query AND lastname LIKE '%$lastname%'"; } } if($address != "") { if($first_one == 1) { $query = "$query WHERE"; $first_one = 0; $query = "$query address LIKE '%$address%'"; } else { $query = "$query AND address LIKE '%$address%'"; } } if($city != "") { if($first_one == 1) { $query = "$query WHERE"; $first_one = 0; $query = "$query city LIKE '%$city%'"; } else { $query = "$query AND city LIKE '%$city%'"; } } if($zipcode != "") { if($first_one == 1) { $query = "$query WHERE"; $first_one = 0; $query = "$query zipcode LIKE '%$zipcode%'"; } else { $query = "$query AND zipcode LIKE '%$zipcode%'"; } } if($territory != "") { if($first_one == 1) { $query = "$query WHERE"; $first_one = 0; $query = "$query territory = '$territory'"; } else { $query = "$query AND territory = '$territory'"; } } $result = mysql_query($query) or die("Query failed"); /* Printing results in HTML */ $numresult = mysql_num_rows($result); print "<h4 align=center><font color=#3C94C4>"; print $numresult; print " results match your search<h4></font>\n"; print "<table cellpadding=6 border=0 align=center>\n"; print "\t<tr align=left bgcolor=#CCCCCC>\n\t\t<td width=30><strong>ID</strong></td>\n\t\t<td width=125><strong>Last Name</strong></td>\n\t\t<td width=125><strong>First Name</strong></td>\n"; print "\t\t<td width=175><strong>Address</strong></td>\n\t\t<td width=125><strong>City</strong></td>\n"; print "\t\t<td width=75><strong>Territory</strong><td width=40><strong>Delete</strong></td></tr>\n"; while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) { </script> <tr align="left"> <td><font size="1"><?php print "<a href='../address_edit.php?idx="; print $line["address_id"]; print "'target=_new>"; print $line["address_id"]; print "</a>";?></font></td> <td><font size="1"><?php echo $line["lastname"]; ?></font></td> <td><font size="1"><?php echo $line["firstname"]; ?></font></td> <td><font size="1"><?php echo $line["address"]; ?></font></td> <td><font size="1"><?php echo $line["city"]; ?></font></td> <td><font size="1"><?php echo $line["territory"]; ?></font></td> <td><font size=1><?php print "<a href='../delete_address.php?idx="; print $line["address_id"]; print "'target=_blank>"; print "<img src=../images/trash_icon.jpg border=0 alt='Delete Address' /img>"; print "</a></font></td>"; ?> </tr> <?php } print "</table>\n"; Quote Link to comment https://forums.phpfreaks.com/topic/85970-solved-order-by-problem/ Share on other sites More sharing options...
adam291086 Posted January 14, 2008 Share Posted January 14, 2008 $query = "SELECT * FROM Address ORDERED BY address should be $query = "SELECT * FROM Address ORDER BY address" Quote Link to comment https://forums.phpfreaks.com/topic/85970-solved-order-by-problem/#findComment-438983 Share on other sites More sharing options...
pkenny9999 Posted January 14, 2008 Author Share Posted January 14, 2008 Sorry, I mistyped the command I used, I used $query = "SELECT * FROM Address ORDER BY address"; and I get Query failed. Quote Link to comment https://forums.phpfreaks.com/topic/85970-solved-order-by-problem/#findComment-438992 Share on other sites More sharing options...
Ken2k7 Posted January 14, 2008 Share Posted January 14, 2008 Sorry, I mistyped the command I used, I used $query = "SELECT * FROM Address ORDER BY address"; and I get Query failed. Where did you use that? I don't see it in the code. Also, please use tags. Quote Link to comment https://forums.phpfreaks.com/topic/85970-solved-order-by-problem/#findComment-438994 Share on other sites More sharing options...
revraz Posted January 14, 2008 Share Posted January 14, 2008 Post the mysql_error that you get. It will state where it fails. Use ORDER BY Address instead of address if the field has a cap in it. Quote Link to comment https://forums.phpfreaks.com/topic/85970-solved-order-by-problem/#findComment-438997 Share on other sites More sharing options...
pkenny9999 Posted January 14, 2008 Author Share Posted January 14, 2008 This is the code that does NOT work: /* Performing SQL query */ $query = "SELECT * FROM Address ORDER BY address"; $first_one = 1; if($lastname != "") { if($first_one == 1) { $query = "$query WHERE"; $first_one = 0; $query = "$query lastname LIKE '%$lastname%'"; } else { $query = "$query AND lastname LIKE '%$lastname%'"; } } if($address != "") { if($first_one == 1) { $query = "$query WHERE"; $first_one = 0; $query = "$query address LIKE '%$address%'"; } else { $query = "$query AND address LIKE '%$address%'"; } } if($city != "") { if($first_one == 1) { $query = "$query WHERE"; $first_one = 0; $query = "$query city LIKE '%$city%'"; } else { $query = "$query AND city LIKE '%$city%'"; } } if($zipcode != "") { if($first_one == 1) { $query = "$query WHERE"; $first_one = 0; $query = "$query zipcode LIKE '%$zipcode%'"; } else { $query = "$query AND zipcode LIKE '%$zipcode%'"; } } if($territory != "") { if($first_one == 1) { $query = "$query WHERE"; $first_one = 0; $query = "$query territory = '$territory'"; } else { $query = "$query AND territory = '$territory'"; } } $result = mysql_query($query) or die("Query failed"); /* Printing results in HTML */ $numresult = mysql_num_rows($result); print "<h4 align=center><font color=#3C94C4>"; print $numresult; print " results match your search<h4></font>\n"; print "<table cellpadding=6 border=0 align=center>\n"; print "\t<tr align=left bgcolor=#CCCCCC>\n\t\t<td width=30><strong>ID</strong></td>\n\t\t<td width=125><strong>Last Name</strong></td>\n\t\t<td width=125><strong>First Name</strong></td>\n"; print "\t\t<td width=175><strong>Address</strong></td>\n\t\t<td width=125><strong>City</strong></td>\n"; print "\t\t<td width=75><strong>Territory</strong><td width=40><strong>Delete</strong></td></tr>\n"; while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) { </script> <tr align="left"> <td><font size="1"><?php print "<a href='../address_edit.php?idx="; print $line["address_id"]; print "'target=_new>"; print $line["address_id"]; print "</a>";?></font></td> <td><font size="1"><?php echo $line["lastname"]; ?></font></td> <td><font size="1"><?php echo $line["firstname"]; ?></font></td> <td><font size="1"><?php echo $line["address"]; ?></font></td> <td><font size="1"><?php echo $line["city"]; ?></font></td> <td><font size="1"><?php echo $line["territory"]; ?></font></td> <td><font size=1><?php print "<a href='../delete_address.php?idx="; print $line["address_id"]; print "'target=_blank>"; print "<img src=../images/trash_icon.jpg border=0 alt='Delete Address' /img>"; print "</a></font></td>"; ?> </tr> <?php } print "</table>\n"; Quote Link to comment https://forums.phpfreaks.com/topic/85970-solved-order-by-problem/#findComment-438999 Share on other sites More sharing options...
Ken2k7 Posted January 14, 2008 Share Posted January 14, 2008 Put ORDER BY at the end of the $query string. So before you call mysql_query, put ORDER BY in there then. Quote Link to comment https://forums.phpfreaks.com/topic/85970-solved-order-by-problem/#findComment-439001 Share on other sites More sharing options...
pkenny9999 Posted January 14, 2008 Author Share Posted January 14, 2008 I'm not exactily sure what the correct syntax would be to do that. Would it go after else { $query = "$query AND territory = '$territory' ORDER BY address"; ? Quote Link to comment https://forums.phpfreaks.com/topic/85970-solved-order-by-problem/#findComment-439011 Share on other sites More sharing options...
revraz Posted January 14, 2008 Share Posted January 14, 2008 Just add it before the query Quote Link to comment https://forums.phpfreaks.com/topic/85970-solved-order-by-problem/#findComment-439063 Share on other sites More sharing options...
sasa Posted January 14, 2008 Share Posted January 14, 2008 try /* Performing SQL query */ $query = "SELECT * FROM Address "; $first_one = 1; if($lastname != "") { if($first_one == 1) { $query = "$query WHERE"; $first_one = 0; $query = "$query lastname LIKE '%$lastname%'"; } else { $query = "$query AND lastname LIKE '%$lastname%'"; } } if($address != "") { if($first_one == 1) { $query = "$query WHERE"; $first_one = 0; $query = "$query address LIKE '%$address%'"; } else { $query = "$query AND address LIKE '%$address%'"; } } if($city != "") { if($first_one == 1) { $query = "$query WHERE"; $first_one = 0; $query = "$query city LIKE '%$city%'"; } else { $query = "$query AND city LIKE '%$city%'"; } } if($zipcode != "") { if($first_one == 1) { $query = "$query WHERE"; $first_one = 0; $query = "$query zipcode LIKE '%$zipcode%'"; } else { $query = "$query AND zipcode LIKE '%$zipcode%'"; } } if($territory != "") { if($first_one == 1) { $query = "$query WHERE"; $first_one = 0; $query = "$query territory = '$territory'"; } else { $query = "$query AND territory = '$territory'"; } } $query .= 'ORDER BY address'; $result = mysql_query($query) or die("Query failed"); /* Printing results in HTML */ $numresult = mysql_num_rows($result); print "<h4 align=center><font color=#3C94C4>"; print $numresult; print " results match your search<h4></font>\n"; print "<table cellpadding=6 border=0 align=center>\n"; print "\t<tr align=left bgcolor=#CCCCCC>\n\t\t<td width=30><strong>ID</strong></td>\n\t\t<td width=125><strong>Last Name</strong></td>\n\t\t<td width=125><strong>First Name</strong></td>\n"; print "\t\t<td width=175><strong>Address</strong></td>\n\t\t<td width=125><strong>City</strong></td>\n"; print "\t\t<td width=75><strong>Territory</strong><td width=40><strong>Delete</strong></td></tr>\n"; while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) { </script> <tr align="left"> <td><font size="1"><?php print "<a href='../address_edit.php?idx="; print $line["address_id"]; print "'target=_new>"; print $line["address_id"]; print "</a>";?></font></td> <td><font size="1"><?php echo $line["lastname"]; ?></font></td> <td><font size="1"><?php echo $line["firstname"]; ?></font></td> <td><font size="1"><?php echo $line["address"]; ?></font></td> <td><font size="1"><?php echo $line["city"]; ?></font></td> <td><font size="1"><?php echo $line["territory"]; ?></font></td> <td><font size=1><?php print "<a href='../delete_address.php?idx="; print $line["address_id"]; print "'target=_blank>"; print "<img src=../images/trash_icon.jpg border=0 alt='Delete Address' /img>"; print "</a></font></td>"; ?> </tr> <?php } print "</table>\n"; Quote Link to comment https://forums.phpfreaks.com/topic/85970-solved-order-by-problem/#findComment-439065 Share on other sites More sharing options...
pkenny9999 Posted January 14, 2008 Author Share Posted January 14, 2008 That did it! Thank you Sasa. Quote Link to comment https://forums.phpfreaks.com/topic/85970-solved-order-by-problem/#findComment-439079 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.