|
PHP - Mysql Sort List
Say I have a column in a MySQL database, that contains the following data (each piece of data is in its own row, as stored as a string):
16b 166 13A 13a 4 402c A66 A66b Currently the list sorts as follows: A66 A66b 13A 13a 16b 166 4 402c I need it to sort as follows: A66 A66b 4 13a 13A 16b 166 402c So that any strings that start with a letter are first, followed by numbers in numerical order, with lower case letters coming before upper case letters. This is a huge issue on my website, which has a database of over 35k such numbers, split up into lists. I can't get these sorted properly. At least it would be nice to sort as follows: 1 4 100 344 Instead of 1 100 344 4 Know what I mean? I know that this is complicated, because the variables are strings and not numbers, but is there an easy way to do this? Similar TutorialsHi All i am wanting a column list like this http://extensions.joomla.org/extensions basically the database is set up as (id, category, parent) I want the parent to group the category section and list like the the joomla example in three columns search various threads throughout the internet but none seem to cover this entirely can any one please help????? i have list of movies A to Z in array i can do simple sort but i want them to sort in grouping like this A [movie name], [movie name], [movie name], [movie name], B [movie name] [movie name] [movie name] [movie name] [movie name] C [movie name] [movie name] [movie name] [movie name] D [movie name] [movie name] [movie name] [movie name] and go on all movies name will be links but A,B,C,D will be remain simple how can i do this i wanna show list like this see this website please visit http://mp3hungama.com/music/genre_albums.php?id=3 thanks Hi, In my database I have tables for: - Tools (sc_tools) - Categories (sc_categories) - Screencasts (sc_screencasts) In the database there are connections between the mentioned as follows: 1) "sc_tools" has an id: tool_id 2) "sc_categories" has an id: (cat_id) AND a relation to sc_tools (cat_rel_to_tool - which is = tool_id) 3) "sc_screencasts" has an: id (sc_id) AND a relation to sc_tools (sc_rel_to_tool - which is = tool_id) AND a relation to sc_categories (sc_rel_to_cat - which is = sc_id) Ok, so I want to list my data as follows: Tool 1 - Category 1 - - Screencast 1.1 - - Screencast 1.2 - Category 2 - - Screencast 2.1 Tool 2 - Category 1 - - Screencast 1.1 osv. I have been tryin with the JOIN-query: $sql_screencasts = mysql_query("SELECT t.tool_id, t.tool_name, c.cat_id, c.cat_name_dk, s.sc_id, s.sc_desc_dk FROM sc_tools t JOIN sc_categories c ON c.cat_rel_to_tool = t.tool_id JOIN sc_screencasts s ON s.sc_rel_to_cat = c.cat_id "); while ($row_screencasts = mysql_fetch_array($sql_screencasts )) { $tool_name = $row_screencasts["tool_name"]; $cat_name_dk = $row_screencasts["cat_name_dk"]; $sc_desc_dk = $row_screencasts["sc_desc_dk"]; $sc_id = $row_screencasts["sc_id"]; echo "<br><font size=3><b>".$tool_name."</b></font><br>"; echo "- ".$cat_name_dk; echo "<br>- - <a href='view.php?id=".$sc_id."'>".$sc_desc_dk."</a><br>"; } But is lists the data as: Tool 1 - Category 1 - - Screencast 1 Tool 1 - Category 1 - - Screencast 2 etc. - I can't get e GROUP BY working either, so I'm lost right now. Hopefully an expert can help me out? Any help is highly appreciated! How to sort data from table? Currently its sorted by ID. But I want to add option that admin can sort data (click on up/down link or whatever). Its about forum script and all forums have categories. Forum and categories are in same table. Forum "parent" is ID of category. So i have following sorting: Cat1 [ID1] ->Forum1 [ID2] [PARENT1] ->Forum2 [ID3] [PARENT1] Cat2 [ID4] ->Forum3 [ID5] [PARENT2] ->Forum4 [ID6] [PARENT2] I need to add additional field "sort". On click admin can UPDATE sorting by increasing/decreasing number. But problem is categories and forum are in same table. If I want to move up FORUM1 (ID2) with (for example) "sort" number 2 I must decrease number (-1) and that forum will be with same sort number as category above. Also if I want to move Category up/down it also must move all forums from that category (but that is not big problem). What is best way to solve this? Hi guys, Having problems sorting mysql results. I can easily order/sort table columns but would like to order the results by a variable.(Distance in Miles). Havent much experience in PHP and really struggling with this. Basically a query echoing out car make and models and how far they are located from a postcode. Can get it to work no porblem, but not to order/sort by least distance. Heres what i'm working with: Code: [Select] $res=mysql_query("SELECT * FROM cars"); while ($row = mysql_fetch_assoc($res)){ echo $row['Make']; echo $row['Model']; //variable will be determimed by user input form $postcode="W12 3SR"; $start=$postcode; $ends = array(); //finds value from database field { $ends[]=$row['Location']; } // Postcode entered by user via input form $result=mysql_query("SELECT * FROM postcodes WHERE Pcode='$start'"); while($row=mysql_fetch_array($result)){ $gridn[0]=$row['Grid_N']; $gride[0]=$row['Grid_E']; } foreach($ends as $fin){ // Postcodes within mysql table $result=mysql_query("SELECT * FROM postcodes WHERE Pcode='$fin'"); while($row=mysql_fetch_array($result)){ $gridn[1]=$row['Grid_N']; $gride[1]=$row['Grid_E']; } // TAKE GRID REFS FROM EACH OTHER TO WORK OUT DISTANCE. $distance_n=$gridn[0]-$gridn[1]; $distance_e=$gride[0]-$gride[1]; // CALCULATE THE DISTANCE BETWEEN THE TWO POSTCODES AND DIVIDE BY 1.6 TO CONVERT KM TO MILES $hypot=sqrt(($distance_n*$distance_n)+($distance_e*$distance_e))/1.609; //VARIABLE FOR DISTANCE AND ROUNDED OF TO NEAREST WHOLE NUMBER. $distance=''.round($hypot/1000,0).''; echo " $distance miles"; echo "<br>"; } } ?> I wish i could just do something like this but isnt possible, is it ? Code: [Select] "Select * FROM cars ORDER BY $distance"; Thanks! So basically I have a database full of players 'items'. Each player has 2 items. and I need to make a circle with these players with only specific items involved. Each circle can only contain 6 players. The items needed to create this circle a pigs spice sugar cows aluminium marble iron lumber wheat water random random so 10 resources are literally needed to create this circle but 12 is needed to make it a 'circle' if you get me. I'd like to note that it is possible that any of these players can have a combination of any of resources listed above. I've created a form that allows people to submit players resources into my mysql database. I heard it was possible that I could code some type of filter/sort code to sort out the players resources from my database automatically into a set that will match what is needed (without any repeat of resources) and having a php code to export it into html format for viewing pleasures. I really have no idea how to go about this in mysql or php. But if somebody could help me out i'd be ever so glad. Also to note: this isn't school work or something. It's from a game I play and i'm just trying to make my job in it easier. I was thinking of maybe a php code that will run through all the possible partners in order to create this set like run through 100s of different sets until the required set is obtained. I heard this is just about possible. Can anyone help me? Looking forward to all the great help around here. Thanks Samuz PS: Each item/resource can only appear once in each circle. Hi again, i'm wondering how can i make Sort (for example a-z) an column in php reading mysql informations. Here's the script: <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <title></title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <link rel="stylesheet" href="table.css" type="text/css"> </head> <body style="margin: 0 0 0 0;"> <? include("passwd.php"); @$start = $_GET["start"]; if($start =='') $start =0; include("lib.php"); $link = mysql_connect($host,$username,$password); if (!$link) { die('Could not connect: ' . mysql_error()); } $db_selected = mysql_select_db($db, $link); if (!$db_selected) { die ("Can't use $db : " . mysql_error()); } //total number of records in the table $res = mysql_query("SELECT * from `$table`"); @$rows = mysql_num_rows ($res); $result = mysql_query("SELECT * from `$table` limit $start,10"); if (!$result) { die('Invalid query: ' . mysql_error()); } echo "<p align=center class = 'menu'> Ocitana tabela: $table </p>"; $cols = mysql_num_fields($result); $records = mysql_num_rows ($result); echo "<table align='center' width='1200' >"; echo "<tr bgcolor='BBCCDD' class='menu'>"; for ($i = 0; $i < $cols;$i++) { echo "<td align='center'>".mysql_field_name($result,$i)."</td>"; } echo "</tr>"; while ($row = mysql_fetch_array($result, MYSQL_NUM)) { echo "<tr bgcolor='F6F6F6' class='normal'>"; foreach ($row as $value) { echo "<td align='center'>".$value ."</td>"; } echo "</tr>"; } $end = $start + $records; echo "<tr align = 'center' bgcolor = 'BBCCDD' class='menu'><td colspan=$cols> Prijema $start do $end od $rows </td></tr>"; echo "<tr align = 'center' class='mylink'><td colspan=$cols> "; if($start != 0) { $prev = $start - 10; echo "<a href='tabela.php?start=$prev'> Prethodna </a> "; } if($start<$rows-10) { $next = $start + 10; echo "<a href = 'tabela.php?start=$next'>Sledeca</a> "; } echo "</td></tr>"; echo "</table>"; ?> </body> </html> I have a MySQL table with a list of albums and there is a field called "views" with the number of views each album has received. I'm looking to generate an array of all the albums in the table and sort the array by the number of views (descending). I have a list of functions defined in a ContentController.php file. I created a new function called build_albumlist, which I've pasted below. The function "get_ip_log" already exists and works, and I used it as a template to create the "build_albumlist" function: public function build_albumlist(){ return $this->select_raw("SELECT * FROM albums WHERE deleted = '0' ORDER BY views DESC",array(),'all'); } public function get_ip_log(){ return $this->select_raw("SELECT * FROM sessions ORDER BY ID DESC",array(),'all'); } When I use the function, I get this warning: Warning: mysql_real_escape_string() expects parameter 1 to be string, array given inC:\xampp\htdocs\Controllers\DBController.php on line 10 [font=cabin, 'trebuchet ms', helvetica, arial, sans-serif]The "select_raw" function that I used in "build_albumlist" is defined in the DBController.php file, and is defined as below:[/font] private function clean_array($params){ $final=array(); foreach($params as $param){ $final[]=mysql_real_escape_string($param); } return $final; } public function select_raw($query,$params,$type=''){ $query=str_replace("?","'%s'",$query); $final_query= call_user_func_array('sprintf', array_merge((array)$query, $this->clean_array($params))); if($type==''){ $result=mysql_query($final_query) or die(mysql_error()); return mysql_fetch_assoc($result); } elseif($type=='all'){ $result=mysql_query($final_query) or die(mysql_error()); $final=array(); while($row=mysql_fetch_assoc($result)){ $final[]=$row; } return $final; } Does anyone know why the "build_albumlist" function is generating this warning, while the "get_ip_log" is not? Any help would be great, as I am obviously pretty new to this. i have been trying to get this code to get a list of usernames from a database and i have now got that to work but when i try and save it it saves all the usernames from the drop down list and not just the one i have selected how can i get it to just use the one i have selected Code: [Select] <?php include "connect.php"; //connection string include("include/session.php"); print "<link rel='stylesheet' href='style.css' type='text/css'>"; print "<table class='maintables'>"; print "<tr class='headline'><td>Post a message</td></tr>"; print "<tr class='maintables'><td>"; // Write out our query. $query = "SELECT username FROM users"; // Execute it, or return the error message if there's a problem. $result = mysql_query($query) or die(mysql_error()); $dropdown = "<select name='username'>"; while($row = mysql_fetch_assoc($result)) { $dropdown .= "\r\n<option value='{$row['username']}'>{$row['username']}</option>"; } $dropdown .= "\r\n</select>"; if(isset($_POST['submit'])) { $name=$session->username; $yourpost=$_POST['yourpost']; $subject=$_POST['subject']; $to=$dropdown; if(strlen($name)<1) { print "You did not type in a name."; //no name entered } else if(strlen($yourpost)<1) { print "You did not type in a post."; //no post entered } else if(strlen($subject)<1) { print "You did not enter a subject."; //no subject entered } else { $thedate=date("U"); //get unix timestamp $displaytime=date("F j, Y, g:i a"); //we now strip HTML injections $subject=strip_tags($subject); $name=strip_tags($name); $yourpost=strip_tags($yourpost); $to=strip_tags($to); $insertpost="INSERT INTO forumtutorial_posts(author,title,post,showtime,realtime,lastposter,name) values('$name','$subject','$yourpost','$displaytime','$thedate','$name','$to')"; mysql_query($insertpost) or die("Could not insert post"); //insert post print "Message posted, go back to <A href='forum.php'>Forum</a>."; } } else { print "<form action='newtopic.php' method='post'>"; print "Your name:<br>"; print "$session->username<br>"; print "User to send to:<br>"; print "$dropdown"; print "Subject:<br>"; print "<input type='text' name='subject' size='20'><br>"; print "Your message:<br>"; print "<textarea name='yourpost' rows='5' cols='40'></textarea><br>"; print "<input type='submit' name='submit' value='submit'></form>"; } print "</td></tr></table>"; ?> MOD EDIT: Changed PHP manual link [m] . . . [/m] tags to [code] . . . [/code] tags. Hi. I am using this script to populate a dropdown list box from sql, it works but does anyone know how to sort the list in alphabetical order? $sql="SELECT * FROM Fish WHERE ***** = '".$_GET['stocktype']."'"; $result=mysql_query($sql); $options=""; while ($row=mysql_fetch_array($result)) { $ID=$row["ID"]; $Stock=$row["Commonn"]; $Options.="<OPTION VALUE=\"$ID\">".$Stock; } <SELECT NAME='stock1'> <OPTION VALUE='$Options'>$Options</option> </SELECT> I am in the process of creating a waiting list that I want to work automatically. When a new member registers for membership, the member must be on a waiting list for a while before the member gets permanent membership. When the membership is activated by me, I want this to work like when I delete the person who is number 1 in the list, and number 2 will be number 1. the remaining numbers in the list will automatically move a number up, just now . 5 will be number 4 and so on. PErson number one is the person that allways will be deleted from the waiting list. An idea would be that this persons name ande rest of data connected to hes membership number will be moved automaticaly to the permanent members list when hi is deleted from the waiting list. Only the name will be displayed in the member list, but the form that the new member submits will have several fields. There are t12 fields to be filled in in the MySQL database, but only the name and surname to be displayed in the list. The numbers will be generated automatically in front of the name. I have tried to do this by myself. I have tried to do this myself, but I do not succeed. I am very new when it comes to PHP and programming in general. Hope someone here can help me with this. I can not find out. Edited February 26 by LeonLatexI am trying to send a list of every name in a database via email but everything i have tried just sends lots of emails with a different name in each email. How can i send every name in one email? Hi i want to make a form LIST that automaticly gets the content "orderid" to choose for editing. can i please get a pointing pin on which function(s) to use ? i have tried with: Code: [Select] $result = mysql_query("SELECT * FROM {$table} WHERE 'ordreid'"); if (!$result) { die("Query to show fields from table failed"); } echo($result); $i = 0; while($i<mysql_num_fields($result)) { $meta=mysql_fetch_field($result,$i); echo $i.".".$meta->name."<br />"; $i++; } like i want to get this part: <option>first item in field orderid</option> <option>2'nd item in field orderid</option> and so on...i know its easy but i have struggled wit this enough now. Been using a function I found here a while back for listing categories and sub categories and it works perfect. Code: [Select] function listSubcats ($parent, $level=0){ global $abc; $sql = "SELECT id, title FROM Cat WHERE parent = $parent"; $res = mysqli_query($abc, $sql); while (list($id, $title) = $res->fetch_row()) { $indent = str_repeat('-', $level); echo "<OPTION value='$id'>$indent $title</OPTION>\n"; listSubcats ($id, $level+1); // list its subcats } } But this is the first time I need change the way the results are displayed. And that leads me to realizing I am a bit confused on how list really works. The mysql table has the typical ID, CatName, parent I need to have each Cat with all subcats related to it listed in its own div. But whatever I try I cannot get the placements of the opening and closing divs in the right spots. So can anyone tell if its even possible to do it with this function. I did work it out using multiple queries but then read on alot of forums that queries inside while statements is not good. Just looking for he best (Correct) way of geting the results laid out properly. like this Code: [Select] <div class="one-third column"> Cat1 </div> <div class="one-third column"> Cat 2 </div> <div class="one-third column"> Cat 3 - Subcat 1 - Subcat 2 -- Sub Subcat 1 </div> <div class="one-third column"> Cat 4 </div> Hope that makes sense... Thanks for any guidance. I'm making a checklist. One table holds the list with IDs. There are about 224 rows, each with its own ideas. Now I have another table to hold user accounts. When you create an account, it shows you a fresh new checklist that you need to start checking off. Could anyone please share techniques so I can have multiple accounts have their own list they need to check off? (ie, when a new person creatures a new account they should have their own list with NOTHING checked) The only way I can think of doing this is making 224 fields for the user account with the IDs of the checklist table to check if I checked it or not. Surely there's an easier way? Thanks I have a mysql table with the structure of Code: [Select] ID Menu_Name Parent_ID 1 Finance NULL 2 Business NULL 3 Investment 1 4 Trading 2 How can I create a html <ul><li> list based on the parent? I have created a drop down list and it does retrieve information from mysql but now I want to use what is been selected to retrieve information. How Do I do this? <?php MYSQL_CONNECT(localhost,'root','') OR DIE("Unable to connect to database"); @mysql_select_db(Examination) or die( "Unable to select database"); $query=("SELECT * FROM subject"); $result=mysql_query($query) or die ("Unable to Make the Query:" . mysql_error() ); echo "<select name=myselect>"; while($row=mysql_fetch_array($result)){ echo "<OPTION VALUE=".$row['Sub_ID'].">".$row['Sub_Name']."</OPTION>"; } echo "</select>"; ?> Hi, basically, here's the deal: I have a lit of checkboxes that are added by the admin (there's an unlimited amount, just depends on how many are added). Then, those are put in a form, in which the user picks whichever ones need to be chosen and those values get sent to a MySQL table. Here's the code that displays the checkboxes Code: [Select] <?php $sql="SELECT * FROM category ORDER BY categoryID ASC"; $result=mysql_query($sql); while($row=mysql_fetch_array($result)){ echo "<input type='checkbox' id='". $row['categoryName'] ."' name='licensed[]' value='" . $row['categoryID'] ."' /><label for='". $row['categoryName'] ."'><br>" . $row['categoryName'] ."</label><br>"; } ?> What I'm making now, is an edit form where whichever checkboxes were checked, will show up checked in the edit form. But I'm not really sure how to go about this, since there is only one actual input tag in the code, I can't select the different ones. I also have this SQL query which selects whichever boxes were inputted into the DB Code: [Select] $sql2="SELECT * FROM category, categoryInfo WHERE category.categoryID = categoryInfo.categoryID AND categoryInfo.parentID = $parentID"; $result2=mysql_query($sql2); Where $parentID is the ID of whichever form you're editing. But yes, I'm basically not really sure how to go about this and would like some help figuring this out Thanks for your time I am working on a project that uses a drop down list to chose the category when inserting new data into the database. What I want to do now is make the drop down list default to the chosen category on the list records page and the update page. I have read several tutorials, but they all say that I have to list the options and then select the default. But since it is possible to add and remove categories, this approch won't work. I need the code to chose the correct category on the fly. There are two tables, one that has the category ID and category name. The second table has the data and the catid which is referenced to the category id in the first table. Code: [Select] -- -- Table structure for table `categories` -- DROP TABLE IF EXISTS `categories`; CREATE TABLE IF NOT EXISTS `categories` ( `id` int(11) NOT NULL AUTO_INCREMENT, `categories` varchar(37) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=40 ; -- -------------------------------------------------------- -- -- Table structure for table `links` -- DROP TABLE IF EXISTS `links`; CREATE TABLE IF NOT EXISTS `links` ( `id` int(4) NOT NULL AUTO_INCREMENT, `catid` int(11) DEFAULT NULL, `name` varchar(255) NOT NULL DEFAULT '', `url` varchar(255) NOT NULL DEFAULT '', `content` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `catid` (`catid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=35 ; Then is the list records file, I have Code: [Select] <?php include ("db.php"); include ("menu.php"); $result = mysql_query("SELECT categories FROM categories") or die(mysql_error()); while ($row = mysql_fetch_array($result)) { $categories=$row["categories"]; $options.= '<option value="'.$row['categories'].'">'.$row['categories'].'</option>'; }; $id = $_GET['id']; $query="SELECT * FROM links ORDER BY catid ASC"; $result=mysql_query($query); ?> <table width="65%" align="center" border="0" cellspacing="1" cellpadding="0"> <tr> <td> <table width="100%" border="1" cellspacing="0" cellpadding="3"> <tr> <td colspan="7"><strong>List data from mysql </strong> </td> </tr> <tr> <td align="center"><strong>Category ID</strong></td> <td align="center"><strong>Category ID</strong></td> <td align="center"><strong>Name</strong></td> <td align="center"><strong>URL</strong></td> <td align="center"><strong>Content</strong></td> <td align="center"><strong>Update</strong></td> <td align="center"><strong>Delete</strong></td> </tr> <?php while($rows=mysql_fetch_array($result)){ ?> <tr> <td> <SELECT NAME=catid> <OPTION>Categories</OPTION> <?php echo $options; ?> </SELECT> </td> <td><? echo $rows['catid']; ?></td> <td><? echo $rows['name']; ?></td> <td><a href="<? echo $rows['url']; ?>"><? echo $rows['url']; ?></a></td> <td><? echo $rows['content']; ?></td> <td align="center"><a href="update.php?id=<? echo $rows['id']; ?>">update</a></td> <td align="center"><a href="delete.php?id=<? echo $rows['id']; ?>">delete</a></td> </tr> <?php } ?> </table> </td> </tr> </table> <?php mysql_close(); ?> So, how do I get this code Code: [Select] $result = mysql_query("SELECT categories FROM categories") or die(mysql_error()); while ($row = mysql_fetch_array($result)) { $categories=$row["categories"]; $options.= '<option value="'.$row['categories'].'">'.$row['categories'].'</option>'; }; <SELECT NAME=catid> <OPTION>Categories</OPTION> <?php echo $options; ?> </SELECT> to give me an output that will be something like if catid exactly matches categories.id echo categories.categorie ??? so far everything I have done produces either a default category of the last category, the catid (which is a number), all of the categories (logical since catid will always be = id, or nothing. How do I get just the category name? I will keep reading and try to figure this out, but any help would be greatly appreciated. Thanks in advance |
|||||||