PHP - Mysql List Contents Of A Feild

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>

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?

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 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?

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'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 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>"; 


?>

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

Hi,  I'm a php newbie, with some mysql experience. I have a mysql database as follows:
Database=watch, Table=events - fields id, reportno, sdate, comments

What I need is:
1. A dropdown list to display reportno from mysql database.
2. Depending on which reportno I choose, I'd like to open a popup(or separate) page to display the stored information.

Tks in advance for any help

hi

i have generated a product list from a mysql table called product_list, once i enter a new product in to the table the product will be shown in the generated list and the list will grow and the table grows.  and i want to allow user the edit/delete/save the products from the generated table, i have no idea how to do it and what is the algorithmic idea to do it so. here is the php and the html code.

<!--Body container  for creating a new product in to the list-->
<div class="body_orderviewform">
  <form name="form1" method="post" action="upload_file.php" enctype="multipart/form-data">
    <p>
      <label for="user_id">User ID:</label>
      <input type="text" name="user_id" id="user_id">
      <label for="customer_name">Customer Name:</label>
      <input type="text" name="customer_name" id="customer_name">
      <label for="customer_family">Customer_family</label>
      <input type="text" name="customer_family" id="customer_family">
      <label for="freelancer_name">Purchaser:</label>
      <input name="freelancer_name" type="text" id="freelancer_name">
     
      <? if (isset($_COOKIE['picAdd'])) echo $_COOKIE['picAdd'];?>
     
    </p>
 <!------------------------------------------------------------------------------------------------------- for generating the list -->
   
<div  class="div.neworder_list" >   

   <div  class="div.neworder_listheader" align="center">
   
    <table width="637" border="1" >
      <tr>
        <td width="193"><label for="link">link:</label>
          <label for="link_new"></label>
          <input type="text" name="link_new" id="link_new"></td>
        <td width="202"><label for="unitprice">Unit Price:</label>
          <label for="Unit_price_new"></label>
          <input type="text" name="Unit_price_new" id="Unit_price_new"></td>
        <td width="220"><label for="qty">Quantity:</label>
          <label for="quantity_new"></label>
          <input type="text" name="quantity_new" id="quantity_new"></td>
      </tr>
      <tr>
        <td><label for="express">Express Fee:</label>
          <label for="express_new"></label>
          <input type="text" name="express_new" id="express_new"></td>
        <td><label for="commission_new">Commission:</label>
          <input type="text" name="commission_new" id="commission_new"></td>
        <td><label for="customer_description">Description</label>
          <label for="description_new"></label>
          <textarea name="description_new" id="description_new" cols="45" rows="5"></textarea></td>
      </tr>
      <tr>
        <td>&nbsp;</td>
        <td colspan="2">Picture Upload:
              <input type="hidden" name="<?php echo ini_get("session.upload_progress.name");?>" value="123" />
              <input name="file" type="file" autofocus="autofocus"/>
              <br />


      </tr>
      <tr>
        <td colspan="3"><input type="reset" name="reset" id="reset" value="Reset">
        <input type="submit" name="submit" id="submit" value="Submit The Product"></td>
      </tr>
    </table>

 
</form>   
</div>
   
<div class="neworder_listview">
  <p>
  <form action="" method="post" name="list">
    <input type="submit" name="del" id="del" value="Save">
    <input type="submit" name="save" id="save" value="Del">
  </p>
  <table width="1022" border="1" align="center">
    <tr>
      <th width="24" scope="col">&nbsp;</th>
    <th width="24" scope="col">Row#</th>
    <th width="137" scope="col">Manager</th>
    <th width="137" scope="col">Purchaser Desc</th>
    <th width="40" scope="col"><p>Link</p>
      <p>/Ссылки</p></th>
    <th width="53" scope="col">ФОТО</th>
    <th width="50" scope="col">Unit Price/Цена за еденицу товара</th>
    <th width="46" scope="col">Quantity/ Кол-во</th>
    <th width="138" scope="col">Total Unit Price/ Общая цена</th>
    <th width="89" scope="col">Express/Доставка по Китаю</th>
    <th width="119" scope="col">Description/Описание</th>
    <th width="89" scope="col">ADDITIONAL LINKS/ЗAMЕНЫ</th>
    </tr>
   


<?php

   $username = "my username";
   $password = "my pass";
   $database = "userinfo";
   $link = mysql_connect("localhost", "$username", "$password");
   
   if(!$link) {echo("Failed to establish connection to mysql server");
               exit();}

   $status = mysql_select_db($database);
   $query = "SELECT * FROM order_list";
   $result = mysql_query($query);
   $num = mysql_num_rows($result);
   $i=0;
 
 
 
  while ($i < $num) {
   $field1_name=mysql_result($result,$i,"admin_st");
   $field2_name=mysql_result($result,$i,"freelancer_st");
   $field3_name=mysql_result($result,$i,"link");
   $field4_name=mysql_result($result,$i,"picture");
   $field5_name=mysql_result($result,$i,"unitprice");
   $field6_name=mysql_result($result,$i,"qty");
   $field7_name=mysql_result($result,$i,"express");
   $field8_name=mysql_result($result,$i,"customer_st");
   $i++;
    }
 
?>







<?php
$i=0;
$row=1;
while ($i < $num) {

$f1=mysql_result($result,$i,"admin_st");
$f2=mysql_result($result,$i,"freelancer_st");
$f3=mysql_result($result,$i,"link");
$f4=mysql_result($result,$i,"pic_address");
$f5=mysql_result($result,$i,"unitprice");
$f6=mysql_result($result,$i,"qty");
$f7=mysql_result($result,$i,"express");
$f8=mysql_result($result,$i,"customer_st");
$totao_unit_price = $f5*$f6;
?>
  <tr>
    <td><input type="checkbox" name="del_chbox" id="del_chbox">
   <td><p><font face="Arial"><input name="row_txtbox" type="text" id="row_txtbox" size="2" value="<?php echo $row; ?>"></font></td>
   <td><p><font face="Arial"> <textarea name="manager_txtbox" cols="10" id="manager_txtbox"><?php echo $f1; ?></textarea></font></td>
   <td><p><font face="Arial">        <textarea name="purchase_txtbox" cols="10" id="purchase_txtbox"><?php echo $f2; ?></textarea>
</font></td>


   <td><font face="Arial"><a href="<?php $f3 ?>" target="_blank"><?php echo $f3; ?></a></font></td>
   <td><font face="Arial"><img src="<?php echo $f4;?>" width="100" align="middle"100></font></td>
   <td><font face="Arial"><input name="unitprice_txtbox" type="text" id="unitprice_txtbox" size="2" value="<?php echo $f5; ?>"></font></td>
   <td><font face="Arial"> <input name="qty_txtbox" type="text" id="qty_txtbox" size="2" value="<?php echo $f6; ?>"></font></td>
   <td><p><font face="Arial"><?php echo $totao_unit_price; ?></font></td>
   <td><p><font face="Arial"><input name="express2" type="text" id="express3" size="2" value="<?php echo $f7; ?>"></font></td>
   <td><p><font face="Arial"> <textarea name="custdesc_txtbox" cols="20" id="custdesc_txtbox"><?php echo $f8; ?></textarea></font></td>
    <td><input name="express2" type="text" id="express3" size="2" value="<?php echo "new link" ?>"></td>
   </tr>

  <p>
    <?php
$i++;
$row++;
}
?>
</table>
   
  </p>
  <p>&nbsp;</p>
  </form>

Lets start out by saying I'm a nube to sql/php things so I am learning as I go. I try to read all that I can before I post, and only post when I cant figure it out on my own.

That being said. What I want to do in simplest terms is be able to assign a variable to each item in an sql table. So say I have an mysql table that has ID, username, fontcolor. I want to be able to pull those out so say....


while($row = mysql_fetch_array($users)) {

$username[$i] = $row[username];
$fontcolor[$i] = $row[fontcolor];
}


Then on the page I can just call to $username[1] type thing. I have tried mixing this several different ways with for and while and I keep getting errors on the page. I realize the code isn't showing everything but its just there to show you the idea of what I'm trying to do. I just want it to generate a list(array) that will make it easier for me to call back just the items I need on parts of the page with out having to have extra coding everywhere.

Thanks in advance.

Jim

Ok, so I've spent quite a bit of time piecing together this solution from a variety of sources. As such, I may have something in my code below that doesn't make sense or isn't neccessary. Please let me know if that is the case.

I'm creating an administrative form that users will you to add/remove items from a MySQL table that lists open positions for a facility. The foreach loop generates all of the possible job specialties from a table called 'specialty_list'. This table is joined to a second table ('open_positions') that lists any positions that have been selected previously.

Where I'm stuck is getting the checkbox to be checked if the facility_ID from the open_positions table matches the $id passed in the URL via ?facility_id=''. Here's where I am so far:


$query = "SELECT specialty_list.specialty_displayname
 , specialty_shortname
     , open_positions.position
     , facility_ID
  FROM specialty_list
LEFT OUTER
  JOIN open_positions
    ON open_positions.position = specialty_list.specialty_shortname
ORDER 
    BY specialty_list.specialty_shortname";

$results = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_assoc($results)) 
{ 
$positions[$row['specialty_shortname']] = $row['specialty_displayname'];
}

echo "<form method='POST' action='checkbox.php'>";
foreach($positions as $specialty_shortname => $specialty_displayname) 
{
$facility_ID = $row['facility_ID'];
  
$checked = $facility_ID == $row['facility_ID'] ? ' checked' : ''; 

echo "<input type='checkbox' name='position[]' value=\"{$specialty_shortname}\"{$checked}>&nbsp;&nbsp;{$specialty_displayname}</input><br/>"; 

}  
echo "<input type='hidden' name='facility_ID' value='$id'>";
echo "<input type='submit' value='Submit Checkboxes!'>";
echo "</form>";


Any ideas how to get this working? I feel like I'm very close, but I just can't get it. I also tried starting from scratch with a WHILE statement instead of a FOREACH, but haven't tweaked it enough to prevent duplicate checkboxes. With that in mind, here it is, just in case that's a better direction:


$query = "SELECT specialty_list.specialty_displayname
 , specialty_shortname
     , open_positions.position
     , facility_ID
  FROM specialty_list
LEFT OUTER
  JOIN open_positions
    ON open_positions.position = specialty_list.specialty_shortname
ORDER 
    BY specialty_list.specialty_shortname";

$results = mysql_query($query) or die(mysql_error());

echo "<form method='POST' action='checkbox.php'>";

while($row=mysql_fetch_assoc($results))
{ 
$facility_ID = $row['facility_ID'];
$specialty_shortname = $row['specialty_shortname'];
$specialty_displayname = $row['specialty_displayname'];


if ($facililty_ID==$id) {

$checked=' checked';
}

echo "<input type='checkbox' name='position[]' value=\"$specialty_shortname\"$checked>&nbsp;&nbsp;$specialty_displayname</input><br/>"; 
}

echo "<input type='hidden' name='facility_ID' value='$id'>";
echo "<input type='submit' value='Submit Checkboxes!'>";
echo "</form>";

Hey guys, I can't wrap my head around how to make this work right...

I have three tables:
Code: [Select]
CREATE TABLE `games` (
  `g_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`g_id`));

CREATE TABLE IF NOT EXISTS `game_player` (
  `r_id` int(11) NOT NULL AUTO_INCREMENT,
  `p_id` int(11) DEFAULT NULL,
  `g_id` int(11) DEFAULT NULL,
  `bool` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`r_id`));

CREATE TABLE IF NOT EXISTS `players` (
  `p_id` int(11) NOT NULL AUTO_INCREMENT,
  `playerid` varchar(150) NOT NULL,
  PRIMARY KEY (`p_id`),
  UNIQUE KEY `playerid` (`playerid`));

The players table is my list of users, and they're tied to the list of games via the game_player table.

So here's my issue... I'm trying to show the full list of games, and then check mark each record where the player does play it. 
This is what I have so far - it shows all the games,  but it's not checking the boxes.

Code: [Select]
$result = mysql_query("SELECT * FROM games") or die(mysql_error());
while($row = mysql_fetch_array($result))
{
$newquery = "SELECT * FROM game_player, players
WHERE game_player.p_id = players.p_id
AND game_player.g_id = ".$row['g_id']. "
AND players.playerid = {$userid}";
$query = mysql_query($newquery) or die(mysql_error());
if($query['bool'] == 1) { $set_checked = " CHECKED";
}
else{ $set_checked = "";
}
echo "<input type=\"checkbox\" name=\"box1\" value=\"".$query['g_id']."\"" . $set_checked . "/>".$row['name']."<br />\n";
}