mysql-query

Entering Content Into A MySQL Database Via A Form

I've been working on creating a form that submits content into my database but I decided that rather than using a drop down menu to select the date I'd rather use a textfield. I was wondering what changes I will need to make to my table creation file. <?php mysql_connect ('localhost', 'root', 'root') ; mysql_select_db ('tmlblog'); $sql...

MYSQL update query php

I am trying to use an update query to change values held in a database, the new values I am trying to store are held in php variables. I'm not sure what it is I'm doing wrong. mysql_query("UPDATE user SET status='full' WHERE user_id = '$user_id'")or die(mysql_error()); here is the error message Duplicate entry '91317691' fo...

Update query with conditional?

I'm not sure if this possible. If not, let me know. I have a PDO mysql that updates 3 fields. $update = $mypdo->prepare("UPDATE tablename SET field1=:field1, field2=:field2, field3=:field3 WHE...

Problem with checkboxes, sql select statements & php

I am trying to display some rows from a database table based on choices submitted by the user. Here is my form code <form action="choice.php" method="POST" > <input type="checkbox" name="variable[]" value="Apple">Apple <input type="checkbox" name="variable[]" value="Banana">Banana <input type="checkbox" name="variable[]" value...

nested sql statment using and

hi guys, how to make this work in mysql? select ID,COMPANY_NAME,contact1, SUBURB, CATEGORY, PHONE from Victoria where (city in ( select suburb from allsuburbs)) and CATEGORY='Banks' this below statement is working: select ID,COMPANY_NAME,contact1, SUBURB, CATEGORY, PHONE from Victoria where city in ( select suburb from allsuburbs...

Mysql NULL datetime field in stored proc

I have a stored procedure which takes in a datetime field along with some other fields. The datetime field is allowed to be empty or NULL but when it is passed in it gets passed as an empty string, ''. This causes a problem as my sql admin says that the datetime field is not proper format. How do I convert the '' to a NULL value. I have ...

Delete all but 5 newest entries in MySQL table

I currently have PHP code that handles the logic for this because I do not know how to handle it in SQL. I want to create a stored procedure that will delete all the rows except for the 5 newest for a given config_id. IE config_id = 5 gets passed to the SP so it knows which config_id it is looking to clean up. CREATE TABLE `TAA`.`RunHi...

SQL group by date using latest timestamp

I have the following sample table structure: id | facilityId | accountId | date | timestamp | amount ------------------------------------------------------------- 1 ABC 100 20100401 272300000 55 2 ABC 300 20100401 272300000 50 3 ABC 100 20100402 272400000 ...

What could be the Consequence of inserting "id" in any autoincrementing 'id' containing table?

What could be the Consequence of inserting "id" in any autoincrementing 'id' containing table? If I have a Tabe in which I have configured the column "id" as the auto incrmented, But still I am using an INSERT query in which id is defined, like wise INSERT INTO XYZ (id) values ('26'); How does it going to effect the table and the proce...

What is the equivalent of REGEXP_SUBSTR in mysql?

I want to extract a word from a string column of a table. description =========================== abc order_id: 2 xxxx yyy aa mmm order_id: 3 nn kk yw Expected result set order_id =========================== 2 3 Table will at most have 100 rows, text length is ~256 char and column always has one order_id present. So performance is ...

Mysql combining multiple rows into one row dependant upon a single field?

Hey guys, This really a question to concatinate multiple rows in to one when outputting: I have three tables, table one (tableA) containing users data like name and email, another (tableB) flagging if they wish to receive an email or sms notice and a last table (tableC) noting which type of notices they require out of four types. tab...

Create MySQL user and database from PHP

Hi, Is there a way to create a new MySQL database, a new MySQL user and give the new user privileges on the new database all using PHP? EDIT - should be pointed out this is run from 1 server to another, so Server A trying to install a DB/user on Server B i've got this: $con = mysql_connect("REMOTE.IP.ADDRESS","root","pass"); mysql_qu...

CakePHP: How to increment count for multiple rows in paginated query

Hello all, In CakePHP, I have this code: $data = $this->paginate('Model', array("MATCH(Model.colA) AGAINST('$q' IN BOOLEAN MODE)")); And I am trying to increment by 1 the values of Model.colB for the rows retrieved (10 per page). So I'm guessing it's something like: $data = $this->paginate('Model', array("MATCH(Model.colA) AGAINST('$...

Creating a Mysql view to SELECT coloumns from different tables

I need help in constructing a VIEW on 4 tables. The view should contain the following columns: ER.ID, ER.EMPID, ER.CUSTID, ER.STATUS, ER.DATEREPORTED, ER.REPORT, EB.NAME, CR.CUSTNAME, CR.LOCID, CL.LOCNAME, DI.DEPTNAME The aliases are: EMP_REPORT ER, EMP_BIO EB, CUST_RECORD CR, CUST_LOC CL, DEPT_ID DI The data models are: describe E...

Mysql on duplicate key update + sub query

Using the answer from this question: http://stackoverflow.com/questions/662877/need-mysql-insert-select-query-for-tables-with-millions-of-records new_table * date * record_id (pk) * data_field INSERT INTO new_table (date,record_id,data_field) SELECT date, record_id, data_field FROM old_table ON DUPLICATE KEY UP...

Will closing the MySQL Query Browser kill any running queries it initiated?

Title says it all. I could easily determine this myself, however I have a query running (that I initiated in this application) that I cannot terminate. ...

Testing performance of queries in mysl

I am trying to setup a script that would test performance of queries on a development mysql server. Here are more details: I have root access I am the only user accessing the server Mostly interested in InnoDB performance The queries I am optimizing are mostly search queries (SELECT ... LIKE '%xy%') What I want to do is to create rel...

How to get data from database but from the exact row?

Hey Guys, I am using mysql. Now I want to write that query in which i will give mysql the row number then mysql will data from that column no only. means.. if i have twenty rows in database but i want to get only 10th no of rows data? so how to write that kind query? ...

Should I use two queries, or is there a way to JOIN this in MySQL/PHP?

Morning y'all! Basically, I'm using a table to store my main data - called 'Code' - a table called 'Tags' to store the tags for each code entry, and a table called 'code_tags' to intersect it. There's also a table called 'users' which stores information about the users who submitted each bit of code. On my homepage, I want 5 results r...

Deleting certain rows based on the count of a column in the mysql database

Hi, I have a very large database with about 120 Million records in one table.I have clean up the data in this table first before I divide it into several tables(possibly normalizing it). The columns of this table is as follows: "id(Primary Key), userId, Url, Tag " . This is basically a subset of the dataset from delicious website. As I s...