mysql

How should I index this MySQL db?

I am creating a site that allows users to share specific pages to the public. It is similar to how jsbin.com let's you create a public url of the script you're working on. The basic MySQL table I am working with now is: CREATE TABLE IF NOT EXISTS `lists` ( `id` int(10) NOT NULL AUTO_INCREMENT, `hash` varchar(6) NOT NULL, `created_...

How to improve MySQL INSERT and UPDATE performance?

Performance of INSERT and UPDATE statements in our database seems to be degrading and causing poor performance in our web app. Tables are InnoDB and the application uses transactions. Are there any easy tweaks that I can make to speed things up? I think we might be seeing some locking issues, how can I find out? ...

Dependent dropdowns or similar, using mootools, php and MySQL

I need to find some way of displaying a drop-down menu that depending on what is chosen will display a second tier of results in another drop-down. All data will be stored in database. Any other similar techniques are welcome. I am currently using mootools, with php and Mysql. Any help or thoughts welcome on this one... ...

ORDER BY condition

I would like to retrieve an ordered query result, but I need to have some specific row(s) to be in front of the list. Something like here on Stack Overflow, in the list of answers the right answer is always the first one. Assumed I need to have the rows with IDs 1,2,3 to be the head, the rest sorted by a date field, is it possible to do...

MySQL: Convert INT to DATETIME

I have a UNIX-type timestamp stored in an INT column in MySQL. What is the proper way to retrieve this as a MySQL DATETIME? (I found the answer when re-scanning the MySQL Date functions, but didn't see the answer on SO. Figured it should be here.) ...

How to update a mysql field when a checkbox status changes? using jquery (ajax), php and mysql

Hi guys, I'm new to jquery and ajax and I'm having trouble doing this... I have a php webpage that builds a table with values from a mysql table. The rows are "messages" and the last field of the row is a boolean field "read" that states if the message has been read or not. Building the table, I transformed the boolean value to a chec...

Using one variable to look up info in two tables

Hello, The code below works nicely. Each "title" in the MySQL table "submission" is printed in reverse chronological order alongside its corresponding "loginid." I have another MySQL table ("login") with the fields "loginid" and "username." So each row has a "loginid" and a "username." The "loginid" in these two MySQL tables "subm...

Will using the php function "insert into" create new columns if they aren't already defined, or return an error?

I'd like to use a foreach loop to insert a _POST associative array into a database using INSERT INTO. What I need to know, is if my _POST data contains keys that don't match the columns of my database, will INSERT INTO simply create these columns in the database? if not, will it return an error and not update my database at all? or wil...

MySQL count total number of rows

I have a complicated MySQL query which takes a lot of time, selecting from a table with more than 150k rows and several JOINS and subqueries. I am limiting the amount of results, but I need to know the total amount of rows. Is there a way not to repeat the query with COUNT(*) as the field to get them? (repeating the query almost doubles...

Best way to store a PHP App's Settings?

What is the best approach to storing a group of global settings for a custom PHP application? I am working on a personal project (first major one really), and need a method of storing key-value pairs for recording overall settings for the application. Things to store as... Website's Global Name. Theme (just a variable, or path to them...

How can I beef up this security with cross domain user sessions?

I have a Mothership domain, and if you log into it, a token is created in the database for your login with user Id, created datetime, expiry datetime and user agent. The expiry is set to an hour after the token was made (created when the user logins). To be able to login from Mothership to Scoutship, I append a query string like so <a ...

What is the easiest and most reliable MySQL data provider for .NET Apps?

There are a couple of data providers to interface with MySQL databases from .NET Apps. Which in your experience have you found the easiest to work with and most reliable? MySQL .NET Connector MyODBC dotConnect for MySQL other I've never done this before so I want to walk down a path that's tried and tested. ...

How to update a database record and still keep the old values in MySQL?

I want to update a column by adding a new value alongside the old ones. So if column "fruits" has a value of "apples" and I run my query it should then have a value of "apples, oranges". Right now when I do an update statement" UPDATE tableName SET fruits='oranges' WHERE id=1; It just overwrites apples with oranges. How can I get it...

Binary data under 20KB : BLOB or filesystem ?

I'm developing web app that user can save his/her work to server. The data will be saved is in variable-length binary form, it's size mostly under 20KB (sometimes little bigger < 100KB). The data will often be changed / updated. I have 2 options saving the data in server: as BLOB (mysql) or as filesystem with reference to its filepath s...

Find an invalid record ID in a list of potential ID's

Given a list of potential ID's is there a quick way using a single MYSQL query to work out which, if any, of the ID's do not have an associated record in the database. e.g. if the list is 1,3,4 and records exist for 1 and 4 but not 3 is there a query that will return 3 from the list. This needs to be applied to a database containing 15...

Creating User Pages

Hello, I have a MySQL table designated for storing usernames, passwords, etc. In this table, one field is called "username." I would like to create a user page for each user that gets entered into the database. I would like the URL for any given user page to be http://foo.com/member/username. How could I do this? Thanks in advance,...

I would like to somehow sort this 'object_id' array so that i can get the relational values from the database

I have some information in my database like 'author' 'book', etc that are all related to a 'note_id', which is related to a 'user'. I query the DB for the note id. and i get thus back: *note_id assoc array* Array ( [0] => 32 ) Array ( [0] => 31 ) Array ( [0] => 33 ) Array ( [0] => 34 ) Array ( [0] => 35 ) Array ( [0] => 36 ) Array ( [0...

mysql query to sort some results but join data may or not be present

SELECT PLD_LINK.ID, PLD_LINK.TITLE, PLD_LINK.URL, PLD_CATEGORY.TITLE, TLL_SORT_STATUS.status, PLD_LINK_COMMENT.DATE_ADDED FROM PLD_LINK, PLD_CATEGORY, TLL_SORT_STATUS, PLD_LINK_COMMENT WHERE PLD_LINK.CATEGORY_ID = PLD_CATEGORY.ID AND PLD_LINK.ID = TLL_SORT_STATUS.link_id AND PLD_LINK.PAGERANK BETWEEN -1 AND 2 ...

Random numbers in java and creation of tables in mysql

Hi all, I have the following table select * from consumer6; Service_ID | Service_Type | consumer_feedback | 93 Computing 1 93 Computing 1 93 Computing 1 93 Computing ...

Am i doing this wrong. Convert array to array value.

I am trying to convert an array to its value. I am getting this: Warning: array_values() expects parameter 1 to be array, string given in C:\xampp\htdocs\ReadNotes\mynote.php on line 42 Array ( [0] => 31 ) Warning: array_values() expects parameter 1 to be array, string given in C:\xampp\htdocs\ReadNotes\mynote.php on line 42 Array ( ...