|
PHP - Ordering A Table Based On Another Table.....
This is a little confusing so please bear with me. I was thinking of using left.join but couldn't figure out how to implement it properly.
I am trying to order all of the results from a table names 'clans' based on how many points the clan has. To calculate the points you have to go into another table 'clanteams' and then loop every team in the clan pulling wins and losses from a row in a specific ladder table. Here is the code i have to calculate the points. Code: [Select] $total[wins] = 0; $total[loss] = 0; $members=mysql_query("SELECT id,clanid,teamid,DATE_FORMAT(datejoined,'%M %d, %Y') FROM clanteams WHERE clanid='$member[id]'"); while(list($id,$clanid,$teamid,$joined)=mysql_fetch_row($members)){ $team=mysql_query("SELECT name,ladderid FROM teams WHERE id='$teamid'"); if(mysql_num_rows($team) == 0) continue; $team=mysql_fetch_array($team); $ladder=mysql_query("SELECT name FROM ladders WHERE id='$team[ladderid]'"); $ladder=mysql_fetch_array($ladder); $linfo=mysql_query("SELECT rank,wins,loss FROM ladder_$team[ladderid] WHERE teamid='$teamid'"); $linfo=mysql_fetch_array($linfo); $total[wins] = $total[wins] + $linfo[wins]; $total[loss] = $total[loss] + $linfo[loss]; } $totalpoints = ($total[wins] * 2) - $total[loss]; So now i want to loop through every row in the clans table, and using the above code oder them by $total points. Ive spent hours wrapping my head around it and still cannot figure it out. Please help. Similar TutorialsHi, I am using multiple rows in my table and I want to order it in ascending order from a column. With SQL statement it is simple as saying ORDER BY column but how I can do this with just numbers? So I want for example: (goes in correct order) Col 1 Col 2 Col3 # 5 # # 43 # # 87 # # 113 # instead of: Col 1 Col 2 Col3 # 113 # # 43 # # 87 # # 5 # Thanks for any help!! Hi there, Firstly this is a link to my working code/script http://www.nexus-guild.eu/adornments/index.php When you click on one of the body images it pulls all the data from the database for that particular slot and feeds it into a table. I am wondering how i can make it so when you click on the table column descriptions such as the Name/Effect/Quality/Level/Colour/Faction Name/Faction Value it will reorder all the information in ascending order or descending order. Ive looked around quite a bit for this but i cant find anything on this. This is the code im currently using. It has alot of repeating code which makes up the majority of the file Code: [Select] <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>neXus - Adornments Calculator</title> <style type="text/css"> <!-- body { background-color: #2f2626; } body,td,th { color: #000; font-family: Trebuchet MS, Arial, Helvetica, sans-serif; } --> </style></head> <body text="#FFFFFF" link="#ff8000" vlink="#ff8000" alink="#ff8000"> <center> <p> </p> <p> </p> <p><img src="character.png" border="0" usemap="#Map"/> <map name="Map" id="Map"> <area shape="rect" coords="2,2,44,44" href="index.php?slot=head" /> <area shape="rect" coords="45,2,85,44" href="index.php?slot=cloak" /> <area shape="rect" coords="2,46,44,86" href="index.php?slot=chest" /> <area shape="rect" coords="45,45,85,86" href="index.php?slot=shoulders" /> <area shape="rect" coords="45,87,85,129" href="index.php?slot=forearms" /> <area shape="rect" coords="2,87,44,129" href="index.php?slot=legs" /> <area shape="rect" coords="45,130,85,170" href="index.php?slot=hands" /> <area shape="rect" coords="3,130,44,170" href="index.php?slot=feet" /> <area shape="rect" coords="46,172,86,216" href="index.php?slot=charm" /> <area shape="rect" coords="3,172,45,215" href="index.php?slot=charm" /> <area shape="rect" coords="45,216,85,257" href="index.php?slot=drink" /> <area shape="rect" coords="3,216,44,256" href="index.php?slot=food" /> <area shape="rect" coords="362,2,403,43" href="index.php?slot=waist" /> <area shape="rect" coords="405,2,443,43" href="index.php?slot=neck" /> <area shape="rect" coords="362,45,403,86" href="index.php?slot=ear" /> <area shape="rect" coords="405,45,444,87" href="index.php?slot=ear" /> <area shape="rect" coords="363,88,402,129" href="index.php?slot=finger" /> <area shape="rect" coords="405,89,444,128" href="index.php?slot=finger" /> <area shape="rect" coords="363,131,402,171" href="index.php?slot=wrist" /> <area shape="rect" coords="405,130,443,170" href="index.php?slot=wrist" /> <area shape="rect" coords="362,174,404,213" href="index.php?slot=ranged" /> <area shape="rect" coords="405,173,443,212" href="index.php?slot=ammo" /> <area shape="rect" coords="362,216,404,256" href="index.php?slot=primary" /> <area shape="rect" coords="405,216,444,256" href="index.php?slot=secondary" /> </map> <br> <br> <p> <?php include 'db_connect.php'; if (isset($_GET["slot"])) { $slot = $_GET["slot"]; } else { $slot=startpage; }; $color1 = "#E1FAE2"; $color2 = "#ffffff"; $row_count = 0; //////////////////////////////////////////////////////////////////////////////// /////////////////////////////STARTPAGE - START////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// if ( $slot == "startpage" ) { echo "This is still in beta. If you find any bugs please send them to <a href=\"mailto:sosia@nexus-guild.eu\">Sosia</a>"; } //////////////////////////////////////////////////////////////////////////////// /////////////////////////////STARTPAGE - END//////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// /////////////////////////////HEAD SLOT - START////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// elseif ( $slot == "head" ) { $sql = "SELECT * FROM $db_table WHERE for_head = '1' ORDER BY name"; $result = mysql_query ($sql,$db); echo "<table cellpadding=\"5\" cellspacing=\"0\" border=\"0\" width=\"90%\" bgcolor=\"#FFDCCC\">"; echo "<tr> <th align=\"left\">Name</th> <th align=\"left\">Effect</th> <th align=\"left\">Quality</th> <th align=\"left\">Level</th> <th align=\"left\">Colour</th> <th align=\"left\">Faction Name</th> <th align=\"left\">Faction Value</th></tr>"; while($row = mysql_fetch_array( $result )) { $row_color = ($row_count % 2) ? $color1 : $color2; // Print out the contents of each row into a table echo " <tr> <td bgcolor=\"$row_color\">" . $row["name"] . "</td> <td bgcolor=\"$row_color\">" . $row["effect"] . "</td> <td bgcolor=\"$row_color\">" . $row["quality"] . "</td> <td bgcolor=\"$row_color\">" . $row["level"] . "</td> <td bgcolor=\"$row_color\">" . $row["colour"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionname"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionvalue"] . "</td> </tr> "; $row_count++; } echo "</table>"; } //////////////////////////////////////////////////////////////////////////////// /////////////////////////////HEAD SLOT - END//////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// /////////////////////////////CLOAK - START////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// elseif ( $slot == "cloak" ) { $sql = "SELECT * FROM $db_table WHERE for_cloak = '1' ORDER BY name"; $result = mysql_query ($sql,$db); echo "<table cellpadding=\"5\" cellspacing=\"0\" border=\"0\" width=\"90%\" bgcolor=\"#FFDCCC\">"; echo "<tr> <th align=\"left\">Name</th> <th align=\"left\">Effect</th> <th align=\"left\">Quality</th> <th align=\"left\">Level</th> <th align=\"left\">Colour</th> <th align=\"left\">Faction Name</th> <th align=\"left\">Faction Value</th></tr>"; while($row = mysql_fetch_array( $result )) { $row_color = ($row_count % 2) ? $color1 : $color2; // Print out the contents of each row into a table echo " <tr> <td bgcolor=\"$row_color\">" . $row["name"] . "</td> <td bgcolor=\"$row_color\">" . $row["effect"] . "</td> <td bgcolor=\"$row_color\">" . $row["quality"] . "</td> <td bgcolor=\"$row_color\">" . $row["level"] . "</td> <td bgcolor=\"$row_color\">" . $row["colour"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionname"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionvalue"] . "</td> </tr> "; $row_count++; } echo "</table>"; } //////////////////////////////////////////////////////////////////////////////// /////////////////////////////CLOAK SLOT - END//////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// /////////////////////////////CHEST SLOT - START////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// elseif ( $slot == "chest" ) { $sql = "SELECT * FROM $db_table WHERE for_chest = '1' ORDER BY name"; $result = mysql_query ($sql,$db); echo "<table cellpadding=\"5\" cellspacing=\"0\" border=\"0\" width=\"90%\" bgcolor=\"#FFDCCC\">"; echo "<tr> <th align=\"left\">Name</th> <th align=\"left\">Effect</th> <th align=\"left\">Quality</th> <th align=\"left\">Level</th> <th align=\"left\">Colour</th> <th align=\"left\">Faction Name</th> <th align=\"left\">Faction Value</th></tr>"; while($row = mysql_fetch_array( $result )) { $row_color = ($row_count % 2) ? $color1 : $color2; // Print out the contents of each row into a table echo " <tr> <td bgcolor=\"$row_color\">" . $row["name"] . "</td> <td bgcolor=\"$row_color\">" . $row["effect"] . "</td> <td bgcolor=\"$row_color\">" . $row["quality"] . "</td> <td bgcolor=\"$row_color\">" . $row["level"] . "</td> <td bgcolor=\"$row_color\">" . $row["colour"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionname"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionvalue"] . "</td> </tr> "; $row_count++; } echo "</table>"; } //////////////////////////////////////////////////////////////////////////////// /////////////////////////////CHEST SLOT - END//////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// /////////////////////////////SHOULDERS SLOT - START////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// elseif ( $slot == "shoulders" ) { $sql = "SELECT * FROM $db_table WHERE for_shoulders = '1' ORDER BY name"; $result = mysql_query ($sql,$db); echo "<table cellpadding=\"5\" cellspacing=\"0\" border=\"0\" width=\"90%\" bgcolor=\"#FFDCCC\">"; echo "<tr> <th align=\"left\">Name</th> <th align=\"left\">Effect</th> <th align=\"left\">Quality</th> <th align=\"left\">Level</th> <th align=\"left\">Colour</th> <th align=\"left\">Faction Name</th> <th align=\"left\">Faction Value</th></tr>"; while($row = mysql_fetch_array( $result )) { $row_color = ($row_count % 2) ? $color1 : $color2; // Print out the contents of each row into a table echo " <tr> <td bgcolor=\"$row_color\">" . $row["name"] . "</td> <td bgcolor=\"$row_color\">" . $row["effect"] . "</td> <td bgcolor=\"$row_color\">" . $row["quality"] . "</td> <td bgcolor=\"$row_color\">" . $row["level"] . "</td> <td bgcolor=\"$row_color\">" . $row["colour"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionname"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionvalue"] . "</td> </tr> "; $row_count++; } echo "</table>"; } //////////////////////////////////////////////////////////////////////////////// /////////////////////////////SHOULDERS SLOT - END//////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// /////////////////////////////FOREARMS SLOT - START////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// elseif ( $slot == "forearms" ) { $sql = "SELECT * FROM $db_table WHERE for_forearms = '1' ORDER BY name"; $result = mysql_query ($sql,$db); echo "<table cellpadding=\"5\" cellspacing=\"0\" border=\"0\" width=\"90%\" bgcolor=\"#FFDCCC\">"; echo "<tr> <th align=\"left\">Name</th> <th align=\"left\">Effect</th> <th align=\"left\">Quality</th> <th align=\"left\">Level</th> <th align=\"left\">Colour</th> <th align=\"left\">Faction Name</th> <th align=\"left\">Faction Value</th></tr>"; while($row = mysql_fetch_array( $result )) { $row_color = ($row_count % 2) ? $color1 : $color2; // Print out the contents of each row into a table echo " <tr> <td bgcolor=\"$row_color\">" . $row["name"] . "</td> <td bgcolor=\"$row_color\">" . $row["effect"] . "</td> <td bgcolor=\"$row_color\">" . $row["quality"] . "</td> <td bgcolor=\"$row_color\">" . $row["level"] . "</td> <td bgcolor=\"$row_color\">" . $row["colour"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionname"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionvalue"] . "</td> </tr> "; $row_count++; } echo "</table>"; } //////////////////////////////////////////////////////////////////////////////// /////////////////////////////FOREARMS SLOT - END//////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// /////////////////////////////LEGS SLOT - START////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// elseif ( $slot == "legs" ) { $sql = "SELECT * FROM $db_table WHERE for_legs = '1' ORDER BY name"; $result = mysql_query ($sql,$db); echo "<table cellpadding=\"5\" cellspacing=\"0\" border=\"0\" width=\"90%\" bgcolor=\"#FFDCCC\">"; echo "<tr> <th align=\"left\">Name</th> <th align=\"left\">Effect</th> <th align=\"left\">Quality</th> <th align=\"left\">Level</th> <th align=\"left\">Colour</th> <th align=\"left\">Faction Name</th> <th align=\"left\">Faction Value</th></tr>"; while($row = mysql_fetch_array( $result )) { $row_color = ($row_count % 2) ? $color1 : $color2; // Print out the contents of each row into a table echo " <tr> <td bgcolor=\"$row_color\">" . $row["name"] . "</td> <td bgcolor=\"$row_color\">" . $row["effect"] . "</td> <td bgcolor=\"$row_color\">" . $row["quality"] . "</td> <td bgcolor=\"$row_color\">" . $row["level"] . "</td> <td bgcolor=\"$row_color\">" . $row["colour"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionname"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionvalue"] . "</td> </tr> "; $row_count++; } echo "</table>"; } //////////////////////////////////////////////////////////////////////////////// /////////////////////////////LEGS SLOT - END//////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// /////////////////////////////HANDS SLOT - START////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// elseif ( $slot == "hands" ) { $sql = "SELECT * FROM $db_table WHERE for_hands = '1' ORDER BY name"; $result = mysql_query ($sql,$db); echo "<table cellpadding=\"5\" cellspacing=\"0\" border=\"0\" width=\"90%\" bgcolor=\"#FFDCCC\">"; echo "<tr> <th align=\"left\">Name</th> <th align=\"left\">Effect</th> <th align=\"left\">Quality</th> <th align=\"left\">Level</th> <th align=\"left\">Colour</th> <th align=\"left\">Faction Name</th> <th align=\"left\">Faction Value</th></tr>"; while($row = mysql_fetch_array( $result )) { $row_color = ($row_count % 2) ? $color1 : $color2; // Print out the contents of each row into a table echo " <tr> <td bgcolor=\"$row_color\">" . $row["name"] . "</td> <td bgcolor=\"$row_color\">" . $row["effect"] . "</td> <td bgcolor=\"$row_color\">" . $row["quality"] . "</td> <td bgcolor=\"$row_color\">" . $row["level"] . "</td> <td bgcolor=\"$row_color\">" . $row["colour"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionname"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionvalue"] . "</td> </tr> "; $row_count++; } echo "</table>"; } //////////////////////////////////////////////////////////////////////////////// /////////////////////////////HANDS SLOT - END//////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// /////////////////////////////FEET SLOT - START////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// elseif ( $slot == "feet" ) { $sql = "SELECT * FROM $db_table WHERE for_feet = '1' ORDER BY name"; $result = mysql_query ($sql,$db); echo "<table cellpadding=\"5\" cellspacing=\"0\" border=\"0\" width=\"90%\" bgcolor=\"#FFDCCC\">"; echo "<tr> <th align=\"left\">Name</th> <th align=\"left\">Effect</th> <th align=\"left\">Quality</th> <th align=\"left\">Level</th> <th align=\"left\">Colour</th> <th align=\"left\">Faction Name</th> <th align=\"left\">Faction Value</th></tr>"; while($row = mysql_fetch_array( $result )) { $row_color = ($row_count % 2) ? $color1 : $color2; // Print out the contents of each row into a table echo " <tr> <td bgcolor=\"$row_color\">" . $row["name"] . "</td> <td bgcolor=\"$row_color\">" . $row["effect"] . "</td> <td bgcolor=\"$row_color\">" . $row["quality"] . "</td> <td bgcolor=\"$row_color\">" . $row["level"] . "</td> <td bgcolor=\"$row_color\">" . $row["colour"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionname"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionvalue"] . "</td> </tr> "; $row_count++; } echo "</table>"; } //////////////////////////////////////////////////////////////////////////////// /////////////////////////////FEET SLOT - END//////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// /////////////////////////////WAIST SLOT - START////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// elseif ( $slot == "waist" ) { $sql = "SELECT * FROM $db_table WHERE for_waist = '1' ORDER BY name"; $result = mysql_query ($sql,$db); echo "<table cellpadding=\"5\" cellspacing=\"0\" border=\"0\" width=\"90%\" bgcolor=\"#FFDCCC\">"; echo "<tr> <th align=\"left\">Name</th> <th align=\"left\">Effect</th> <th align=\"left\">Quality</th> <th align=\"left\">Level</th> <th align=\"left\">Colour</th> <th align=\"left\">Faction Name</th> <th align=\"left\">Faction Value</th></tr>"; while($row = mysql_fetch_array( $result )) { $row_color = ($row_count % 2) ? $color1 : $color2; // Print out the contents of each row into a table echo " <tr> <td bgcolor=\"$row_color\">" . $row["name"] . "</td> <td bgcolor=\"$row_color\">" . $row["effect"] . "</td> <td bgcolor=\"$row_color\">" . $row["quality"] . "</td> <td bgcolor=\"$row_color\">" . $row["level"] . "</td> <td bgcolor=\"$row_color\">" . $row["colour"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionname"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionvalue"] . "</td> </tr> "; $row_count++; } echo "</table>"; } //////////////////////////////////////////////////////////////////////////////// /////////////////////////////WAIST SLOT - END//////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// /////////////////////////////NECK SLOT - START////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// elseif ( $slot == "neck" ) { $sql = "SELECT * FROM $db_table WHERE for_head = '1' ORDER BY name"; $result = mysql_query ($sql,$db); echo "<table cellpadding=\"5\" cellspacing=\"0\" border=\"0\" width=\"90%\" bgcolor=\"#FFDCCC\">"; echo "<tr> <th align=\"left\">Name</th> <th align=\"left\">Effect</th> <th align=\"left\">Quality</th> <th align=\"left\">Level</th> <th align=\"left\">Colour</th> <th align=\"left\">Faction Name</th> <th align=\"left\">Faction Value</th></tr>"; while($row = mysql_fetch_array( $result )) { $row_color = ($row_count % 2) ? $color1 : $color2; // Print out the contents of each row into a table echo " <tr> <td bgcolor=\"$row_color\">" . $row["name"] . "</td> <td bgcolor=\"$row_color\">" . $row["effect"] . "</td> <td bgcolor=\"$row_color\">" . $row["quality"] . "</td> <td bgcolor=\"$row_color\">" . $row["level"] . "</td> <td bgcolor=\"$row_color\">" . $row["colour"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionname"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionvalue"] . "</td> </tr> "; $row_count++; } echo "</table>"; } //////////////////////////////////////////////////////////////////////////////// /////////////////////////////NECK SLOT - END//////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// /////////////////////////////EAR SLOT - START////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// elseif ( $slot == "ear" ) { $sql = "SELECT * FROM $db_table WHERE for_head = '1' ORDER BY name"; $result = mysql_query ($sql,$db); echo "<table cellpadding=\"5\" cellspacing=\"0\" border=\"0\" width=\"90%\" bgcolor=\"#FFDCCC\">"; echo "<tr> <th align=\"left\">Name</th> <th align=\"left\">Effect</th> <th align=\"left\">Quality</th> <th align=\"left\">Level</th> <th align=\"left\">Colour</th> <th align=\"left\">Faction Name</th> <th align=\"left\">Faction Value</th></tr>"; while($row = mysql_fetch_array( $result )) { $row_color = ($row_count % 2) ? $color1 : $color2; // Print out the contents of each row into a table echo " <tr> <td bgcolor=\"$row_color\">" . $row["name"] . "</td> <td bgcolor=\"$row_color\">" . $row["effect"] . "</td> <td bgcolor=\"$row_color\">" . $row["quality"] . "</td> <td bgcolor=\"$row_color\">" . $row["level"] . "</td> <td bgcolor=\"$row_color\">" . $row["colour"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionname"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionvalue"] . "</td> </tr> "; $row_count++; } echo "</table>"; } //////////////////////////////////////////////////////////////////////////////// /////////////////////////////EAR SLOT - END//////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// /////////////////////////////FINGER SLOT - START////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// elseif ( $slot == "finger" ) { $sql = "SELECT * FROM $db_table WHERE for_finger = '1' ORDER BY name"; $result = mysql_query ($sql,$db); echo "<table cellpadding=\"5\" cellspacing=\"0\" border=\"0\" width=\"90%\" bgcolor=\"#FFDCCC\">"; echo "<tr> <th align=\"left\">Name</th> <th align=\"left\">Effect</th> <th align=\"left\">Quality</th> <th align=\"left\">Level</th> <th align=\"left\">Colour</th> <th align=\"left\">Faction Name</th> <th align=\"left\">Faction Value</th></tr>"; while($row = mysql_fetch_array( $result )) { $row_color = ($row_count % 2) ? $color1 : $color2; // Print out the contents of each row into a table echo " <tr> <td bgcolor=\"$row_color\">" . $row["name"] . "</td> <td bgcolor=\"$row_color\">" . $row["effect"] . "</td> <td bgcolor=\"$row_color\">" . $row["quality"] . "</td> <td bgcolor=\"$row_color\">" . $row["level"] . "</td> <td bgcolor=\"$row_color\">" . $row["colour"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionname"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionvalue"] . "</td> </tr> "; $row_count++; } echo "</table>"; } //////////////////////////////////////////////////////////////////////////////// /////////////////////////////FINGER SLOT - END//////////////////////////////////// ////////////////////////////////////////////////////////////////////////////////// ////////////////////////////////////////////////////////////////////////////// /////////////////////////////WRIST SLOT - START////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// elseif ( $slot == "wrist" ) { $sql = "SELECT * FROM $db_table WHERE for_head = '1' ORDER BY name"; $result = mysql_query ($sql,$db); echo "<table cellpadding=\"5\" cellspacing=\"0\" border=\"0\" width=\"90%\" bgcolor=\"#FFDCCC\">"; echo "<tr> <th align=\"left\">Name</th> <th align=\"left\">Effect</th> <th align=\"left\">Quality</th> <th align=\"left\">Level</th> <th align=\"left\">Colour</th> <th align=\"left\">Faction Name</th> <th align=\"left\">Faction Value</th></tr>"; while($row = mysql_fetch_array( $result )) { $row_color = ($row_count % 2) ? $color1 : $color2; // Print out the contents of each row into a table echo " <tr> <td bgcolor=\"$row_color\">" . $row["name"] . "</td> <td bgcolor=\"$row_color\">" . $row["effect"] . "</td> <td bgcolor=\"$row_color\">" . $row["quality"] . "</td> <td bgcolor=\"$row_color\">" . $row["level"] . "</td> <td bgcolor=\"$row_color\">" . $row["colour"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionname"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionvalue"] . "</td> </tr> "; $row_count++; } echo "</table>"; } //////////////////////////////////////////////////////////////////////////////// /////////////////////////////WRIST SLOT - END//////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// /////////////////////////////CHARM SLOT - START////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// elseif ( $slot == "charm" ) { $sql = "SELECT * FROM $db_table WHERE for_charm = '1' ORDER BY name"; $result = mysql_query ($sql,$db); echo "<table cellpadding=\"5\" cellspacing=\"0\" border=\"0\" width=\"90%\" bgcolor=\"#FFDCCC\">"; echo "<tr> <th align=\"left\">Name</th> <th align=\"left\">Effect</th> <th align=\"left\">Quality</th> <th align=\"left\">Level</th> <th align=\"left\">Colour</th> <th align=\"left\">Faction Name</th> <th align=\"left\">Faction Value</th></tr>"; while($row = mysql_fetch_array( $result )) { $row_color = ($row_count % 2) ? $color1 : $color2; // Print out the contents of each row into a table echo " <tr> <td bgcolor=\"$row_color\">" . $row["name"] . "</td> <td bgcolor=\"$row_color\">" . $row["effect"] . "</td> <td bgcolor=\"$row_color\">" . $row["quality"] . "</td> <td bgcolor=\"$row_color\">" . $row["level"] . "</td> <td bgcolor=\"$row_color\">" . $row["colour"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionname"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionvalue"] . "</td> </tr> "; $row_count++; } echo "</table>"; } //////////////////////////////////////////////////////////////////////////////// /////////////////////////////CHARM SLOT - END//////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// /////////////////////////////PRIMARY SLOT - START////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// elseif ( $slot == "primary" ) { $sql = "SELECT * FROM $db_table WHERE for_primary = '1' ORDER BY name"; $result = mysql_query ($sql,$db); echo "<table cellpadding=\"5\" cellspacing=\"0\" border=\"0\" width=\"90%\" bgcolor=\"#FFDCCC\">"; echo "<tr> <th align=\"left\">Name</th> <th align=\"left\">Effect</th> <th align=\"left\">Quality</th> <th align=\"left\">Level</th> <th align=\"left\">Colour</th> <th align=\"left\">Faction Name</th> <th align=\"left\">Faction Value</th></tr>"; while($row = mysql_fetch_array( $result )) { $row_color = ($row_count % 2) ? $color1 : $color2; // Print out the contents of each row into a table echo " <tr> <td bgcolor=\"$row_color\">" . $row["name"] . "</td> <td bgcolor=\"$row_color\">" . $row["effect"] . "</td> <td bgcolor=\"$row_color\">" . $row["quality"] . "</td> <td bgcolor=\"$row_color\">" . $row["level"] . "</td> <td bgcolor=\"$row_color\">" . $row["colour"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionname"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionvalue"] . "</td> </tr> "; $row_count++; } echo "</table>"; } //////////////////////////////////////////////////////////////////////////////// /////////////////////////////PRIMARY SLOT - END//////////////////////////////////// //////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// /////////////////////////////SECONDARY SLOT - START////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// elseif ( $slot == "secondary" ) { $sql = "SELECT * FROM $db_table WHERE for_head = '1' ORDER BY name"; $result = mysql_query ($sql,$db); echo "<table cellpadding=\"5\" cellspacing=\"0\" border=\"0\" width=\"90%\" bgcolor=\"#FFDCCC\">"; echo "<tr> <th align=\"left\">Name</th> <th align=\"left\">Effect</th> <th align=\"left\">Quality</th> <th align=\"left\">Level</th> <th align=\"left\">Colour</th> <th align=\"left\">Faction Name</th> <th align=\"left\">Faction Value</th></tr>"; while($row = mysql_fetch_array( $result )) { $row_color = ($row_count % 2) ? $color1 : $color2; // Print out the contents of each row into a table echo " <tr> <td bgcolor=\"$row_color\">" . $row["name"] . "</td> <td bgcolor=\"$row_color\">" . $row["effect"] . "</td> <td bgcolor=\"$row_color\">" . $row["quality"] . "</td> <td bgcolor=\"$row_color\">" . $row["level"] . "</td> <td bgcolor=\"$row_color\">" . $row["colour"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionname"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionvalue"] . "</td> </tr> "; $row_count++; } echo "</table>"; } //////////////////////////////////////////////////////////////////////////////// /////////////////////////////SECONDARY SLOT - END//////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// /////////////////////////////RANGED SLOT - START////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// elseif ( $slot == "ranged" ) { $sql = "SELECT * FROM $db_table WHERE for_head = '1' ORDER BY name"; $result = mysql_query ($sql,$db); echo "<table cellpadding=\"5\" cellspacing=\"0\" border=\"0\" width=\"90%\" bgcolor=\"#FFDCCC\">"; echo "<tr> <th align=\"left\">Name</th> <th align=\"left\">Effect</th> <th align=\"left\">Quality</th> <th align=\"left\">Level</th> <th align=\"left\">Colour</th> <th align=\"left\">Faction Name</th> <th align=\"left\">Faction Value</th></tr>"; while($row = mysql_fetch_array( $result )) { $row_color = ($row_count % 2) ? $color1 : $color2; // Print out the contents of each row into a table echo " <tr> <td bgcolor=\"$row_color\">" . $row["name"] . "</td> <td bgcolor=\"$row_color\">" . $row["effect"] . "</td> <td bgcolor=\"$row_color\">" . $row["quality"] . "</td> <td bgcolor=\"$row_color\">" . $row["level"] . "</td> <td bgcolor=\"$row_color\">" . $row["colour"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionname"] . "</td> <td bgcolor=\"$row_color\">" . $row["factionvalue"] . "</td> </tr> "; $row_count++; } echo "</table>"; } //////////////////////////////////////////////////////////////////////////////// /////////////////////////////RANGED SLOT - END//////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// /////////////////////////////FOOD SLOT - START////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// elseif ( $slot == "food" ) { echo "<font color=#ffffff>Dream on sunshine there aint no food adorns yet</font>"; } //////////////////////////////////////////////////////////////////////////////// /////////////////////////////FOOD SLOT - END//////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// /////////////////////////////DRINK SLOT - START////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// elseif ( $slot == "drink" ) { echo "<font color=#ffffff>Dream on sunshine there aint no drink adorns yet</font>"; } //////////////////////////////////////////////////////////////////////////////// /////////////////////////////DRINK SLOT - END//////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// /////////////////////////////DRINK SLOT - START////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// elseif ( $slot == "ammo" ) { echo "<font color=#ffffff>Dream on sunshine there aint no ammo adorns yet</font>"; } //////////////////////////////////////////////////////////////////////////////// /////////////////////////////DRINK SLOT - END//////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// else { echo "<font color=#ffffff>Either you have clicked something stupidly or edited the url or there is a bug with this script. You should never see this error message. If you do please contact Sosia :No Slot Choosen</font>"; } ?> </center> </body> </html> Hi All ,
I have a small table with 4 fields namely Day_ID, Dues, Last_Visit, Points. where Day_ID is an auto-increment field. The table would be as follows:
Day_ID -- Dues --- Last_Visit --- Points.
1 --------- 900 -------- 1/12 -------- 6
2 --------- 700 -------- 4/12 -------- 7
3 --------- 600 -------- 7/12 -------- 5
4 --------- 600 -------- 9/12 -------- 6
5 --------- 600 -------- 10/12 ------- 6
6 --------- 600 -------- 14/12 ------- 6
So this is the record of a person's visit to say a club. The last row indicates the last date of his visit to the club. His points on this date are 6. Based on this point value of 6 in the last row I want to retrieve all the previous BUT adjoining all records that have the same Points i.e. 6.
So my query should retrieve for me, based on the column value of Points of the last row (i.e. Day_ID - 6 ), as follows:
4 --------- 600 -------- 9/12 -------- 6
5 --------- 600 -------- 10/12 ------- 6
6 --------- 600 -------- 14/12 ------- 6
This problem stated above had been completely resolved, thanks to a lot of help from Guru Barand by this following query :-
$query = "SELECT cv.day_id, cv.dues, cv.last_visit, cv.points FROM clubvisit cv WHERE last_visit >= ( SELECT MAX(last_visit) FROM clubvisit WHERE points <> ( SELECT points as lastpoints FROM clubvisit JOIN ( SELECT MAX(last_visit) as last_visit FROM clubvisit ) as latest USING (last_visit) ) )";I am using this and it works perfectly except that now there is a slight change in the table because the criteria for points is now dependent on more than one column cv.points and is more like cv.points1, cv.points2, cv.points3 etc. So now I need to make a selection based on each of these cv.points columns. As of now I can still get the results by running the query multiple times for each of the cv.points columns ( seperately for cv.points1, cv.points2, cv.points3) and it works correctly. However I am wondering if there is a better way to do this in just one go. This not only makes the code repetitive but also since the queries are interconnected, involves the use of transactions which I wish to avoid if possible. The values that I require for each of the cv.point columns is 1. day_id of the previous / old day on which the cv.points value changed from the current day value, and 2. cv.points on that old/ previous day. So for example if the table is as below: Day_ID -- Dues --- Last_Visit --- Points1 --- Points2. 1 --------- 900 -------- 1/12 ----------- 9 ------------ 5 2 --------- 600 -------- 4/12 ----------- 6 ------------ 6 3 --------- 400 -------- 7/12 ----------- 4 ------------ 7 4 --------- 500 -------- 9/12 ----------- 5 ------------ 8 5 --------- 600 -------- 10/12 ---------- 6 ------------ 8 6 --------- 600 -------- 11/12 ---------- 6 ------------ 8 7 --------- 600 -------- 13/12 ---------- 6 ------------ 7 8 --------- 500 -------- 15/12 ---------- 5 ------------ 7 9 --------- 500 -------- 19/12 ---------- 5 ------------ 7 Then I need the following set of values : 1. day_id1 -- Day 7, points1 ---- 6, days_diff1 -- (9-7 = 2) . // Difference between the latest day and day_id1 2. day_id2 -- Day 6, points2 ---- 8, days_diff2 -- (9-6 = 3) 3. day_id3 -- .... and so on for other points. Thanks all ! hi i have table in my database holding food items. i need users to be able to order those items individually for example...if apples are third from the top..they should be able to move them up or down the list 1 place at a time perhaps with an up and down arrow. i have a field in my table called item_order the only problem is...users are going to be able to insert new items to the database...but i dont know how they can populate the order field...i cant set it to auto increment because the item_id is the primary key auto incrementing...how can i populate the order field? and then once i have this problem sorted how do i then allow users to change this order? im looking for code if possible please because ive spent WAY to long on this now and im struggling to grasp things people advise me to do. terminology and methods of accomplishing this are just going over my head. please help. I am reading in a table and ordering it by a column labeled "sub" and generating an HTML table. The "sub" column entries are in numerical order (i.e. 1 2 3 4 5 6 7 8 10 11 12 etc. etc.). There are over 100 entries in this table as well. The problem I'm facing is probably obvious to some of you as the display is
1 Obviously I'd like the display to be in numerical order. Â What can I do? Â Ok I was wondering is there a way I can select a mysql database, and order it but only show data of a certain criteria: for example Mysql database: ID | User | XP | Won | Lost 1 Freid 100 1 4 2 Smic 300 5 4 3 Retna 300 3 2 4 OBW 200 3 2 And then some php code that will retrieve this information but only display the users that have lost = 4 in a list. In other words some php code that will only list the players Freid and Smic from the msql database becuase they have Lost = 4 the code would not show Retna or OBW becuase there Lost is not = to 4 Any help would be great thanks Freid001 how to format only one specific row based on the value of one cell in a row? $result = mysql_query(select bal, etc. etc ..query ) while($row = mysql_fetch_assoc($result)) echo "<tr>"; echo "<td>" . $row['Account'] . "</td>"; echo "<td>" . $row['licence'] . "</td>"; echo "<td>" . $row['own'] . "</td>"; echo "<td>" . $row['bal'] . "</td>"; echo "</tr>"; Would appreciate help on how to format only the $row['bal'] output to red colour IF bal >5000 Thanks I'm wondering how to do this with javascript for a table that i have made in php and html. I want to hide the row for the table based on the value inside my first column for each row of the table. Like if column 1 has "apple" inside it, then the row would not be shown. I'm pretty sure this can be done in javascript but i'm not a coding guy, so any help would be appreciated! My table.php file that has the table i want to do this for: Code: [Select] <script language="javascript"> imageX1='plus'; imageX2='plus'; imageX3='plus'; function toggleDisplay(e){ imgX="imagePM"+e; tableX="table"+e; imageX="imageX"+e; tableLink="tableHref"+e; imageXval=eval("imageX"+e); element = document.getElementById(tableX).style; if (element.display=='none') {element.display='block';} else {element.display='none';} if (imageXval=='plus') {document.getElementById(imgX).src='http://www.pvpstreams.com/icons/wowicon.png';eval("imageX"+e+"='minus';");document.getElementById(tableLink).title='Hide WoW';} else {document.getElementById(imgX).src='http://www.pvpstreams.com/icons/wowicon.png';eval("imageX"+e+"='plus';");document.getElementById(tableLink).title='Show WoW';} } </script> <?php $username="user"; $password="password"; $tmp="database"; mysql_connect(localhost,$username,$password); @mysql_select_db($tmp) or die( "Unable to select database"); $query="SELECT * FROM users"; $result=mysql_query($query); $num=mysql_numrows($result); mysql_close(); ?> <table class="sortable" id="wow" border="1" cellpadding="0" cellspacing="0" style="margin-left:auto;margin-right:auto;text-align:left"> <tr> <th><font face="Arial, Helvetica, sans-serif">Game</font></th> <th><font face="Arial, Helvetica, sans-serif">Character</font></th> <th><font face="Arial, Helvetica, sans-serif">Rating</font></th> <th><font face="Arial, Helvetica, sans-serif"><img src="http://www.pvpstreams.com/table-images/xfirelogo.png"> Status</font></th> <th><font face="Arial, Helvetica, sans-serif"><img src="http://www.pvpstreams.com/table-images/xfirelogo.png"> Link</font></th> <th><font face="Arial, Helvetica, sans-serif">Realm</font></th> <th><font face="Arial, Helvetica, sans-serif"><img src="http://www.pvpstreams.com/table-images/livestreamlogo.png"> Status</font></th> <th><font face="Arial, Helvetica, sans-serif"><img src="http://www.pvpstreams.com/table-images/livestreamlogo.png"> Link</font></th> <th><font face="Arial, Helvetica, sans-serif">Team Setup</font></th> </tr> <?php $i=0; while ($i < $num) { $f1=mysql_result($result,$i,"wowcharacter"); $f2=mysql_result($result,$i,"username"); $f3=mysql_result($result,$i,"livestream"); $f4=mysql_result($result,$i,"wowserver"); $f5=mysql_result($result,$i,"wowplayer1"); $f6=mysql_result($result,$i,"wowplayer2"); $f9=mysql_result($result,$i,"wowplayer3"); $f7=mysql_result($result,$i,"wowrealm"); $f8=mysql_result($result,$i,"xfire"); $f10=mysql_result($result,$i,"xfirecheck"); $f11=mysql_result($result,$i,"wowrating"); ?> <tr> <td><font face="Arial, Helvetica, sans-serif"><?php echo "<center><img src=http://www.pvpstreams.com/table-images/wowtable.png>"; ?></center></font></td> <td><font face="Arial, Helvetica, sans-serif"><?php echo "<a class=blue href=http://$f4.battle.net/wow/en/character/$f7/$f1/simple>$f1</a>"; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><?php echo $f11; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><?php echo "$f10"; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><?php echo "<a class=blue href=http://www.pvpstreams.com/xfire.php?user=$f2>$f8</a>"; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><?php echo $f7; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><?php include("livestreamcheck.php"); ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><?php echo "<a class=blue href=http://www.pvpstreams.com/livestream.php?user=$f2>$f3</a>"; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><?php echo "<img src=http://www.pvpstreams.com/wclassicons/$f5.png><img src=http://www.pvpstreams.com/wclassicons/$f6.png><img src=http://www.pvpstreams.com/wclassicons/$f9.png>" ?></font></td> </tr> <?php $i++; } ?> </table> hi, Instead giving its default on "Choose Process", I would like to display its process based on the value in database. How can I do it? Code: [Select] <?php require_once '../../connectDB.php'; require_once '../include/functions.php'; $sql = " SELECT * FROM tb_item ORDER BY itm_id asc"; $result = mysql_query($sql) or die(mysql_error()); ?> <form> <h1>Items</h1> <table width="75%" border="1" align="left" cellpadding="5" cellspacing="1"> <tr> <th width="100" align="left">Item ID</th> <th width="100" align="left">Parts</th> <th width="100" align="left">Sub-Process</th> <th width="100" align="left">Confirmation</th> </tr> <tr> <?php while ($row = mysql_fetch_array($result)) { extract($row); ?> <td><?php echo $itm_id; ?></td> <td><?php echo $itm_parts; ?></td> <td> <select name='cboSub' id='cbosub'> <option value='' selected>---- Choose Process ----</option> <?php createComboBox(); ?> </select> </td> <td> <a href='processItem.php?itm_id=<?php echo $itm_id; ?>'> <img src='../images/modify.png' width='20' height='20' alt='confirm'/> </a> </td> </tr> <?php }; ?> </table> </form> <?php /** * Create combo box for sub-process */ function createComboBox() { $sql = " SELECT * FROM tb_sub_process ORDER BY sub_id asc"; $result = mysql_query($sql) or die(mysql_error()); while ($row = mysql_fetch_array($result)) { extract($row); echo "<option value='$sub_id' $select>$sub_name</option>"; } } ?> I've attached an example of what basically the output will looks like. Any help are appreciated. Thanks in advance. Regards, Juz [attachment deleted by admin] hi I have 2 tables (members & friendship) Members table columns: -------------------------------------------- username | password | email | etc .... -------------------------------------------- Person 1 xxxxxx x@x.com person 2 yyyyyy y@y.com Friendship columns: --------------------------------------- username | friend | status | --------------------------------------- person 1 person 2 friends person 2 person 1 friends person 1 person 3 pending I want to query the friendship table: "SELECT friend FROM friendship WHERE username ='$_SESSION['myusername'];' AND status='friends' " (so its basically getting the user names of anyone who is the logged in users friend) And then use the returned user names to select their data from the members table. Any help much appreciated! ^.^ Dear All I want to display my data in tabular columns with the headings of table like 00:06-00:08,00:08-00:10,00:10:00:12:00........22:00-24:00.i.e.time In database I have two columns one containing value and other containing time when this reading was done. I could retrieve data from my mysql table in php and look at time. I want to know how could I insert the value to the correct column of table based on the time. e.g If I have record in table in database say containing value 09:45 and 5. I want this value to go to the column heading 00:08-00:10 and same thing for the other values. I hope I am clear with the problem definition but if you need more info.Please let me know. Thanks, elp Hi all, firstly apologies as this is a cross post from another forum and we have hit a block.. I am hoping that opening this up to another set of gurus we can get a resolution. What I am trying to achieve is this... I have 2 tables Main and FinancialYear. Main holds all data which I use a form to post the data to it..(all works fine). I use this code to create a drop down in the insert.php form. again this works. Code: [Select] <tr><td>Financial Year: xxxx/xxxx</td><td> <!-- pulls the data from the table variable to populate the dropdown menu --> <?php $database = 'Projects_Main'; $fintable = 'FinancialYear'; if (!mysql_connect($db_host, $db_user, $db_pwd)) die("Can't connect to database 'cos somethin' is wrong"); if (!mysql_select_db($database)) die("Can't select database"); $result = mysql_query("SELECT FinancialYear_id, FinancialYear FROM {$fintable} order by FinancialYear"); $options=""; while ($row=mysql_fetch_array($result)) { //$id=$row["FinancialYear_id"]; $thing=$row["FinancialYear"]; $options.="<OPTION VALUE=\"$thing\">".$thing.'</option>'; } ?> <SELECT NAME="FinancialYear"> <OPTION VALUE=0>Choose</OPTION> <?=$options?> </SELECT> </td></tr> What I have done is built another form which list all records in the database and creates an update url for every record that passes the field Project_id where i use $_get to retrieve the Project_id to retrieve the relevant data into the update.php form. I am able to populate the form with all the correct information BUT I am looking to introduce some dropdowns to aid updating the data and provide consistency to the data. . Code: [Select] // Connect to server and select database. mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db('Projects_Main')or die("cannot select DB"); // get value of id that sent from address bar $Project_id=$_GET['Project_id']; //define vars $FinancialYear=$_POST['FinancialYear']; // other vars defined here also.. about 30 // Retrieve data from database $sql="SELECT * FROM Main WHERE Project_id='$Project_id'"; $result=mysql_query($sql); $rows=mysql_fetch_array($result); ?> //update_record_ac.php posts the data to the dbase. <form name="form1" method="post" action="update_record_ac.php"> <center> <table> <tr><td><b>Store Details<b></td></tr> <tr><td>Financial Year:</td><td> // takes the data from $rows and present to form <input name="FinancialYear" type="text" id="FinancialYear" value="<?php echo $rows['FinancialYear']; ?>"> // this is where I need to create the drop down.. see my other comments in the post..... </td></tr> the financialYear table consists if the following; financialyear_id - pri, auto inc. ---- data format is 2010/2011, 2011/2012.... financialyear the main table contains 30 fields .. won't list em all... Project_id - pri, auto inc financialyear I need the drop down to pull the data from the financialyear table and then to present or focus on the currently stored data... so if the store value in the table Main is 2010/2011 if Ii was to select the update url in the list_record.php it will pull all the relevant data into update_record.php form. the financialyear field in the form should be a dropdown with all the financial years listed but the 2010/2011 is selected or focused. I still need to be able to change the entry and post this back to the table Main..... So the dropdown contains the list of years from the financialyear table but when the record is pulled from table main the year that is stored in table Main should be highlighted in the dropdown and I should be able to select a new record and post back to the table Main.. any thoughts... please don't slate for the cross post, I haven't sanatised the data at any stage. I know i'm open to injection attacks. and yes my code is a little dirty... all these will be rectified as i finalise the process and ensure the consept works. Thanks for taking the time to read and hopefully you are able to understand the requirement and are able to assist. thanks Balgrath What I'm trying to do here is generate a table based on a form in which the user selects two options. The first option tells the script which database entries to put in the table, the second option tells it how to arrange them. The first works perfectly, the second not at all - it doesn't produce an error, it just doesn't do anything. I've found a number of tutorials that seem to suggest that the problem is somewhere in my punctuation around ORDER BY '$POST[sort]' but I've been unable to find a solution that actually works. Any help would be much appreciated, my code is below. Thank you! mysql_select_db($database, $con); $result = mysql_query("SELECT * FROM main WHERE state='$_POST[state]' ORDER BY '$POST[sort]'"); if(mysql_num_rows($result)==0){ echo "<p align='left'>View Pantries by State</p><div id='stateform'> <form action='../admin/viewstate.php' method='post'> <select name='state' /> <option value='AL'>Alabama</option> <option value='AK'>Alaska</option> <option value='AZ'>Arizona</option> </select> <select name='sort' /> <option value='name'>Name</option> <option value='id'>Id</option> <option value='city'>City</option> <option value='zip'>Zip</option> <option value='timestamp'>Timestamp</option> </select> <input type='submit' value='Go'></input></form> </div>"; } else{ echo "<p align='left'>View Pantries by State</p> <div id='stateform'> <form action='../admin/viewstate.php' method='post'> <select name='state' /> <option value='AL'>Alabama</option> <option value='AK'>Alaska</option> <option value='AZ'>Arizona</option> </select> <select name='sort' /> <option value='name'>Name</option> <option value='id'>Id</option> <option value='city'>City</option> <option value='zip'>Zip</option> <option value='timestamp'>Timestamp</option> </select> <input type='submit' value='Go'></input></form> </div>"; echo "<div id='fptext'><span class='h1'>Food Pantries for " . $_POST['state'] . "</span><br><br></div>"; } echo "<table border='1' align='center' cellpadding='3' width='900px'> <tr> <th>ID</th> <th>Name</th> <th>Type</th> <th>Address</th> <th>State</th> <th>Phone</th> <th>E-mail</th> <th>Website</th> <th>Hours</th> <th>Requirements</th> <th>Additional Information</th> <th>Lat</th> <th>Lng</th> <th>Update</th> </tr>"; while($row = mysql_fetch_array($result)) { echo "<tr>"; echo "<td>" . $row['id'] . "</td>"; echo "<td>" . $row['name'] . "</td>"; echo "<td>" . $row['type'] . "</td>"; echo "<td>" . $row['address'] . "</td>"; echo "<td>" . $row['state'] . "</td>"; echo "<td>" . $row['phone'] . "</td>"; echo "<td>"; echo "<a href=mailto:".$row['email'].">".$row['email']."</a>"; echo "</td>"; echo "<td>"; echo "<a href=".$row['website']."\>".$row['website']."</a>"; echo "</td>"; echo "<td>" . $row['hours'] . "</td>"; echo "<td>" . $row['requirements'] . "</td>"; echo "<td>" . $row['additional'] . "</td>"; echo "<td>" . $row['lat'] . "</td>"; echo "<td>" . $row['lng'] . "</td>"; echo "<td><a href='../public/updatepage.php?id=".$row['id']."'>Update Pantry</a></td>"; echo "</tr>"; } echo "</table>"; mysql_close($con); ?> I have 3 tables w/ the following fields: Cities id | state_id | city_name States id | state_name New_cities id | state_id | city_name I am trying to write a script (and still working on it) that will INSERT new cities in cities WHERE New_cities.state_id = Cities.state_id. Obviously I am still in testing and results sort out, but honest to all hell, I might be going about this all wrong, and would appreciate any comments, criticism, assistance or feedback on what I am trying to do. <?php $query = "SELECT * FROM cities, states, new_cities"; if ($results = mysqli_query($cxn, $query)) { $row_cnt = mysqli_num_rows($results); echo $row_cnt . " Total Records in Query.<br /><br />"; if (mysqli_num_rows($results)) { while ($row = mysqli_fetch_array($results)) { if ($row['cities']['state_id'] == $row['new_cities']['new_state_id']) { $insert_city_query = "INSERT INTO cities id, new_state_id, city_name VALUES ('','$new_state_id','$city_name')" or mysqli_error(); } //if ($row['cities']['state_id'] == $row['new_cities']['new_state_id']) echo "$row[new_city_name]<br />"; } //while ($row = mysqli_fetch_array($results)) } //if (mysqli_num_rows($results)) } //if ($results = mysqli_query($cxn, $query)) ?> Hi all, I have a number of tables within a mysql database and I'm stuck on how to pull information from one based on the results being displayed by another. The site shows items for sale where by all the product information is held in tablea, when an individual item is being displayed I need to be able to pull a manufacturer profile from tableb based on the manufacturer name stored in tablea column profile. The manufacturer name is already being called into the page by existing code as the site has always shown the manufacturer name, I just can't work out out to pull the new profile information in as well from the new table. Any help appreciated Iain This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=308916.0 Hello all,
For the UPDATE portion of my CRUD WebApp what I would like to do is to bring in (and display) the values (of a selected row) from my transaction table.
This is working just fine for all fields which are of the "input type". The problem I'm having is with two fields which are of the "select type" i.e. dropdown listboxes.
For those two fields, I would like to bring in all the valid choices from the respective lookup/master tables, but then have the default/selected value be shown based on what's in the transaction table. The way I have it right now, those two fields are showing (and updating the record with) the very first entry's in the two lookup tables/select query.
The attached picture might make things a little bit clearer. You'll notice in the top screenshot that the first row (which is the one I'm selecting to update) has a "Store Name" = "Super Store" and an "Item Description" = "Old Mill Bagels". Now, when I click the "update" botton and I'm taken to the update screen, the values for those two fields default to the very first entries in the SELECT resultset i.e. "Food Basics" and "BD Cheese Strings". Cricled in green (to the top-left of that screenshot) is the result of an echo that I performed, based on the values that are in the transaction record.
I cannot (for the life of me) figure out how to get those values to be used as default/selected values for the two dropdown's...so that if a user does not touch those two dropdown fields, the values in the transaction table will not be changed.
Your help will be greatly appreciated.
Here's a portion of the FORM code:
<form class="form-horizontal" action="update.php?idnumber=<?php echo $idnumber?>" method="post">
<?php
// Connect to Store_Name (sn) table to get values for dropdown
$pdo = Database::connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT DISTINCT store_name FROM store_master ORDER BY store_name ASC";
$q_sn = $pdo->prepare($sql);
$q_sn->execute();
$count_sn = $q_sn->rowCount();
$result_sn = $q_sn->fetchAll();
Database::disconnect();
// Connect to Item_Description (id) table to get values for dropdown
$pdo = Database::connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT DISTINCT product_name FROM product_master ORDER BY product_name ASC";
$q_id = $pdo->prepare($sql);
$q_id->execute();
$count_id = $q_id->rowCount();
$result_id = $q_id->fetchAll();
Database::disconnect();
foreach($fields AS $field => $attr){
$current_store_name = $values['store_name'];
$current_item_description = $values['item_description'];
//Print the form element for the field.
if ($field == 'store_name')
{
echo $current_store_name;
echo '<br />';
echo $current_item_description;
echo '<div class="control-group">';
echo "<label class='control-label'>{$attr['label']}: </label>";
echo '<div class="controls">';
//Echo the actual input. If the form is being displayed with errors, we'll have a value to fill in from the user's previous submission.
echo '<select class="store-name" name="store_name">';
// echo '<option value="">Please select...</option>';
foreach($result_sn as $row)
{
echo "<option value='" . $row['store_name'] . "'>{$row['store_name']}</option>";
}
// $row['store_name'] = $current_store_name;
echo "</select>";
echo '</div>';
echo '</div>';
}
elseif ($field == 'item_description')
{
echo '<div class="control-group">';
echo "<label class='control-label'>{$attr['label']}: </label>";
echo '<div class="controls">';
echo '<select class="item-desc" name="item_description">';
// echo '<option value="">Please select...</option>';
foreach($result_id as $row)
{
echo "<option alue='" . $row['product_name'] . "'>{$row['product_name']}</option>";
}
echo "</select>";
echo '</div>';
echo '</div>';
}
else
{
echo '<div class="control-group">';
echo "<label class='control-label'>{$attr['label']}: </label>";
echo '<div class="controls">';
//Echo the actual input. If the form is being displayed with errors, we'll have a value to fill in from the user's previous submission.
echo '<input type="text" name="'.$field.'"' . (isset($values[$field]) ? ' value="'.$values[$field].'"' : '') . ' /></label>';
echo '</div>';
echo '</div>';
}
And here's some other declarations/code which I think might be required.
$fields = array(
'store_name' => array('label' => 'Store Name',
'error' => 'Please enter a store name'),
'item_description' => array('label' => 'Item Description',
'error' => 'Please enter an item description'),
'qty_pkg' => array('label' => 'Qty / Pkg',
'error' => 'Please indicate whether it\'s Qty or Pkg'),
'pkg_of' => array('label' => 'Pkg. Of',
'error' => 'Please enter the quantity'),
'price' => array('label' => 'Price',
'error' => 'Please enter the price'),
'flyer_page' => array('label' => 'Flyer Page #',
'error' => 'Please enter the flyer page #'),
'limited_time_sale' => array('label' => 'Limited Time Sale',
'error' => 'Please enter the days for limited-time-sale'),
'nos_to_purchase' => array('label' => 'No(s) to Purchase',
'error' => 'Please enter the No. of items to purchase')
);
...
...
....
{
// If [submit] isn't clicked - and therfore POST array is empty - perform a SELECT query to bring in
// existing values from the table and display, to allow for changes to be made
$pdo = Database::connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT * FROM shoplist where idnumber = ?";
$q = $pdo->prepare($sql);
$q->execute(array($idnumber));
$values = $q->fetch(PDO::FETCH_ASSOC);
if(!$values)
{
$error = 'Invalid ID provided';
}
Database::disconnect();
}
If there's anything I've missed please ask and I'll provide.
Thanks
This topic has been moved to HTML Help. http://www.phpfreaks.com/forums/index.php?topic=323045.0 I have a mysql table which will store users email addresses (each is unique and is the primary field) and a timestamp. I have added another column called `'unique_code' (varchar(64), utf8_unicode_ci)`. What I would very much appreciate assistance with is; a) Generating a 5 digit alphanumeric code, ie: 5ABH6 b) Check all rows the 'unique_code' column to ensure it is unique, otherwise re-generate and check again c) Insert the uniquely generated 5 digit alphanumeric code into `'unique_code'` column, corresponding to the email address just entered. d) display the code on screen. What code must I put and where? **My current php is as follows:** Code: [Select] require "includes/connect.php"; $msg = ''; if($_POST['email']){ // Requested with AJAX: $ajax = ($_SERVER['HTTP_X_REQUESTED_WITH'] == 'XMLHttpRequest'); try{ if(!filter_input(INPUT_POST,'email',FILTER_VALIDATE_EMAIL)){ throw new Exception('Invalid Email!'); } $mysqli->query("INSERT INTO coming_soon_emails SET email='".$mysqli->real_escape_string($_POST['email'])."'"); if($mysqli->affected_rows != 1){ throw new Exception('You are already on the notification list.'); } if($ajax){ die('{"status":1}'); } $msg = "Thank you!"; } catch (Exception $e){ if($ajax){ die(json_encode(array('error'=>$e->getMessage()))); } $msg = $e->getMessage(); } } |
|||||||