mysql

PHP /MySQL - *-to-Many Relationships

Howdy! So, I understand how the relationships work in mysql but I'm having a hard time figuring out how its implemented in my code. For example, say I have the 3 tables. Table 1: users - user id, username, user city Table 2: categories - category id, category name Table 3: user_categories - user id, category id If I were to query...

How can I search all of the databases on my mysql server for a single string of information

I have around 150 different databases, with dozens of tables each on one of my servers. I am looking to see which database contains a specific person's name. Right now, i'm using phpmyadmin to search each database indvidually, but I would really like to be able to search all databases and all tables at once. Is this possible? How wou...

SELECT '0' AS variable ... with Zend_Db_Select

I am trying to create a select statement that uses the following structure: $db ->select() ->from( array('i' => ...), array('provisional', 'itemID', 'orderID')) ->columns(array("'0' AS provisionalQty", "'ballast' AS productType")) ->joinLeft( array('o' => ...), 'i.orderID = o...

Mysql BEFORE UPDATE trigger activation time

I have two InnoDB tables: CREATE TABLE master( id INTEGER UNSIGNED NOT NULL auto_increment PRIMARY KEY ); CREATE TABLE details( id INTEGER UNSIGNED NOT NULL auto_increment PRIMARY KEY, master_id INTEGER UNSIGNED NOT NULL, CONSTRAINT `byMasterId` FOREIGN KEY (`master_id`) REFERENCES `master`(`id`) ON UPDATE CASCADE ON DELE...

Insert LargeBlog sql issue

Right I seem to be having a issue inserting a image into a database (don't ask why lol) If tryed a simple UPDATE player SET Image = load_file('94.jpg') WHERE id =94; And had the image directly located from where im running the sql commands from UPDATE player SET Image = load_file('C:\Users\***\Documents\databases\Scripts\94.jpg') ...

MySQL data change management

I have content stored in a table with the following schema: id unsigned int primary_key, name varchar(20) I have two levels of users: 1.Content editor: Who can change content and submit for approval(involves deleting rows as well as editing fields) 2.Content admin: Who can see changes and approve them. The content editor works on a ...

SQL Server to MySQL - but what to do with sprocs?

I have been tasked with converting a SQL Server database into a MySQL 5.* database. I feel well-read up about converting between datatypes. However, I read that MySQL does ANSI, not T-SQL, and doesn't support cursors. My question is, what am I supposed to do with all of my SQL Server functions and sprocs (some of which use cursors) ? ...

MySQL database not wanting to add any new records

Hi every1! ok so I have this code `$sql = "INSERT INTO userTable (username, password, gender, city, zip, email) VALUES ('$username', '$password', '$male', '$city', '$email')"; mysql_query($sql) or die ("unable to process query");` and for some reason it works on my local server but not on the webserver, all the variables are set for...

How to index a MySQL table, where 99% of time I need to query 1% of the data

Hi I have a MySQL table, let's say it is a helpdesk ticketing system. It has a CLOSED column, which I want to index. 99% of time, I will need to only select on OPEN tickets, so something like "SELECT * FROM TICKET_TABLE where CLOSED='N'; " And over time, more and more tickets are CLOSED, while a small constant number of OPEN tickets r...

Inserting one record at a time to multiple tables from a table

Hi, I have one flat MAIN_TABLE. I need to insert the records from this table to multiple TABLES. eg. MAIN_TABLE col1, col2, col3, col4, col5 PARENT1_TABLE PT1_ID(PK), col1,col2 PARENT2_TABLE PT2_ID(PK), col3,col4 PARENT2_CHILD_TABLE P2C_ID(PK), PT2_ID(FK), col5, col6 so on. The goal is, I have to move the record from that flat M...

Help writing a mysql query - this mustve been done 1000 times before but I am struggling..please help?

Update: I am editing my question in the hope of getting a better answer. I see this is not so simple but I cant believe there is not a simpler solution than what has been mentioned so far. I am now looking to see if there is some kind of php, mysql solution to deal with this in the most efficent way. I have modified my question below to ...

java equivalent of mysql string search operator 'like'

Edit: thanks for the reg answers below. What if my goal is to strictly emulate mysql like operator,which seems to ignore \n in between characters? what's java equivalent of mysql string search operator 'like'? I need to search for strings that might have \n end of line or other white spaces between them. for example, abc\n efg string....

Printing Results in a Simple Table

I have a MySQL table with a structure similar to this: id1 id2 title url date I would like to print out a simple table in PHP that with the following structure sorted in reverse chronological order for the most recent 10 entries (date above = date submitted) from the MySQL table: title id2 How could I do this? ...

MySQL innodb seeks vs. contiguous reads tradeoff

Would mysql (innodb) support a higher rate (queries per second) of queries like (A) or (B)? (A) SELECT * FROM t1 WHERE pkey BETWEEN 2000 and 2001 AND x > 300 In (A), the primary key selects a range of 800 rows. "x" is unindexed. there's one range lookup and 1 contiguous read of length 200kb. (B) (SELECT * FROM t1 WHERE pkey BETWEEN 20...

PHP query join question.

if i have a table that looks like this: sample_id date sample_number 12 1-24-10 5 09 1-25-10 2 12 2-14-10 5 How can I join both (sample_id:12)'s ? sample_id:12 has a total of 10 sample numbers. ...

Recommended language for modifying a MySQL DB

I've got a rather large MySQL database of various pieces of text. I need to slightly modify approximately 90% of all entries using standard string manipulation algorithms and some regexps. All of my current DB manipulation scripts are PHP frontends for a website. Recommendations for a language/technology to do this in? Support for JSON...

Java MySQL web host recommendation

Not a programming question, but a question which is uniquely important to programmers. Can anyone recommend a good Java Servlets/Mysql/Tomcat hosting provider. Cost and ease of setup is the main consideration as this is just a demo. ...

random numbers in java

Hi all, I have the following table created using java as the front end and mysql as the backend. mysql> select * from consumer9; ------------- 4 rows in set (0.13 sec) Service_ID Service_Type consumer_feedback 100 computing ...

what would be the database URL of the mysql database that we access through phpMyAdmin ?

I am writing a code that access a MySql database. The database is on a server that we access through our web-browser Let's say, URL: http://cbm.goo.com/phpMyAdmin Username: username Password: password The above username and password are used to access the MySql database through a web-browser. Q1. What would be database ...

Can not connect to mysql when using FastCGI for PHP under Apache on Windows Vista

I configured PHP to use Apache mod_fcgid. PHP is working, but after changes in configuration I am not able to connect to mysql via php. How to fix this? Any ideas, which can hint me, are very desired! To check mysql connection I use the following php code: error_reporting(E_ALL); $link = mysql_connect('127.0.0.1', 'root', 'password_he...