mysql

Is this a suitable SQL schema for a site similar to StackOverflow?

I'm trying to create a site similar to StackOverflow. I just created a SQL schema today and need some feedback on what needs improvement. Here's a link to the schema (Requires MySQL Workbench) Here's a picture of the schema: Is there anything I can do to improve this schema? ...

cakePHP performance

hello friends, when i am hosting cakePHP and database is in different servers it takes too much time to respond but when both are in the same server itz performance is good is there any way to tune performance of cakePHP keeping database in different server the performance of database server is good i already tested another applicatio...

Problems designing / mapping dynamic data-entry value store

This is tightly related to a previous question I had. It's a row/column view where columns can be several different datatypes and each row might have a value for each column. I thought of having the following structure case field fieldType bool_values fieldId caseId value int_values fieldId caseId value ...

Recursive categories with a single query?

Hi there, I have a website with articles and sections, each sections can have a parent section, as much as they like for example: subject 1 -subject 2 --subject 3 -subject 4 --subject 5 --subject 6 ---subject 7 subject 8 subject 9 etc.. Now, i want to fetch them recursively, what is the most efficient way to do it via php and...

How to change the structure of a name field in mysql

Hi, I have the following problem. My table, say tab1, has name column as follows "LastName, FirstName". I want to make it so that the column becomes "FirstName LastName". Any ideas on how this is to be done? Note that there is no comma present, but i guess that can be easily removed once I figure out how to actually flip the first and ...

Simple MySQL question - MAX(field) ?

Hi just a simple question I know how to get the most recurring field from a table SELECT MAX(field) FROM table But how do I get the Second most recurring field from a table? Thank you. ...

Getting syntax error

I am getting syntax error in following statement...but I want to use it by USING STATEMENT. SELECT user_job.level1,user_job.tab_level,job.money_gain,job.exp_gain,job.energy_required,job.name,job_tem.no LEFT JOIN job USING(job_id) AND LEFT JOIN job_item USING(job_id) job_id is common in all the tables. ...

mysql error IS NOT proper use?

Hello I am bit of a newb when it comes to mysql I running a query and I am getting the following error, to my knowledge the query syntax is correct (obvioulsy not though) this is the error that I am getting, #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right...

Multiple count values on one column

Hi All, I want to query my database and get counts of the occurences of each id in a single column. The two tables related to this query are: categories: this table has a list of all categories with titles id | title ---------- 1 | project 2 | tech 3 | other category_news: this table is used to assign news items to categories, ea...

mysql date query

my table look like this Name Created ganesh 2010-06-25 10:54:54 vasant 2010-06-25 11:54:54 charlie 2010-06-25 12:54:54 sam 2010-06-25 13:54:54 vasamtj 2010-06-25 01:54:54 jack 2010-06-25 02:54:54 nima 2010-06-24 10:54:54 kumar 2010-06-24 10:54:54 jay 2010-06-24 10:54:54 paala 2010-06-23 ...

Codeigniter - multiple file upload ->insert into db - having problems!

Hi, I am using Codeigniter and I am trying to upload multiple files into a database. The files are seperate fields not multiple files for one field. I followed this post -http://codeigniter.com/forums/viewthread/110130/P0/ I almost have it working but I am having some trouble with adding the uploaded data into my db. Here is my Contr...

Retrieve maximum value from a table containing duplicate values according to a condition

Hi, I have a table tbl_usertests from which i want to retrieve the user who have maximum testscore for each test. Note: User here means usertestid which is unique. Its colums are: pk_usertestid attemptdate uploaddate fk_tbl_tests_testid fk_tbl_users_userid testscore totalquestionsnotattempted totalquestionscorre...

using LIKE operator in mysql

I want search companies from my company table when i give company name...here am using like operator eg: saravana stores it gives the result saravana stores texttiles,saravana stores thanga maligai,etc(which is contained with saravana stroes...coz of using LIKE operator) Now my problem is when i give lcd projectors in the companyname...

UK postcodes -> co-ordinates : possible purely in php?

Say have a mysql database with these cols: | house | postcode | lat | lon | The postcode's are complete but lat and lon are empty. I want to write/find a script that i can run once to complete the table. Is this possible purely in php (any tips?) or do i need to use a javascript api and $_POST to a php file? ...

A join query for three tables!! for the given condtion below

I have three tables Orders table and customers table and orderstatus table, both order and customer table have customerid as common field,and order and orderstatus have order_status_id as common field customer table have firstname, lastname, phone and email address fields. Now if I have to search/select the orders according to custome...

MySQL: Insert if foreign key exists

I have an excel sheet with around 2.000 rows that i want to insert into my database. The problem is that the table i want to insert the 2.000 rows into has a field that references to a foreign key in another table. Unfortunately a lot of queries fail, since the given foreign key does NOT exist. I know that I can ignore foreign key chec...

ISQL app migration option, Progress - "OpenEdge" Personal RDBMS with 4GL(ABL) development system?

Back in 1985, when I was a Product Planner for AT&T-IS Labs (Unix Product Management), one of my primary duties was to evaluate emerging 4GL/RDBMS products, submit product plans and recommend whether AT&T should pursue a co-marketing or co-labeling agreement with the vendor or just test/certify it as compatible on our 3B-series systems. ...

Mysql Select Query problem

I am using this below given query: SELECT o.orders_id, o.customers_name, o.customers_id, o.payment_method, o.google_order_id, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name FROM orders o, orders_status s, customers c WHERE o.customers_id = c.customers_id AND o.orders_status = s.orders_status_id AND...

Select statement does not see characters after @ in a row value

Hi! Very weird problem. Saving email addresses to my database, but when I query those emailaddresses it shows up as name@ instead of [email protected]. I suspect some internal filtering going on but have no idea how to solve it. CREATE TABLE `bredeschool`.`users` ( `userID` smallint(6) NOT NULL AUTO_INCREMENT, `gebruikersnaam` varc...

Large scale MySQL changes to active sites

Hello Just some pointers here. I am making fairly extensive modifications to a site, including the MySQL database. My plan is to do everything on my development server, export the new MySQL structure for the db and import it onto the clients server. Basically I need to know that performing a structure only import will not overwrite/de...