|
PHP - Moved: Help Using Distinct With Select *
This topic has been moved to MySQL Help.
http://www.phpfreaks.com/forums/index.php?topic=343149.0 Similar TutorialsThis topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=322629.0 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>"; } } 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/> 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 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. 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>'; ?> 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." ; } ?> Hello folks,
In trying to improve the user experience for my first WebApp I have decided to create two new tables - one a master file to contain a list of all stores, and the second a master file to contain a list of all products that are normally purchased - and I would like to use the values from these two tables as lookup values in dropdown listboxes, to which the user can also add new entries.
As it turns out, I'm stuck on the very first objective i.e. to lookup/pull-in the distinct values from the master tables.
The problem I'm having is that the query seems to return no rows at all...in spite of the fact that there a records in the table, and the exact same query (when run within the MySQL environment) returns all the rows correctly.
Is there something wrong with my code, or how can I debug to see whether or not the query is being executed?
Objective # 2, which is to allow new values to be entered into the dropdown listbox, and then inserted into the respective table is certainly waaay beyond my beginner skills, and I'll most certainly need to some help with that as well..so if I can get some code/directions in that regard it will be most appreciated.
Thank you.
<?php
$sql = "SELECT DISTINCT store_name FROM store_master ORDER BY store_name ASC";
$statement = $conn->prepare($sql);
$statement->execute();
$count = $statement->rowCount();
echo $count;
// fetch ALL results pertaining to the query
$result = $statement->fetchAll();
echo "<select name=\"store_name\">";
echo '<option value="">Please select...</option>';
foreach($result as $row)
{
echo "<option value='" . $row['store_name'] . "'></option>";
}
echo "</select>";
?>
I am trying to print the list of a table which I requested with "SELECT DISTINCT" as below Code: [Select] $db_connect = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME); $sql_get = "SELECT DISTINCT category FROM con"; $sql_run = mysqli_query($db_connect, $sql_get) or mysqli_error($db_connect); $sql_assoc = mysqli_fetch_assoc($sql_run); What is now needed to print the list of the table data by this conditions? I tried the while loop, but I seem to approach wrong, and get endless loops or errors. This topic has been moved to Ajax Help. http://www.phpfreaks.com/forums/index.php?topic=320341.0 This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=310935.0 This topic is now in MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=357554.0 This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=309828.0 This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=357712.0 This topic has been moved to JavaScript Help. http://www.phpfreaks.com/forums/index.php?topic=347920.0 This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=313023.0 This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=350716.0 This topic has been moved to JavaScript Help. http://www.phpfreaks.com/forums/index.php?topic=305968.0 This topic has been moved to JavaScript Help. http://www.phpfreaks.com/forums/index.php?topic=348848.0 |
|||||||