Read instructions from files in MySQL
I have a text file with commands to, say, create a table. How do I execute all the instructions in this file? I'm only acquainted with using the prompt. ...
mysql
Can't create a MySQL query that generates 4 rows for each row in the table it references.
I need to create a MySQL query that generates 4 rows for each row in the table it references. I need some of the information in those rows to repeat and some to be different. In the table each row stands for one day. I need to break the day up in 6 hour increments, hence the four rows for each entry. I need to create one column which for...
PHP & MYSQL: using group by for categories
Hi, My database has the following setup productid | productname | category id and I want to output them like so: category #1 item 1 item 2 item 3 category #2 item 1 item 2 item 3 I used group by the group them together and that works fine, but I want to loop through each group and display the contents of that group. How would I...
What is the best way to go about grouping rows by the same timestamp?
Right due to not getting the right answer last time, I will reword this. I want to create a page where all fixtures from 'tbl_fixtures' are shown. id - compname - home_user - home_team - away_user - away_team - date That is the table setup. The date column is a timestamp. I want to show all fixtures in blocks based on the date column...
In MySQL: How to pass a table name as stored procedure and/or function argument?
For instance, this does not work: DELIMITER // CREATE PROCEDURE countRows(tbl_name VARCHAR(40)) BEGIN SELECT COUNT(*) as ct FROM tbl_name; END // DELIMITER ; CALL countRows('my_table_name'); Produces: ERROR 1146 (42S02): Table 'test.tbl_name' doesn't exist However, this works as expected: SELECT COUNT(*) as ct FROM my_tab...
PHP, MySQL: How to retain and retrieve correct data when dealing with pagination
Hi all, I have a form (that consists of 25+ fields) and the values for these fields, range from a tiny value to a concatenated string. This is like a search tool. Now when the user fills the form and submits the info, he gets to see all the relevant data that matches the criteria in the form. I am displaying 15 records at a time to the ...
MySQL Backup Table if it Exists
I am trying to write a script that will copy all the data in table a to table b if table a exists. Table b is the exact same structure as table a would be if it exists, though it may not. I am able to copy using the following statement: INSERT INTO 'B' SELECT * FROM 'A', but I don't know where to use IF EXISTS, or if I even can to determ...
Help with php/mysql mailer
Hi there! I'm working on a real estate site and need to make notification mailer: when new property is inserted on a site, people who subscribed for notification in that particular country and/or area and/or city and/or particular property operation (rental, selling) will receive a notification on email. One person could subscribe for di...
Whats Wrong with this MySQL Script?
SELECT @cnt := COUNT(table_name) FROM information_schema.tables WHERE table_schema = 'TAA' and table_name = 'Clients'; IF (@cnt > 0) THEN INSERT INTO `ClientsBAK` SELECT * FROM `Clients`; END IF; I get a syntax error at IF(@cnt > 1) THEN error 1064 ...
Retrieve missing dates from database via MySQL
Hi guys, So I have a table where I collect data for the jobs that I do. Each time I create a job I assign it a date. The problem with this is the days I don't have jobs aren't stored in the database therefore when I graph my data I never see the days that I had zero jobs. My current query looks like this: SELECT job_data_date, SUM(job...
How to get the player rank from there stats in php and mysql
I have the following code i am trying to get the play speed rank from mysql. Mysql Table Called Accounts Username | Speed ----------------- Player1 21 Player2 52 Player3 33 Player2 52(Ranked:1) Player3 33(Ranked:2) Player2 21(Ranked:3) $result = mysql_query("SELECT * FROM accounts") or die (mysql_error()); wh...
MySQL Index Structure: multiple or single index?
Hi, I have question about database optimizing, indexing. I have table that called "projects" and i will execute queries like this: Ordering Queries SELECT * FROM projects WHERE active = 1 ORDER BY created SELECT * FROM projects WHERE active = 1 ORDER BY project_deadtime SELECT * FROM projects WHERE active = 1 ORDER BY project_allowed...
MySQL: How to turn one database to read-only?
I need to turn one database into read-only, so I can clone the database and make schema modifications on the clone before we switch the application to the new database. I know the way to turn the MySQL database server into read-only, but that will make it impossible to do the schema change on the new cloned database. I searched and didn...
Foreign Characters not showing up correctly in php
I am saving some foreign characters quite fine into my mysql database. This is the ones I am using right now for example: いち When I try to echo out that column from the database, it only shows up as '??' Why are there question marks showing up when I am trying to echo foreign characters? ...
Lost in dates and timezones
I'm working on an application that stores conferences with their start and end date. Up until now, I was developing in Belgium and my server is in France, so everything is in the same timezone, no problem. But today, I'm in San Francisco, my server is in France and I noticed I have a bug. I'm setting dates from a Flex client (ActionScri...
sql alias field
in SQL, supposes I need to refer to an aliased field in the having clause but the alias has quotes, how do I do that? select (select...) as '005' group by ... having '005'>0 ...
Uploading data to mysql database parallelly
Hi, I have a mysql database and a table of size 74 GB. I am currently using mysql command to load this data into mysql database. It took more than 10 hours and still running. Is there a way to load the data into the mysql database parallelly. One way would be to actually split the table data into multiple files and then call load on eac...
Limit results from joined table to one row
Here is a simplified table structure: TABLE products ( product_id INT (primary key, auto_increment), category_id INT, product_title VARCHAR, etc ); TABLE product_photos ( product_photo_id (primary key, auto_increment), product_id INT, photo_href VARCHAR, photo_order INT ); A product can have multiple photos, the first product...
CSV to MySQL conversion and import
Hi all, Im working on a large project, havent had to do what I need help with before. I have a csv file, it contains a large amount of data, namely all of the cities, towns, suburbs in Australia. I need to convert the csv file to sql for mysql, and then import it into the database. What would be the best way to achieve this? Thanks al...
Mysql: Unique Index = Performance Characteristics for large datasets?
Hello Experts, what is the performance characteristic for Unique Indexes in Mysql and Indexes in general in MySQl (like the Primary Key Index): Given i will insert or update a record in my databse: Will the speed of updating the record (=building/updating the indexes) be different if the table has 10 Thousand records compared to 100 Mi...