I have this table CREATE OR REPLACE TABLE hits (ip bigint, page VARCHAR(256), agent VARCHAR(1000), date datetime) and I want to calculate googlebot visit frequency for every page. ... WHERE agent like '%Googlebot%' group by page ...
I'm developing an image gallery for one of my customers, they want a very minimal, simple and easy user experience. The image gallery must contain a title and description of each image, we have created a app to upload the images and set titles and descriptions this is stored in a mySQL database. We're developing this site with php. Befo...
I have the following MySQL query and tables from which I am querying: SELECT `Song`.`id`, `Song`.`file_name`, `User`.`first_name`, `Vote`.`value`, Sum(`Vote`.`value`) AS score FROM `songs` AS `Song` LEFT JOIN votes AS `Vote` ON (`Song`.`id`=`Vote`.`song_id`) LEFT JOIN `users` AS `User` ON (`Song`.`user_id` = `User`.`id`) GROUP BY `...
I need to analyze a few million geocoded records, each of which will have latitude and longitude. These records include data of at least three different types, and I will be trying to see if each set influences the other. What database is best for the underlying data store for all this data? Here's my desires: I'm familiar with the DB...
I'm using PHP and MySQL. MySQL will not update the last cell in my table. I can't figure out why. Basically, I do 10 INSERTs with a foreach() loop on an array. On each loop I use the following code: $sql = "INSERT INTO table1 (name, address, phone, date_time, process_started, process_ended) VALUES ('$name', '$address', '$phone...
hello, I am trying to run an application developed on another machine where it was perfectly running, so it should have something to do with the configurations on the machine I am trying to run it on now. I'm using netbeans 6.9, tomcat 6.0.26 and maven. When I try to run it, it gives me the following error: Sep 19, 2010 12:51:02 AM or...
Basic question about a good way of organizing data in a database. Let's say I have a search site for multiple stores. Each store has a number of products. What is the best way of organizing price and inventory data? I see two ways of doing this: a table for every store , with the first column or two identifying a product, a column for ...
I am using MySQL on wamp server. It takes 0.6 secs to establish a connection with mysql database, however when I use persistence connection it takes 0.035 secs. I read people comments on php manual and found that mysql_pconnect runs out of connections soon. Any suggestion how to improve connectivity time? ...
Consider this query: SELECT table1.id, table1.review, table1.time, table2.author, table2.title FROM table1, table2 WHERE table1.id = table2.id AND table1.reviewer = '{$username}' ORDER BY table1.id I'm using the above quite a lot around my site's code. I find that adding the table prefixes etc. befo...
Im creating a simple message board with php/mysql. Users enter their name and a message, javascript displays the message immediately, and php/mysql stores it in a database. When the page loads, it should display all the messages in the database in a formatted list. However, it seems that my php is displaying only some of the messages ar...
Anyone know of a PHP script that will clone an entire MySQL database to another on another server? As a sort of backup? ...
I'm looking at MySQL stored procedures and function. What is the real difference ? They seem to be similar, but a function have more limitations. I'm likely wrong, but it seems a stored procedure can do everything and more an stored function can. Why/when would I use a procedure vs a function ? ...
Using MySQL and PHP, I have a simple ranking table with 2 columns user and score. Once a week, I re-run the ranking script and it computes scores for each user. many users have new scores some do not some times there are new users to add to the table What's the best way to approach this with MySQL? Does update work if I need to add ...
I'm wondering what the rules are for mysql type conversions are. e.g. select foo/2 from table seems to yield a decimal if the foo column is an int. select sum(foo) from table gives the sum(foo) column back as a double if foo is a float. select i*i from table gives a bigint if i is an int. What are the rules for the type conversion her...
i have two tables both have columns of fname and lname how would i write the query to check and see if one of the tables is missing a record that doesnt exist in the other table? here are the tables and its columns tbl_client -fname -lname tbl_lease -fname -lname i need to keep checking the tbl_lease to see if the records match the...
I would like to convert an existing Derby database to MySQL. ...
I'm working with your standard three table tag setup item_table item_id item_name tag_table tag_id tag_name tag_map map_id tag_id item_id How this works should be obvious to anyone who has used a similar scheme for their tagging architecture, now, using this scheme I have a scenario in which I need to print the results of the...
If the same sql run many times from different sessions, will mysql parse the same sql many times? In oracle/sql server, the plan for a sql is cached and can be reused. Since it is told that parse and creating sql plan is costly, if mysql doesn't cache them, will it be a problem to parse it many time which could potentially cost a lot? ...
This related to my other question. I have this table CREATE OR REPLACE TABLE hits (ip bigint, page VARCHAR(256), agent VARCHAR(1000), date datetime) and I want to calculate average time between googlebot visit for every page. ... WHERE agent like '%Googlebot%' group by page order by date Something ...
I'm trying to make a simple php shopping cart. I don't have any idea how to make one myself so I tried searching and found this one the simplest to understand for a beginner like me:http://jameshamilton.eu/content/simple-php-shopping-cart-tutorial The only problem is I get the error mentioned on the title on this line of code: $_SESSIO...