PHP?
--- In [email protected], kray blue <[EMAIL PROTECTED]> wrote:
>
> hello group,
>
> pl/sql is to oracle;
> ______ is to mysql?
>
> thanks in regards,
> kray
>
> Patrick Newberry <[EMAIL PROTECTED]> wrote:
> Have mysql problem as follows:
> when the if statement below is executed, it seems fine unless a
space
> follows an "&", "." or "of".
> The whole procedure is listed below with some samples of the data
below
> this if statement.
> Basically it drops element after the space if the element before
the
> space contains one of the strings listed above.
>
> Thus Alfred E. Newman
> drops the Newman
> where as
> You Only Live Twice
> does not drop anything.
>
> Here is the code we think is dropping the element.
>
> IF INSTR(v_record_r,' ') <> 0
> THEN
> SET v_element = SUBSTR(v_record_r, 1, INSTR
(v_record_r,'
> ')-1); -- parce out lead element
> SET v_record_r = SUBSTR(v_record_r,
> LENGTH(SUBSTR(v_record_r, 1, INSTR(v_record_r,' ')+1)));
> ELSE
> SET v_element = v_record_r;
> -- load last element
> SET v_record_r = NULL;
> END IF;
>
>
>
> Example
> Data:
> INSERT INTO tbl_rtv (rtv) VALUES ('You Only Live Twice');
> INSERT INTO tbl_rtv (rtv) VALUES ('Live & Let Die');
> INSERT INTO tbl_rtv (rtv) VALUES ('Alfred E. Newman');
>
> with each word in this table:
> INSERT INTO tbl_element (element) VALUES ('Newman');
> INSERT INTO tbl_element (element) VALUES ('Die');
> INSERT INTO tbl_element (element) VALUES ('E.');
> INSERT INTO tbl_element (element) VALUES ('&');
> etc....
>
> Output from procedure:
>
> +------------------------------------------------------------+-----
-----
> -----------+
> | ABSOLUTELY BRAND SPANKING NEW RECORD TO VERIFY ELEMENTS OF |
v_record
> |
> +------------------------------------------------------------+-----
-----
> -----------+
> | ABSOLUTELY BRAND SPANKING NEW RECORD TO VERIFY ELEMENTS OF | You
Only
> Live Twice |
> +------------------------------------------------------------+-----
-----
> -----------+
> 1 row in set (0.03 sec)
>
> +----+-----------+-----------------+
> | ID | v_element | remainder |
> +----+-----------+-----------------+
> | 32 | You | Only_Live_Twice |
> +----+-----------+-----------------+
> 1 row in set (0.03 sec)
>
> +----+-----------+------------+
> | ID | v_element | remainder |
> +----+-----------+------------+
> | 21 | Only | Live_Twice |
> +----+-----------+------------+
> 1 row in set (0.03 sec)
>
> +----+-----------+-----------+
> | ID | v_element | remainder |
> +----+-----------+-----------+
> | 15 | Live | Twice |
> +----+-----------+-----------+
> 1 row in set (0.03 sec)
>
> +----+-----------+-----------+
> | ID | v_element | remainder |
> +----+-----------+-----------+
> | 29 | Twice | NULL |
> +----+-----------+-----------+
> 1 row in set (0.03 sec)
>
>
> This one does not Work:
> +------------------------------------------------------------+-----
-----
> ------+
> | ABSOLUTELY BRAND SPANKING NEW RECORD TO VERIFY ELEMENTS OF |
v_record
> |
> +------------------------------------------------------------+-----
-----
> ------+
> | ABSOLUTELY BRAND SPANKING NEW RECORD TO VERIFY ELEMENTS OF |
Live &
> Let Die |
> +------------------------------------------------------------+-----
-----
> ------+
> 1 row in set (0.03 sec)
>
> +----+-----------+-----------+
> | ID | v_element | remainder |
> +----+-----------+-----------+
> | 15 | Live | &_Let_Die |
> +----+-----------+-----------+
> 1 row in set (0.03 sec)
>
> +----+-----------+-----------+
> | ID | v_element | remainder |
> +----+-----------+-----------+
> | 1 | & | + |
> +----+-----------+-----------+
> 1 row in set (0.03 sec)
>
> +------+-----------+-----------+
> | ID | v_element | remainder |
> +------+-----------+-----------+
> | NULL | + | Die |
> +------+-----------+-----------+
> 1 row in set (0.03 sec)
>
> +----+-----------+-----------+
> | ID | v_element | remainder |
> +----+-----------+-----------+
> | 8 | Die | NULL |
> +----+-----------+-----------+
> 1 row in set (0.03 sec)
>
> another example that does not work:
> +------------------------------------------------------------+-----
-----
> --------+
> | ABSOLUTELY BRAND SPANKING NEW RECORD TO VERIFY ELEMENTS OF |
v_record
> |
> +------------------------------------------------------------+-----
-----
> --------+
> | ABSOLUTELY BRAND SPANKING NEW RECORD TO VERIFY ELEMENTS OF |
Alfred E.
> Newman |
> +------------------------------------------------------------+-----
-----
> --------+
> 1 row in set (0.03 sec)
>
> +----+-----------+-----------+
> | ID | v_element | remainder |
> +----+-----------+-----------+
> | 2 | Alfred | E._Newman |
> +----+-----------+-----------+
> 1 row in set (0.03 sec)
>
> +----+-----------+-----------+
> | ID | v_element | remainder |
> +----+-----------+-----------+
> | 9 | E. | |
> +----+-----------+-----------+
> 1 row in set (0.03 sec)
>
> +------+-----------+-----------+
> | ID | v_element | remainder |
> +------+-----------+-----------+
> | NULL | | NULL |
> +------+-----------+-----------+
> 1 row in set (0.03 sec)
>
> Complete procedure:
>
> DROP PROCEDURE IF EXISTS verify_record;
> DELIMITER //
> CREATE PROCEDURE verify_record
> ( IN v_record VARCHAR(255)
> , INOUT v_errors VARCHAR(50)
> )
> BEGIN
> --
> -- VARIABLES
> DECLARE v_record_r VARCHAR(255);
> DECLARE v_element VARCHAR(255);
> DECLARE v_elementid INT;
> --
> DECLARE element_existance INT DEFAULT 1;
> --
> -- CURSORS
> DECLARE elementid_cur
> CURSOR FOR
> SELECT elementid
> FROM tbl_element
> WHERE element = v_element;
> --
> -- HANDLERS
> DECLARE CONTINUE HANDLER FOR NOT FOUND SET element_existance
= 0;
> --
> -- BEGIN ACTION
> SET v_record_r = v_record;
> --
> -- loop through record
> WHILE v_record_r IS NOT NULL
> DO
> IF INSTR(v_record_r,' ') <> 0
> THEN
> SET v_element = SUBSTR(v_record_r, 1, INSTR
(v_record_r,'
> ')-1); -- parce out lead element
> SET v_record_r = SUBSTR(v_record_r,
> LENGTH(SUBSTR(v_record_r, 1, INSTR(v_record_r,' ')+1)));
> ELSE
> SET v_element = v_record_r;
> -- load last element
> SET v_record_r = NULL;
> END IF;
> --
> -- determine valid elements
> OPEN elementid_cur;
> FETCH elementid_cur INTO v_elementid;
> CLOSE elementid_cur;
>
> SELECT v_elementid ID, v_element, REPLACE(v_record_r, ' ','_')
> remainder;
> --
> -- keep track of the missing
> IF element_existance = 0
> THEN
> SET element_existance = 1;
> INSERT INTO tbl_missing_element
> ( element
> )
> VALUES(v_element
> );
> END IF;
> --
> -- clear variable
> SET v_elementid = NULL;
> END WHILE;
> END;
> //
> DELIMITER ;
>
>
> The php_mysql group is dedicated to learn more about the PHP/MySQL
web database possibilities through group learning.
>
>
>
> SPONSORED LINKS
> American general life and accident insurance company
American general life insurance company American general life
American general mortgage American general life insurance
Computer internet security
>
> ---------------------------------
> YAHOO! GROUPS LINKS
>
>
> Visit your group "php_mysql" on the web.
>
> To unsubscribe from this group, send an email to:
> [EMAIL PROTECTED]
>
> Your use of Yahoo! Groups is subject to the Yahoo! Terms of
Service.
>
>
> ---------------------------------
>
>
>
>
>
> __ _
> ____ | |(_)
> / || | __ _ ,_ _______
> | -- | || |> \/'\/ || __ |
> \ \ | || || +-+-+ || ___/
> /+__\ \| || || | | | | \ \
> < > copyryt 2003 (r)
>
>
>
> ---------------------------------
>
> What are the most popular cars? Find out at Yahoo! Autos
>
> [Non-text portions of this message have been removed]
>
The php_mysql group is dedicated to learn more about the PHP/MySQL web database
possibilities through group learning.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/php_mysql/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
