|
PHP - Pull Distinct Value From Database
field1 / field2 10 / England 15 / Italy 20 / France 15 / France 30 / USA When searching for France: SELECT DISTINCT field2, field1 FROM $tableName would return a distinct value. I want to ensure it returns the highest value in field1. Something like this: SELECT DISTINCT field2 (but ensure returns highest field 1 value), field1 FROM $tableName Similar Tutorialsanybody has any idea what the code is to put in a main image. So far i was able to find this but it pulls up all the images added in the album. I am only looking for the first image. Thank you. <ul class="thumbs thumbs_nocaptions"> <?php foreach( $this->paginator as $photo ): ?> <li> <a class="thumbs_photo" href="<?php echo $photo->getHref(); ?>"> <span style="background-image: url(<?php echo $photo->getPhotoUrl('thumb.normal'); ?>);"></span> </a> </li> <?php endforeach;?> </ul> hi all, i have a profile page which is a form that asks users to enter there personal details such as name, location, mobile number etc and saves this info with a mysql database, however when you return to the edit profile page the fields are blank and i want them to pull the details the user has entered (if any) and display it, so if a user entered there name as Lee, then returned to the edit profile page there name field would show lee instead of being empty. heres my profiles page code, any help would be great Code: [Select] <?PHP session_start(); if (!(isset($_SESSION['username']) && $_SESSION['username'] != '')) { header ("Location: login.php"); } ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <!-- Author: Reality Software Website: http://www.realitysoftware.ca Note: This is a free template released under the Creative Commons Attribution 3.0 license, which means you can use it in any way you want provided you keep the link to the author intact. --> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title></title> <link href="style.css" rel="stylesheet" type="text/css" /> <style type="text/css"> .auto-style2 { font-size: 25px; } </style> </head> <body> <div id="container"> <!-- header --> <div id="header"> <div id="logo"><a href="#"><span class="orange">SouthWest</span> LAN's</a></div> <div id="menu"> <ul> <li><a href="index.php">home</a></li> <li><a href="register.php">Register</a></li> <li><a href="account.php">My Account</a></li> <li><a href="forum.php">Forums</a></li> <li><a href="faq.php">FAQ</a></li> </ul> </div> </div> <!--end header --> <!-- main --> <div id="main"> <div id="content"> <div id="head_image"> <div id="slogan"><strong><span class="auto-style2">Organising LAN Parties in the SouthWest</span></strong><br /></div> <div id="under_slogan_text"></div> </div> <div id="text"> <h1>Edit Profile</h1> <br /> <form name="register" method="post" action="process_p.php"> <table border="0" width="225" align="center"> <tr> <td width="219" bgcolor="#999999"> <p align="center"><font color="white"><span style="font-size:12pt;">Please fill out as much as possible</span></font></p> </td> </tr> <tr> <td width="219"> <table border="0" width="282" align="center"> <tr> <td width="116"><span style="font-size:10pt;">Username: </span></td> <td width="156"><?php echo $_SESSION['username']; ?></td> </tr> <tr> <td width="116"><span style="font-size:10pt;">Email: </span></td> <td width="156"><input type="text" name="email" maxlength="30"></td> </tr> <tr> <td width="116"><span style="font-size:10pt;">Real Name: </span></td> <td width="156"><input type="text" name="real_name"></td> </tr> <tr> <td width="116"><span style="font-size:10pt;">Location: </span></td> <td width="156"><input type="text" name="location"></td> </tr> <tr> <td width="116"><span style="font-size:10pt;">Mobile Number: </span></td> <td width="156"><input type="text" name="mobile_number"></td> </tr> <tr> <td width="116"><span style="font-size:10pt;">Instant Messager: </span></td> <td width="156"><input type="text" name="instant_messaging"></td> </tr> <tr> <td width="116"> </td> <td width="156"> <p align="right"><input type="submit" name="update" value="Update Profile"></p> </td> </tr> </table> I have a form that is submitting to a database currently. I would like to check the data and be able to update it with a notes section. The form is currently 80 text boxes, dropdowns, and text areas being submitted to the database. I need this info to populate after the user id is entered and a button is pressed. I will have to update this page frequently with notes. I am going to create a new table just for the notes and will attach the customer id to it also. If you can help me out with this i would appricate it. I can post my current code or an example of the code. Hello All, I have a contact directory database. It has all the employees of my company (name, phone, email, department, building, etc). Say on one page I have the Marketing Department, and I want to say: "The Marketing Department Director is ________" How would I assign that value from the database? Do I want to put in a unique "keyword" field in the database, but then how would I store all the values automatically on the page? I see pages where I would want to list the Marketing Director, and his secretary, then another page with the Sales Director, and his secretary, etc.... all with being able to change the values in the database, and it changing across all the pages instantly. Do I need to say on every page "select * from database where keyword = marketingdirector" and then store that result as a variable? It seems unpractical to repeat that a few times for each different person I want to list. Is there a better way to do this then I'm thinking? Thanks all! Hi am having a few problems solving this code with select distinct clause. None of what i tryed works. Can anyone help please thanks. this is just some of the query's i tryed $query7 = yasDB_select("SELECT DISTINCT * FROM useronline WHERE id;"); $query7 = yasDB_select("SELECT DISTINCT ip FROM useronline WHERE id;"); $query7 = yasDB_select("SELECT DISTINCT ip FROM useronline WHERE ip;"); $query7 = yasDB_select("SELECT DISTINCT ip,timestamp FROM useronline WHERE id;"); $query7 = yasDB_select("SELECT DISTINCT id,ip,timestamp FROM useronline WHERE id;"); $query7 = yasDB_select("SELECT DISTINCT ip FROM useronline;"); and again but without ";" $query7 = yasDB_select("SELECT DISTINCT * FROM useronline WHERE id"); $query7 = yasDB_select("SELECT DISTINCT ip FROM useronline WHERE id"); $query7 = yasDB_select("SELECT DISTINCT ip FROM useronline WHERE ip"); $query7 = yasDB_select("SELECT DISTINCT ip,timestamp FROM useronline WHERE id"); $query7 = yasDB_select("SELECT DISTINCT id,ip,timestamp FROM useronline WHERE id"); $query7 = yasDB_select("SELECT DISTINCT ip FROM useronline"); this is the code am working on. Code: [Select] $query7 = yasDB_select("SELECT DISTINCT * FROM useronline WHERE id;"); $visitors_online = $query7->fetch_array(MYSQLI_ASSOC); $visitors_online = $query7->num_rows; $query7->close(); visitors online : <?php echo $visitors_online;?><br/> How can I stop duplication in the below code? Where do I implement the DISTINCT function? $sql="SELECT * FROM ((resource l inner join resource_skill ln on l.Resource_ID = ln.Resource_ID) inner join skill n on ln.Skill_ID = n.Skill_ID) WHERE First_Name LIKE '%" . $name . "%' OR Last_Name LIKE '%" . $name ."%' OR Skill_Name LIKE '%" . $name ."%'"; //-run the query against the mysql query function $result=mysql_query($sql); //-create while loop and loop through result set while($row=mysql_fetch_array($result)){ $First_Name =$row['First_Name']; $Last_Name=$row['Last_Name']; $Resource_ID=$row['Resource_ID']; //-display the result of the array echo "<ul>\n"; echo "<li>" . "<a href=\"a.php?id=$Resource_ID\">" .$First_Name . " " . $Last_Name . "</a></li>\n"; echo "</ul>"; } } Hello, Hoping someone can help... I am pulling records from a db of cities and state abbrevs. My db has many duplicate city names but i want to echo out only the distinct ones. My query is based on a radius around a city and i think this might be what is tripping up the DISTINCT mysql query. Here is what i have: Code: [Select] $teachradius = 50; $add_under = array(" " => "_"); $query = sprintf("SELECT DISTINCT city, state_abbrev, ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM cities HAVING distance < '%s' ORDER BY distance LIMIT 0 , 20", mysql_real_escape_string($lat_i), mysql_real_escape_string($lng_i), mysql_real_escape_string($lat_i), mysql_real_escape_string($teachradius)); $result = mysql_query($query); while ($row = @mysql_fetch_assoc($result)){ echo '<li><a href="/' . strtolower($row['state_abbrev']) . '/' . strtolower(strtr($row['city'],$add_under)) . '.html">' . ucwords($row['city']) . ', ' . $row['state_abbrev'] . ' Dogs</a></li>',"\n"; } Can you see what I might be doing incorrectly in my query so that i can echo out distinct city names / state abbrevs only? Thanks in advance... This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=343149.0 Hello. I am trying to display only one instance of records that have the same memberid in my db. I am using the following statement but it continues to show all of the records that have the same memberid. Any ideas what I may be doing wrong? Code: [Select] $sql = "select DISTINCT memberid, event, category, date, enddate, locality, location, address, city, state, zip, contact, phone, notes, doc1, doc2, doc3, doc4, doc5 from event where date >= '$datenow' ORDER by date ASC"; Thanks for any help! Hi guys, using the code below within an admin panel to create a drop down allowing the user to select the profiles they wish to assign to the record they're creating, problem we have is that once a record is created, if they need to edit it for what ever reason the selected profile option isn't sticking. I've played around with lots of variants of if existing_record to try and get it add selected="selected" into the code but failed at every attempt, any advice gratefully received. Code: [Select] <?php // List only breeder profiles in the database echo '<select name="profile" class="textinput noborder">'; echo '<option value="any">Any</option>'; $qryGetDistinctProfile = "SELECT * FROM profiles ORDER BY title ASC"; $resGetDistinctProfile = mysql_query($qryGetDistinctProfile,$connection) or die(mysql_error()); if(mysql_num_rows($resGetDistinctProfile) > 0){ $id = mysql_result($resProfile, 0, "id"); while ($row = mysql_fetch_assoc($resGetDistinctProfile)){ echo '<option value="'.$row['id'].'" >'.$row['title'].'</option>'; } } echo '</select>'; ?> I need to add likes_username to this query and use a DISTINCT on it.
It currently counts how many likes a status has but in the table there are some statuses with multiple likes from the same username.
SELECT s.*, COUNT(l.likes_location_id) AS likeCount FROM stream AS s LEFT JOIN likes AS l ON ( l.likes_location_id = s.stream_id ) GROUP BY s.stream_id ORDER BY s.stream_id DESC LIMIT 50Many thanks, So, I've been trying to get this query working and can't quite get it to work. I'm trying to get an "array" of distinct browsers from the database, but it's only showing one of them. There are 3 unique browsers in the table and only "Chrome 30" gets returned. Here is the query:
SELECT DISTINCT `browser` AS `unique_browsers`,
COUNT(DISTINCT `ip`) AS `unique_visitors`,
COUNT(DISTINCT `country`) AS `unique_countries`,
COUNT(`id`) AS `total_count`,
(SELECT COUNT(`id`) FROM `table` WHERE `browser` LIKE '%Chrome%') AS `chrome_count`,
(SELECT COUNT(`id`) FROM `table` WHERE `browser` LIKE '%Internet Explorer%') AS `ie_count`,
(SELECT COUNT(`id`) FROM `table` WHERE `browser` LIKE '%Firefox%') AS `firefox_count`,
(SELECT COUNT(`id`) FROM `table` WHERE `browser` LIKE '%Safari%') AS `safari_count`,
(SELECT COUNT(`id`) FROM `table` WHERE `browser` LIKE '%Opera%') AS `opera_count`,
(SELECT COUNT(`id`) FROM `table` WHERE `browser` NOT LIKE '%Chrome%' AND `browser` NOT LIKE '%Internet Explorer%' AND `browser` NOT LIKE '%Firefox%' AND `browser` NOT LIKE '%Safari%' AND `browser` NOT LIKE '%Opera%') AS `unknown_count`
FROM `table`
GROUP BY `browser`
Everything works properly except the line:I am getting a little frustrated, and I need some help. I've working on this all day (I am new... otherwise I'd fly through it). This is a simple problem I just can't seem to put a ' in the right spot or something... Here's the gist. In file myphotos.php is the following: <?PHP include('functions.php'); $link $title = 'Light Graspers Certification'; $linktent = '<div class="titler">Photo Review</div><div class="contentm">'.$data[index].'</div>'; $excess = '<div class="titlerex">Testimonials</div><div class="contentmex">'.$displayoptions.'</div>'; echo getphoto($title, $linktent, $excess); ?> Now my goal is under $displayoptions the following will happen. SELECT DISTINCT name FROM homework WHERE uid='$loggedin' ORDER BY id"; With proper code I should see a distinct list of all the "name" rows. The user clicks on it to be directed to just those rows... etc... Here is was some old code I tried manipulating to get the same result... but I couldn't seem to get it to work. Of course I only need one column in my new code. Any help would be a appreciated. Adam $columns = 5; //change the query to get another field from the database $query = "SELECT DISTINCT city,state FROM church WHERE state='$state' ORDER BY city"; $result = mysql_query($query); $num_rows = mysql_num_rows($result); $rows = ceil($num_rows / $columns); while($row = mysql_fetch_array($result)) { $data[] = $row['city']; //store the other field into an array } for($i = 0; $i < $rows; $i++) { { echo "<tr valign=bottom>"; echo "<td bgcolor=#2172A1 colspan=10><img src=img/blank.gif width=1 height=1></td>"; echo "</tr>"; } echo "<TR valign=center>"; for($j = 0; $j < $columns; $j++) { if(isset($data[$i + ($j * $rows)])) { echo "<td class=tabval><b><a href='findnew.php?state=".$state."&city=" . $data[$i + ($j * $rows)] . "'>" . $data[$i + ($j * $rows)] . "</a></b></td>"; } } echo "</TR>"; } echo "<tr valign=bottom>"; echo "<td bgcolor=#2172A1 colspan=10><img src=img/blank.gif width=1 height=8></td>"; echo "</tr>"; echo "$state represents $num_rows3 \n of our $num_rows2 churches\n that we serve. "; } i have 12 distinct albums displaying 8 per page $number rows echo's out to be 1 so to me says my query is wrong how to count only distinct albums Code: [Select] $sql = "SELECT COUNT(DISTINCT album) FROM belsgallery GROUP BY album"; $result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR); $r = mysql_fetch_row($result); $numrows = $r['0']; echo $numrows; // RETURNS 1 $rowsperpage = 8; $totalpages = ceil($numrows / $rowsperpage); I have 5 entries in a table Code: [Select] $sql = "select count(distinct columnName) from table"; $result = mysql_result($sql); $count = mysql_fetch_array($result); echo $count[0];The output is 5 as expected. Code: [Select] $sql = "select distinct columnName from table"; $result = mysql_result($sql); $count = mysql_fetch_array($result); echo $count[0];the ouput is the first file name as expected, however Code: [Select] echo $count[1];gives undefined offset 1, which does not make any sense. Can anyone explain why the offset 1 is undefined if the count is 5? Hello, Ive got a mysql database but i used a script to add a bunch of file names but it entered with a few errors and theres quite a few that got entered 4-5 times. I can get all the names fine using "SELECT DISTINCT name FROM games" but how can i export all the other fields not just the names. I want to select everything but only DISTINCT on the names I am trying to create an autocomplete form for 'city,state zip' I want to be able to search by either a distinct zip code that will show 'city, state zip' or by distinct city 'city, state' Can anyone tell me how to fix my script? $sql = "SELECT DISTINCT zip,city,state FROM `residential` WHERE `zip` LIKE '$input%' OR `city` LIKE '$input%' OR `state` LIKE '$input%' UNION SELECT DISTINCT city,state,zip FROM `residential` WHERE `zip` LIKE '$input%' OR `city` LIKE '$input%' OR `state` LIKE '$input%' UNION SELECT DISTINCT state,city,zip FROM `residential` WHERE `zip` LIKE '$input%' OR `city` LIKE '$input%' OR `state` LIKE '$input%' LIMIT $limit"; $result = mysql_query($sql); if (!$result || !mysql_num_rows($result)) exit; include_once "headers.php"; echo "<response>"; while ($row = mysql_fetch_array($result)) { $keywords = "$row[city], $row[state] $row[zip]"; echo "<keywords>". $keywords ."</keywords>"; } while ($row = mysql_fetch_array($result)) { $keywords = "$row[city], $row[state]"; echo "<keywords>". $keywords ."</keywords>"; } echo "</response>"; Hi: I have the foll. code. The table "Reports" has multiple records for a given value of CID in the Field CID. I'd like to be able to select only 1 of them so that a list of customers appearing in the Reports table is available for selection in the dropdown alphabetically. The foll. code does it but it doesnt list the Customers alphabetically. And when I use Join, the query doesnt run. I get a blank list . The Field CID is common to both tables- Reports and Customers. Could someone help me with the Join ? Thanks. Swat Code: [Select] <?php $sqlco = "SELECT DISTINCT CID FROM `Reports` "; $resultco = mysql_query($sqlco) or die (mysql_error() ) ; if ($myrowco = mysql_fetch_array($resultco) ) { do { $cid = $myrowco["CID"]; $sqlrep = "SELECT * FROM `Customers` WHERE `CID` = '$cid' " ; $resultrep = mysql_query($sqlrep) or die (mysql_error() ) ; $myrowrep = mysql_fetch_array($resultrep); $company = $myrowrep["Company"]; printf("<option value=%d> %s , %s", $myrowco["CID"], $myrowrep["Company"], $myrowco["Mdate"]); } while ($myrowco = mysql_fetch_array($resultco)); } else { echo "No records found." ; } ?></select></a> What i tried was this : Code: [Select] <?php $sqlco = "SELECT DISTINCT CID FROM `Reports` r JOIN `Customers` c WHERE r.CID = c.CID ORDER BY c.Company asc "; $resultco = mysql_query($sqlco) or die (mysql_error() ); if ($myrowco = mysql_fetch_array($resultco) ) { do { printf("<option value=%d> %s ", $myrowco["CID"], $myrowco["Company"]); } while ($myrowco = mysql_fetch_array($resultco)); } else { echo "No records found." ; } ?> Ive been scanning over and over this code and I cant work out why my 2nd drop down menu doesnt have unique values. Please if anyone can give me guidance, you never know I may be able to get rid of my headache! Code: [Select] <body> <p> <form action="" method="post"> <select name="drop_1" id="drop_1"> <option value="" selected="selected" disabled="disabled">Select a Category</option> <?php getTierOne(); ?> </select> <span id="wait_1" style="display: none;"> <img alt="Please Wait" src="ajax-loader.gif"/> </span> <span id="result_1" style="display: none;"></span> </form> </p> <p> <?php if(isset($_POST['submit'])){ $drop = $_POST['drop_1']; $tier_two = $_POST['Subtype']; echo "You selected "; echo $drop." & ".$tier_two; } ?> </body> Code: [Select] <?php function getTierOne() { $result = mysql_query("SELECT DISTINCT Type FROM business") or die(mysql_error()); while($tier = mysql_fetch_array( $result )) { echo '<option value="'.$tier['Type'].'">'.$tier['Type'].'</option>'; } } if($_GET['func'] == "drop_1" && isset($_GET['func'])) { drop_1($_GET['drop_var']); } function drop_1($drop_var) { include_once('db.php'); $result = mysql_query("SELECT DISTINCT Subtype FROM business WHERE Type='$drop_var'") or die(mysql_error()); echo '<select name="Subtype" id="Subtype"> <option value=" " disabled="disabled" selected="selected">Choose one</option>'; while($drop_2 = mysql_fetch_array( $result )) { echo '<option value="'.$drop_2['Subtype'].'">'.$drop_2['Subtype'].'</option>'; } echo '</select> '; echo '<input type="submit" name="submit" value="Submit" />'; } ?> |
|||||||