How To Apply Php Or User-defined Functions Inside A Query
Instead of applying a php or user-defined function to data after fetching, I want to apply functions inside queries, not sure if this is possible.
This is how it's done with MySQL function:
SELECT
FROM_UNIXTIME(table_articles.article_pubdate, %M %d, %Y)
AS pub_date FROM...
Is it possible to replace FROM_UNIXTIME() with the php function date() ? so it's something like
SELECT
".date("table_articles.article_pubdate", 'F d, Y')."
AS pub_date FROM...
The above is just an example, actually I want to apply another user-defined function, which formats text of the field article_content.
View Complete Forum Thread with Replies
Related Forum Messages:
Roles, Stored Procedures, User-defined Functions
We are considering switching to mySQL for a Web application. The current functionality relies on roles, stored procedures, and user-defined functions that are created specifically for the application. We use roles to provision different access levels to groups of users. I understand that mySQL v4.1 does not have these features yet. However, is there a timetable for when these will be available?
View Replies !
Access User Defined Variable In Mysql Query
using loop and getting string in user defined variable like uvariable. now executing the statement in java file resultset=statement.executeQuery("select abc from bcd where T=""+uvariable+"""); t and bcd are column.getting error message "check syntax".
View Replies !
User-defined Function: ERROR 2013 (HY000): Lost Connection To MySQL Server During Query
With mysql-4.1.1-alpha, using any User Defined Function ( UDF) at the query execution stage, on Linux 2.6, gave me the following error: "ERROR 2013 (HY000): Lost connection to MySQL server during query" However, upgrading to mysql-4.1.2-alpha seemed to solve the problem. Both versions were configured exactly as follows ../configure --with-openssl --enable-thread-safe-client --with-mysqld-ldflags =-rdynamic The only way I could solve this was by upgrading to 4.1.2-alpha-log. Then, recompiling the functions, drop the old UDF functions, then, recreate. So far so good. Anyway, if you run into this problem, or just want more detail on the sample User Defined Functions that come with MySQL ( /sql/udf_example.cc), I've put my notes in the following link ( TIP 27 )
View Replies !
User Defined Exceptions
We are trying to convert stored procedures from mssql to mysql as a part of db migration, and we are facing the following issue. In mssql , business exceptions can be raised by using RAISERROR function as given below ....
View Replies !
User Defined Variable
I'm creating a variable using the sql: select @birth := birth from president where last_name='jackson' and first_name='andrew'; I'm also getting a result with a date. But when I use this variable in any other queries, it does not give me anything. The variable does not hold any value inside it. It shows me the result as 'NULL' when I select the value for the variable in the SELECT statement.Am I missing anything here? Do I have to save the variable?
View Replies !
User Defined Variables
trying the features of MySql 'SQL' I stumbled over this: I tried to use a variable within some sql statements and consulted chap. 9.3. User-Defined Variables of the 5.0 reference. found example: mysql> SET @a='test'; mysql> SELECT @a,(@a:=20) FROM tbl_name; So I tried the two syntax forms from MySql Query Browser and found that there was no way using the "set ..." syntax.
View Replies !
Using User Defined Variables
The following works using user defined variable: mysql> SELECT count(*) FROM rpt_totalSales INTO @c; mysql> SELECT @c; +---------+ | @c | +---------+ | 4851547 | +---------+ 1 row in set (0.00 sec) However, the following fails: mysql> SHOW CREATE TABLE rpt_totalSales INTO @c; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @c' at line 1 Is there a work around for this?
View Replies !
How To Apply One Query To Multiple Databses?
I've had a good search round this forum for an answer to my question, but nothing is quite what I'm looking for. I would like to apply a single query to multiple databases on the same server. I thought it should be fairly simple, but no luck so far! I have the query sorted if you need that, here it is: PHP Code:  SELECT COUNT(*) AS posts FROM wp_posts WHERE post_author='1' AND post_type = 'post'
View Replies !
Apply Query Timeout In Mysql
I have to apply Query Timeout in Mysql.I wants that whenever Query take more than 10sec an exception will be thrown from mysql side.Is It possible in Mysql? If possible how can I do it?
View Replies !
User Defined Variables & Queries
Im trying to write an SQL file which contains SQL queries which are to be run individually one after the other. Im using user defined variables so that I can save time on having to manually insert constant values. Hope this makes sense: SET @sequence := SELECT COUNT(*) FROM tracks WHERE cd_id = 12; INSERT INTO tracks (song, artist, sequence, cd_id) SET ('Fools Gold', 'Stone Roses', @sequence, 12); Basically here Im trying to SET the value of $sequence based on the result of the select statement. So when I first run the two queries @sequence will have a zero value, the next time it will count the tracks table and will now return a value of 1, then 2, 3,4 etc. Each time I will replace the values of the INSERT query but the SET SELECT query will remain. The SELECT statement runs fine on its own but obviously Im either trying to do something that is not possible with the user-defined variables or more likely Ive got the query wrong.
View Replies !
Creating A User-defined Table
I am wanting to create a user defined table in mySQL. The general idea is that they all would have an asset number (PK). After that, the items can be different. For instance, I may be keeping track of a table, in paticular it's color, size, and cost. OR I might keep track of a computer system, the OS it has installed, who owns it, etc.
View Replies !
User Defined Global Variables
using 5.0.45 and researching global variables. Is it possible to have a user defined global variable? When atmysqlting to set one through phpMyAdmin I am returned the following message: "#1193 - Unknown system variable 'DG_RECEIVER'".
View Replies !
User Defined Variable Overflow
I've got a query that i'm using to dynamically generate a new query and save in a user-defined variable. Then i'm using PREPARE and EXECUTE to run the query that is saved in the variable. The problem is the query string i want to save into the variable is pretty long, and seems to be getting truncated i assume because of maximum variable size. Using the query browser it looks like the variable is a Blob and the data is getting truncated after 1024 bytes, and yet everywhere i see the blob maximum size is 2^16-1 (65,535) bytes. Anyone know why the blob is getting truncated after 1024 or a way around it? ....
View Replies !
Sorting A Table Using A User Defined Function
Here's what I am working on and was hoping for expertise . There's a database with the following table table1 with columns (date1, extension, date2) In normal circumstances I want to sort by date2, i.e. SELECT * FROM table1 SORT BY date2; However, if date2 is empty, then I want to to "date1 + extension" to be used as the date of comparison. It would be nice if Mysql had a way for the user to specify a user defined comparator that is called during the sort. Or is there a simpler way to achieve the same thing? Pointers to sections in the manual would be welcome too or examples of code snippets would bring oodles of thanks.
View Replies !
Alter Auto_increment To User Defined Variable @var
I'm atmysqlting to build a statement that reads the max id of a table and resets the auto_increment value to the max id + 1. My approach has been to set a mysqlorary variable like: SELECT @mysql:=MAX(ID) FROM `accounting`; and then set the value of auto_increment to the variable value like: ALTER TABLE accounting AUTO_INCREMENT = (@mysql+1);
View Replies !
Finding User Details Inside UDF
i heared that its possible to get user details inside the UDf by adding the mysql_priv.h header file. But where will this file be available. i am using MySql 3.23.52 on linux8.0 when i try to compile it says :"mysql_priv" file not found. If i can include this file, i can get the thread constructor called...
View Replies !
Error 1305 - Trying To Execute User Defined Function
In my investigation, I attempted to execute the function (named 'esp') and I started getting the error message: (from database named XQ) SELECT esp(0); Error 1305 (42000): FUNCTION XQ.esp does not exist So from there, I attempted to re-install it: CREATE FUNCTION esp RETURNS INTEGER SONAME 'mysqlExtension.so'; I then get this back from mysql: FUNCTION 'esp' already exists. I also tried in vain: DROP FUNCTION esp; same message comes back: Error 1305 (42000): FUNCTION XQ.esp does not exist Not sure where this went wrong. It was working fine then something made it go amok. In one case it says it exists, the other it does not.
View Replies !
Failure Calling User-defined Function In Procedure
I have de following (a function that I created and a procedure that I also created) : This function checks if already exist a user in the table Users. If exists it returns the user's Uid, otherwise returns NULL. CREATE FUNCTION Check_User (_Username VARCHAR (64), _Password VARCHAR (64)) RETURNS SMALLINT DETERMINISTIC BEGIN DECLARE Ret_Val SMALLINT; SELECT Uid INTO Ret_Val FROM Users WHERE Username = _Username AND Password = SHA1(_Password); RETURN Ret_Val; END; The procedure makes use of the function defined above. First it check if there is a user with a particular user/password, if not then creates the new user : Code:
View Replies !
Adding User Defined Date Range For Report
Hello I have a very simple query that list customer info within a date range. I would like to have a interactive form that would allow the customer to choose between two dates and query the DB for information between the two dates entered. it is coded like (WHERE `date_purchased` > '2005-08-01 00:00:00' AND `date_purchased` < '2005-09-01 00:00:00') below is the code but I need help or guidence on how to get the customer input into the query instead of the hard coded area that is there now....
View Replies !
Does MySQL Support User-defined Data Types?
My group is in the process of migrating our Interbase databases to MySQL. We have numerous SQL scripts that use Interbase's CREATE DOMAIN statement (essentially creating user-defined data types) such as: CREATE DOMAIN PRIMARY_KEY AS INTEGER; CREATE DOMAIN FOREIGN_KEY AS INTEGER; CREATE DOMAIN USERNAME AS VARCHAR(20) DEFAULT USER; CREATE DOMAIN WHEN_ENTERED AS TIMESTAMP DEFAULT 'now'; CREATE DOMAIN WHEN_MODIFIED AS TIMESTAMP DEFAULT 'now'; CREATE DOMAIN NAME_TYPE AS VARCHAR(50); CREATE DOMAIN FULL_NAME_TYPE AS VARCHAR(101); CREATE DOMAIN DATE_TYPE AS CHAR(8); CREATE DOMAIN TIME_TYPE AS VARCHAR(20); CREATE DOMAIN COMMENT_TYPE AS VARCHAR(250); CREATE DOMAIN YES_NO AS CHAR(1) CHECK (VALUE IN ('Y', 'N')); CREATE DOMAIN TRUE_FALSE AS CHAR(1) CHECK (VALUE IN ('T', 'F')); I've searched the manual and couldn't find an MySQL equivalent, but it is a large book and I certainly could have missed it. I know we could just do a lot of replacing text and there are workarounds, but my boss wants to keep the original scripts as intact as possible and she asked me to look into this.
View Replies !
Unable To Create User-defined Data Type
I want to create a user defined data type, but when I use the syntax: CREATE DOMAIN newdataType AS INTEGER; (for example) MYSQL returns the following error message: ERROR 1064 (42000) : You have an error in your sql syntax; check the manual that corresponds to your MYSQL Server version for the right syntax to use CREATE DOMAIN newdataType AS INTEGER; I have checked the MYSQL documentation and this forum but came up empty.
View Replies !
No Index Defined Error When Query
Will anyone tell me what's wrong with the following select statement: select * from email where (MdnName like '%".$lastName."%') This query does not return anything although there are records in the database that matches the criteria. I'm querying the contents from a php page with this code: $query = "select * from email where (MdnName like '%".$lastName."%')"; $result = mysql_query($query); $num_results = mysql_num_rows($result); When I tried runing the same select statement in PHPMyAdmin, I received this error: No Index Define!
View Replies !
Accumulating Values Inside A Query
I only have experience of simple queries, and this one is a bit beyond me. I've done some research, and I think it might be possible to do within a single query, but I'm not sure. I have a single table. It contains columns (among others): date, number. I want to produce a table of DATE, and new_number_count, where 'new_number_count' is the number of numbers on day with date DATE that do not have rows on previous DATEs. For example, if this is my table:
View Replies !
Trying To Avoid Using Query Inside While Loop
I've done a lot of reading on here and I learned from some of Rudy's posts that it's a bad idea to do a query inside a while loop. I don't know why this is but he seems like an expert so I'll listen What I am trying to do is take the top ten points from a player and display them. First I will post the tables then the code. CREATE TABLE `tournament` ( `gameid` int(11) unsigned NOT NULL auto_increment, `gametype` tinyint(2) unsigned NOT NULL default Ɔ', `gamedate` datetime NOT NULL default ��-00-00 00:00:00', `leagueid` int(11) unsigned NOT NULL default Ɔ', `seasonid` int(11) unsigned NOT NULL default Ɔ', `roomid` int(11) NOT NULL default Ɔ', `gamename` varchar(50) NOT NULL default '', `cost` mediumint(4) NOT NULL default Ɔ', `seats` mediumint(4) NOT NULL default Ɔ', `notes` tinytext NOT NULL, PRIMARY KEY (`gameid`) ) ; CREATE TABLE `tournament_results` ( `id` bigint(20) unsigned NOT NULL auto_increment, `gameid` int(11) unsigned NOT NULL default Ɔ', `memberid` int(11) NOT NULL default Ɔ', `place` smallint(4) NOT NULL default Ɔ', `earnings` smallint(5) default Ɔ', `bounties` float unsigned default Ɔ', `points` float NOT NULL, PRIMARY KEY (`id`) ); Now the follow code would be what I would use if I just wanted to add all the points and not limit it PHP mysql_query("SELECT members.firstname, members.lastname, SUM(tournament_results.points) AS tpoints, COUNT(*)as numgames, AVG(tournament_results.points) AS apoints, members.memberid AS membersid FROM members, tournament_results, leaguemembers, tournament WHERE members.memberid = tournament_results.memberid AND leaguemembers.memberid = members.memberid AND leaguemembers.leagueid = '$lid' AND tournament_results.gameid = tournament.gameid AND tournament.seasonid = '$sid' GROUP BY members.memberid ORDER BY tpoints DESC"); That will total all of their games but I want to limit it their top 10 scores how can I do this without introducing a query inside of the while loop?
View Replies !
Calling A Php Funtion Inside A Mysql Query
hi there, im trying to call a php function that i have created inside a query and im getting an error.when i remove the funtion statment the query is successful. i was wondering is it possible to call a php function inside a query, if so how do i go about doing so. this is my code:
View Replies !
Parsing Query Result From Inside Bash
Environment: OS X 10.4.11 MySql 5.0.37 To generate parsing data, I have the following: {code} #!/bin/bash val=`mysql -uUSER -p'PASSWRD' dbName<<ENDOFMYSQL select email from webUserRecords where status = "pending" order by userNum limit 0, 1; ENDOFMYSQL echo "${val}" {code}
View Replies !
Preventing A Count Query Inside A Loop How?
I have an idea in my head and im pretty sure its possible but I don't have enough mysql knowledge to apply. Basically, I have this: SELECT user_id FROM ml_group_subscriptions WHERE user_group = $id then in php I initialise a loop go over the results: PHP while ($row = mysql_fetch_array($get_users)) { $this_user = $row['user_id']; and then an insert query: INSERT INTO ml_subscriptions (`user_id`, `campaign_id`) VALUES ($this_user, $add_to_campaign) while this is all dandy, I don't want duplicate data. My only current method would be PHP $get_users = mysql_query("SELECT user_id FROM ml_group_subscriptions WHERE user_group = $id"); while ($row = mysql_fetch_array($get_users)) { $this_user = $row['user_id']; $check = mysql_result(mysql_query("SELECT COUNT(user_id) FROM ml_subscriptions WHERE user_id = $this_user"),0); if ($check == 0) { $insert_users = mysql_query("INSERT INTO ml_subscriptions (`user_id`, `campaign_id`) VALUES ($this_user, $add_to_campaign)"); } } as you can see this is putting a query inside a loop which could be potentially huge. Can I not adapt the first SELECT query to only accept data that is not about to be duplicated?
View Replies !
Last_insert_id() Function INSIDE An INSERT Query
so i've got an insert query, puts a reference to a file on the system in a table. good, great. Now i've got another table ( a join table?) that has 3 columns (id, fileId, entryId) that i need to make an association on. I've got the entryId, but the fileId is what I don't have but can get with the last_insert_id() function but don't know how to 'use' it. INSERT INTO siteentryattachements(entryId, fileId) VALUES ('#ARGUMENTS.entryId#','SELECT LAST_INSERT_ID() as fileId');
View Replies !
Using Time Functions Within A Query
I'm currently working on a script that searches through a database of restauarant information. One of the things it does is allow the user to search only for restaurants that are open at the time of the search, however i get errors when trying to implement this. Here is the query im using:
View Replies !
Query Inside Query?
I would like to know if its practical to Insert a Select statement inside a previous select statements (array). With that said, the 'inner' select having a WHERE statement thats dependant upon the array results...
View Replies !
Can't Apply Security Settings
I got MySQL5.0 working, turns out I forgot to remove my old data files. Here is my problem. I can install fine if I don't modify the security settings, but if I do modify them, it fails saying access was denied on the root account and not using a password. If I don't configure the security settings, I can't connect to it to admin it, either by command line or the GUI admin, it always says that access was denied. I'm not sure what to do now, I've reinstalled several times and the problem keeps commign back.
View Replies !
Apply A LIMIT Per Column Value?
I have a table with device information and the date field is stored as an integer (IE. 20081211). However, not every device has new data inserted every day so the date field isn't alway uniform. I want to keep only the last 3 days for each device and delete everything else. How can I accomplish this? Relevant columns are DEVICE and SAVEDATE. Sample data: CHAIR 20081212 CHAIR 20081211 CHAIR 20081209 CHAIR 20081130 TABLE 20081201 TABLE 20081123 TABLE 20081122 TABLE 20081031 I need to remove CHAIR-20081130 and TABLE-20081031. I'm trying to use LIMIT, GROUP BY and HAVING COUNT(*)
View Replies !
Apply SHA() To Every Item In Column?
I have a password column with the passwords already set for every user. Is there a simple statement I can use to apply the SHA() encryption function to every value in the password column in my table users?
View Replies !
Automatic Operation When Some Condition Apply
I have a table with a big number of rows, on which I do simple operation (basically I need the sums of some columns). As the table gets bigger and bigger, I would like to know if there is a way such than when it reaches a given number of rows (let's say 100000), it stores the results on a new table and use this results for future query (I mean I want to avoid of summing all the rows each time, but I would prefer to use the results I already have obtained). If I have understood well, this could be done with triggers and procedures, but I have no idea about how.
View Replies !
Cross Apply &Top Function Not Working Together
Select PA.Melon, PA.Orange, PA.Kiwi, S.Apple, DD.Pear, CA.Banana From dbo.Fruit As S Inner Join dbo.Bowl As DD On S.Pear = DD.Pear Inner Join dbo.Tree as PA On S.Orange = PA.Orange CROSS APPLY (Select Top (10)* From dbo.Ripe As P Where S.Starfish = P.Starfish And S.Starfish_ID In (76,77,78,79) And S.Date < getdate() Order By S.Date Desc) As CA Order By CA.Banana, S.Date Desc ..........
View Replies !
Apply A Dense Mask To A MySQL Database
I would like to be able to perform queries against a large database (1M+ entries) only looking at specifically masked set of entries. I currently have the candidate entries as a packed C-style bit vector. How do I tell mySQL to only look at the records within this dense mask? The mask can be very dense and the last thing I want is 250,000 entries in a IN clause of a WHERE.
View Replies !
Query Cache For Every User
enabled Query cache using root user... I see the query_cache_type and query_cache_size variables correctly set. I also get correct status values for Qcache_inserts and so on. But when I login as another user (the user with which my web app logs in to DB) and see these values, the query_cache_size is still zero...and the Qcache_inserts don't get updated either...
View Replies !
How To Apply New Database Structure To An Existing Database, Without Overwriting Data?
I am writing a simple blog software application. We released a version of this software a while ago. It comes with a sql-tables.sql file, that you can just import into your mysql database and it will setup all the tables for you, for a fresh installation. We made the sql file by just doing an export I believe, without exporting the data that was in OUR database. Well, since then several people have downloaded and installed our software, and are using it. We are about to release a new version soon, however. Since it is all still under development, many things have changed in our database structure. We've added new tables, and modified or even removed fields from existing tables. So for the new release, we're going to include a new sql-tables.sql file obviously, so that for fresh installations they can still install it just like normal. However, the question is... how do you "upgrade" all the previous version's users's databases, without losing any of their data? Is there anyway to export just the structure of our current database... then somehow apply it so that it just goes through and changes another database to match the new structure? Or any ideas at all how to do this?
View Replies !
Variable Defined By AS
I need to be able to reference a variable I define via the 'AS' keyword in a where clause. I read that this inst possible though. Here is my query: SELECT CASE invoices.associated_status_id WHEN 1 THEN invoices.date_quote_due WHEN 2 THEN invoices.date_follow_up WHEN 3 THEN invoices.date_job_complete END AS custom_due_date FROM invoices WHERE custom_due_date BETWEEN " . mysql_smart_quote(convert_date_2_mysql_date($_POST['first_date_filter'])) . " AND " . mysql_smart_quote(convert_date_2_mysql_date($_POST['second_date_filter'])) . "ORDER BY custom_due_date DESC;" See the problem is that I am trying to use the variable custom_due_date, which I declare on the fly, in the WHERE clause of the BETWEEN. Evidently, this inst possible, though I can reference custom_due_date in the ORDER BY clause just fine.So, what is a fix?
View Replies !
No Index Defined!
I am working on editing of articles. Whatever the user enters to edit a particular article then that will be saved wow table(actual table), the already existing data goes to wow_history PHP $add_history = "INSERT INTO wow_history SELECT * FROM wow WHERE id = $id"; "; mysql_query($add_history); Before updating the table with new edits so i have wow and wow_history table identical except that in wow_history the id is not auto incremented (if auto incremented then ONLY THE ORIGINAL data copied stored in wow_history, but rest of the successive edits are lost) But now it says in wow_history that index not defined! So what should i do? Should i leave the wow_history without an index? The table i use is CREATE TABLE `wow_history` ( `id` int(11) unsigned default NULL, `contributed_by` text, `title` tinytext, `content` text, `date` bigint(20) NOT NULL default Ɔ', `mood` int(2) NOT NULL default Ɔ', `tags` text, `trusted` tinyint(1) NOT NULL default Ɔ', `modified` tinyint(1) NOT NULL default Ɔ', `ip` varchar(80) NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1; I tried making uid (another field as autoincremented index but then data of wow is not copied to wow_history). Can i make timestamp as index? What are the drawbacks if there is no index?
View Replies !
Wildcard Vs Defined Selects
At which point is it better to do a wildcard select compared to defined as per below, or is it better in all cases to do a wildcard or defined? Is there a speed difference? This is for use within php scripts and not via command line type selects. wildcard- SELECT t.*, ................from $threads t........... defined- SELECT t.field1, t.field2, t.field3, t.field4 ................from $threads t........... Also... SELECT t.*,f.name FROM $threads t, $forums f WHERE t.fid=f.fid is this better being done with a join and which mysql version started supporting joins?
View Replies !
|
TRACKER
