How to Setup MySQL Connection Using PDO in PHP

In previous article we have discussed that MySQL is depreciated and their alternative options are MySQLi and PDO(PHP Data Object) . In that you have learned.......

Read more

Live Search System Using Ajax in PHP with MySQL

In this Tutorial we are going to discuss about instant live search in PHP using AJAX and jQuery. Live search .......

Read more

CREATE ALGORITHM=UNDEFINED DEFINER In MySQL

Error: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

When try to import SQL file with below query on server

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mobile_registered_view`.....;



Solution:

CREATE VIEW `mobile_registered_view`

OR

CREATE ALGORITHM=UNDEFINED DEFINER=`control_panel_username`@`localhost`

function for query the database in PHP

//function of query

function runqueries($query) {
$result = mysql_query($query);

while($rowss = mysql_fetch_array($result))

{
   $listingdata[] = $rowss;

 
}
if(!empty($listingdata))
return $listingdata;
}


//Where need to display Data

$sqls= runqueries("SELECT * from Products");

foreach($sqls as $rows)

{


}

how to match comma separated values in column

select * from table where FIND_IN_SET("value to match", `column-name`)

to drop primary key in mysql

to drop primary key in mysql

ALTER TABLE `mycheck` CHANGE COLUMN `id` `id` INT(23) NOT NULL, DROP PRIMARY KEY;

sql query to find second largest value

1>  SELECT MAX( col ) FROM table  WHERE col < ( SELECT MAX( col ) FROM table );

or

2> SELECT MAX(col) FROM table WHERE col NOT IN (SELECT MAX(col) FROM table);

or

3> SELECT `column` FROM `table` GROUP BY `column` ORDER BY `column` DESC LIMIT 1,1;

or

4>SELECT * FROM Table ORDER BY NumericalColumn DESC LIMIT 1 OFFSET 1;

or

5> SELECT * FROM Table ORDER BY NumericalColumn DESC LIMIT (1, 1);

or

6> SELECT `Column` FROM `Table` ORDER BY `Column` DESC LIMIT 1,1;

MySQL SUM function is used to find out the sum of a field in various records.

id	name	class	mark
1	A	Four	75
2	B	Three	85
3	C	Three	55
4	D	Four	60
5	E	Four	60
6	F	Four	55

query:-

SELECT sum( mark ) FROM `student`

Result:-

sum(mark)
390

SQL: DISTINCT CLAUSE

The SQL DISTINCT clause is used to remove duplicates from the result set of a SELECT statement.

SYNTAX:

SELECT DISTINCT column_name,column_name

FROM table_name;