mysql

SQL statement - "join" vs "group by and having"

This is a very basic query I can't figure out.... Let's say I have a two column table like this: userid - roleid 1 - 1 1 - 2 1 - 3 2 - 1 I want to get all distinct userids that have roleids 1, 2 AND 3. Using the above example, the only result I want returned is userid 1. How do I do this? ...

Why won't my PHP return a MySQL stored procedure?

Well I have this MySQL stored procedure that I wrote and if I run the following in phpMyAdmin things return properly: SELECT game_name,urlfriendly(game_name) AS game_name2 FROM games However if I try to run the following code I get the error "Warning: Invalid argument supplied for foreach() in /filepath.php on line #" foreach ($this-...

How do you enable customers use their openid on your website, just like stackoverflow?

I want customers to use their openId on my site. I googled for this but didn't find any good tutorial. I use PHP and MySQL. There is one at Plaxo. But it says we should download something from JanRain.com. I saw the openId module of Drupal. It doesn't want anything to be downloaded. Can anyone tell me what to do exactly? ...

PHP/MySQL - building a nav menu hierarchy

So the final menu will look something like this: Item B Item B-1 Item B-1-2 Item B-1-1 Item A SubItem A-1 SubItem A-2 Item C Based on the following DB records: id menu_title parent_menu_id menu_level weight 1 Item A 0 1 ...

Signed or unsigned in MySQL

I wonder is there any positive effect in using UNSIGNED flag on defining some integer field in MySQL? Does it make queries faster or database smaller? Or should I only bother with it if I'm concerned about upper limit? ...

Can I order by multiple columns and somehow keep the ordering related between columns in MySQL?

I know the title doesn't explain my question very well (if someone can come up with a better title then please edit it). Here's what I want to do, say I have the following table: id | a | b | c ------------------ 1 | 3 | 3 | 3 2 | 20 | 40 | 30 3 | 40 | 30 | 10 4 | 30 | 10 | 15 5 | 10 | 15 | 6 6 | 15 | 6 | 20 This is slight...

Copy MySQL structure across servers via PHP

Hi to all, I'm in need of help - I've got two mysql databases on different servers with a web host, and I need to copy a Joomla installation from one to the other. The problem I have is that the only way I can access the data is via php, as direct odbc connections to the database are locked down. I don't mind doing it table by table, bu...

Why do I have to copy the libmysql.dll to the apache/bin directory to get the PHP extension to load properly?

I'm on a Windows machine. This seems like it should be unnecessary, but when I do it, everything suddenly works. Is there something wrong with my path? Do I need to add something to it to avoid having to copy DLLs? ...

Does MySqlDump have a problem backing up big database ?

Is there any limitations on the size of a MySQL database that can be backed up with MySql Dump ...

Whats the best way to populate two tables with records that need to reference each other?

What is the best way to populate records in two tables that each need a reference to the primary key of the other? My thoughts are either having a "link" table between them which is populated once both have been written to the db or by the following complex series of commands Insert1 get identity1 Insert2 get identity2 update 1 How...

How do I generate a series of hourly averages in MySQL?

I've got data in ten minutes intervals in my table: 2009-01-26 00:00:00 12 2009-01-26 00:10:00 1.1 2009-01-26 00:20:00 11 2009-01-26 00:30:00 0 2009-01-26 00:40:00 5 2009-01-26 00:50:00 3.4 2009-01-26 01:00:00 7 2009-01-26 01:10:00 7 2009-01-26 01:20:00 7.2 2009-01-26 01:30:00 3 2009-01-...

How do you enable customers to login to your site using their google account?

I just saw http://uservoice.com/login It uses google accounts, myspace, yahoo, openid and all to sign in customers into its site? Can I do that? I mean, customers need not register to my site. They can just sign in with their accounts on the above sites. If you've a solution, I'd prefer a PHP and MySQL based one. ...

converting from mysql to mysqli - outputting variables

I have been using the mysql api in PHP, and am now converting to mysqli for the increased security. The example syntax I have seen uses printf, and I would like to know if this is necessary. At the moment I use echo, like so: echo "<h1>".$row['ARTICLE_NAME']."</h1> <div id='leftlayer' class='leftlayer'> <p><strong>Username: </strong>".$...

User variables - fighting syntax

Can anyone set my MySQL syntax straight please? I am trying to set a user variable called "seconds" to equal the output of the query shown below (and it does work by it's self), but I am constantly geting "You have an error in your SQL syntax" errors. SET @seconds=AVG(t2.epoch-t1.epoch) FROM tmp_4045_metrics AS t1, tmp_4045...

nested insert in mysql for tagging

hi all, i'd like to add a tag to a blogpost with a single sql statement. say my tables would look as follows: tags +-------+-----------+ | tagid | tag | +-------+-----------+ | 1 | news | | 2 | top-story | +-------+-----------+ tag2post +----+--------+-------+ | id | postid | tagid | +----+--------+-------+ ...

should i really use a relation table when tagging blog posts?

hi all, while trying to figure out how to tag a blog post with a single sql statement here, the following thought crossed my mind: using a relation table tag2post that references tags by id as follows just isn't necessary: tags +-------+-----------+ | tagid | tag | +-------+-----------+ | 1 | news | | 2 | top-story ...

Is it a bad idea to open a separate MySQL connection inside Rails' environment?

I'm in a situation where I need to make a call to a stored procedure from Rails. I can do it, but it either breaks the MySQL connection, or is a pseudo hack that requires weird changes to the stored procs. Plus the pseudo hack can't return large sets of data. Right now my solution is to use system() and call the mysql command line dir...

UTF-8 only in Grails database tables

When using Grails 1.0.4 together with a MySQL the charsets of the auto-generated database tables seem to default to ISO-8859-1. I'd rather have everything stored as pure UTF-8. Is that possible? From the auto-generated database definitions: ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1; Note the "latin1" part. ...

Filter a one-to-many query by requiring all of many meet criteria.

Imagine the following tables: create table boxes( id int, name text, ...); create table thingsinboxes( id int, box_id int, thing enum('apple,'banana','orange'); And the tables look like: Boxes: id | name 1 | orangesOnly 2 | orangesOnly2 3 | orangesBananas 4 | misc thingsinboxes: id | box_id | thing 1 | 1 | orange 2 | 1...

Maintaining/updating record order in mysql

I have a table of records in mySql. I need to maintain an order for them as specified by the user. So I've added a 'position' column. What would be the SQL statement to update all the records when I move a specific record? I've got something like: UPDATE items SET position = '2' WHERE id ='4'; UPDATE items SET position = position+1 WHE...