PHP - Help Selecting Distinct Values From 3 Columns

This:
Code: [Select]
echo "<pre>";
print_r($torrent->content());
echo "</pre>";
Returns:
Code: [Select]
Array
(
    [Eureka.S05E01.HDTV.XviD-ASAP\Eureka.S05E01.HDTV.XviD-ASAP.avi] => 367011826
    [Eureka.S05E01.HDTV.XviD-ASAP\eureka.s05e01.hdtv.xvid-asap.nfo] => 4798
    [Eureka.S05E01.HDTV.XviD-ASAP\Sample\eureka.s05e01.hdtv.xvid-asap.sample.avi] => 7883092
)
Needs to be inserted to MySQL like:


In other words, the array $torrent->content() needs to be inserted to MySQL as new lines at columns "filename" and "size".

Please help me..

Hello,   I've been going about reading different posts on here and other forums but so far I haven't been able to come across something that works for my purpose so after all the reading I thought I'd finally just ask. I'm rather new at php/sql queries so please bare with me.   First what I'm trying to accomplish.   I need a form with several fields (options) to fetch information from a table that will then display the results based on the options selected.   - from date - to date - employee name - department - branch - product line   In other words, the purpose is to be able to choose a date range (from one day to up to a year or more) and then to be able to choose either ONE employee name to view statistics invididually from a given department and branch or to select a department to view all the statistics for everyone under that given department and/or brach and/or product line.   Now the tricky part is that besides fetching the records, calculations need to be made before the records are displayed and that part right here is what is giving me a huge headache.   This is the query that I have.

SELECT
report_daily_id,
report_date,
emp_id,
emp_fullname,
emp_dept,
emp_branch
prod_line
calls,
tk_time,
hld_time,
ac_time,
tran_calls,
work_time,
tran_rate,
ah_time
FROM daily_report
GROUP BY emp_id, emp_fullname, emp_branch, emp_dept, prod_line

The calculations based on the date range and one or more of the other options need to give me the following results.

SUM(calls) AS 'Total Calls'
SUM(tk_time) / SUM(calls) AS 'Talk Time'
SUM(hld_time) / SUM(calls) AS 'Held Time'
SUM(ac_time) / SUM(calls) AS 'AC Time'
SUM(tran_calls) AS 'Total Trans'
SUM(tran_calls) / SUM(calls) AS 'Tran Rate'
SUM(ah_time) / SUM(calls) AS 'AH Time'

I don't know how else to explain myself past this point but if you have any questions, perhaps I can answer it and give more details.   Thank you,

Hello,
I am trying insert the values of csv file into a table. I want to concatenate the values of some columns of csv into one String column of table by delimiting them my commas.
 
$im="INSERT into TB1 (col1, col2 ) values ('$a[2]','$a[3]')";
$b = "";
$b=".'$a[6]'."||".'$a[7]'."||".'$a[8]'."||".'$a[9]'.";
$im1=$im+"UPDATE TB1 set latlng=.'$aline'.";
mysql_query($im1) OR die(mysql_error());

I need to enter the value of $a[6] till $a[9] into a string column with delimiters and insert into the column col3 of TB1 with value as $a[4].
I am stuck up here could anyone help me with this issue.

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=313325.0

I created a simple search box which will query my table and match the input value to one of my columns. two of these columns store comma separated values. if i query a column other than a column which stores my csv i can see my search results. if i query a column which stores my csv i will not see results unless the search value matches the first value within the column. how would i be able to get say the second or third or forth value. here is the code i am using to query the table any help would be appreciated thanks.

Code: [Select]
$q = $this->db->query("SELECT * FROM table WHERE col1 LIKE 'searchvalue'".
             " OR col2 LIKE 'searchvalue'".
             " OR col3 LIKE 'searchvalue'".
             " OR col4 LIKE 'searchvalue'".
             " OR FIND_IN_SET('searchvalue', col5) > 0 ".
             " OR FIND_IN_SET('searchvalue', col6) > 0");

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 !

PHP script return 20 UL LIST values like,

< ul >

A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T

< /ul >

How to display UL LIST into row wise 5 columns like

A B C D
E F G H
I J K L
M N O P
Q R S T (20 ITEMS)

Edited December 7, 2019 by aveeva

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

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

Many thanks,  

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=343149.0

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

}