mysql

PHP and MySQL search across lots of columns

I need a way to search through 50+ columns, both varchar and int, in a specific table. I've seen people recommend Lucene and Sphinx, but I have no clue how to go about using those and MySQL's Full-Text won't allow me to combine more than 16 columns or both varchar and int columns. ...

MySQL - How to SUM times?

I have a table that contains date-time values in this format: START 1/13/2009 7:00:00AM END 1/13/2008 2:57:00PM I use the 'str to date' function to convert these into a date-time format. How do I calculate a difference between them? And then sum it all up so that it displays in total hours (ie total ho...

CakePHP Model with "Between dates"

I have a large data set (over a billion rows). The data is partitioned in the database by date. As such, my query tool MUST specify an SQL between clause on every query, or it will have to scan every partition.. and well, it'll timeout before it ever comes back. So.. my question is, the field in the database thats partitioned is a dat...

Select only some columns from a table on a LEFT JOIN

Hello guys, Is it possible to select only some columns from a table on a LEFT JOIN? ...

Multi-Query SQL Condensed to One Result

There's information that my web-app needs from my database that I can only generate for the entire database with many queries populating a brand new table. Can anyone suggest to me a single query that returns the results the exact same as this populated table? First, here are the two tables that have the information. CREATE OR REPLACE ...

db:schema:load vs db:migrate with capistrano

Hi, I have a rails app that I'm moving to another server and I figure I should use db:schema:load to create the mysql database because it's recommended. My problem is that I'm using capistrano to deploy and it seems to be defaulting to rake db:migrate instead. Is there a way to change this or is capistrano using db:migrate for a good re...

What's the correct way to reuse connections in case of PHP and MySQL?

I've tried restoring connections in $_SESSION, but can only reuse for one user. Any better solutions? ...

Using LDAP (AD) for MySQL authenication

I'm trying to come up with a plan to allow users to auth with a MySQL database (many, actually) using LDAP. More specifically, ActiveDirectory. Database will likely be accessed through applications, not web. What are my options? EDIT: Okay. It seems that there is no "official" way to allow authentication on MySQL using LDAP. What othe...

How do pass in a limit to a query run inside a mysql 5 stored procedure?

Here's a sample of what I'd like to do. The stored procedure fails to create while the LIMIT is present on the query. If I take the LIMIT off the procedure creates. CREATE PROCEDURE LimitTest(IN in_start INTEGER UNSIGNED, IN in_limit INTEGER UNSIGNED) BEGIN DECLARE no_more_results INTEGER DEFAULT 0; DECLARE var_result INTEGER UNS...

Access denied when attempting to connect to MySQL from jsp in NetBeans

I'm trying run a simple Java/MySQL web app tutorial in NetBeans. I am able to connect to my database from a standard Java program in NetBeans, and Tomcat is working. But when I try to connect to the database from a JSP, I get the following error: javax.servlet.ServletException: javax.servlet.jsp.JspException: Unable to get connection...

Problem with DBDesigner

Hi everyone. The DBDesigner can synchronize only with MySQL? I tried with SQL Server but don't works. DBDesigner Ver: 4.0.5.6 Tks. ...

Subscriptions - How to handle large content and subscriptions amount?

Hello, I am sending notification to my users depending on what kind of subscription each user has. For example: User A subscribed to all news articles User B subscribed to all comments User C subscribed to everything that is new on the site I have a script running every 5 minutes (unless the script is still running) who does the fo...

Database changes and VCS branching

Suppose I am developing a web application that uses a database, and I create a branch in my version control system which requires significant database changes. Let's suppose I can't just use the normal trunk database as I often do for other branches, and in this case I actually need a new copy of the database for my branch. Now, suppos...

weird error in php and mysql

$result=mysql_query("SELECT * FROM playerlocations WHERE player<>'0'"); $returntext=""; while($row=mysql_fetch_array($result)) { if($returntext!=""){$returntext.="&";} $returntext.=$row['player']."#".$row['locations']; } the error message claims that $result isn't a valid result set. I tested it in phpmyadmin, and it worked. I don't ...

mysql join limit with two tables

i have two tables like this -> Categories ----------- id name --------- 1 --> a 2 --> b 3 --> c Messages ------------ id catid message --------------------------- 1 --> 1 --> aa 2 --> 1 --> bb 3 --> 1 --> cc 4 --> 2 --> d...

Mysql Join query using a group by

I'm trying to write a query that will return ... Each distinct user id in the accesslog table. A count of how many distinct ip addresses each userid has accessed the site with, also from the accesslog table. That userid's name, email address, and company name, from the users table. I've got 1 and 2, but I'm not sure how to get 3. I s...

mysql insert select conundrum

I'm trying to insert select but I'm confused as to how to populate just 1 item into the destination table from the select, and populate the rest with static values. This is all in a stored procedure... so... here's what i'm trying to do... table:animals id |type| name 1 |cat | mittens 2 |cat | fluffy 3 |dog | rex table p...

mysql LIKE returning only one row when three match.

When I query my database with the following in my file, search.php, it only returns the first result it comes across. $qry = "SELECT business_id FROM business WHERE zip like '%91326%'"; $rs = mysql_query($qry); $rec = mysql_fetch_array($rs); echo $session->showContents($rec); showContents is just a utility function... function showC...

uninitialized constant MysqlCompat::MysqlRes (using mms2r gem)

Hi, moved a rails app of mine onto a new server and had to install a few gem dependencies. However, after installing the mysql gem I get the error, uninitialized constant MysqlCompat::MysqlRes, whenever I try to run a rake command that involves the mysql database. It seems I only get this error when I require the mms2r gem. Has anyone ...

Backticking MySQL Entities

I've the following method which allows me to protect MySQL entities: public function Tick($string) { $string = explode('.', str_replace('`', '', $string)); foreach ($string as $key => $value) { if ($value != '*') { $string[$key] = '`' . trim($value) . '`'; } } return implode('.', $string); } ...