order by numbers text

didgydont · March 27, 2008

hi all

im trying to sort my feilds but they have numbers and text and it comes back like this EG"

CAT5e Patch Lead 10m

CAT5e Patch Lead 2m

CAT5e Patch Lead 5m

"

what i want is "

CAT5e Patch Lead 2m

CAT5e Patch Lead 5m

CAT5e Patch Lead 10m"

any ideas on how this is my code

$result = mysql_query("SELECT * FROM products WHERE type='$typesearch' and item LIKE '%$_POST[itemsearch]%' 
    ORDER BY item

thank you for your time..

Barand · March 27, 2008

use a "natural sort"

<?php
$arr = array (
        'CAT5e Patch Lead 10m',
        'CAT5e Patch Lead 2m',
        'CAT5e Patch Lead 5m'
        );

natsort($arr);

echo '<pre>', print_r($arr, true), '</pre>';
?>

didgydont · March 27, 2008

sorry im trying to sort a table and im still fairly new to making web pages this is my full code

  <html>
  <head>
  <title>Jennings Price List</title>
  <link rel="stylesheet" type="text/css" href="admin/site.css" />
  </head>
                 <?php

include("admin/connect.php");
include("toolbar.php");


$typesearch = @"$_POST[typesearch]" ;
$itemsearch = @"$_POST[itemsearch]" ;

echo "<h3>Shipping and GST included in sell price. If price is red please call jennings.</h3><br />";
echo "
<table border=\"0\" cellpadding=\"5\" cellspacing=\"0\">
<form action=\"index.php\" method=\"post\">
<tr><td>Item Description:</td><td><input type=\"text\" name=\"itemsearch\" value=\"$itemsearch\" class=\"TEXTB\" /></td>
<td>Type: </td><td><select type=\"text\" name=\"typesearch\" class=\"SELECTA\" id=\"typesearch\" onchange=\"this.form.submit()\" />
<option value=\"$typesearch\">$typesearch &nbsp&nbsp (current)</option>
<option value=\"All Items\">All Items</option>";
include("admin/type.php");
echo "</select></td>
<td></td><td></td></tr>
</form></table><br /><br />";


// Begin your table outside of the array
echo "<table border=\"0\" cellpadding=\"5\" cellspacing=\"0\">
    <tr>
        <td><b>Item</b></td>
        <td><b>&nbsp Type</b></td>
        <td><b>&nbsp Sell</b></td>";
        // <td><b>&nbsp Price Date &nbsp </b></td>
    echo "</tr>";

// Define your colors for the alternating rows 

$color1 = "#E3E4FA";
$color2 = "#C8BBBE";
$row_count = 0; 

// Perform an statndard SQL query:
if (empty($typesearch) and empty($itemsearch)){
    $result = mysql_query("SELECT * FROM products WHERE item LIKE '%bigcat$itemsearch%' ORDER BY type");
    Echo "Please select or type an item in the search box. </br></br>";
}

elseif (eregi("^All Items$", $typesearch)){
    $result = mysql_query("SELECT * FROM products WHERE item LIKE '%$_POST[itemsearch]%' ORDER BY type, item");
}

elseif (empty ($typesearch)){
    $result = mysql_query("SELECT * FROM products WHERE item LIKE '%$itemsearch%' ORDER BY type, item");
}
else {
    $result = mysql_query("SELECT * FROM products WHERE type='$typesearch' and item LIKE '%$_POST[itemsearch]%' 
    ORDER BY item
");
}



// We are going to use the "$row" method for this query. This is just my preference. 

while($row = mysql_fetch_array($result)) {
    $cost = $row['cost'];
    $nodate = $row['nodate'];
    $markup = $row['markup'];
    $shipping = $row['shipping'];
        if ($markup==0){$sell = ($cost * 1.2 * 1.1 + $shipping);}
    else {$sell = (($cost + $markup) * 1.1 + $shipping);}
    //$cost3 = number_format ($sell,2);
    //$cost2 = roundup ($cost3,0);
    $cost7 = ceil ($sell);
    $cost2 = number_format  ($cost7,0);
    $shipping2 = number_format ($shipping,0);
    $item = $row['item'];
    $supplier = $row['supplier'];
    $type = $row['type'];
    $dandt = date('Y-m-d');
    $pricedate = $row['pricedate'];
    $timestamp = strtotime($pricedate);
    $formatted_date = date('F-d-Y', $timestamp);
    $daysold = round((strtotime($dandt) - strtotime($pricedate))/(24*60*60),0);


    /* Now we do this small line which is basically going to tell  
    PHP to alternate the colors between the two colors we defined above. */

    $row_color = ($row_count % 2) ? $color1 : $color2;

    // Echo your table row and table data that you want to be looped over and over here. 
/*  if  (($daysold <= 30) AND ($shipping2==0)){
    echo "<tr>
    <td bgcolor=\"$row_color\">&nbsp $item</td>
    <td bgcolor=\"$row_color\">&nbsp $type</td>
    <td align=\"right\" bgcolor=\"$row_color\">&nbsp Free</td>
    <td align=\"right\" bgcolor=\"$row_color\">&nbsp \$$cost2</td>
    <td align=\"right\" bgcolor=\"$row_color\">&nbsp $daysold day(s) old </td>
    </tr>";} */
    if  ($type == "Custom Pricing"){
}

            elseif  ($nodate == 1){
    echo "<tr>
    <td bgcolor=\"$row_color\">&nbsp $item</td>
    <td bgcolor=\"$row_color\">&nbsp $type</td>
    <td align=\"right\" bgcolor=\"$row_color\">&nbsp \$$cost2</td>
    "; //<td align=\"right\" bgcolor=\"$row_color\">&nbsp $daysold day(s) old </td>
    echo "</tr>";}

      elseif  ($daysold <= 30){
    echo "<tr>
    <td bgcolor=\"$row_color\">&nbsp $item</td>
    <td bgcolor=\"$row_color\">&nbsp $type</td>
    <td align=\"right\" bgcolor=\"$row_color\">&nbsp \$$cost2</td>
    "; //<td align=\"right\" bgcolor=\"$row_color\">&nbsp $daysold day(s) old </td>
    echo "</tr>";}
/*    elseif  ($shipping2==0){echo "<tr>
    <td bgcolor=\"$row_color\">&nbsp $item</td>
    <td bgcolor=\"$row_color\">&nbsp $type</td>
    <td align=\"right\" bgcolor=\"$row_color\">&nbsp Free</td>
    <td align=\"right\" bgcolor=\"$row_color\" class=\"redtext\">&nbsp \$$cost2</td>
    <td align=\"right\" bgcolor=\"$row_color\">&nbsp $daysold day(s) old </td>
    </tr>";}   */

    else  {echo "<tr>
    <td bgcolor=\"$row_color\">&nbsp $item</td>
    <td bgcolor=\"$row_color\">&nbsp $type</td>
    <td align=\"right\" bgcolor=\"$row_color\" class=\"redtext\">&nbsp \$$cost2</td>
    "; //<td align=\"right\" bgcolor=\"$row_color\">&nbsp $daysold day(s) old </td>
    echo "</tr>";}


    // Add 1 to the row count 

    $row_count++; 
} 

// Close out your table. 

echo "</table>";
mysql_close($con);
?>
<br /><br />


</html>

didgydont · April 4, 2008

i have been looking everywhere still no luck is it possible to sort the table from a sql query or do i have to make it an array some how ?

PFMaBiSmAd · April 4, 2008

Short answer - No, with the existing data structure, there is no easy and consistent way of sorting by a product "description" that contains quantifying information in a query.

If you could guarantee that 'CAT5e Patch Lead' (or any description for a set of same product) would always be spelled, spaced, punctuated, and capitalized the same and you added leading zero's to the quantifying information, then it would work.

If all the above conditions don't exist, you would need to find a different way. Even the natsort code that Barand posted will only work if the spelling, spacing, and punctuation of all the similar lines is the same.

My recommendation would be to make the "description" field just the description and if you have any quantifying information that you want to ORDER BY, such as size, length, color..., that you add a "sort_by" field.

didgydont · April 4, 2008

thank you

it would have ram harddrive and other stuff so thank you all for your time and help anyway.

bryan52803 · April 4, 2008

I think this is more an issue of how your database is setup. If you have the option to modify it at this point, you should post how your table is setup, and what you're trying to do. Organizing and categorizing data by descriptions is highly inefficient. MySQL is a machine, not a man, and thrives in numbers.

Bryan

didgydont · April 6, 2008

would be easyer to sort if instead of this

CAT5e Patch Lead 10m
CAT5e Patch Lead 2m
CAT5e Patch Lead 5m

i did this

10m CAT5e Patch Lead 
2m CAT5e Patch Lead 
5m CAT5e Patch Lead

and ram and harddrives like this

512mb ddr2 667 ect
250gb seagate ect

this is my table layout for bryan52803

its the item field im tyring to sort

Barand · April 6, 2008

You still get the same problem, even with the size at the beginning of the text.

(those are pretty long item descriptions if they need 65,000 chars to store)

You need something like

[pre]

item size unit

varchar int varchar

--------------------+-------+---------+

CAT5e Patch Lead | 2 | m |

CAT5e Patch Lead | 5 | m |

CAT5e Patch Lead | 10 | m |

ddr2 667 ect | 512 | mb |

seagate ect |250000 | mb |

didgydont · April 7, 2008

those are pretty long item descriptions if they need 65,000 chars to store

what do you mean im guessing i have set the field wrong ?

and are you saying the only way to sort them would be with different fields ?

some items wont have size like mice and keyboards..

didgydont · April 7, 2008

i looked at manual and changed text fields to tinytext

Sign In

order by numbers text

Recommended Posts

didgydont

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

didgydont

Link to comment

Share on other sites

didgydont

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

didgydont

Link to comment

Share on other sites

bryan52803

Link to comment

Share on other sites

didgydont

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

didgydont

Link to comment

Share on other sites

didgydont

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information