mysql

Howto import xls/csv file with unicode charset into php/mysql?

Hi I want to give the user the ability to import a csv file into my php/mysql system, but ran into some problems with encoding when the language is russian which excel only can store in UTF-16 tab-coded tab files. Right now my database is in latin1, but I will change that to utf-8 as described in question "a-script-to-change-all-tables...

Building a RSS feed with links embedded

Edit: I have this line: $comments = "http://www.kiubbo.com/index.php?action=comments&amp;param=".$articles[$index]-&gt;getId(); And if I put it here it works out in the title tag, but not on the link or guid tags. fwrite ($fp, "<item>\n"); fwrite ($fp, " <title>$title</title>\n"); fwrite ($fp, " <link>$comments</link>\n"); fwrite ($...

How can I count the non-unique combinations of values in MySQL?

I have a table with some legacy data that I suspect may be a little messed up. It is a many-to-many join table. LIST_MEMBERSHIPS ---------------- list_id address_id I'd like to run a query that will count the occurrences of each list_id-address_id pair and show the occurrence count for each from highest to lowest number of occurrences...

Problem with SQL statement using PHP

I'm currently trying to pull data from MYSQL using PHP and I keep getting the following error: "Could not retrieve records: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%PC4198% OR oem LIKE %Fluke%' at line 1 " My SQL statement is as follows: $sq...

MYSQL SUM by QTY's And Values

Hi All I would like to know if its possible to get 2 sums from one query using the table values and then add them togther. Here is some simple table & data which might help. CREATE TABLE `cartcontents` ( `id` int(11) NOT NULL auto_increment, `code` varchar(40) NOT NULL, `qty` int(10) NOT NULL, `price` decimal(30,2) NOT NUL...

display mysql results on same page

Ok, so I'm used to working with C# and ASP.NET which automatically allows one to display database results immediately on the same page from which they were requested. I am, however, now using PHP which I haven't coded in in years. I have the results popping up in a separate page because the form tag's 'action' option is set to that spec...

Is there an equivalent to PHP's `date_default_timezone_set()` that works in MySQL?

This works great in PHP date_default_timezone_set('my/timezone'); $actualDate = date('Y-m-d'); Now when doing queries, I like to do INSERT INTO articles (title, insert_date) VALUES ('My Title', now()) The problem with this is the now() in the MySQL is different to what it would be had it been calculated in PHP (and therefore again...

compare results with another table in PHP/MySQL

I have 2 tables... Table1: ID, Name, Country Table2: ID, accountID, parent table1.id = table2.acountID My script does a search for all records with a particular parent. I want to compare those results with table1 and return all entries that wern't in the search. eg. Table1: 1, Bill, AU 2, Charles, US 3, Clare, CA Table2: 1, 1...

How to ensure that only table row will be created with a particular state ?

I have a domain object that looks something like this: class UserStuff { String userid; boolean primordial; } In the table, primordial is a TINYINT. A User can have possibly many UserStuff. What I want to ensure is that only one row (the first row created) will have primordial == 1. All subsequent rows will have primordial ==...

array in MySQL

...

SQL: Statement to show 1 child record per parent in a One-to-Many relationship

I have 2 tables, a product table and an image table. Products can have more than 1 image per record. I need an SQL statement that will list 1 image per product rather than all images per product. A scaled down example of my current effort is: SELECT Product.RefCode, Img.ImgPath FROM Product INNER JOIN ( SELECT ProductImg.ImgPath, ...

Newbie Question-- calling multiple .sql scripts into PHP file

I have a PHP file that executes multiple sql scripts. I find that the first two scripts execute, but the last two never seem to complete. I tested the last two scripts individually in their own PHP files and the scripts worked, so I'm wondering if it's because of the number of exec() calls I'm making in my PHP script. Any suggestions? He...

Want to select records due to their status in the table using mysql

good morning guys, i have a problem of selecting all records which are inactive or active in a database, but if i select all estates and all status it returns all the estates and their different status, active or inactive, but if i select all estates and active status it returns all the estates and display that they are all active, same ...

MySQL: duplicate entry error with SELECT...INSERT INTO with UNIQUE constraint

I've got the following table: CREATE TABLE `products_quantity` ( `id` int(11) NOT NULL auto_increment, `product_id` varchar(100) NOT NULL, `stock_id` int(11) NOT NULL, `quantity` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `product_id` (`product_id`,`stock_id`), KEY `products_quantity_product_id` (`product_id`), KE...

SQL magic - query shouldn't take 15 hours, but it does

Ok, so i have one really monstrous MySQL table (900k records, 180 MB total), and i want to extract from subgroups records with higher date_updated and calculate weighted average in each group. The calculation runs for ~15 hours, and i have a strong feeling i'm doing it wrong. First, monstrous table layout: category element_id date_upd...

php/MySQL insert row then get 'id'

the 'id' field of my table auto increases when I insert a row, I want to insert a row and then get that ID. I would do it just as I said it but I'm wondering if there's a way I can do it without worrying about the time between inserting the row and getting the id. I know I can query the database for the row that matches the information...

problem maintiaing form state with mysql and php

I have a page which should show a form with checkboxes, post back if it is checked or not, store the result in the database, and then on next loading of the page show the checkbox as checked, and update it as unchecked as necessary. I understand my solution having the floating "checked" is not ideal, but it works fine, and I want to sti...

Random Behaviour of Load_File and Extract Function of MSQL 5.1

Hi Guys. I am Using Mysql 5.1 as a datastore. Here, I am having a lot of Data, So I have created a big XML File(max upto 1-GB Size), and I have Created a Procedure for Doing this, I m using few functios i.e., Declare XML BIGTEXT ; SET xml = LOAD_FILE(file_name); SET nrows = ExtractValue(xml, 'count(/.../row)') and same way ncols f...

MySql FLOAT datatype and problems with more then 7 digit scale

We are using MySql 5.0 on Ubuntu 9.04. The full version is: 5.0.75-0ubuntu10 I created a test database. and a test table in it. I see the following output from an insert statement: mysql> CREATE TABLE test (floaty FLOAT(8,2)) engine=InnoDb; Query OK, 0 rows affected (0.02 sec) mysql> insert into test value(858147.11); Query OK, 1 row ...

mysql data into Dojo datagrid table

Greetings all. I have a php script calling MYSQL and displaying data in a table. It is rather ugly and I would rather have it displayed in a Dojo style table/datagrid. Can anyone point me in the right direction? thanks! ...