I have a sort problem in my database, I could use some direction or help.

[vetman]

In my database I have unit no, owner, phone, contact. The problem I'm seeing is the unit no. is divided by floor-unit (1-2, 2-4, b-3, 4-11, 3,6) for example. Some owners have multiple units (2-3, 2-4, 2-. I sort  in assending order and get the following table:

 

1-2 Depend Service

1-5 K Manage

1-6 CSJL John Law

1-8, B-12, B-13 Sky Skan,

3-7 Collins Precision 

B-2 D.L.R370

1-1 Markman 

1-13 Duane son

1-3,1-9,1-11 Sayer P Sawyer 

1-7 Diagnostic Systems Ron 

2-1, 2-2, 2-3, 2-4, 2-5, 2-6, 2-8 Sempco Dong 

2-7 Wameist Group, L Bradley 

3-1 akumi Studio Ric Cruze

3-10 wilight LLC 

3-2, 3-11, 4-1, 4-3, 4-8 TwoOne Manufactur

3-4, 4-4 WHOB Mario

3-6 Systems, Inc.

3-8 LTeagu

4-2 Joseph Joseph

4-5, 4-7 Quailty P Inc

4-6 Robert Robert 

B-1 R Machine 

B-10, B-17 Three Machine 

B-4 , B-5 Joyce H Anthony

B-9, B-11, B-14, B-16 Joseph KJKale

 

My questions is, is there a way to sort by unit no starting with the following:

 

1-1

1-2

.

.

2-3

.

.

4-2

.

a-1

.

.

.

b-1

 

Basically numbers, then letters etc.

 

Thanks for any help provided.

[Zane]

Show your table layout

[gizmola]

What does the database structure look like?

[gizmola]

Also, given your test data, order by works fine:

 

mysql> select * from ts order by addy;

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

| addy                                          |

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

| 1-1 Markman                                  |

| 1-13 Duane son                                |

| 1-2 Depend Service                            |

| 1-3,1-9,1-11 Sayer P Sawyer                  |

| 1-5 K Manage                                  |

| 1-6 CSJL John Law                            |

| 1-7 Diagnostic Systems Ron                    |

| 1-8, B-12, B-13 Sky Skan                      |

| 2-1, 2-2, 2-3, 2-4, 2-5, 2-6, 2-8 Sempco Dong |

| 2-7 Wameist Group, L Bradley                  |

| 3-1 akumi Studio Ric Cruze                    |

| 3-10 wilight LLC                              |

| 3-2, 3-11, 4-1, 4-3, 4-8 TwoOne Manufactur    |

| 3-4, 4-4 WHOB Mario                          |

| 3-6 Systems, Inc.                            |

| 3-7 Collins Precision                        |

| 3-8 LTeagu                                    |

| 4-2 Joseph Joseph                            |

| 4-5, 4-7 Quailty P Inc                        |

| 4-6 Robert Robert                            |

| B-1 R Machine                                |

| B-10, B-17 Three Machine                      |

| B-2 D.L.R370                                  |

| B-4 , B-5 Joyce H Anthony                    |

| B-9, B-11, B-14, B-16 Joseph KJKal            |

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

25 rows in set (0.00 sec)

[vetman]

That is my table data from the database in my original post. But your testing data shows a different listing.

 

I'm at work now, can't continue I believe my php ver is 4.1, are you using the same

or a later version?

Thanks

 

[gizmola]

First off, all I did was use a sql select from the mysql command line.  If you used the same select and fetched the data and displayed it, you will get the same results. 

 

The 2nd issue with that structure is that you have a mix of two things:

 

1. Owner

2. The Unit owned

 

 

What you really want is 2-3 tables.  At minimum with a 2 table setup what you want is

 

Owner  -

 

 

Where you have an Owner table and a Unit table related 1 - M (One Owner can have many units).  Depending on what the application needs to do, what your current structure can not do right now for example, is to query and find which owner owns a unit in any sensible way.  Sure you can do "%$unit%" but try that for '1-1' and then question why you also get '1-10' and '11-1' in your results!  Not to mention that no indexes can ever be used in a %% like query.

 

Well, the rest, as they say is up to you.  You can attempt to code around the eggregiously bad structure, or you can restructure to something that will make development sensible and provide functionality.  What's most prudent really requires your input.

[vetman]

Like I said I not sure what I'm doing, I can do what I have done. I have no idea how to use 2 tables together as you are describing.

Here is my code:

 

<?php


	// Make a MySQL Connection
	include 'config.php';

	$con = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
	mysql_select_db("vetmanpc") or die(mysql_error());

	// echo "Connected to Database <br>";
	// Retrieve all the data from the "lakestmill" table
	$result = mysql_query("SELECT * FROM lakestmill ORDER BY unit ASC") or die(mysql_error());

?>
<?php include('header.php');?>
<?php include('mainnav.php');?>

<div id="contents">
<div class="blogentry">
<table border='1' width='540'>
	<tr>
		<th>Unit No.</th>
		<th>Company</th>
		<th>Contact Person</th>
		<th>Phone</th>

	</tr>

<?php
	while($row = mysql_fetch_array( $result )) {
		// Print out the contents of each row into a table
		// I use <?= ... which is a cleaner way of saying <? echo ...
?>
	<tr>
		<td><?= $row['unit']; ?></td>
		<td><?= $row['company']; ?></td>
		<td><?= $row['firstname']; ?> <?= $row['lastname']; ?></td>
		<td><?= $row['phone']; ?></td>

	</tr>
<?php
	} // END WHILE LOOP
?>
</table>
</div>
</div>
<?php include('footer.php');?>

 

Like I said any help is appreciated.

Thanks.