How to select distinct rows without using group by statement

A B C
1 1 1
1 1 1
2 2 2
2 2 2
3 3 3
3 3 3
4 4 4
4 4 4
5 5 5
5 5 5
5 5 5
6 6 6
6 6 6
I am to output only the distinct rows without using the group by statement. I cannot use group by because it makes mysql hang. So it should return

1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6

Solutions:
If the name of your table is mytable , select distinct * from mytable will do the trick.

MySQL uses GROUP BY under the hood to execute DISTINCT !!!
If the table is called mytable, do these two things:

First run this

ALTER TABLE mytable ADD INDEX ABC (A,B,C);`
Second, run this query

SELECT A,B,C FROM mytable GROUP BY A,B,C;
GROUP BY actually works better with an index present !!!

Here is sample code to prove it works

mysql> drop database if exists cool_cs;
Query OK, 1 row affected (0.04 sec)

mysql> create database cool_cs;
Query OK, 1 row affected (0.00 sec)

mysql> use cool_cs
Database changed
mysql> create table mytable
    -> (A int,B int,C int, key ABC (A,B,C));
Query OK, 0 rows affected (0.08 sec)

mysql> INSERt INTO mytable VALUES
    -> (1,1,1),(1,1,1),(2,2,2),(2,2,2),(3,3,3),
    -> (3,3,3),(4,4,4),(4,4,4),(5,5,5),(5,5,5),
    -> (5,5,5),(6,6,6),(6,6,6);
Query OK, 13 rows affected (0.06 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql> select a,b,c FROM mytable group by a,b,c;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    1 |    1 |
|    2 |    2 |    2 |
|    3 |    3 |    3 |
|    4 |    4 |    4 |
|    5 |    5 |    5 |
|    6 |    6 |    6 |
+------+------+------+
6 rows in set (0.02 sec)

mysql>

Function: Convert Days Into Seconds w/ PHP

## DAYS INTO SECONDS
<?php

function days_to_seconds($days) {
if(is_numeric($days)) {
return 86400*$days;
} else {
return ‘string must be numeric’;
}
}

## PARSE
echo days_to_seconds(8); // NUMBER OF DAYS

## RESULT
// 691200

?>

MySQL column: Change existing phone numbers into specific format?

I have a MySQL column that contains phone numbers, the problem is that they're in different formats, such as:

• 2125551212
• 212-555-1212
• (212)5551212

I'd like to know if it's possible to take the existing 10 digits, remove the formatting, and change them all to this format: (212) 555-1212

Not a duplicate, as I'm looking to update several thousand entries instead of masking new entries.

Solution:

Sample data

create table nums ( num text );

insert into nums values 

    ('2125551212'),

    ('212-555-1212'),

    ('(212)5551212');

Query formatting your data

select 

  num, 

  concat('(',substr(num_cleansed,1,3),') ',substr(num_cleansed,4,3),'-',substr(num_cleansed,7)) AS num_formatted

from (

  select 

    num, 

    replace(replace(replace(num,'(',''),')',''),'-','') as num_cleansed

  from nums

  ) foo

“INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE”

In case you want to see what this all means, here is a blow-by-blow of everything:

CREATE TABLE `users_partners` (
  `uid` int(11) NOT NULL DEFAULT '0',
  `pid` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`uid`,`pid`),
  KEY `partner_user` (`pid`,`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
Primary key is based on both columns of this quick reference table. A Primary key requires unique values.

Let's begin:

INSERT INTO users_partners (uid,pid) VALUES (1,1);
...1 row(s) affected

INSERT INTO users_partners (uid,pid) VALUES (1,1);
...Error Code : 1062
...Duplicate entry '1-1' for key 'PRIMARY'

INSERT IGNORE INTO users_partners (uid,pid) VALUES (1,1);
...0 row(s) affected

INSERT INTO users_partners (uid,pid) VALUES (1,1) ON DUPLICATE KEY UPDATE uid=uid
...0 row(s) affected
note, the above saved too much extra work by setting the column equal to itself, no update actually needed

REPLACE INTO users_partners (uid,pid) VALUES (1,1)
...2 row(s) affected
and now some multiple row tests:

INSERT INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...Error Code : 1062
...Duplicate entry '1-1' for key 'PRIMARY'

INSERT IGNORE INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...3 row(s) affected
no other messages were generated in console, and it now has those 4 values in the table data. I deleted everything except (1,1) so I could test from the same playing field

INSERT INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4) ON DUPLICATE KEY UPDATE uid=uid
...3 row(s) affected

REPLACE INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...5 row(s) affected
So there you have it. Since this was all performed on a fresh table with nearly no data and not in production, the times for execution were microscopic and irrelevant. Anyone with real-world data would be more than welcome to contribute it.

How to use MySQL DECIMAL?

Suppose that your column is set to be DECIMAL(13,4). This means that the column will have a total size of 13 digits where 4 of these will be used for precision representation.

The declaration syntax for a DECIMAL column is DECIMAL(M,D). The ranges of values for the arguments are as follows:

• M is the maximum number of digits (the precision). It has a range of 1 to 65.
• D is the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger than M.
   DECIMAL columns do not store a leading + character or - character or leading 0 digits. If you insert +0003.1 into a DECIMAL(5,1) column, it is stored as 3.1. For negative numbers, a literal - character is not stored.
  DECIMAL columns do not permit values larger than the range implied by the column definition. For example, a DECIMAL(3,0) column supports a range of -999 to 999. ADECIMAL(M,D) column permits at most M - D digits to the left of the decimal point. 

Which one is better XML or JSON ?

​

Which one is better XML or JSON ?

8 inShare

In our daily web application development, we use XML or JSON as data interchange formats; but do we really know when to use XML and JSON. Here in this post i have tried to explain it.
XML and JSON both are the data interchange formats accessed over WEB. Both of these formats have their own pros/cons. No one is replacement of other. Use the right tool for the right job.
Talking about which one is better, is something related to the applications/requirements and the collaboration between client and server. Selection and strength of data interchange media types, is application specific.
Below i have given a summary of each format.
XML: Extensible Markup Language

1. An open standard for describing data defined by the World Wide Web Consortium (W3C).

2. XML lets Web developers and designers create customized tags that offer greater flexibility in organizing and presenting information. 

3. XML defines rules to mark-up a document in a way that allows the author to express semantic meaning in the mark-up. XML does not necessarily restrict the author to certain tags (elements) as HTML does.

4. Internet media type: application/xml

JSON: JavaScript Object Notation

1. A lightweight text-based open standard designed for human-readable data interchange.

2. A text-based format for exchanging objects.

3. It is an alternative to XML that is more concise because, unlike XML,  it is not a markup language that requires open and close tags.

4. It is derived from the object literals of JavaScript.

5. Design goals were for it to be minimal, portable, textual, and a subset of JavaScript.

6. Internet media type: application/json

JSON is built on two structures:

1. A collection of name/value pairs. In various languages, this is realized as an object, record, struct, dictionary, hash table, keyed list, or associative array.

2. An ordered list of values. In most languages, this is realized as an array, vector, list, or sequence.

Below are the differences between XML and JSON.
XML vs JSON
1.  JSON format is lightweight over XML.
2. JSON is recognized natively by JavaScript.
3. JSON can contain integers, strings, lists, arrays. XML is just elements and nodes that need to be parsed into integers and so on before it can be consumed.
4. The most important disadvantage of JSON is that the format is very hard to read for humans, and that, of course,    every single comma, quote, and bracket should be in exactly the correct place. While this is also true of XML, JSON’s welter of complicated-looking syntax, like the }}]} at the end of the data snippet, may frighten the newbies and make for complicated debugging.
5. Serialization format for your data, JSON is smaller, lighterweight and generally faster than XML.
6. JSON is best for consumption of data in web applications from webservices for its size and ease of use, especially due to the built-in support in JavaScript.
Imagine the computation overhead for parsing an xml fragment compared to the instant lookup in JSON.
7. XML is still great. JSON’s just the “latest and greatest” compared to XML.
8. For configurations file XML is better choice to make because it more human readable.
9. A browser JSON is faster to serialize/deserialize as it’s simpler, more compact and more importantly natively supported.
10. XML is document-oriented. JSON is data-oriented. JSON can be mapped more easily to object-oriented systems.
11. XML and JSON both use Unicode.That help in support for internationalization.
12. JSON does not have afeature, so it is not well suited to act as a carrier of sounds or images or other large binary payloads. JSON is optimized for data.
13. XML documents can contain any imaginable data type – from classical data like text and numbers, or multimedia objects such as sounds, to active formats like Java applets or ActiveX components.
14. JSON is a better data exchange format. XML is a better document exchange format. Use the right tool for the right job.
15. XML requires translating the structure of the data into a document structure. This mapping can be complicated. JSON structures are based on arrays and records. That is what data is made of. XML structures are based on elements (which can be nested), attributes (which cannot), raw content text, entities, DTDs, and other meta structures.
16. JSON is not extensible because it does not need to be. JSON is not a document markup language, so it is not necessary to define new tags or attributes to represent data in it.

Below is the final summary.
Summary:
1. For Data delivery between servers and browsers, JSON is better choice.
2. For storing Information in configuration files on the server side, XML is better choice.
3. On Browser Side: The speed and ease with which JSON is parsed and the ease of simple data retrieval from JavaScript object; makes JSON is a better choice.
4. Server Side: The querying data and format changes; makes XML a better choice.
Querying data: Using XPath, it’s possible to get direct access to a part of multiple parts of an XML data structure; no such interface exists for JSON. To get data from a JSON structure, you must know exactly where it is or else iterate over everything until you find it.
Format changes: You have your data in one format but you want it in another. If the data is in XML, you can write an XSLT template and run it over the XML to output the data into another format: HTML, SVG, plain text, comma-delimited, even JSON. When you have data in JSON, it’s pretty much stuck there. There’s no easy way to change it into another data format.
5. Security: JSON is less secure because of absence of JSON parser in the Browser; only way is to use eval() function. For security reasons on the browser side XML is better choice.
6. To extract data from database; XML is the only choice.

Below is the example; i used to explain each one’s usage in the Browser. This usage will also show you which one is easy to use on the browser side.
Example Data Operations:
XML

<?xml version="1.0" encoding="UTF-8"?>
<root>
 <data> 
  <id>1</id>                 
  <name>PHP </name> 
 </data>
 <data> 
  <id>2</id>                 
  <name>Table of Contents</name> 
 </data>
<root>

JSON

 {
                "data": [
                           {
                                "id" :    "1",
                                "name" :    "PHP"                            
                            },
                            {
                                "id" :    "2",
                                "name" :    "Table of Contents" 
                            }
]}

XML Responses + Accessing the data
The following code shows the source code used in retrieving the XML document. The getPage() function takes the URL from where you will retrieve the XML document. The function does so by setting up a reference to the XMLHTTP Request object, which does the task of retrieving and parsing the XML document as described in the XMLDOM. This is done after checking the status and availability of the document on the server.

var root = xhr.responseXML;
var dataElements = root.getElementsByTagName('data'); 
var idElementsValue = dataElements[0].getElementsByTagName('id')[0].firstChild.nodeValue;
var nameElementsValue = dataElements[0].getElementsByTagName('name')[0].firstChild.nodeValue;

JSON Responses + Accessing the data
While it might not be obvious, that lengthy string above is just an array, and once you’ve got that array in a JavaScript variable, you can access it easily. In fact, you can simply separate the array with period delimiters. So, to access the name of the first entry of the PHP documentation pages, you would use code like this in your JavaScript:

data.name[0];

Modifying JSON data
Just as you can access data with the dot and bracket notation shown above, you can easily modify data in the same way:

data.name[1] = "JSON is HERE";

That’s all you need to do to change data in a variable once you’ve converted from a string to JavaScript objects.

PHP - Assign same value to multiple variables

How can I assign the same value for multiple variables in PHP?

I have something like:

$var_a = 'A';
$var_b = 'A';
$same_var = 'A';
$var_d = 'A';
$some_var ='A';

In my case, I can't rename all variables to have the same name (that would make things more easy), so is there any way to assign the same value to all variables in a much more compact way?

Answer:

$var_a = $var_b = $same_var = $var_d = $some_var = 'A';

 This can't be used in Php class and this is the raw type. ( Please check it once while using for classes)

One more answer:

list( $var_a, $var_b, $same_var, $var_d,$some_var) = array( 0, 0, 0, 0,0,0 );