Archives for PHP mySQL category
Hide Intro Text
we usually set the defaults in the relevant XML files for the user to make it easier when they are adding and editing new content. Like you, we often find we’re wanting Hide intro text on by default. So, instead of setting it globally we edit
administrator/components/com_content/content.xml
change line 28 where it says
update esalud_content set introtext = replace(introtext,CONVERT( _utf8 ‘%{ }%’ USING latin1 ),’ ‘)
{moseasymedia media=/consulta/banner.swf height=195 width=170}
El valor inicial para AUTO_INCREMENT para la tabla. En MySQL 5.0, sólo funciona para tablas MyISAM y MEMORY. También se soporta para InnoDB desde MySQL 5.0.3. Para inicializar el primer valor de auto incremento para motores que no soporten esta opción, inserte un registro de prueba con un valor que sea uno menor al deseado tras crear la tabla, y luego borre este registro.
Para motores que soportan la opción de tabla AUTO_INCREMENT en comandos CREATE TABLE puede usar ALTER TABLE para resetear el valor tbl_name AUTO_INCREMENT = nAUTO_INCREMENT .
SELECT LAST_INSERT_ID();
“Si me quieres, te querré
más y más a cada instante mientras viva
porque si no me quieres no soy nada”
“Mi esperanza es ser parte de tu vida
y que tú seas parte de la mía.”
MySQL:
CREATE TABLE ORDERS
(Order_ID integer,
Order_Date date,
Customer_SID integer,
Amount double,
Primary Key (Order_ID),
Foreign Key (Customer_SID) references CUSTOMER(SID));
Oracle:
CREATE TABLE ORDERS
(Order_ID integer primary key,
Order_Date date,
Customer_SID integer references CUSTOMER(SID),
Amount double);
SQL Server:
CREATE TABLE ORDERS
(Order_ID integer primary key,
Order_Date datetime,
Customer_SID integer references CUSTOMER(SID),
Amount double);
Below are examples for specifying a foreign key by altering a table. This assumes that the ORDERS table has been created, and the foreign key has not yet been put in:
MySQL:
ALTER TABLE ORDERS
ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID);
Oracle:
ALTER TABLE ORDERS
ADD (CONSTRAINT fk_orders1) FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID);
SQL Server:
ALTER TABLE ORDERS
ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID);
Simply put, referential integrity means that when a record in a table refers to a corresponding record in another table, that corresponding record will exist. Look at the following:
update tablename set field = replace(field,'search_for_this','replace_with_this');
27 May
Getting the recent one month or year records from MySQL table
Some time we have to collect last 7 or 15 days or X days (or month, year or week) data from MySQL table. For example let us find out who are the new members joined in our forum in last week. One shop may be interested in knowing new products added in last one month. What are the books arrived in last one year. Here irrespective of the date values we want the records of last X days from today, or we can say that the records between today and last X days ( month , year or week) are required.
We will use the MySQL function CURDATE() to get the today’s date.
To get the difference in today date and previous day or month we have to use the MySQL function DATE_SUB
DATE_SUB is a MySQL function which takes date expression, the interval and the constant to return the date value for further calculation.
Here are some sample queries on how to get the records as per requirements .
select * from dt_tb where `dt` >= DATE_SUB(CURDATE(), INTERVAL 15 DAY)
The above query will return last 15 days records. Note that this query will return all future dates also. To exclude future dates we have to modify the above command a little by using between query to get records. Here is the modified one.
SELECT * FROM dt_tb WHERE `dt` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 15 DAY ) AND CURDATE( )
Let us try to get records added in last one month
select * from dt_tb where `dt` >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
Here also future records will be returned so we can take care of that by using BETWEEN commands if required.
select * from dt_tb where `dt` >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
You can easily make out what the above query will return.
Now let us try a different requirement. How to get the records of the working days of the week so far ? If today is Thursday then records from Monday to Thursday should be returned. We will discuss this in our next section >>.
Here is the sql code to create and fill the table with records
CREATE TABLE `dt_tb` ( `id` int(2) NOT NULL auto_increment, `dt` datetime NOT NULL default ‘0000-00-00 00:00:00′, `dt2` date NOT NULL default ‘0000-00-00′, UNIQUE KEY `id` (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;
INSERT INTO `dt_tb` VALUES (1, ‘2007-02-15 00:00:00′, ‘2005-01-25′);
INSERT INTO `dt_tb` VALUES (2, ‘2007-02-12 23:56:54′, ‘2005-06-12′);
INSERT INTO `dt_tb` VALUES (3, ‘2005-12-08 13:20:10′, ‘2005-06-06′);
INSERT INTO `dt_tb` VALUES (5, ‘2005-02-10 00:00:00′, ‘2006-01-02′);
INSERT INTO `dt_tb` VALUES (6, ‘2006-11-26 00:00:00′, ‘2006-12-25′);
INSERT INTO `dt_tb` VALUES (7, ‘2006-11-26 00:00:00′, ‘2007-02-25′);
INSERT INTO `dt_tb` VALUES (8, ‘2007-10-20 00:00:00′, ‘2007-10-25′);
INSERT INTO `dt_tb` VALUES (9, ‘2007-02-11 00:00:00′, ‘2007-01-25′);
INSERT INTO `dt_tb` VALUES (10, ‘2007-01-22 00:00:00′, ‘2007-01-15′);
ZiVo Nix Blog