mysql

multiple select in one statement

This is hard to explain but i need to - select id from customers - foreach customer find the order with that id -- foreach order (for that customer) --- select product.name, product.max on order.productId=product.id I dont know how to pull this data. I would need multiple separate selects? can i write a long single sql statement (';' ...

mysql SELECT COUNT(*) ... GROUP BY ... not returning rows where the count is zero

Hello. Here is my query: SELECT student_id, section, count( * ) as total FROM raw_data r WHERE response = 1 GROUP BY student_id, section There are 4 sections on the test, each with a different number of questions. I want to know, for each student, and each section, how many questions they answered correctly (response=1). However, with...

Is the concept of abstraction relevant to tables in MySQL? If so, how can I do it?

I want to store data on various engines in a MySQL database, which includes both piston and rotary engines. In OO languages, I can create and extend an Engine superclass to obtain PistonEngine and RotaryEngine subclasses. The PistonEngine subclass would contain properties such as CylinderNo, PistonBore and PistonStroke. The RotaryEn...

Why is there extra space being added whenever I execute PHP code that updates any text field in a MYSQL database?

Hello, I am building a blog site and am having trouble with updating fields in my MYSQL database. Whenever I hit the update button on the form that uses PHP, it adds extra space before the text string in the MYSQL text field. Here is the PHP code: //UPDATE TEXT $updated_text = $_POST['text'.$the_post['ID'][$n]]; if ($updated_t...

Dilemma regarding database design for a discussion board using MySQL

In a discussion board there are generally many more posts, than topic headlines (titles). IMO it would be a cleaner practice to treat headlines as posts, but wouldn't it put serious penalty on performance if MySQL searches through every post to catch the headlines? Making a seperate table for the headlines would certainly increase the p...

MySQL design question - which is better, long tables or multiple databases?

So I have an interesting problem that's been the fruit of lots of good discussion in my group at work. We have some scientific software producing SQLlite files, and this software is basically a black box. We don't control its table designs, formats, etc. It's entirely conceivable that this black box's output could change, and our design...

mysql last month date statement

I have a DATE, how do i write a where that checks for last month until now (either the first of or just todays day -1month). ...

Hibernate code generation in eclipse isnt generating all my tables (weird)

Im just getting my head around java and eclipse. Im trying to use the hibernate plugin to generate my java classes. Yesterday I created a table in mysql and managed to (after a lot of messing around) get it to generate the code for this table. Today I have added some tables to the database and tried to generate code for them. Now I ca...

Where do I look for the database script generated from JPA Entities?

Hi, I am having a few errors like the following when I deploy my JPA entities into JBoss. 18:34:53,462 ERROR [SchemaExport] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Group (id)' at line 1 18:34:53,684 ERROR [SchemaExport] Unsuccessful: alter t...

what this error means? [Erlang, mochiweb, MySQL]

Hi there, I made a comet chat server with Erlang and Mochiweb. And I run the "./start-dev.sh" to start the server. But after about 1 month I got the following error: =ERROR REPORT==== 26-Sep-2009::09:21:06 === {mochiweb_socket_server,235, {child_error, {badmatch, {error, [70,97,105,108,101,100,32...

compile basic mysql c code with xcode

Anyone manage to write code that uses the mysqlclient library? I can get compiling working but not linking :( XCode produces the following output: Build TestMysql of project TestMysql with configuration Debug Ld build/Debug/TestMysql normal x86_64 cd /Users/jacob/Documents/cocoa/TestMysql setenv MACOSX_DEPLOYMENT_TARGET 10.5 /Develope...

MySQL: Unknown column in where clause error

I have a PHP script and for some reason mysql keeps treating the value to select/insert as a column. Here is an example of my sql query: $query = mysql_query("SELECT * FROM tutorial.users WHERE (uname=`".mysql_real_escape_string($username)."`)") or die(mysql_error()); That turns into: SELECT * FROM tutorial.users WHERE (uname=`test`)...

Comparing rows in table for differences between fields

I have a table (client) with 20+ columns that is mostly historical data. Something like: id|clientID|field1|field2|etc...|updateDate If my data looks like this: 10|12|A|A|...|2009-03-01 11|12|A|B|...|2009-04-01 19|12|C|B|...|2009-05-01 21|14|X|Y|...|2009-06-11 27|14|X|Z|...|2009-07-01 Is there an easy way ...

Call to undefined function mysql_connect()

In my googling it seems as though the mysql functions are not included in the base download of php5? I am seeing instructions for Linux, but I am running on a WAMP. Can anyone help me get these enabled? I am fairly sure I have mysql/php/apache configured properly now (certainly apache and php are working), but I get an error when I try ...

MySQL: Correctness in the face of heavy concurrency (SELECT ... FOR UPDATE)

Hi everybody, I'm running a website where users can adopt virtual pets. There is a per-user adoption limit on each pet. So, for example, you can adopt one of our pets a maximum of 10 times. At the moment we do something like this: CREATE TABLE `num_adopted` ( `petid` int(11) NOT NULL, `userid` int(11) NOT NULL, `total` int(11) un...

MySQL to return only last date / time record

We have a database that stores vehicle's gps position, date, time, vehicle identification, lat, long, speed, etc., every minute. The following select pulls each vehicle position and info, but the problem is that returns the first record, and I need the last record (current position), based on date (datagps.Fecha) and time (datagps.Hora)...

Dependent insertion of data into MySql table

I have 2 tables: user_tb.username user_tb.point review_tb.username review_tb.review I am coding with PHP(CodeIgniter). So I am trying to insert data into review_tb with the review the user had submitted and if that is a success, i will award the user with some points. Well this look like a very simple process. We will first insert th...

MySQL on Windows: Using SHA-2

Hello guys, I need to store sensitive data (usernames and passwords) and wanted to do things the right way (storing SHA-256 hashes of (password).(large random number) instead of cleartext passwords. This needs to be done using MySQL and, as far as i know, only MySQL 6 plans to incorporate SHA-2, hence i assume some kind of external appl...

Storing Tags in Database. Store tag once or many times?

I would like a bit more clarification on the toxi method of storing tags in a database – mentioned elsewhere on SO. The database schema is: Table: Item Columns: ItemID, Title, Content Table: Tag Columns: TagID, Title Table: ItemTag Columns: ItemID, TagID This is probably a stupid question (but I don't know the answer)... Should eac...

how to implement search for 2 different table data?

Using mysql and PHP I am using MATCH AGAINST clauses already. It is working fine against individual tables. Like if i want to search in shops table. No problem. What i want is to be able to search and DISPLAY results from different tables in a single result page. Eg if i type "chocolate clothes" i may get 4 results as follows: Sho...