mysql

bulk insert mysql - can i use ignore clause? is there a limit to no. of records for bulk insert?

I have a bunch of data that i want to insert and i have decided to use bulk insert for mysql. insert into friends (requestor, buddy) values (value1, value2), (value2, value1), (value3, value4), (value4, value3), ... i would like to know the following: 1) can i use ignore? eg insert ignore into friends (requestor, buddy) values (value...

Quickest way to get total records from a mysql table in PHP

Below is the current code I use to get the total number of photos in a DB, An estimated number would even be acceptable as the number of results is just used to determine which folder number I would like to upload into for folder balance of files. I have a feeling there is a better way then I am using below? $sql = 'SELECT auto_id FROM...

How do you solve MySQL & Django Admin Foreign Key constraint

I have a Style table to identify a model number for each product type: class Style(models.Model): style = models.AutoField(primary_key=True) name = models.CharField('Title', max_length=150) etc.. And I need a product table which identifies all the product variations of the aforemention model number: class Product(models.Mod...

mysql high cpu usage

recently my server cpu has been going very high CPU load averages 13.91 (1 min) 11.72 (5 mins) 8.01 (15 mins) and my website has only had a slight increase in traffic. after running a top command i saw my mysql was using 160% cpu! recently i've been optimizing table and i switch to persistant connections, could this be causing mysql...

Transaction MySQL

How can I start a transaction when there is any error in the SQL statements the system will rollback the changes automatically? I've the following SQL statement START TRANSACTION; BEGIN; INSERT INTO `users`(id,name,gender,email,age) VALUES(11121,'sss',0,'ssss',22); INSERT INTO `teachers`(`UserId`,`teachingSubject`) VALUES(11...

Use the result of one Mysql command in Another Mysql Command??

Excuse my ignorance but I'm having a tough time figuring this out. I'm trying to take the result from one mysql command, and use it in another command. Here's my code, it doesnt work. //select the event end date of event ID $sql = "SELECT enddate FROM mm_eventlist_dates WHERE id = $id"; $result = mysql_query($sql); //plug in the eve...

Does [My]SQL have a Pre-Processor-like Facility?

I'm writing a small deployment SQL script for my first database-driven app. In the process, I find that I repeat myself a lot, for instance: GRANT USAGE ON *.* TO 'foo'@'localhost'; DROP USER 'foo'@'localhost'; CREATE USER 'foo'@'localhost' IDENTIFIED BY 'password'; It would be fantastic if I could use a variable or a macro to replac...

loop categories with assigned forums

forumcats: id, name forums: id, name, cat_id How can i join these together and print the forums assigned to the categories under them Would really appriciate if someone could give me a hand This is what i mean: A category A forumA forum A 2nd category Another forum Another forum Another forum A 3rd catagory Another forum A...

Error creating spatial index on MySql BLOB column

Hi, I am trying to add a spatial index to a table column named Location of type BLOB. If I try this: ALTER TABLE route ADD SPATIAL INDEX(Location); I get: Error: BLOB/TEXT column 'Location' used in key specification without a key length But in the official docs for MySql 5.1 (the version I am using), it clearly says when...

How to find out all the commands executed on a MySQL database within a given timeframe?

Is it possible to find out all the command executed against a database in a given timeframe? For example, I would like to know who executed insert command on a database table in the last 24 hours. Any help would be greatly appreciated. Thanks. ...

Longest Distance Between Lat/Longs in a List

I have a database of geocoded entries. I need to determine which two entries are the furthest apart from a subset of the total entries. For example, I select a list of 10 entries then, from that list, determine which two places represent the greatest distance within that list. I cannot wrap my head around how to approach this. I've cons...

Searching a nested set

I've got a MySQL table that acts like a nested set in order to contain a hierarchy of categories. The table schema looks like: CREATE TABLE IF NOT EXISTS `categories` ( `id` int(11) NOT NULL auto_increment, `name` varchar(200) NOT NULL, `parent_id` int(11) default NULL, `lft` int(11) default NULL, `rgt` int(11) default NULL, ...

How to fix Server Status Code: 302 Found by SQL Inject Me Firefox Addon

Hi folks. I scanned my login script using SQL Inject Me Firefox addon According to the Test Results, my script was vulnerable to SQL Injection. Result by example Results: Server Status Code: 302 Found Tested value: &#49&#39&#32&#79&#82&#32&#39&#49&#39&#61&#39&#49 Server Status Code: 302 Found Tested value: 1' O&#x52...

what is the best software to draw ERD for a mysql database for windows

hi all, i am looking for the best software to draw ERD in windows for a mysql database. ...

Selecting a specific row from a table

How can I select a particular row from a table? I want to do achieve something like the following: select * from (select * from tablename1 where type=1 order by id desc) where rownum = 5 select * from (select * from tablename1 where type=1 order by id desc) where rownum = $variable ...

Selecting Random Rows in MySQL

I am developing a quiz website, and I have a database which stores all of the questions. There are different types of quizzes, like math, science, history, etc. All of the questions are stored in one table. My questions table looks like this: questions ( qno(int) ,type(int), question, .... ,... ) qno is the primary key, and type is ...

Advise on DB table and index please

Hey everyone, I am currently building a highly efficient website on culinary recipes (let's just put it that way). Each recipe has 10 or less ingredients. So given an ingredient, how can I find 1) All the recipes that the subject is present, and 2) All the other ingredients that are present in each of the recipes in 1) QUICKLY. My ini...

Why make Objects when you can just use a MySQL database?

So I am a little confused on the object oriented part of PHP. Right away I will apologize for the fact I know very little about PHP and databases. My question is when your making per say a database to hold users in it, why would you want to make a class/object for that user when you can just pull info from the database. Also if you ...

connecting three tables in one query

I have the following tables mixes mid | date | info 1 | 2009-07-01 | no info yet music-review mid | song | buy 1 | Example - Example | http://example.com 2 | Exam - Exam | http://example.com tracklist tid | mid | mrid 1 | 1 | 1 2 | 1 | 2 is it possible to have an SQL query where you can li...

Unicode characters in MySQL returning different character code values in PHP and ASP

Hi there, I have a MYSQL database which needs to be accessed by both PHP and MySQL scripts, this works fine in most cases, but some "special" characters e.g. double quotes, apostrophes don't display correctly in the ASP scripts. E.g the MySQL database is from a Drupal installation and contains a table with a field containing the text...