Multiple Inserts in 1 Query


$sql = "INSERT INTO beautiful (name, age)
VALUES
('Helen', 24),
('Katrina', 21),
('Samia', 22),
('Hui Ling', 25),
('Yumie', 29)";

source:

http://www.desilva.biz/mysql/insert.html

Advertisements

Friend of Friend


———-
UID | FID
———-
1 | 2
1 | 3
1 | 4
2 | 4
4 | 5
Dst…

UID: User ID
FID: Friends ID

—————————————————————————–

join similar table

select u1.uid as people, u2.fid as friend
from tabel u1
inner join tabel u2
on u1.fid=u2.uid
where u1.uid=1;

——————————————————–

select u1.uid as people, u3.fid as friend
from tabel u1
inner join tabel u2
on u1.fid=u2.uid
inner join tabel u3
on u2.fid=u3.uid
where u1.uid=1;

—————————————————————————–
search whose uid=1 :

SELECT fid FROM t WHERE uid=1

search friend of friend whose uid=1 :

SELECT fid FROM t WHERE uid IN (SELECT fid FROM t WHERE uid=1)

or (maybe) :

SELECT DISTINCT t2.fid FROM t t1
INNER JOIN t t2 ON t2.uid=t1.fid
WHERE t1.uid=1

merge friend + friend of friend, use UNION.
—————————————————————————–

display it like friendster do :

select u1.uid as first, u1.fid as second, u2.fid as third
from tabel u1
inner join tabel u2
on u1.fid=u2.uid
where u1.uid=1;

—————————————————————————–

1 level: (result: You > XXX > Target)

$sql=”SELECT friend_2.uid as XXX
FROM friend_3, _users
INNER JOIN friend_2
ON friend_3.fid = friend_2.uid
WHERE
friend_3.uid=$uid and
friend_2.fid=$fid and
limit 1″;
“);

2 level: (result: You > XXX > YYY > Target)

$sql=”SELECT friend_2.uid as XXX, friend_1.uid as YYY
FROM friend_3, _users
INNER JOIN friend_2
ON friend_3.fid = friend_2.uid
INNER JOIN friend_1
ON friend_2.fid = friend_1.uid
WHERE
friend_3.uid=$uid and
friend_1.fid=$fid and
limit 1”;

note: all friend table friend_1, teman_2, friend_3 has 75.000 rows,

problem:

1.very slow especially for 2 level
2. added with order by rand() sometimes time-out
3. how to make it simpler?

—————————————————————————

Oracle: Alter Table


Oracle provides “alter table” syntax to modify data columns in-place in this form:

alter table
table_name
modify
column_name  datatype;

If you are brave you can use a single “alter table” syntax to modify multiple columns:

alter table
table_name
modify
(
column1_name  column1_datatype,
column2_name  column2_datatype,
column3_name  column3_datatype,
column4_name  column4_datatype
);

Here are some examples of Oracle “alter table” syntax to modify data columns and note that you can add constraints like NOT NULL:

ALTER TABLE
customer
MODIFY
(
cust_name varchar2(100) not null,
cust_hair_color  varchar2(20)
)
;

We can also use Oracle “alter table” syntax in dynamic PL/SQL to modify data columns

BEGIN
SQL_STRING := ‘ALTER TABLE ‘||:TABLE_NAME||’ MODIFY ‘||:COLUMN_NAME||’ VARCHAR2(100′;

. . .
END;