mysql

My SQL isn't limiting my list to just the instance I want

So I am trying to return only the records that are set to default from a mapping table but it is setting all languages to default related to that item from that market. Bellow is the exact sql I am using. SELECT `teams`.*, `markets`.`title` AS `market`, `markets`.`short_name`, `market_countries`.*, `count...

Insert array in ONE field in mysql

Hello .. I want to insert an array in ONE field in mysql database using PHP .. this is work fine : HTML : anotherField :<input type="text" name="anotherField" /> fax :<input type="text" name="f[]" /> email :<input type="text" name="f[]" /> phone :<input type="text" name="f[]" /> PHP (I use CodeIgniter frame) : <?php functi...

What is wrong with this SQL schema?

I'm designing my first database for MySQL and the idea here is that we have orders that can contain multiple different items. So i decided to store order with all the relevant info in one table, items in another, and then create a third table where each ordered item is stored. Of course every time i will need to list an order i first wi...

How to list all table in mysql database

Hi I want to list all tables in my mysql database. I want that each table list with column name and datatype. So how could i do this, any query or something like that. Running on php Thanks Avinash ...

Index for BETWEEN operation in MySql

I have several tables in MySQL in wich are stored chronological data. I added covering index for this tables with date field in the end. In my queries i'm selecting data for some period using BETWEEN operation for date field. So my WHERE statement consists from all fields from covering index. When i'm executing EXPLAIN query in Extra co...

update replace command in mysql

All, In a field named(path) in The table named is recn i have the follwoing data /home/user1/Computer-Science-10_1-10_7-17//html/Compu.html how do i replace /home/user1/Computer-Science-10_1-10_7-17//html/Compu.html with /home/user1/path/files/Computer-Science-10_1-10_7-17//html/Compu.html in mysql Also There are many rows like /h...

Input text and special characters and MySQL

I have a simple textbox in a form and I want to safely store special characters in the database after POST or GET and I use the code below. $text=mysql_real_escape_string(htmlspecialchars_decode(stripslashes(trim($_GET["text"])),ENT_QUOTES)); When I read the text from the database and put it in the text value I use the code above. ...

mysql custom sort

hello, I have a query like this: SELECT * FROM table WHERE id IN (2,4,1,5,3); However, when I print it out, it's automatically sorted 1,2,3,4,5. How can we maintain the order (2,4,1,5,3) without changing the database structure? Thanks! ...

insert ip into mysql

I came accross this statement for inserting an IP into a mysql table. INSERT INTO `best` VALUES (132+256*(172+256*(109+256*(115)) I would like to know why an IP is being inserted this way, and how to actually work out what IP is being inserted ...

Inserting Sum of 2 tables into a row

Hi All I would like to store in a row of a 'Totals' table the sum calculated from 2 other tables. I want to use a column for each table. I tried the following: INSERT INTO Totals ( Date, FirstTableSum, SecondTableSum ) SELECT '2010/01/31', SUM( t.Data1 ), SUM( v.Data2 ) FROM FirstTable as t, SecondT...

PDO::MYSQL_ATTR_USE_BUFFERED_QUERY and multiple delete statements in prepared statement

I am trying to use a prepared statement that deletes from two (temporary) tables: public function clearTempTables() { static $delStmt = null; if (null == $delStmt) { $delStmt = $this->pdo->prepare("DELETE FROM product_string_ids; DELETE FROM product_dimension_valu...

error in mysql connection "The given key was not present in the dictionary"

I have problem while connecting to mysql database using "ADO.NET Driver for MySQL (Connector/NET)" I got this exception The given key was not present in the dictionary. Edit: MySqlConnection con = new MySqlConnection("Server=localhost;Database=pgs_db;Uid=root;Pwd=;"); MySqlCommand com = new MySqlCommand(); com.CommandType = CommandT...

Can Django ORM do an ORDER BY on a specific value of a column?

I have a table 'tickets' with the following columns id - primary key - auto increment title - varchar(256) status - smallint(6) - Can have any value between 1 and 5, handled by Django When I'll do a SELECT * I want the rows with status = 4 at the top, the other records will follow them. It can be achieved by the following query: sel...

Need advice on how to do this in a single MySQL query

Is this possible to do this in one MySQL query? Basically I need to sort users by how many responses they have. I have 2 tables table "users": id username ------------------ 1 hunter 2 loserville and another table "responses": id user_id response ------------------------------- 1 1 yes 1 ...

Loading results text PHP

I need a simple way to show that results are loading.. from database, while the page loads. I am not using jQuery to do this. What I tried now is: I put a <div> tag with loading text in it and using JavaScript I made the display to none after the whole while loop. But I don't get to see the text at all. <div id="loading">Loading Resu...

How to search in 5 different fields with all possible choices in no particular order in MySQL using LIKE

Hi, I have a database which contains 5 fields, which are: name, address, idcard, town, street I then have a PHP query which accepts one parameter. In this parameter, you can enter whatever fields you like and the results must still be accurate. For example, if I enter john doe skate street in the search box you will get all the record...

How do I drop a primary key in MySQL?

I have a primary key name idnumber. how can I remove its primary key in mysql ...

SQL "ContainsAll" query

I have recipes table and ingredients table and table that connect ingredients to the recipes. I have a list of ingredients, how to write a SELECT statement (or store procedure) that will return a recipes that have ALL given ingredients? How to write this query for MySQL? ...

Deleting Duplicates in MySQL

Hello I have a table like this userid visitorid time 1 10 2009-12-23 1 18 2009-12-06 1 18 2009-12-14 1 18 2009-12-18 1705 1678 2010-01-24 1705 1699 2010-01-24 1705 1700 2010-01-24 1712 1 2010-01-25 1712 640 2010-01-24 1...

SQL Server to MySql converstion, i want convert the SQL Server query dump to mysql database

SQL Server to MySql conversion, i want convert the SQL Server query dump to mysql database. I have SQL Server query dump. Please give me a good conversion tool free of cost. I tried with phpmyadmin import feature, its not working. ...