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...
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...
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...
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...
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...
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...
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...
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...
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...
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. ...
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...
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, ...
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: 1' OR '1'='1 Server Status Code: 302 Found Tested value: 1' OR...
hi all, i am looking for the best software to draw ERD in windows for a mysql database. ...
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 ...
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 ...
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...
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 ...
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...
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...