Results 1 to 8 of 8
-
08-14-2011, 09:19 AM #1New Member
- Join Date
- Aug 2011
- Location
- Liverpool, UK
- Posts
- 4
Change mysql database to list from id to alpabetical
Hi all
At present my PHP script echo's the address's direct from the DB id, I would like to change this so it lists by alphanumeric:
address1 contains the number and street so at present lists as:
8 better street, city, postcode
1 better street, city, postcode
9 any street, city, postcode
1 any street, city, postcode
I would like:
1 any street, city, postcode
9 any street, city, postcode
1 better street, city, postcode
8 better street, city, postcode
Present script:
Code:<?php $useron = $_SESSION['user_on']; if (isset($_GET['msg'])) { echo "<div class=\"error\">$_GET[msg]</div>"; } $query="SELECT * FROM $useron ORDER BY `address1` ASC"; $result=mysql_query($query); $num=mysql_num_rows($result); if ($num > 0 ){ }; mysql_close(); if ($num > 0 ) { $i=0; while ($i < $num) { $address1 = mysql_result($result,$i,"address1"); $address2 = mysql_result($result,$i,"address2"); $postcode = mysql_result($result,$i,"postcode"); $status = mysql_result($result,$i,"status"); $id = mysql_result($result,$i,"id"); ?> <table width="850" border="0" style="font-size:12px" > <tr> <th width="350" height="10" scope="col" align="left"><? echo $id, ", ",$address1, ", ", $address2, ", ", $postcode; ?></th> <th width="150" height="10"scope="col" align="left"> Work Status: <font color="#FF0000"> <?php echo $status; ?></font>
this script may seem rubbish but it works
Any help much appreciated.
regards
johnliverpool
-
08-14-2011, 09:33 AM #2Temporarily Suspended
- Join Date
- Feb 2004
- Location
- UK
- Posts
- 1,431
Hi
You'd need to redesign your Database to to have the street number as another column, to be able to list things as you want.
Thanks
-
08-14-2011, 10:38 AM #3New Member
- Join Date
- Aug 2011
- Location
- Liverpool, UK
- Posts
- 4
Thanks abtme
Done so code now as follows:
Code:<?php $useron = $_SESSION['user_on']; if (isset($_GET['msg'])) { echo "<div class=\"error\">$_GET[msg]</div>"; } $query="SELECT * FROM $useron ORDER BY `address1` ASC"; $result=mysql_query($query); $num=mysql_num_rows($result); if ($num > 0 ){ }; mysql_close(); if ($num > 0 ) { $i=0; while ($i < $num) { $housenum = mysql_result($result,$i,"housenum"); $address1 = mysql_result($result,$i,"address1"); $address2 = mysql_result($result,$i,"address2"); $postcode = mysql_result($result,$i,"postcode"); $status = mysql_result($result,$i,"status"); $id = mysql_result($result,$i,"id"); ?> <table width="850" border="0" style="font-size:12px" > <tr> <th width="350" height="10" scope="col" align="left"><? echo $housenum, " ", $address1, ", ", $address2, ", ", $postcode; ?></th> <th width="150" height="10"scope="col" align="left"> Work Status: <font color="#FF0000"> <?php echo $status; ?></font> <th width="350" height="10"scope="col" align="left"><? echo " <a href=\"update.php?id=$id&useron=$useron\">Update</a> - <a href=\"delete.php?id=$id&useron=$useron\">Delete</a> - <a href=\"workrequest.php?id=$id&useron=$useron\">work request</a>"; if ($status=="Active"){ echo " <a href=\"vwr.php?id=$id&useron=$useron\">View work requested</a>"; } ?>
Thanks for the reply
John
-
08-14-2011, 10:46 AM #4Temporarily Suspended
- Join Date
- Feb 2004
- Location
- UK
- Posts
- 1,431
no worries, glad it worked out for you
-
08-14-2011, 11:01 AM #5Web Hosting Master
- Join Date
- Nov 2010
- Posts
- 620
Two tips:
- watch out for SQL injection (use mysql_real_escape_string or prepared statements)
- only use mysql_result if you only need to retrieve one cell, use mysql_fetch_assoc (or something similar) in other cases)
-
08-14-2011, 11:41 AM #6New Member
- Join Date
- Aug 2011
- Location
- Liverpool, UK
- Posts
- 4
Is there a script that I can add that would numerically and alphabetically list both the housenum and address1 fields in order, dont really need but would be nice.
I can do your two tips but is it that important as its all in pages covered by secure log in scripts.
Thanks to all for replies
John
-
08-14-2011, 11:46 AM #7Web Hosting Master
- Join Date
- Nov 2010
- Posts
- 620
-
08-14-2011, 12:00 PM #8New Member
- Join Date
- Aug 2011
- Location
- Liverpool, UK
- Posts
- 4
Took a little time reading on on the differences between the mysql_ queries and now understand your tips,
Thanks much appreciated, changes made, less script and easier to read aswell.
John
Similar Threads
-
Change blocks of data in by database
By registercheaper in forum Programming DiscussionReplies: 2Last Post: 10-31-2009, 02:50 PM -
How to change user database ??
By X-x-X in forum Hosting Security and TechnologyReplies: 4Last Post: 09-17-2006, 11:36 AM -
If i change the hoster is that effect on my database ?
By Eid Adam in forum Web HostingReplies: 3Last Post: 06-02-2006, 02:42 PM -
Change mysql database to old version
By MoonTamil in forum Hosting Security and TechnologyReplies: 6Last Post: 05-31-2006, 06:19 PM -
Members Database list / Emailing list
By 247ursites in forum Programming DiscussionReplies: 1Last Post: 01-28-2004, 11:57 PM