mysql

MySQL stored procedure porting?

I am having a great deal of trouble porting some stored procedures to regular MySQL statements. We have stuff like this example http://dev.mysql.com/doc/refman/5.0/en/stored-programs-defining.html BEGIN SET @x = 0; REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; END Where many statements are executed. Several If statements and...

Complexity of adding n entries to a database

What the complexity in big O notation of adding n entries to a database with m entries with i indexes in MySQL and afterwards committing? ...

How do I find all instances of only capital letters, spaces and numbers in a mysql search?

I have a MySQL database with roughly 70,000 records. I want to be able to search the "Person" table on the "Address1" column for all address that only contain capital letters, spaces, and numbers. The end goal is to flag any addresses that look like this: 124 DOLPHIN STREET so they can be converted to 124 Dolphin Street. I tried using...

Replace PHP Sessions

I have a social network type app I have been working on for at least 2 years now, I need it to scale well so I have put a lot of effort into perfecting the code of this app. I use sessions very often to cache database results for a user when a user logs into the site I cache there userID number, username, urer status/role, photo URL, on...

Pagination while using a GET variable

Hello, I have had a difficult time paginating the code below. I think it has to do with passing the GET variable $find on to the next page. Anyway, how would I paginate the code below, so that the table below shows only 100 rows per page? Thanks in advance, John <?php ob_start(); session_start(); $find = strip_tags($_GET['find']); ...

Show records that contain a certain value first in MySQL

I need the records in my database that are "featured" to be listed first. If a listing is "featured" the value in the featured column is "yes". I am not sure what kind of MySQL Query would give me this result, or if it even exists. But the other idea I have is to have one query that gets all featured ones and lists them, and then anothe...

Simple AJAX Submit and update mysql

I am stumped, I am tossing out my code and I need help with a cross browser ajax submit. Can anyone PLEASE give me a simple working ajax submit script for updating mysql? The one I have is all bad. Works in FF and Safarai (iphone), but in IE7, it has caching problem and in IE8 it doesn't even submit. ...

How to SELECT the newest four items per category?

I have a database of items. Each item is categorized with a category ID from a category table. I am trying to create a page that lists every category, and underneath each category I want to show the 4 newest items in that category. For Example: Pet Supplies img1 img2 img3 img4 Pet Food img1 img2 img3 img4 I know that I could easily...

MySQL Query Questions with a Foreign Key Database

Here's some background info. I have three MySQL tables (all InnoDB). The first table is used to store images records. The second table is used to store tags (to tag the images with. OMG). The third table is used to store relationships between the images and the tags. Yippee. Here's the structure of the tables. CREATE TABLE `images` ( ...

Is it possible to compare two tables when no common key exists between them?

I have two tables that I would like to compare for duplicates. These tables are just basic company information fields like name, city, state, etc. The only possibly common field that I can see would be the name column but the names are not quite exact. Is there a way that I can run a comparison between the two using a LIKE statement? I'm...

MSSQL to MYSQL Migration tool

I want to migrate my MSSQL database into MYSQL. Is there any open source or free software for that? ...

how to make lyrics site database

I'm new to PHP and MySQL. For my project I want to make a site for lyrics. How to design the database and the relationships? Here is what I have so far: Artist Artist_id Artist_name Artist_bio Artist_thumb Albums Album_id Artist_id Genre_id Album_title Release_year Genre genre_id genre_name Tracks track_id track_title alb...

mysql ft_stopword_file question

Hi, This is really embarrassing but I can't get the stopword file to function as I want in mysql. My mysql base dir in my my.cnf is /usr and my server's root directory is below that. My my.cnf file is located at /etc/mysql/my.cnf I name the stopword file stop.txt and have placed it in the /usr folder, the /etc/mysql/ folder, and...

How to build Threaded comments with a 1 or 2 queries?

Can anyone suggest a creative database structure + fetching algorithm for a threaded comments system, that would output x amount of threads per page (with unlimited replies for each)? I can run a query to get the threads, and in each instance of a loop, run another query to echo out the replies.... but that's a bad idea. ...

MySQL error when trying to get unique values using DISTINCT together with LEFT JOIN

I have two mysql tables, courses and student_courses, I need to get unique course_names from the course table. But I get a erro when trying to execute my query. Heres my query SELECT sc.c_id, DISTINCT c.course_name FROM Courses AS c LEFT JOIN Student_Courses AS sc ON c.c_id = sc.c_id WHERE sc.s_id = 4 This i...

what is collation_database, collation_connection, collation_server used for in mysql?

Illegal mix of collations (big5_chinese_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'like' sometimes this error will be throw, what's up ? what's the collation_* used for ? Can someone give me a example to show how the character is encoded and transited and selected relate to collation_*? ...

Associative Array/Hash/Hashtable using Connector/NET

Hey Guys, working with asp.NET and c#, I'm using MySQL's connector/NET plug-in thingy to connect to a MySQL db (no surprises there!). And that works fine, can connect, run queries etc etc all fine and dandy, but is it possible to return a Hashtable or similar of the results? Save running a describe on the same table to get the column na...

Best approach to make an online dictionary?

Hi, guys! I'm trying to create an online dictionary (some rare languages) and need you suggestions. My php, mysql and javascript knowledge though is far from excellent, but is enough to understand the code and make some changes to it. I've done some projects with Drupal before and now thinking about trying a php framework for this proje...

Update first then Insert if row doesn't exist (SQL) Rather than check if row exists first?

Hiya All, I've always used the method of checking a table to see if a row exists, and then update it with my new data or insert it if it doesn't exist, but it's got me thinking what would be wrong with simply doing an update, if no rows are effected, then do an insert statment, that could potentially speed up my script and put less load...

MySQL: which is preferable, separate indexes or same index?

I'm trying to speed up SELECT queries in MySQL tables which already have some indexes. Hopefully without significant impact on frequent INSERT INTO queries running at the same time (by other clients). The tables have millions of entries and have already passed the Gigabyte size (they have a BLOB field). The tables have a pair of fields ...