mysql-query

Is there a limit to MySQL queries?

I am trying to insert 10,000+ fields into my database? Is there a limit? $sql = 'INSERT INTO `_'.$test.'` (`user`, `pass`) VALUES ' . preg_replace($test, $replace, $final_check) . ';'; mysql_query($sql) or die(mysql_error()); Every time I try to insert the data, it fails. ...

timediff is greater than 2 seconds

I am trying to write a query where I can eliminate a timediff of less than 2 minutes. I have tried variations on the following which returns no results timediff(sessions.producer_on,sessions.producer_off)>'00:02:00' the timediff without the > works fine and returns all results - I am having difficulty with the >00:02:00 condition. C...

Is there a faster way to count records on a join...

This query gets me the profiles with the most evidence records when the profile is the actor. Is there any way to make it faster in a mysql query? SELECT profiles.*, count(*) AS counted_profiles FROM `profiles` INNER JOIN (SELECT ev.actor_id FROM evidences AS ev WHERE ev.actor_type = 'Profile') AS ev2 ON ev2....

Getting table name in a trigger in MySQL

Hi guys, is it possible to get table name in a MySQL trigger ? i use trigger to log insert actions on some tables in MySQl and i need the name of the table where the trigger is placed at. Thanx before :) ...

SQL Query For Total Points Within Radius of a Location

I have a database table of all zipcodes in the US that includes city,state,latitude & longitude for each zipcode. I also have a database table of points that each have a latitude & longitude associated with them. I'd like to be able to use 1 MySQL query to provide me with a list of all unique city/state combinations from the zipcodes tab...

SQL Query Data Collect

I have a SQL server table that has a list of Usernames and a list of points ranging from 1 to 10,000. I want to display the number of points in my VB.NET program from the username given. For example, if I type in "john" in the first box the program will give me a message box saying whatever amount of points "john" has. I'm not really s...

How can i do this query in sql? count number of category

my table is: name | category | name01 category01 name02 category01 name03 category02 name04 category02 name05 category02 name06 category02 name07 category02 ..... I would like to count for each category the number of name the output i woulk like is something like this (by the example above): category | num category01 2 cate...

How to loop through a mysql result set.

What are some different ways to loop through a mysql result set? I'm new to PHP and MySQL so I'm looking for simple ways to loop through and an explanation as to how the provided code works. ...

Dislay MySQL Data in a Label - Vb.Net

Hey guys, I have some code here that connects to my Mysql database. It checks your username and password and if your login was successful and it retrieves the phone number under that account. How would the SQL Query look like for retrieving the phone number once connected? Then how would I be able to display that phone number on a label...

MySQL Query Glitch

I have this MySQL Query glitch in my VB.NET program. Here is the query in VB.NET: "SELECT Points FROM user WHERE Username = '" & UsernameText.Text & "';" It will select the points column from the user table from the Username they entered. When I deploy this it will set the value for points to 0 on their account. What's going wrong? I...

How do I convert a MySQL function result to tinyint(1)

Here's the problem. In MySQL's Connector/NET a TINYINT(1) field properly translates back and forth into a .NET bool value. If I select from a table with a TINYINT(1) column, everything is golden. However, when you use built-in MySQL v5.0 functions like: SELECT (3 BETWEEN 2 AND 4) AS oddly_not_boolean; The actual return type from the d...

I need help with a MySQL query.

Ok I have a table in my mysql query browser like shown below: NAME: Jobs: Kate Contractor John Janitor Bob Writer Don Waitress Let's say I want replace the job of Kate to artist. how would I do this as a MySQL Query. I know it involves the INSERT INTO thingy, but I'm not really sure. ...

How to Represent Rules using a MySQL Table?

I have a table of 100,000s of USERS (name, age, gender, phone, company, street, city, state, country, zipcode, etc). I also have a table of thousands of PROMOTIONS which are offered to users. Now, for each promotion I need to add a rule which defines which subset of users it applies to. For example, a rule might be: All users which hav...

SQL "GROUP BY" issue

Hi, I'm designing a shopping cart. To circumvent the problem of old invoices showing inaccurate pricing after a product's price gets changed, I moved the price field from the Product table into a ProductPrice table that consists of 3 fields, pid, date and price. pid and date form the primary key for the table. Here's an example of what ...

MySQL: Getting data for histogram plot?

Is there a way to specify bin sizes in MySQL? Right now, I am trying the following SQL query: select total, count(total) from faults GROUP BY total; The data that is being generated is good enough but there are just too many rows. What I need is a way to group the data into predefined bins. I can do this from a scripting language, but...

MYSQL fetch 10 posts, each w/ vote count, sorted by vote count, limited by where clause on posts

I want to fetch a set of Posts w/ vote count listed, sorted by vote count (e.g.) Post 1 - Post Body blah blah - Votes: 500 Post 2 - Post Body blah blah - Votes: 400 Post 3 - Post Body blah blah - Votes: 300 Post 4 - Post Body blah blah - Votes: 200 I have 2 tables: Posts - columns - id, body, is_hidden Votes - columns - id, post_i...

sql join query question (mysql)

I have the following query, its only returning two records when i have three in the database- where am I going wrong? SELECT items.id, items.link, items.title, items.image, lists.user FROM lists, items WHERE lists.user = '506161637' AND lists.item = items.id GROUP BY lists.dated DESC LIMIT 0, 10000; The data set of a join table is b...

collating stats into time chunks

I'm trying to work out an SQL statement for MySQL. I have a series of stats for a range of servers that are reported on a half hourly basis. I have a stats table with columns similar to this: server varchar(64), time datetime, bytesIn int, bytesOut int Now, I want to generate a series of reports that collate all of the bytesIn and by...

Inconsistent MySQL DATEDIFF behavior

When I use this query: SELECT COUNT(*) FROM `my_table` WHERE DATEDIFF(NOW(), updated) > 2 MySQL executes the query with no errors and I get the count of rows that have not been updated within the last 2 days. However, if I change the query like this: SELECT * FROM `my_table` WHERE DATEDIFF(NOW(), updated) > 2 I get the following e...

trouble creating a sql statement select statement reference same table twice

Ok, here is setup. Note the code below is SIMPLIFIED version. PHP 5.2 ENGINE: MyISAM table quotes q table users u FROM quotes q LEFT JOIN users u ON q.qid = u.uid Soo... the quotes table references a user (from users table) (the owner of quote) but quotes table ALSO has a field called createdby (the user who created the quot...