mysql

How to pick table in query based on subtype?

I have 4 tables, Transactions, Orders - SuperType, and then Site_Orders and Product_Orders SubType [Transactions] id PK Orders_id [FK Orders.orders_id] [Orders] Orders_id PK Orders_type [Site_Orders] Orders_id [FK Orders.orders_id] == other data == [Product_Orders] Orders_id [FK Orders.orders_id] == other data == My question is, h...

MySQL: Delete singular entires

If I have this table: +------+-------+---------------+--------+-----------------+------------+-----------+----------------+------+------+--------+------------+------------+ | type | class | username | userid | userip | usermobile | useremail | daysleft| pin1 | pin2 | pin3 | active | schoolname | schoolsite | +------+------...

Which foreign key to use in a many-to-one table

Suppose I have a table Books with columns Book_ID (Primary Key), ISBN (Unique), and several others such as Title/author/etc. I have another table Sales, with primary key Sale_ID, a foreign key to link to Books, and other fields with info on Sales. Sales only exist for books with ISBNs. Is it better database design to have Book_ID or I...

How do I turn the following INSERT into a prepared statement so that its secure?

Hi everyone, I'm trying to get my head around prepared statements. Basically, I would do a insert like so normally: $sql = ' INSERT INTO customers (customer_first, customer_last, customer_address, customer_email) VALUES (' . mysql_real_escape_string($_POST['customer_first']) . ', ' . mysql_real_escape_strin...

Does instant search take a lot of computing power?

I was wondering whether the instant search using jQuery would cause a massive load on a database (MySQL). I know there are a lot of factors to consider, but for argument's sake let's say you have 30,000 records to flick through? ...

Distinct mixing rows values?

I'm getting the result with mixed dates values, instead get the last revision for each title i get them mixed. I'm using MySQL. The general idea is retireve all rows for each entry, the last revision of each entry. My current sql query: SELECT DISTINCT w.owner_id, w.date, w.title, MAX(w.revis...

PHP/MySQL Security Checklist: The definitive practical guide

After seeing this awesome guide on Stack and needing a practical PHP/MySQL security checklist in-house, I have decided to pay homage to the original guide. What I need is a practical security checklist for PHP and MySQL. The contents here can function as the checklist, while the answers should function as the guides. By practical I mea...

How do you create a simple function to query the database? Mine isn't working!

I want to make a simple function that I can call on to query my database. I pass the "where" part of the query to it and in the code below, my $q variable is correct. However, what should I then do to be able to use the data? I'm so confused at how to do something I'm sure is extremely easy. Any help is greatly appreciated! function get...

mysql grouping question

Hey guys, quick question. I have a table of messages. There are two types of messages: Messages with a unique group id autonomous messages Messages with the same group id as at least one other message - these I want to group together when they are presented. The grouped messages are messages with replies to them. What I want to do...

How to avoid the same record inserted twice in MongoDB (using Mongoid) or ActiveRecord (Rails using MySQL)?

For example, if we are doing Analytics recording the page_type, item_id, date, pageviews, timeOnPage. It seems that they are several ways to avoid it. Is there an automatic way? create index on the fields that uniquely identify the record, for example [page_type, item_id, date] and make the index unique, so that when adding the sam...

close to messing up my db on heroku: mysql error on db:pull...

I think I am close to mess up my db on heroku I am trying to download the remote DB on heroku by heroku db:pull I get the following message: news_items: 100% |==========================================| Time: 00:00:01 /usr/lib/ruby/gems/1.8/gems/sequel-3.13.0/lib/sequel/adapters/mysql.rb:169:in `query': Mysql::Error: Duplicate entr...

How to get intro record from each group

I have a following table id group name 1 2 dodo 2 1 sdf 3 2 sd 4 3 dfs 5 3 fda .... and i want to get intro record from each group like following id group name ... 1 sdf 2 dodo 3 dfs ... ...

How to determine which images of the earth's surface overlap with an arbitrary given image?

Suppose you are given an image of the earth's surface and that it is stored in the database as a polygon defined by the latitude/longitude pairs of its corners. Now suppose there are millions of images covering the earth's surface that are also stored similarly. What is a good strategy to find those images which intersect with your give...

How do I make my own true / false return function?

Hi there, I want to make my own function which performs in a similar manner to the following actual code.. ie if(mysql_num_rows($res) == FALSE) { // DO SOMETHING BECAUSE THERE ARE NO RESULTS } In my code, i'm repeating an SQL statement a few times around the place, and if there are results, then I go ahead and do stuff. What I'd...

SQL - How to find index

Hello, I am using MySQL 5.0. I have table with following structure and data. CREATE TABLE `test` ( `text1` varchar(100) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `test` (`text1`) VALUES ('ABC'), ('PQR'), ('XYZ'), ('LMN'); I want to display following output. Index   Text 1          ABC 2          L...

Java MySQL Programming

I am accessing a MySQL table that has over 1 million or more Records. I am using My SQL query browser which is unable to grab all the records and it break the connection in the middle. Now I have to write a Java Program which access that particular table without being broken in the middle as this table will be modified and accessed freq...

php mysql query display one instance of a value in field

I have this query that searches my database for accommodation that has a type that is equal to e.g. "Hotel" it then outputs the results in an list displaying the location of the hotels. Basically because there are lets say 4 hotels in Windermere, Windermere is coming up 4 times. Is there anyway I can tell it to only display one instance ...

Is there a limitation on the number of tables a PostgreSQL database can have?

I have created a database in PostgreSQL, let's call it testdb. I have a generic set of tables inside this database, xxx_table_one, xxx_table_two and xxx_table_three. Now, I have Python code where I want to dynamically create and remove "sets" of these 3 tables to my database with a unique identifier in the table name distinguishing dif...

PHP: Making this grabbing from db easier/simpler way?

Is there any method i could do this easier: $query = mysql_query("SELECT full_name FROM users WHERE id = '$show[uID]'"); $row = mysql_fetch_array($query); echo $row["full_name"] . " "; as i only need to grab the full_name, then i make a var for the fetch_array and so, is there any way to make this simpler and echo? There was something...

Auto Generate Field Names from table PHP

Hi There! There is a problem of automatically retrieving field names from a MySQL table. If possible could the name be placed in this format along with the dynamically created text box? : The codes that I have created so far are located below: <?php include "db_connect.php"; $name = mysql_query("SELECT * from users"); $property = m...