mysql

Assign permissions to a user on tables with a certain prefix

I'd like to assign a user all privileges on tables which have a specific prefix, eg. 'abc_' I'm aware of the use of the wildcard to select all tables thus: GRANT ALL ON dbname.* TO ... Essentially, what I'd like to do is: GRANT ALL ON dbname.abc_* TO ... This doesn't work so I'm wondering if there is a solution, perhaps using LIKE...

Options for using ImageMagick from PHP with MySQL blobs?

So my host provides ImageMagick on the server, but no MagickWand or IMagick API for PHP. I can do operations with the PHP exec command to manipulate images. But that requires full path file names to work, and I want to pull my images from my MySQL database. Will I have to have pull them out of the database and put them to a file every...

How do I make an asynchronous GET request in PHP?

Hello all, I wish to make a simple GET request to another script on a different server. How do I do this? In one case, I just need to request an external script without the need for any output. make_request('http://www.externalsite.com/script1.php?variable=45'); //example usage In the second case, I need to get the text output. $o...

Foreign key creation issue in MySQL

I have followed this article: http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html to create a foreign key between two tables. Every single attempt fails. Is there anything that I am missing?! It's really frustrating and I never expected to encounter this issue at all! Thanks. ...

PHP: Using quote() on strings in MDB2 with MYSQL

Hi I'm using Pear's MDB2 and really enjoying it, but there is something that is starting to annoy me. I build the SQL string in the following way, using the quote method of the mdb2 package: $sql = sprintf("INSERT INTO profiles(email, fullName) VALUES(%s,%s)", $mdb2->quote($email), $mdb2->quote($fullName)); (as explained in...

How do you program against a Database you can't see in Visual Studio?

I'm writings some ASP.NET and I tried connecting to my MySQL database. I found out GoDaddy doesn't support remote connecting to the database (free hosting). Is there a way to program against it? Maybe prototyping the database locally? ...

MySQL FULLTEXT indexes problem…

So I’m trying to create a FULLTEXT index on an attribute of a table. Mysql returns ERROR 1214: The used table type doesn’t support FULLTEXT indexes. Any idea what I’m doing wrong? ...

How to retrieve 10 latest DISTINCT IP's from a MySQL table?

Hey guys, I have a table containing pagehit (normalized) data and I need to grab the 10 latest unique ips. I tried to do it like this: SELECT * FROM spy_hits ORDER BY date desc GROUP BY ip LIMIT 10; Which should give me this result: +-----+------------+-----+---------+----+------+------+---------+-------+-------+ | id | date ...

PHP RSS not parsing at line 55

Hi I wonder if anyone can help with this code. I am trying to show the records from a URL table for a category, eg url_category_id = to 1. where the url and category is the same in the associating table. I am having some issues with this rss as it has a parse error on line 55. I have tried to resolve the issue but without avail. <?php ...

Problem writing this query in mysql (marking read messages in a forum)

Hey. i am writing a forum, and i have this table that marks messages a specific user read: `read_messages`(`message_id`,`user_id`) a simplified version of the messages table: `messages`(`id`,`forum_id`,`author_id`) now, i want to be able, when retrieving the message data from the database for a given forum, to add a variable that w...

Efficient query to get all child nodes of a tree (mysql)

I'm looking for an efficient query to return the child nodes of a tree . The data structure is: `ct_cid` int(10) unsigned NOT NULL default '0', // the data `ct_level` int(10) unsigned NOT NULL default '0', // the level (0 = topmost) `ct_parent` int(10) unsigned NOT NULL default '0', // parent ct_cid I need to make sure there won...

What is wrong with this query (Select as RES1 Union Select where param IN (RES1)

The query: SELECT ct_cid as level1 FROM cat_tree WHERE ct_sid=$sid_int AND ct_parent =$cid_int) UNION (SELECT ct_cid as level2 FROM cat_tree WHERE ct_sid=$sid_int AND ct_parent IN level1) The query is supposed to find the child nodes up to 2 levels of a tree node. I get "You have an error in your SQL syntax; check the manual that corr...

Store in Session Data vs store in Sql Database for temporary data

Hello, I am wondering whats more efficient, to store temporary data (related to that session) in a session using say $_SESSION variable in PHP or store and retrieve from an SQL database? Thank you for your time. ...

How to filter a result set using the query string in PHP / MySQL?

I'm building an events page similar to last.fm/events The user can filter events by date, category etc, with the parameters passed in the query string My question is, what's the best way to structure your code (queries, loops) to allow for these different query types, and potentially combine them (e.g. filter by date and category) I'm...

SQL Regex function that is similar to the MySql REGEX Function

I am looking for a function that would be able to do the same thing as the MySQL REGEX function for TSQL. Basically i need my Query to look something like the following: SELECT * FROM Routing WHERE (@Message REGEX RouteRegex); I am not to keen to use CLR at this point in time. Any Ideas? ...

How to sync the clocks of multiple Fedora systems

I have a subscription system that allows users to subscribe to things or people they are interested in, and receive notifications when new posts or files or images are submitted. To determine whether something is new, I track their views by tstamp. The problem is that if the webserver and the MySQL database are out of sync, a user could...

Passing variable number of parameters to Stored proc

I am trying to pass a variable amount of parameters into a MySql stored proc. I have done this in Sql Server by using xml or parsing a delimited list. I am guessing I could do the parsing of a list in MySql but are there other options? What I am trying to do is I have a configuration that stores a filter of data to return. The filter...

How do I use a Left Join twice on the same colum with MySQL?

I have two tables, one for airports and one for routes. Airports looks a little like this Airports ------------------------------------- id | code | name | ------------------------------------- 01 | LGW | London Gatwick | ------------------------------------- 02 | LHR | London Gatwick | and so o...

MySQL MAX_JOIN_SIZE errors

I am asking this question on behalf of a small group of my users that have this problem. Once the script they are using gets to the 21st ID, it generates the following error: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay ...

Porting existing php application to cakephp

Hello I have an existing PHP/MySQL application that I want to convert over to CakePHP. Unfortuaintly I can not change the column names and they do not follow the CakePHP standard. For example: One of the table names is called "user" and its not plural. The primary key is called "user_id" instead of "id". The creation date is called ...