Merging columns if a column has a count > 1

Question 1

Lotier’s Question:

I’m trying to create a column that is merged column if the count of column_1 is greater than 1, and just the value of one of the columns if 1, when matching to column_1. So far I can get the column to merge properly, but it only returns the first row back, not all of them.

Example Table

id, col_1,   col_2,   col_3
---------------------------
1, 'Name1', 'Value',  '1',
2, 'Name2', 'Value',  '2',
3, 'Name2', 'Value',  '3',
4, 'Name3', 'Value2', '1',
5, 'Name2', 'Value3', '1';

What I would like back

2, Name2, Value 2
3, Name2, Value 3
5, Name2, Value3

What I’ve attempted so far

SELECT id,
IF (count(col_2) > 1, concat(col_2, ' ', col_3), col_2) as merge
FROM mytable 
where col_1 = 'Name2'

which only returns ‘2, Value 2’ Thanks for the help. I’ve messed with it at http://sqlfiddle.com/#!2/9ce7e/7

Answer

Starx

count() is a aggregate function, thus it return single row as output.

You can user the following query to make sure col_2 has some value before concatenating

SELECT id,
IF (col_2 <> "" , concat(col_2, ' ', col_3), col_2) as merge
FROM mytable 
where col_1 = 'Name2'

Question 2

Sravis’s Question:

sravis

I have a Table structure like below

+--------+---------+
| cat_id | user_id |
+--------+---------+
|     10 |       1 |
|     10 |       2 |
|     11 |       3 |
|     11 |       4 |
+--------+---------+

I’m trying to get the result like below

Array
(
    [cat_id] => 10,
    Array
    (
        [user_id] => 1,
        [user_id] => 2
    )
)

Array
(
    [cat_id] => 11,
    Array
    (
        [user_id] => 3,
        [user_id] => 4
    )
)

I tried using group it didn’t work, If i try with sub query i get error message “Subquery returns more than 1 row.”

Is it possible to achieve this kind of result using mysql query?

Answer

Starx

No, MySQL api funcions provide a result set of your records. They cannot customize to your requirements. You have to create your own functions to develop that format.

Start by getting unique category id and they go through them in a loop to get there remaining details.

$mainArray = []; //Create a array to store the records
$result = mysqli_query($conn, "SELECT DISTINCT('cat_id') as catid FROM `yourtable`");
while($row = mysqli_fetch_assoc($result)) {       

    //Now another query to fetch the users
    $res = mysqli_query($conn, "SELECT * FROM `yourtable` WHERE cat_id='$row[0]'");

    $users = array(); //array to store users
    while($r = mysqli_fetch_assoc($res)) {
         $users[] = $r['user_id'];
    }

    // Now add to your main array
    $mainArray[] = array('cat_id' => $row['catid'], "users" => $users);
}
var_dump($mainArray);

Question 3

Question by Rogers

Rogers

Ok , so I am trying to dynamically insert elements into two arrays , which are $prov_date and $decom_date. The thing is that when I test the elements that are entered for both arrays .. $decom_date has all the 2 dates pushed into the array when the status is decommissioned ,which is correct but $prov_date only has one element pushed into the array and it should have also 2 dates as shown in the database table, but only one date is there .. what could be wrong with my code below for the provisioned status ?

 $prov_sql2 = '
  SELECT
    date_format(StatusChangeTimestamp,"%d-%m-%Y %H:%m") as StatusChangeTimestamp,
    date_format(StatusChangeTimestamp,"%e/%c/%Y %H:%m") as display_StatusChangeTimestamp,
    month(StatusChangeTimestamp) as prov_month,
    year(StatusChangeTimestamp) as prov_year,
    date_format(StatusChangeTimestamp,"%d-%m-%Y %H:%m")as display_date,
    Status
  FROM 
    STxStatusChangeHistory
  WHERE 
    ESN = "'.$row2["ESN"].'"';

 $prov_date  = array();
 $decom_date = array();
 $prov_result2 = mysql_query($prov_sql2);

 while ($prov_row2 = mysql_fetch_array($prov_result2, MYSQL_ASSOC)) {
   if ($prov_row2['Status'] == "Provisioned") {
     array_push($prov_date, $prov_row2["display_date"]);
   }

   if ($prov_row2['Status'] == "Decommissioned") {
     array_push($decom_date, $prov_row2["display_date"]);
   }

 }

@Starx this is what is returned from the query

array(6) { ["StatusChangeTimestamp"]=> string(16) "16-10-2010 13:10" ["display_StatusChangeTimestamp"]=> string(16) "16/10/2010 13:10" ["prov_month"]=> string(2) "10" ["prov_year"]=> string(4) "2010" ["display_date"]=> string(16) "16-10-2010 13:10" ["Status"]=> string(11) "Provisioned" } array(6) { ["StatusChangeTimestamp"]=> string(16) "18-10-2010 10:10" ["display_StatusChangeTimestamp"]=> string(16) "18/10/2010 10:10" ["prov_month"]=> string(2) "10" ["prov_year"]=> string(4) "2010" ["display_date"]=> string(16) "18-10-2010 10:10" ["Status"]=> string(14) "Decommissioned" } array(6) { ["StatusChangeTimestamp"]=> string(16) "12-10-2010 13:10" ["display_StatusChangeTimestamp"]=> string(16) "12/10/2010 13:10" ["prov_month"]=> string(2) "10" ["prov_year"]=> string(4) "2010" ["display_date"]=> string(16) "12-10-2010 13:10" ["Status"]=> string(12) "Provisioned " } array(6) { ["StatusChangeTimestamp"]=> string(16) "14-10-2010 13:10" ["display_StatusChangeTimestamp"]=> string(16) "14/10/2010 13:10" ["prov_month"]=> string(2) "10" ["prov_year"]=> string(4) "2010" ["display_date"]=> string(16) "14-10-2010 13:10" ["Status"]=> string(14) "Decommissioned" } array(1) { [0]=> string(16) "16-10-2010 13:10" }

Answer

Answer by Dave Hale

Dave Hale

You have a space after the provisioned in your db table for the 2nd entry.

Use trim maybe to ensure no whitespace carried over from the db.

if (trim($prov_row2['Status']) == "Provisioned") {

Answer

Answer by Starx

Starx

How about you change your implementation. Is basically does the same thing.

$prov_date[] = $prov_row2["display_date"];

Question 4

Question by HartleySan

HartleySan

I apologize for the vagueness of my question title, but I don’t even know what to call what I’m trying to accomplish.

The best way to describe what I want is that I want to be able to chain a bunch of items together, and then (possibly) recursively find all the items that are part of any chains that contain the target item. For example, note item3 in the following chains:

item1 => item2 => item3 => item4
item5 => item3 => item6  
item3 => item7 => item8  
item3 => item9 => item10  
item11 => item12 => item13 => item3

If a user were to do a search for item3, then I’d want all five chains above to be displayed. In other words, I want to be able to find all descendants and ancestors of item3, so that I can display the data in an HTML table (or whatever HTML structure works best).
The thing that makes this tricky is that (as shown above) any given item may have many descendants and many ancestors. As such, I’m not sure that regular recursion in MySQL would work.
I did have a look at both of the articles linked to in the top answer for the following SO thread, but I don’t think that the suggested solutions will work for my desired data structure:
Mysql recursion?

Is there any way to structure this kind of data into a MySQL DB so that with fairly easy and lightweight queries (i.e., hopefully one query per item request), I can get the information and structure I’m looking for?
Thank you very much.

Answer

Answer by Starx

Starx

I have a suggestion.

Store item in the following structure.

+---------+-----------+
|   id    |    item   |
+---------+-----------+
|   1     |   item3   |
+---------+-----------+

And add the link references in the following

+---------+-----------+------------+
|  itemid |  ancestor | descendant |
+---------+-----------+------------+
|  1      |  3        | 2          |
+---------+-----------+------------+
|  1      |  5        | 7          |
+---------+-----------+------------+

Create a index on all three columns. This will enable you to add same time as many times as it appears on a chain.
Also you can query a particular item to find all its related links.

Question 5

Question by neeko

neeko

I have some old mysql_query queries in my code which i want to convert in to PDO but am struggling to get to work.

my original code was:

mysql_query("UPDATE people SET price='$price', contact='$contact', fname='$fname', lname='$lname' WHERE id='$id' AND username='$username' ")
or die(mysql_error());

now i am trying:

$sql = "UPDATE people SET price='$price', contact='$contact', fname='$fname', lname='$lname' WHERE id='$id' AND username='$username'";
$q   = $conn->query($sql) or die("failed!");

but can’t seem to get it to work, any ideas?

UPDATED CODE:

$conn = new PDO("mysql:host=$host;dbname=$db",$user,$pass);


 // check if the form has been submitted. If it has, process the form and save it to the   database
 if (isset($_POST['submit']))
 { 
 // confirm that the 'id' value is a valid integer before getting the form data
 if (is_numeric($_POST['id']))
  {
 // get form data, making sure it is valid
 $id = $_POST['id'];
 $fname = mysql_real_escape_string(htmlspecialchars($_POST['fname']));
 $lname = mysql_real_escape_string(htmlspecialchars($_POST['lname']));
 $contact = mysql_real_escape_string(htmlspecialchars($_POST['contact']));
 $price = mysql_real_escape_string(htmlspecialchars($_POST['price']));


 // check that firstname/lastname fields are both filled in
 if ($fname == '' || $lname == '' || $contact == '' || $price == '' )
 {
 // generate error message
 $error = 'ERROR: Please fill in all required fields!';

 //error, display form
 renderForm($id, $fname, $lname, $contact, $price, $error);
 }
 else
 {
 // save the data to the database
 $username = $_SESSION['username'];

 $query = "UPDATE people 
         SET price=?, 
             contact=?, 
             fname=?, 
             lname=? 
          WHERE id=? AND 
                username=?";
$stmt = $db->prepare($query);
$stmt->bindParam(1, $price);
$stmt->bindParam(2, $contact);
$stmt->bindParam(3, $fname);
$stmt->bindParam(4, $lname);
$stmt->bindParam(5, $id);
$stmt->bindParam(6, $username);    
$stmt->execute();


 // once saved, redirect back to the view page
header("Location: view.php"); 
}

Answer

Answer by John Woo

John Woo

For more information visit this link: PHP PDO

based on your example,

<?php

    $query = "UPDATE people 
             SET price=?, 
                 contact=?, 
                 fname=?, 
                 lname=? 
              WHERE id=? AND 
                    username=?";
    $stmt = $dbh->prepare($query);
    $stmt->bindParam(1, $price);
    $stmt->bindParam(2, $contact);
    $stmt->bindParam(3, $fname);
    $stmt->bindParam(4, $lname);
    $stmt->bindParam(5, $id);
    $stmt->bindParam(6, $username);    
    $stmt->execute();

?>

PDO Prepared statements and stored procedures

enter image description here

Answer

Answer by Starx

Starx

Few things you have to be clear while using PDO extension is that there are multiple ways to get things done.

The way you are currently using being one of them including few more. However it is always a good idea to bind parameters separately, because this prevents many problems like SQL Injection and many more.

Other important things to look at are statement, prepare and execute.

$conn = new PDO("...."); //Creating the handler

//Create the statement
$stmt = $conn -> prepare("UPDATE people SET price = :price, contact = :contact, fname = :fname, lname = :lname WHERE id= :id AND username = :username");

// Bind the params
$stml -> bindParam(":contact", $contact, PDO::PARAM_STR); //This way you can also define the DATATYPE of the parameter

//Execute
$stmt -> execute(array(
   ":price" => $price, //another way of binding the params
   ":fname" => $fname, 
   ":lname" => $lname,
   ":id" => $id, 
   ":username" => $username));

Question 6

Question by Salvador Dali

Salvador Dali

I have a table with a following schema:

id, field, time
3, 'test0', 2012-08-29
3, 'test1', 2012-08-30
3, 'test2', 2012-08-31
2, 'test0', 2012-08-19
2, 'test1', 2012-08-20
2, 'test2', 2012-08-26
...

I need for every id in the list find it’s last and previous to last value.
For example if the ids = [2,3] the result should return

3, 'test1', 2012-08-30
3, 'test2', 2012-08-31 
2, 'test1', 2012-08-20
2, 'test2', 2012-08-26

If i will need just last value, I would use

SELECT *
FROM table
WHERE id IN (2, 3)
GROUP BY id

Any ideas how I can achieve this?

Answer

Answer by Starx

Starx

Give this a try

SELECT *
    FROM table
    WHERE id = '1'
    ORDER BY `id` desc
    LIMIT 0,2
UNION
    SELECT *
    FROM table
    WHERE id = '2'
    ORDER BY `id` desc
    LIMIT 0,2

UPDATE:

If can also try something like this:

SELECT t1.*
FROM `tablename` t1
LEFT OUTER JOIN `tablename` t2
  ON (t1.id = t2.id AND t1.time > t2.time)
GROUP BY t1.id, t1.field, c1.time
HAVING COUNT(*) < 2;

Reference

Question 7

Question by David542

David542

What would be the difference between doing:

SELECT person FROM population WHERE id = 1 or id = 2 or id = 3

and –

SELECT person FROM population WHERE id IN (1,2,3)

Are they executed the exact same way? What difference is there? Would there ever be a reason where one would you IN rather than multiple =‘s?

Answer

Answer by Starx

Starx

No, they perform the same thing. The IN minimizes the query string. That’s all. Such statements help in query optimization.

One difference in these two comparison operators would be that IN uses a SET of values to compare, unlike the “=” or “<>” which takes a single value.

According to the manual:

if expr is equal to any of the values in the IN list, else returns 0.
If all values are constants, they are evaluated according to the type
of expr and sorted. The search for the item then is done using a
binary search. This means IN is very quick if the IN value list
consists entirely of constants.

Question 8

Question by Viktors Golubevs

Viktors Golubevs

I am using codeigniter and have working query which take user 3 images. I want to make a count an give every image a number 1,2,3,4,5,6,7 … and want that query output

-number (count),
-id,
-image,
-date

my sql query :

function bar_images_first($user_id)
{   
    $sql = "SELECT id, image, UNIX_TIMESTAMP(date) as date FROM images WHERE user_id = 3 LIMIT 3";
    $query = $this->db->query($sql, $user_id);
    return $query->result_array();
}

Is it possible to do counter in query?

Answer

Answer by Starx

Starx

It is possible by setting a SQL parameter as

SET @cnt := 0;
SELECT
    @cnt := @cnt + 1,
    id,
    image,
    UNIX_TIMESTAMP(date) as date 
FROM images WHERE user_id = 3 LIMIT 3";

But such multiple statements cannot be executed from the PHP’s mysql_query() method. But, mysqli function like mysqli_multi_query() does allow to execute multiple queries too, so if possible use mysqli method rather than the AR methods.

However, you can run multiple sets of query one by one.

$query = array(
    "SET @cnt := 0;",
    "SELECT
        @cnt := @cnt + 1,
        id,
        image,
        UNIX_TIMESTAMP(date) as date 
    FROM images WHERE user_id = 3 LIMIT 3"
);

foreach($query as $qry) {
     $result= $this->db->query($qry);
     //.........
}

Question 9

Question by Sherif

Sherif

I have a simple JOIN query:

$lstCheck = $dbWHMCS->query('SELECT * FROM tblmonitorports mp 
                            INNER JOIN tblmonitorhosts h ON h.hst_id = port_mon 
                            INNER JOIN tblhosting ho ON ho.id = h.hst_serverid
                            INNER JOIN tblclients cl ON cl.id = ho.userid');

while ($data = $lstCheck->fetch())
{
            $serveridx = $data['ho.id'];
            $clientid = $data['cl.id'];
}

My problem is that I have an “id” column in both the tblhosting and tblclients tables, so my variables both have the same id. I tried to set it using an alias in the example above (ho. and cl.) but it doesn’t work. How can I do this in the example above?

Thank you!

Answer

Answer by Mirko

Mirko

How about this? (a bit rusty on php details, but this should do it):


$lstCheck = $dbWHMCS->query('SELECT ho.id hid, cl.id cid FROM tblmonitorports mp 
                            INNER JOIN tblmonitorhosts h ON h.hst_id = port_mon 
                            INNER JOIN tblhosting ho ON ho.id = h.hst_serverid
                            INNER JOIN tblclients cl ON cl.id = ho.userid');

while ($data = $lstCheck->fetch())
{
            $serveridx = $data['hid'];
            $clientid = $data['cid'];
}

Answer

Answer by Starx

Starx

You are selecting the records, with a wild card *, so you can’t select the fields like that.

As per your query h.hst_serverid & ho.userid have the exact same value as you want. SO simply do this

while ($data = $lstCheck->fetch())
{
            $serveridx = $data['hst_serverid'];
            $clientid = $data['userid'];
}

However, selecting specific rows might sound better too

$lstCheck = $dbWHMCS->query('SELECT ho.id hid, cl.id cid, ....');

while ($data = $lstCheck->fetch())
{
            $serveridx = $data['hid'];
            $clientid = $data['cid'];
}

Question 10

Question by lauthiamkok

lauthiamkok

I want to join these two table but the join key of the second table is in a query string,

page table,

page_id     url
1           a
2           c
3           d

system table,

system_id       query
1               page_id=1&content=on&image=on
2               type=post&page_id=2&content=on

as you can see that page_id is part of the query string in system table.

so how can I join them like the standard joining table method below?

SELECT*
FROM page AS p

LEFT JOIN system AS s
ON p.page_id = s.page_id

EDIT:

I def can change the system table into something like this,

system_id       page_id    query
1               1           page_id=1&content=on&image=on
2               2           type=post&page_id=2&content=on
3               NULL        type=page

But the reason why I don’t want to do this is that the page_id is no need for many certain records. I don’t want make a column with too many null.

Answer

Answer by Nation

Nation

I guess you wanted something like this (MSSQL!):

DECLARE @query VARCHAR(50)
DECLARE @Lenght INT  
DECLARE @PageID INT 

SET @query = '4kkhknmnkpage_id=231&content=on&image=on'
SET @Lenght = PATINDEX('%&%', substring(@query,PATINDEX('%page_id=%', @query),50)) - 9
SET @PageID = CAST(SUBSTRING(@query,PATINDEX('%page_id=%', @query) + 8,@Lenght) AS INT)

SELECT @PageID -- you can do as you please now :)

OR:

SELECT*
FROM page AS p
LEFT JOIN (SELECT CAST(SUBSTRING(query,PATINDEX('%page_id=%', query) + 8,(PATINDEX('%&%', substring(query,PATINDEX('%page_id=%', query),50)) - 9)) AS INT) AS page_id
                FROM system) AS s
ON p.page_id = s.page_id 

-- Do as you please again :)

I guess what you really wanted was something like this (MYSQL!):

SET @query := '4kkhknmnkpage_id=231&content=on&image=on';
SET @Lenght := POSITION('&' IN (SUBSTR(@query,POSITION('page_id=' IN @query),50))) - 9;
SET @PageID := CAST(SUBSTR(@query,POSITION('page_id=' IN @query) + 8,@Lenght) AS  SIGNED );

SELECT @PageID

OR

SELECT*
FROM page AS p
LEFT JOIN (SELECT CAST(SUBSTR(query,POSITION('page_id=' IN query) + 8,(POSITION('&' IN (SUBSTR(query,POSITION('page_id=' IN query),50))) - 9)) AS  SIGNED) AS pageID
           FROM system) AS s
ON p.page_id = s.pageID

Answer

Answer by Starx

Starx

Joining two tables without the common field and data type, is fundamentally wrong IMO.

I will suggest that you extract the page_id and insert it in the database and use a normal join to accomplish what you are searching for.

SO making the columns like

+------------+-----------+---------+
| system_id  |  page_id  |  query  |
------------------------------------

Here is a snippet with which you are extract the page_id.

$query = 'page_id=1&content=on&image=on';
$queryParts = explode('&', $query);

$params = array();
foreach ($queryParts as $param) {
    $item = explode('=', $param);
    $params[$item[0]] = $item[1];
} 
$page_id = $parems['page_id'];

Then you can go on with the insert and use simple join statement to solve your problem in a proper way.

Update:

Since you are able to change the schema to a feasible one. You dont need to worry about some rows having empty rows on this.

Tag: sql

Merging columns if a column has a count > 1

Lotier’s Question:

Need to know if mysql query can get this type of result

Sravis’s Question:

What's wrong with this PHP code dealing with the database

Question by Rogers

Answer by Dave Hale

Answer by Starx

How do I structure a bunch of items that chain together in a MySQL database?

Question by HartleySan

Answer by Starx

PHP changing old mysql_query to PDO

Question by neeko

Answer by John Woo

Answer by Starx

MySQL – for every ID in the list select 2 last element

Question by Salvador Dali

Answer by Starx

Using multiple ='s for IN?

Question by David542

Answer by Starx

Make counter in sql query

Question by Viktors Golubevs

Answer by Starx

Set variable from alias in SQL query

Question by Sherif

Answer by Mirko

Answer by Starx

Joining two tables but the join key is in a query string

Question by lauthiamkok

Answer by Nation

Answer by Starx

Joining two tables without the common field and data type, is fundamentally wrong IMO.

Please fill the form - I will response as fast as I can!