mort

Hi all Last week we launched our brand new site http://www.meloncreative.co.uk Wondered if you guys would take a look / let us know your thoughts Cheers

Nevermind, solved it using a mix of LEFT JOIN, INNER JOIN, and UNION

So I have written a query that will grab an order (this is for an ecommerce type site), and from that order id it will get all order items (ecom_order_items), print options (c_print_options) and images (images). The eoi_p_id is currently a foreign key from the images table. This works fine and the query is: SELECT eoi_parentid, eoi_p_id, eoi_po_id, eoi_quantity, i_id, i_parentid, po_name, po_price FROM ecom_order_items, images, c_print_options WHERE eoi_parentid = '1' AND i_id = eoi_p_id AND po_id = eoi_po_id; The above would grab all the stuff I need for order #1 Now to complicate things I added an extra table (ecom_products), which needs to act in a similar way to the images table. The eoi_p_id can also point at a foreign key in this table too. I have added an extra field 'eoi_type' which will either have the value 'image', or 'product'. Now items in the order could be made up of a mix of items from images or ecom_products. Whatever I try it either ends up with too many records, wont actually output any with eoi_type = 'product', and just generally wont work. Any ideas on how to achieve what I am after? Can provide SQL samples if needed? SELECT eoi_id, eoi_parentid, eoi_p_id, eoi_po_id, eoi_po_id_2, eoi_quantity, eoi_type, i_id, i_parentid, po_name, po_price, po_id, ep_id FROM ecom_order_items, images, c_print_options, ecom_products WHERE eoi_parentid = '9' AND i_id = eoi_p_id AND po_id = eoi_po_id The above outputs duplicate rows and doesnt work as expected. Am I going about this the wrong way? Should I have seperate foreign key fields for the eoi_p_id depending it its an image or a product? Should I be using JOINs? Here is a mysql explain of the tables in question ecom_products +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | ep_id | int( | NO | PRI | NULL | auto_increment | | ep_title | varchar(255) | NO | | NULL | | | ep_link | text | NO | | NULL | | | ep_desc | text | NO | | NULL | | | ep_imgdrop | text | NO | | NULL | | | ep_price | decimal(6,2) | NO | | NULL | | | ep_category | varchar(255) | NO | | NULL | | | ep_hide | tinyint(1) | NO | | 0 | | | ep_featured | tinyint(1) | NO | | 0 | | +-------------+--------------+------+-----+---------+----------------+ ecom_order_items +--------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+----------------+ | eoi_id | int( | NO | PRI | NULL | auto_increment | | eoi_parentid | int( | NO | | NULL | | | eoi_type | varchar(32) | NO | | NULL | | | eoi_p_id | int( | NO | | NULL | | | eoi_po_id | int( | NO | | NULL | | | eoi_quantity | int(4) | NO | | NULL | | +--------------+-------------+------+-----+---------+----------------+ c_print_options +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | po_id | int( | NO | PRI | NULL | auto_increment | | po_name | varchar(255) | NO | | NULL | | | po_price | decimal(6,2) | NO | | NULL | | +------------+--------------+------+-----+---------+----------------+ images +--------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+----------------+ | i_id | int( | NO | PRI | NULL | auto_increment | | i_filename | varchar(255) | NO | | NULL | | | i_data | longtext | NO | | NULL | | | i_parentid | int( | NO | | NULL | | +--------------+--------------+------+-----+---------+----------------+

Sorry I forgot to say, that only marks it as a duplicate if something has changed. I need it to be marked as a duplicate even if the row is identicle. Actually just thought, this works if I update a field such as a time stamp nice one!!

So I have a script that imports CSV's for contact lists into a table ( http://www.phpfreaks.com/forums/php-coding-help/large-csv-import/ ) and to make it as efficient as possible it detects duplicates purely in SQL rather than relying on PHP and bloating the code. The script can be set to either overwrite or ignore any duplicates, and I need to be able to count how many duplicates it found. Depending on which option is set for how to handle duplicates, the query looks like the follow. Note: These are bulk inserts where multiple rows are inserted in a single query. This example shows 5 CSV rows in the batch query, but this number is much higher on the live script INSERT IGNORE INTO contacts (site_id, c_registered, c_domain, c_subscribed, c_name, c_email) VALUES ('10', NOW(), 'domain.co.uk', '1', 'Chris', 'chris@domain.co.uk'), ('10', NOW(), 'domain.co.uk', '1', 'Cyn', 'cyn@domain.co.uk'), ('10', NOW(), 'domain.co.uk', '1', 'Chloe', 'chloe@domain.co.uk'), ('10', NOW(), 'domain.co.uk', '1', 'Jennie', 'jennie@domain.co.uk'), ('10', NOW(), 'domain.co.uk', '1', 'Steve', 'steve@domain.co.uk'); INSERT INTO contacts (site_id, c_registered, c_domain, c_subscribed, c_name, c_email) VALUES ('10', NOW(), 'domain.co.uk', '1', 'Chris', 'chris@domain.co.uk'), ('10', NOW(), 'domain.co.uk', '1', 'Cyn', 'cyn@domain.co.uk'), ('10', NOW(), 'domain.co.uk', '1', 'Chloe', 'chloe@domain.co.uk'), ('10', NOW(), 'domain.co.uk', '1', 'Jennie', 'jennie@domain.co.uk'), ('10', NOW(), 'domain.co.uk', '1', 'Steve', 'steve@domain.co.uk') ON DUPLICATE KEY UPDATE c_name = VALUES(c_name); My problem is that I need somehow to be able to detect accurately how many duplicates there were mixed in with rows that weren't. Some people have suggested mysql_errno and mysql_affected_rows but these dont seem to work for the way I have implemented things Any ideas?

Thanks for the tip PFMaBiSmAd although I dont think this will work in my case, as the query is a batch insert dealing with multiple records in one query.

only question now is, how can I count how many duplicates were found?

Nice one That should do the trick. Lemme have a play

I was actually just looking at this, only downsides I can see is that my email field isnt a primary key, the table is used by multiple sites so the emails can technically be duplicated, but only 1 per site_id

pretty vague, depends what the script should do. obviously scripts that run forever are bad due to execution and memory limits sounds like you want to run a script that will run a batch, and then set it to repeat using CRON jobs

Hi all So... I am creating an import script for putting contacts into a database. The script we had worked ok for 500kb / 20k row CSV files, but anything much bigger than that and it started to run into the max execution limit. Rather than alter this I wish to create something that will run in the background and work as efficiently as possible. So basically the CSV file is uploaded, then you choose if the duplicates should be ignored / overwritten, and you match up the fields in the CSV (by the first line being a field title row), to the fields in the database. The field for the email address is singled out as this is to be checked for duplicates that already exist in the system. It then saves these values, along with the filename, and puts it all into an import queue table, which is processed by a CRON job. Each batch of the CRON job will look in the queue, find the first import that is incomplete, then start work on that file from where it left off last. When the batch is complete it will update the row to give a pointer in the file for the next batch, and update how many contacts were imported / how many duplicates there were So far so good, but when checking for duplicity it is massively slowing down the script. I can run 1000 lines of the file in 0.04 seconds without checking, but with checking that increases to 14-15 seconds, and gets longer the more contacts are in the db. For every line it tries to import its doing a SELECT query on the contact table, and although I am not doing SELECT * its still adding up to a lot of DB activity. One thought was to load every email address in the contacts table into an array before hand, but this table could be massive so thats likely to be just as inefficient. Any ideas on optimising this process?

Hey all Had been writing my own image manipulation code for a while, and then discovered http://asido.info which works very nicely. However I wonder if anyone else has any image manipulation libraries they use and find particularly good. I am basically looking for something with the following points (in order of importance): - Good support - Lightweight footprint - Active project - PHP5 - Ability to use GD / imagemagick / others if needed Any ideas?

hey, thanks for the reply i already have an ACL in place, however its more the interaction with the database and how the system should handle keeping multiple versions of a post that i am interested in

Hey all I have been working on a custom CMS and have made a dynamic script to essentially generate the Add / Edit / Delete forms with configuration data I have provided eg. table, fields, field types, options, etc I want to build in some kind of approval / moderation system as well that can be turned on or off for the various different forms I am making. I have seen some other posts on here about similar systems but none that go into as much detail as I want to achieve, and I could do with some other opinions on the planning stage. Obviously I could make a basic one where the 'post' has a separate db field called 'approved' which is either 0 or 1, and a function to either approve it or not. This is about as basic as it gets but doesn't really allow for anymore complex interactions such as multiple users, users with different permissions, the ability to edit live data and for it to create a copy with those changes in etc. My main goal is to have an admin level, and a user level. The Admin can add / edit / delete live to the database, and would act as most CMS systems do already. The user is where things get a bit more complicated. They need to be able to add content, and for that content to not be displayed until approved by an admin (such as the basic functionality explained above). But also to be able to edit / delete live content and for that to not actually reflect on the live site until those actions have been approved as well. This means I will have to have some form of duplicated data, and somewhere to keep track of whats awaiting approval, old, and live. I am thinking initially to keep all the approval data in a separate table, as it will need to be applied to potentially multiple other tables. This would act as an index for the other tables to mark row ID's status. I guess first off is anyone aware of any articles on such a system, and secondly does anyone know of any better / more efficient way to achieve this? Cheers

something like this is probably what you need LightCMS - www.speaklight.com