mysql

Use Single Row Query with MySQL and PHP

Hey there say I have this $result = mysql_query('SELECT views FROM post ORDER BY views ASC'); and I want to use the value at index 30 I assumed I would use mysql_data_seek($result, 30); $useableResult = mysql_fetch_row($result); echo $useableResult . '<br/>'; But that is returning my whole table What have I got wrong? Edit: Woo...

how to import a very large query over phpmyadmin?

if my host does not allow me to upload a file directly to my mysql folder and i can only do so throught phpmyadmin? are there any alternatives aside from its native import feature so that my connection would not time out while uploading a query that's around 8mb? ...

Persisting Money into the database. Design decision

I need a table to store the state of a financial transaction. The state of this transaction can be roughly modelled by this class. class FinancialTransaction { Integer txId, Money oldLimit, Money newLimit, Money oldBalance, Money newBalance, Date txDate } class Money { Currency curr, BigDecimal amount ...

delete with joining the same table

is it posssible to execute a delete query statement that joins the same table, i've tried various joins (inner, left) but no luck mysql returns error example of what i need: DELETE `a` FROM `t1` AS `a` INNER JOIN `t1` AS `b` USING `some_field_b` WHERE `a`.`some_field_a` = 'value_x' AND `b`.`some_field_a` = 'value_y' ...

Can I use MySql for free on my machine for developing my application?

I have one query related to mysql licensing. I am developing a desktop application for commercial purpose and using mysql community server (5.0) as backend database server. We dont install mysql server and driver from our application installer. If user wants to install our application on his machine then he first needs to install MySql s...

What is the best/fastest MySQL Table Schema for temporary/rotating storage, e.g. for session management?

When it comes to writing custom MySQL database-driven PHP session management for a VERY dynamic website, what is the best (fastest read/write access) structure for your session table? Bad Example (Not Optimized): CREATE TABLE `session` ( `session_id` VARCHAR(32) NOT NULL, `session_data` TEXT NOT NULL, `t_created` DATETIME ...

How do Views work in a DBM ?

Hi, Say that I have two tables like those: Employers (id, name, .... , deptId). Depts(id, deptName, ...). But Those data is not going to be modified so often and I want that a query like this SELECT name, deptName FROM Employers, Depts WHERE deptId = Depts.id AND Employers.id="ID" be as faster as it can. To my head comes t...

SQL: Batch update table based on MIN of another? Probably easy, but stuck.

I think this will be easy but I can't see how to do it! I want to update the Author Table, so that the Earliest field contains the year of the earliest book. I need to use MIN somehow but how to update them all with one query? BookTable (BookID, AuthorFK, Year) 01 34 1943 02 34 1933 03 99 1910 04 62 1990 05 99 1901 AuthorTable (Autho...

How can I tell whether data in table has incorrect encoding?

I have a couple tables that are set to the latin1 character set but I suspect have been erroneously been inserted with some values that are actually encoded using utf8. MySQL makes this a little more complicated because it silently converts everything based on your connection settings. How can I test my hypothesis that there are some u...

Best practices for bit flags in PHP

I'm writng a small application in PHP + MySQL and have come to the point where there is an object that has a couple (8 so far but not expected to increase) of flags associated with it. The flags are pretty much unrelated although there are some combinations that would make no sense. The object represents a row in a DB (has some methods f...

group by query challenge

Hi, I have these tables: customer -------- customer_id int name varchar(255) order ----- order_id int customer_id int discount boolean I can get the number of orders made by each customer with a query like: select c.id, count(o.order_id) from customer c left join order as o using c.customer_id = o.customer_id group by...

Confused how to write this simple UPDATE/INSERT

OK, I'm confused how to write an INSERT/UPDATE for this simple app I'm developing locally to learn more about db interactions. I have an "edit" page, which is populated from the db and lists up to 9 user links (user_id 2 in this case), so something like: <input type="text" name="link1" value="www.yahoo.com"> <input type="text" name="...

ColdFusion and getting data from MySQL

Hi All, I am working on a CF site and need to get data from MySQL tables. I can create the CFQuery fine and check for records returned, but how do I take the records returned and loop through them and get data from specific fields in each row. I can write while if/end if, etc, I just dont recall how to get access to the data. -JAson ...

Schema design for when users can define fields

Greetings stackers, I'm trying to come up with the best database schema for an application that lets users create surveys and present them to the public. There are a bunch of "standard" demographic fields that most surveys (but not all) will include, like First Name, Last Name, etc. And of course users can create an unlimited number of ...

Pulling Data from a Mysql Table Field and putting it into an array

I can find lots of tutorials showing you how to load an array into a database field but can't seem to figure out how to pull each entry in a field into an array as seperate items. Seems simple enough just can't get it to work, any help? ...

MySQL Question - Unique Key Not functioning correctly, or am I misunderstanding?

I'm trying to create a relation where any of four different parts may be included, but any collection of the same parts should be handled as unique. Example: An assignment must have an assigned company, may optionally have an assigned location, workgroup and program. An assignment may not have a workgroup without a location. Let's assu...

Bypassing SET NAMES UTF8 for MySQL

The mysql manual says that SET NAMES 'x' is equivalent to SET character_set_client = x; SET character_set_results=x; SET character_set_connection=x; According to my config file for SERVER variables, I have all three of these set to utf8. However, if I actually do a SHOW VARIABLES %character_set%, it shows the wrong character sets...

How can I make this query easier to write

I have a query that pulls up questions from one table and answers from another. SELECT questions.question, questions.answers, (SELECT COUNT(answer) FROM answers WHERE question_id = 1 AND answer = 1 GROUP BY answer) as ans1, (SELECT COUNT(answer) FROM answers WHERE question_id = 1 AND answer = 2 GROUP BY...

Trying to design/model simple database app

I'm trying to learn more about db interactions which has me developing a local app to get started. Well, basically, what I've done so far has had some mixed results and I've changed so much stuff I'm not even sure what I've change at this point, lol. I'm not quite sure one of my tables was correct, so I've decided to just start that over...

PHP: Holding two matching items in an array of data from MySQL

$story_query = "SELECT table_name, id FROM planning WHERE parent = '$novelnum'"; $story_result = db_query($story_query); while($story_row = db_fetch_array($story_result)) { $taleTable_Name = $story_row['table_name']; $postid[] = $story_row['id']; $q2 = "Select * from $taleTable_Name where approved='Y' order by id"; $bset...