MySQL can be used to do some string replacements rather using any programming languages.
The following is used to replace space with hyphen in list names
UPDATE list SET list_name = REPLACE(list_name, ' ', '-') WHERE list_name LIKE '% %';
Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts
Posted by
Unknown
Labels:
SQL
Like keyword can be used with Zend_Db select query
$query = $this->select()
->where("firstname like ?", $letter."%");
try that works.
There is a way to select comma separated data from any mysql table.
SELECT poll_id,group_concat(option_id) FROM `poll_elements`
group by poll_id
this will output as follows:
poll_id | group_concat(option_id)
1 | 3855,5098,8474
3 | 3855,9469,15677
6 | 509,3855,9469,10489
This is the way to select records from tables of different databases
SELECT * FROM `db`.table tb
INNER JOIN `db2`.table2 tb2 ON tb2.id = tb.id
hope it helps someone.
If any chance to do an UPDATE on the same table, here is the SQL for that
UPDATE my_list AS a
INNER JOIN my_list AS b
ON a.id = b.id
SET a.list_name_val = md5( b.list_name )
the above SQL will update list_name_val column with md5 of list_name.
If you are trying to select records whose ids are not present in the other table,
eg: articles table has article_id, categorized table has article_id and cat_id.
If you are trying to select the article that is not categorized yet
SELECT * FROM articles WHERE article_id NOT IN ( SELECT article_id FROM categorized );
That will solve your problem.
If you are trying to filter out duplicate records from your table.
SELECT DISTINCT column_name,id FROM table_name
GROUP BY coulumn_name
HAVING COUNT(column_name) > 1
The above sql would filter out duplicate records with their ids.
cheers.
This is for MySQL I have no idea about other DBs
$query = mysql_query("SELECT count(*) FROM accounts");
$ctr = mysql_result($query,0,0);
Hope it helps someone.
CREATE TABLE members (
member_id int(10) unsigned NOT NULL auto_increment,
first_name varchar(100) NOT NULL,
last_name varchar(100) NOT NULL,
email_address varchar(100) NOT NULL,
date_registered datetime NOT NULL,
last_accessed datetime NOT NULL,
password char(32) NOT NULL,
profile_name varchar(30) NOT NULL,
PRIMARY KEY (member_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Subscribe to:
Posts (Atom)