[SOLVED] ORDER BY problem

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

print $numresult;

print " results match your search<h4>\n";

print "<table cellpadding=6 border=0 align=center>\n";

print "\t<tr align=left bgcolor=#CCCCCC>\n\t\t<td width=30>ID</td>\n\t\t<td width=125>Last Name</td>\n\t\t<td width=125>First Name</td>\n";

print "\t\t<td width=175>Address</td>\n\t\t<td width=125>City</td>\n";

print "\t\t<td width=75>Territory<td width=40>Delete</td></tr>\n";

while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {

</script>

<td><?php print "<a href='../address_edit.php?idx=";

print $line["address_id"];

print "'target=_new>";

print $line["address_id"];

print "</a>";?></td>

<td><?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></td>"; ?>

</tr>

<?php

}

print "</table>\n";

adam291086 · January 14, 2008

$query = "SELECT * FROM Address ORDERED BY address

should be

$query = "SELECT * FROM Address ORDER BY address"

pkenny9999 · January 14, 2008

Sorry, I mistyped the command I used, I used $query = "SELECT * FROM Address ORDER BY address"; and I get Query failed.

Ken2k7 · 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.

revraz · 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.

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

Ken2k7 · 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.

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

?

revraz · January 14, 2008

Just add it before the query

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

pkenny9999 · January 14, 2008

That did it! Thank you Sasa.

Sign In

[SOLVED] ORDER BY problem

Recommended Posts

pkenny9999

Link to comment

Share on other sites

adam291086

Link to comment

Share on other sites

pkenny9999

Link to comment

Share on other sites

Ken2k7

Link to comment

Share on other sites

revraz

Link to comment

Share on other sites

pkenny9999

Link to comment

Share on other sites

Ken2k7

Link to comment

Share on other sites

pkenny9999

Link to comment

Share on other sites

revraz

Link to comment

Share on other sites

sasa

Link to comment

Share on other sites

pkenny9999

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information